|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Backup errorthe subsequent error: BACKUP DATABASE [Companies DB] TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\Companies DB.bak' Msg 3271, Level 16, State 1, Line 1 A nonrecoverable I/O error occurred on file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CompaniesDBSQL.mdf:" 1450(Insufficient system resources exist to complete the requested service.). Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally. I'm running Windows XP Professional SP2 with the lastest updates, SQL Server 2005 v. 9.00.3054.00 (which I believe is the latest version), a PC with dual-core extreme processor with 4GB of memory (although I understand not of it may be available to XP), and I have plenty of hard drive capacity available. The file I'm backing up is almost 2.5GB in size. The backup command worked this morning, although I have received this error a couple times in the past and shut down various programs and tried again and it worked. This time I can't seem to get it to work at all. I definitely need to backup this file! What do I need to do to get it to work? (Note: Curiously, the error command references subfolder MSSQL.1 even though I'm trying to back up to MSSQL.2). Also, why can't I just copy the file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CompaniesDBSQL.mdf to make my backup instead of running the backup command? I've tried this in the past but I couldn't restore it. Apparently some other files would have to be copies as well? -- Ed Ed,
I am not sure why you are getting the error (maybe the swap file isn't large enough) but one thing you can do until you get it resolved is to detach the database wither with SSMS or sp_detach_db. Then you can copy the .mdf & .ldf files (and any others if there are any) somewhere with windows explorer. Then you can attach the db back again and do the same with the detached copy in place of a restore if needed. -- Show quoteAndrew J. Kelly SQL MVP Solid Quality Mentors "Ed White" <ewhite@newsgroups.nospam> wrote in message news:E13A0680-D110-4EB3-8B96-0102120AF59A@microsoft.com... > When I run the following SQL, either from VB 2005 or directly in SSMS, I > get > the subsequent error: > > BACKUP DATABASE [Companies DB] TO DISK='C:\Program Files\Microsoft SQL > Server\MSSQL.2\MSSQL\Backup\Companies DB.bak' > > Msg 3271, Level 16, State 1, Line 1 > A nonrecoverable I/O error occurred on file "C:\Program Files\Microsoft > SQL > Server\MSSQL.1\MSSQL\Data\CompaniesDBSQL.mdf:" 1450(Insufficient system > resources exist to complete the requested service.). > Msg 3013, Level 16, State 1, Line 1 > BACKUP DATABASE is terminating abnormally. > > I'm running Windows XP Professional SP2 with the lastest updates, SQL > Server > 2005 v. 9.00.3054.00 (which I believe is the latest version), a PC with > dual-core extreme processor with 4GB of memory (although I understand not > of > it may be available to XP), and I have plenty of hard drive capacity > available. The file I'm backing up is almost 2.5GB in size. The backup > command worked this morning, although I have received this error a couple > times in the past and shut down various programs and tried again and it > worked. This time I can't seem to get it to work at all. I definitely > need > to backup this file! What do I need to do to get it to work? (Note: > Curiously, the error command references subfolder MSSQL.1 even though I'm > trying to back up to MSSQL.2). > > Also, why can't I just copy the file > > C:\Program Files\Microsoft SQL > Server\MSSQL.1\MSSQL\Data\CompaniesDBSQL.mdf > > to make my backup instead of running the backup command? I've tried this > in > the past but I couldn't restore it. Apparently some other files would > have > to be copies as well? > -- > Ed I still occasionally get the error using the SQL, and the only way to avoid
it is to reboot and try again, which usually works. Couple of question about Detach and Attach: When I Detach and then re-Attach, after Attach a message appears: "The dependent aliases were dropped." What does this mean? What are "dependent aliases"? Also, which files in all do I need to copy to backup? Just the ...DBSQL.mdf and the ...DBSQL_log.ldf, or are there others? -- Show quoteEd "Andrew J. Kelly" wrote: > Ed, > > I am not sure why you are getting the error (maybe the swap file isn't large > enough) but one thing you can do until you get it resolved is to detach the > database wither with SSMS or sp_detach_db. Then you can copy the .mdf & .ldf > files (and any others if there are any) somewhere with windows explorer. > Then you can attach the db back again and do the same with the detached copy > in place of a restore if needed. > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > > "Ed White" <ewhite@newsgroups.nospam> wrote in message > news:E13A0680-D110-4EB3-8B96-0102120AF59A@microsoft.com... > > When I run the following SQL, either from VB 2005 or directly in SSMS, I > > get > > the subsequent error: > > > > BACKUP DATABASE [Companies DB] TO DISK='C:\Program Files\Microsoft SQL > > Server\MSSQL.2\MSSQL\Backup\Companies DB.bak' > > > > Msg 3271, Level 16, State 1, Line 1 > > A nonrecoverable I/O error occurred on file "C:\Program Files\Microsoft > > SQL > > Server\MSSQL.1\MSSQL\Data\CompaniesDBSQL.mdf:" 1450(Insufficient system > > resources exist to complete the requested service.). > > Msg 3013, Level 16, State 1, Line 1 > > BACKUP DATABASE is terminating abnormally. > > > > I'm running Windows XP Professional SP2 with the lastest updates, SQL > > Server > > 2005 v. 9.00.3054.00 (which I believe is the latest version), a PC with > > dual-core extreme processor with 4GB of memory (although I understand not > > of > > it may be available to XP), and I have plenty of hard drive capacity > > available. The file I'm backing up is almost 2.5GB in size. The backup > > command worked this morning, although I have received this error a couple > > times in the past and shut down various programs and tried again and it > > worked. This time I can't seem to get it to work at all. I definitely > > need > > to backup this file! What do I need to do to get it to work? (Note: > > Curiously, the error command references subfolder MSSQL.1 even though I'm > > trying to back up to MSSQL.2). > > > > Also, why can't I just copy the file > > > > C:\Program Files\Microsoft SQL > > Server\MSSQL.1\MSSQL\Data\CompaniesDBSQL.mdf > > > > to make my backup instead of running the backup command? I've tried this > > in > > the past but I couldn't restore it. Apparently some other files would > > have > > to be copies as well? > > -- > > Ed > > I am not sure what the "dependent aliases " means for sure but someone
probably setup a database alias that is no longer useful when you drop or detach the database. How many files depends on how the database was created or altered. By default there are only 2 files. xxx.mdf and xxx.ldf. But you can have secondary data files usually with the extension of .ndf and secondary log files with .ldf as well. If you run sp_helpdb it should show all the files. -- Show quoteAndrew J. Kelly SQL MVP Solid Quality Mentors "Ed White" <ewhite@newsgroups.nospam> wrote in message news:5D97496D-11BA-4ACC-BCA9-0A3101355C8A@microsoft.com... >I still occasionally get the error using the SQL, and the only way to avoid > it is to reboot and try again, which usually works. > > Couple of question about Detach and Attach: > When I Detach and then re-Attach, after Attach a message appears: > "The dependent aliases were dropped." > What does this mean? What are "dependent aliases"? > > Also, which files in all do I need to copy to backup? Just the > ...DBSQL.mdf > and the ...DBSQL_log.ldf, or are there others? > -- > Ed > > > "Andrew J. Kelly" wrote: > >> Ed, >> >> I am not sure why you are getting the error (maybe the swap file isn't >> large >> enough) but one thing you can do until you get it resolved is to detach >> the >> database wither with SSMS or sp_detach_db. Then you can copy the .mdf & >> .ldf >> files (and any others if there are any) somewhere with windows explorer. >> Then you can attach the db back again and do the same with the detached >> copy >> in place of a restore if needed. >> >> -- >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> >> "Ed White" <ewhite@newsgroups.nospam> wrote in message >> news:E13A0680-D110-4EB3-8B96-0102120AF59A@microsoft.com... >> > When I run the following SQL, either from VB 2005 or directly in SSMS, >> > I >> > get >> > the subsequent error: >> > >> > BACKUP DATABASE [Companies DB] TO DISK='C:\Program Files\Microsoft SQL >> > Server\MSSQL.2\MSSQL\Backup\Companies DB.bak' >> > >> > Msg 3271, Level 16, State 1, Line 1 >> > A nonrecoverable I/O error occurred on file "C:\Program Files\Microsoft >> > SQL >> > Server\MSSQL.1\MSSQL\Data\CompaniesDBSQL.mdf:" 1450(Insufficient system >> > resources exist to complete the requested service.). >> > Msg 3013, Level 16, State 1, Line 1 >> > BACKUP DATABASE is terminating abnormally. >> > >> > I'm running Windows XP Professional SP2 with the lastest updates, SQL >> > Server >> > 2005 v. 9.00.3054.00 (which I believe is the latest version), a PC with >> > dual-core extreme processor with 4GB of memory (although I understand >> > not >> > of >> > it may be available to XP), and I have plenty of hard drive capacity >> > available. The file I'm backing up is almost 2.5GB in size. The >> > backup >> > command worked this morning, although I have received this error a >> > couple >> > times in the past and shut down various programs and tried again and it >> > worked. This time I can't seem to get it to work at all. I definitely >> > need >> > to backup this file! What do I need to do to get it to work? (Note: >> > Curiously, the error command references subfolder MSSQL.1 even though >> > I'm >> > trying to back up to MSSQL.2). >> > >> > Also, why can't I just copy the file >> > >> > C:\Program Files\Microsoft SQL >> > Server\MSSQL.1\MSSQL\Data\CompaniesDBSQL.mdf >> > >> > to make my backup instead of running the backup command? I've tried >> > this >> > in >> > the past but I couldn't restore it. Apparently some other files would >> > have >> > to be copies as well? >> > -- >> > Ed >> >> "Dependent aliases" could also possible mean logins aliased to some user in the db. This has been
deprecated since 7.0, and the functionality might not even be in 2005. ....just a thought... -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:%23txZDRXMIHA.820@TK2MSFTNGP06.phx.gbl... >I am not sure what the "dependent aliases " means for sure but someone probably setup a database >alias that is no longer useful when you drop or detach the database. How many files depends on how >the database was created or altered. By default there are only 2 files. xxx.mdf and xxx.ldf. But >you can have secondary data files usually with the extension of .ndf and secondary log files with >.ldf as well. If you run sp_helpdb it should show all the files. > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > > "Ed White" <ewhite@newsgroups.nospam> wrote in message > news:5D97496D-11BA-4ACC-BCA9-0A3101355C8A@microsoft.com... >>I still occasionally get the error using the SQL, and the only way to avoid >> it is to reboot and try again, which usually works. >> >> Couple of question about Detach and Attach: >> When I Detach and then re-Attach, after Attach a message appears: >> "The dependent aliases were dropped." >> What does this mean? What are "dependent aliases"? >> >> Also, which files in all do I need to copy to backup? Just the ...DBSQL.mdf >> and the ...DBSQL_log.ldf, or are there others? >> -- >> Ed >> >> >> "Andrew J. Kelly" wrote: >> >>> Ed, >>> >>> I am not sure why you are getting the error (maybe the swap file isn't large >>> enough) but one thing you can do until you get it resolved is to detach the >>> database wither with SSMS or sp_detach_db. Then you can copy the .mdf & .ldf >>> files (and any others if there are any) somewhere with windows explorer. >>> Then you can attach the db back again and do the same with the detached copy >>> in place of a restore if needed. >>> >>> -- >>> Andrew J. Kelly SQL MVP >>> Solid Quality Mentors >>> >>> >>> "Ed White" <ewhite@newsgroups.nospam> wrote in message >>> news:E13A0680-D110-4EB3-8B96-0102120AF59A@microsoft.com... >>> > When I run the following SQL, either from VB 2005 or directly in SSMS, I >>> > get >>> > the subsequent error: >>> > >>> > BACKUP DATABASE [Companies DB] TO DISK='C:\Program Files\Microsoft SQL >>> > Server\MSSQL.2\MSSQL\Backup\Companies DB.bak' >>> > >>> > Msg 3271, Level 16, State 1, Line 1 >>> > A nonrecoverable I/O error occurred on file "C:\Program Files\Microsoft >>> > SQL >>> > Server\MSSQL.1\MSSQL\Data\CompaniesDBSQL.mdf:" 1450(Insufficient system >>> > resources exist to complete the requested service.). >>> > Msg 3013, Level 16, State 1, Line 1 >>> > BACKUP DATABASE is terminating abnormally. >>> > >>> > I'm running Windows XP Professional SP2 with the lastest updates, SQL >>> > Server >>> > 2005 v. 9.00.3054.00 (which I believe is the latest version), a PC with >>> > dual-core extreme processor with 4GB of memory (although I understand not >>> > of >>> > it may be available to XP), and I have plenty of hard drive capacity >>> > available. The file I'm backing up is almost 2.5GB in size. The backup >>> > command worked this morning, although I have received this error a couple >>> > times in the past and shut down various programs and tried again and it >>> > worked. This time I can't seem to get it to work at all. I definitely >>> > need >>> > to backup this file! What do I need to do to get it to work? (Note: >>> > Curiously, the error command references subfolder MSSQL.1 even though I'm >>> > trying to back up to MSSQL.2). >>> > >>> > Also, why can't I just copy the file >>> > >>> > C:\Program Files\Microsoft SQL >>> > Server\MSSQL.1\MSSQL\Data\CompaniesDBSQL.mdf >>> > >>> > to make my backup instead of running the backup command? I've tried this >>> > in >>> > the past but I couldn't restore it. Apparently some other files would >>> > have >>> > to be copies as well? >>> > -- >>> > Ed >>> >>> > Are all the files that I need to copy stored in the same folder, i.e.
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data There are also a MSSQL.2, .3, and .4 directories that contain some data. In the MSSLQ.1\MSSQL\Data folder, I only see the .mdf and _log.ldf files associated with my database. If there are additional files, would they be in the same folder or do I need to look elsewhere? -- Show quoteEd "Andrew J. Kelly" wrote: > I am not sure what the "dependent aliases " means for sure but someone > probably setup a database alias that is no longer useful when you drop or > detach the database. How many files depends on how the database was created > or altered. By default there are only 2 files. xxx.mdf and xxx.ldf. But you > can have secondary data files usually with the extension of .ndf and > secondary log files with .ldf as well. If you run sp_helpdb it should show > all the files. > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > > "Ed White" <ewhite@newsgroups.nospam> wrote in message > news:5D97496D-11BA-4ACC-BCA9-0A3101355C8A@microsoft.com... > >I still occasionally get the error using the SQL, and the only way to avoid > > it is to reboot and try again, which usually works. > > > > Couple of question about Detach and Attach: > > When I Detach and then re-Attach, after Attach a message appears: > > "The dependent aliases were dropped." > > What does this mean? What are "dependent aliases"? > > > > Also, which files in all do I need to copy to backup? Just the > > ...DBSQL.mdf > > and the ...DBSQL_log.ldf, or are there others? > > -- > > Ed > > > > > > "Andrew J. Kelly" wrote: > > > >> Ed, > >> > >> I am not sure why you are getting the error (maybe the swap file isn't > >> large > >> enough) but one thing you can do until you get it resolved is to detach > >> the > >> database wither with SSMS or sp_detach_db. Then you can copy the .mdf & > >> .ldf > >> files (and any others if there are any) somewhere with windows explorer. > >> Then you can attach the db back again and do the same with the detached > >> copy > >> in place of a restore if needed. > >> > >> -- > >> Andrew J. Kelly SQL MVP > >> Solid Quality Mentors > >> > >> > >> "Ed White" <ewhite@newsgroups.nospam> wrote in message > >> news:E13A0680-D110-4EB3-8B96-0102120AF59A@microsoft.com... > >> > When I run the following SQL, either from VB 2005 or directly in SSMS, > >> > I > >> > get > >> > the subsequent error: > >> > > >> > BACKUP DATABASE [Companies DB] TO DISK='C:\Program Files\Microsoft SQL > >> > Server\MSSQL.2\MSSQL\Backup\Companies DB.bak' > >> > > >> > Msg 3271, Level 16, State 1, Line 1 > >> > A nonrecoverable I/O error occurred on file "C:\Program Files\Microsoft > >> > SQL > >> > Server\MSSQL.1\MSSQL\Data\CompaniesDBSQL.mdf:" 1450(Insufficient system > >> > resources exist to complete the requested service.). > >> > Msg 3013, Level 16, State 1, Line 1 > >> > BACKUP DATABASE is terminating abnormally. > >> > > >> > I'm running Windows XP Professional SP2 with the lastest updates, SQL > >> > Server > >> > 2005 v. 9.00.3054.00 (which I believe is the latest version), a PC with > >> > dual-core extreme processor with 4GB of memory (although I understand > >> > not > >> > of > >> > it may be available to XP), and I have plenty of hard drive capacity > >> > available. The file I'm backing up is almost 2.5GB in size. The > >> > backup > >> > command worked this morning, although I have received this error a > >> > couple > >> > times in the past and shut down various programs and tried again and it > >> > worked. This time I can't seem to get it to work at all. I definitely > >> > need > >> > to backup this file! What do I need to do to get it to work? (Note: > >> > Curiously, the error command references subfolder MSSQL.1 even though > >> > I'm > >> > trying to back up to MSSQL.2). > >> > > >> > Also, why can't I just copy the file > >> > > >> > C:\Program Files\Microsoft SQL > >> > Server\MSSQL.1\MSSQL\Data\CompaniesDBSQL.mdf > >> > > >> > to make my backup instead of running the backup command? I've tried > >> > this > >> > in > >> > the past but I couldn't restore it. Apparently some other files would > >> > have > >> > to be copies as well? > >> > -- > >> > Ed > >> > >> > > That depends on where they were put when the db or files were created. By
default they will be there but they don't have to be. The other folders are for other instances or things like Reporting services or Integration services etc. If all you care about is that one db then you don't need anything in the other folders. But I can't say that for sure since I don't know what they were used for. -- Show quoteAndrew J. Kelly SQL MVP Solid Quality Mentors "Ed White" <ewhite@newsgroups.nospam> wrote in message news:5229C645-EF08-4777-8C12-F5D8E9F9E14D@microsoft.com... > Are all the files that I need to copy stored in the same folder, i.e. > C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data > > There are also a MSSQL.2, .3, and .4 directories that contain some data. > In > the MSSLQ.1\MSSQL\Data folder, I only see the .mdf and _log.ldf files > associated with my database. If there are additional files, would they be > in > the same folder or do I need to look elsewhere? > -- > Ed > > > "Andrew J. Kelly" wrote: > >> I am not sure what the "dependent aliases " means for sure but someone >> probably setup a database alias that is no longer useful when you drop or >> detach the database. How many files depends on how the database was >> created >> or altered. By default there are only 2 files. xxx.mdf and xxx.ldf. But >> you >> can have secondary data files usually with the extension of .ndf and >> secondary log files with .ldf as well. If you run sp_helpdb it should >> show >> all the files. >> >> -- >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> >> "Ed White" <ewhite@newsgroups.nospam> wrote in message >> news:5D97496D-11BA-4ACC-BCA9-0A3101355C8A@microsoft.com... >> >I still occasionally get the error using the SQL, and the only way to >> >avoid >> > it is to reboot and try again, which usually works. >> > >> > Couple of question about Detach and Attach: >> > When I Detach and then re-Attach, after Attach a message appears: >> > "The dependent aliases were dropped." >> > What does this mean? What are "dependent aliases"? >> > >> > Also, which files in all do I need to copy to backup? Just the >> > ...DBSQL.mdf >> > and the ...DBSQL_log.ldf, or are there others? >> > -- >> > Ed >> > >> > >> > "Andrew J. Kelly" wrote: >> > >> >> Ed, >> >> >> >> I am not sure why you are getting the error (maybe the swap file isn't >> >> large >> >> enough) but one thing you can do until you get it resolved is to >> >> detach >> >> the >> >> database wither with SSMS or sp_detach_db. Then you can copy the .mdf >> >> & >> >> .ldf >> >> files (and any others if there are any) somewhere with windows >> >> explorer. >> >> Then you can attach the db back again and do the same with the >> >> detached >> >> copy >> >> in place of a restore if needed. >> >> >> >> -- >> >> Andrew J. Kelly SQL MVP >> >> Solid Quality Mentors >> >> >> >> >> >> "Ed White" <ewhite@newsgroups.nospam> wrote in message >> >> news:E13A0680-D110-4EB3-8B96-0102120AF59A@microsoft.com... >> >> > When I run the following SQL, either from VB 2005 or directly in >> >> > SSMS, >> >> > I >> >> > get >> >> > the subsequent error: >> >> > >> >> > BACKUP DATABASE [Companies DB] TO DISK='C:\Program Files\Microsoft >> >> > SQL >> >> > Server\MSSQL.2\MSSQL\Backup\Companies DB.bak' >> >> > >> >> > Msg 3271, Level 16, State 1, Line 1 >> >> > A nonrecoverable I/O error occurred on file "C:\Program >> >> > Files\Microsoft >> >> > SQL >> >> > Server\MSSQL.1\MSSQL\Data\CompaniesDBSQL.mdf:" 1450(Insufficient >> >> > system >> >> > resources exist to complete the requested service.). >> >> > Msg 3013, Level 16, State 1, Line 1 >> >> > BACKUP DATABASE is terminating abnormally. >> >> > >> >> > I'm running Windows XP Professional SP2 with the lastest updates, >> >> > SQL >> >> > Server >> >> > 2005 v. 9.00.3054.00 (which I believe is the latest version), a PC >> >> > with >> >> > dual-core extreme processor with 4GB of memory (although I >> >> > understand >> >> > not >> >> > of >> >> > it may be available to XP), and I have plenty of hard drive capacity >> >> > available. The file I'm backing up is almost 2.5GB in size. The >> >> > backup >> >> > command worked this morning, although I have received this error a >> >> > couple >> >> > times in the past and shut down various programs and tried again and >> >> > it >> >> > worked. This time I can't seem to get it to work at all. I >> >> > definitely >> >> > need >> >> > to backup this file! What do I need to do to get it to work? >> >> > (Note: >> >> > Curiously, the error command references subfolder MSSQL.1 even >> >> > though >> >> > I'm >> >> > trying to back up to MSSQL.2). >> >> > >> >> > Also, why can't I just copy the file >> >> > >> >> > C:\Program Files\Microsoft SQL >> >> > Server\MSSQL.1\MSSQL\Data\CompaniesDBSQL.mdf >> >> > >> >> > to make my backup instead of running the backup command? I've tried >> >> > this >> >> > in >> >> > the past but I couldn't restore it. Apparently some other files >> >> > would >> >> > have >> >> > to be copies as well? >> >> > -- >> >> > Ed >> >> >> >> >> >> Hi Ed,
Normally the error 3271 often indicates that the backup medium is full. You may refer to: MSSQLSERVER_3271 http://msdn2.microsoft.com/en-us/library/aa337272.aspx However I noticed that you said that there were enough disk space on your computer and your SQL Server 2005 was SP2. In this case, I recommend that you first run DBCC CHECKDB/DBCC CHECKALLOC to check if there are any errors in your database. If there are no errors, this pushes me think that it might be related to your hard drive, and I would like to check with you whether or not you can reproduce this issue on your other machines? You may copy your .mdf file to a test server, attach the file to the SQL Server 2005 instance on the test server, and then run BACKUP DATABASE to see if this issue persists. I recommend that you try the above methods and let us know the result. Thanks for your cooperation and have a nice day! Best regards, Charles Wang Microsoft Online Community Support ====================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== My hard drive partition has 172GB, with only 64GB used. My DB uses about
2.5GB, so there's way more than enough space on the HD. I'll try running the program on another PC to see if the problem recurs. The problem is intermittent, although it tends to occur the more often I run my program which accesses the DB. I've had the problem again a couple times recently, and I checked the Task Manager after each occurance. I found that my Available Memory is unusally low (only about 140GB), and when I sorted the Processes by Memory Usage, I found that sqlserver.exe is using 1,584,920K of memory (I have almost 3GB total--I'm running XP Professional). It continues to hog the memory even after I shut down both SSMS and VS (the only programs using SQL Server on my PC). Last night, after closing SSMS and VS, I re-opened SSMS, and Restarted SQL Server. The memory usage went back to normal, and the backup worked. This morning, however, while restarting SQL Server did cause memory usage to go back to normal, I still can't backup. I can think of only two reasons why this problem my have started to occur (the backup command has worked flawlessly on my DB for over a year). First, my DB grows in size continuously, so it just may have gotten so big that the problem has started. Second, I recently added more memory to my PC. I used to have 2GB, but I upgraded to 4GB. I'm using Windows XP Professional. In order to get the PC to recognize the extra memory, my computer guy added "/usepmtimer /3GB /userva=3072" to the boot.ini file. I don't know if this could be contributing to the problem. -- Show quoteEd "Charles Wang[MSFT]" wrote: > Hi Ed, > Normally the error 3271 often indicates that the backup medium is full. You > may refer to: > MSSQLSERVER_3271 > http://msdn2.microsoft.com/en-us/library/aa337272.aspx > > However I noticed that you said that there were enough disk space on your > computer and your SQL Server 2005 was SP2. In this case, I recommend that > you first run DBCC CHECKDB/DBCC CHECKALLOC to check if there are any errors > in your database. If there are no errors, this pushes me think that it > might be related to your hard drive, and I would like to check with you > whether or not you can reproduce this issue on your other machines? You may > copy your .mdf file to a test server, attach the file to the SQL Server > 2005 instance on the test server, and then run BACKUP DATABASE to see if > this issue persists. > > I recommend that you try the above methods and let us know the result. > Thanks for your cooperation and have a nice day! > > Best regards, > Charles Wang > Microsoft Online Community Support > > ====================================================== > When responding to posts, please "Reply to Group" via > your newsreader so that others may learn and benefit > from this issue. > ====================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. > ====================================================== > > > HELP! I'm in real trouble now. I changed the default DB to my own DB from
master, and then Detached the DB. Now, when I pull up SSMS and try to do anything, it says "Cannot open user default database. Login failed." The default DB is my own, and it is Detached. I try using sp_defaultdb to change the default DB, but it keeps saying I do not have a vaild login or do not have permission. I use local system as my login, and I've tried multple variations on that for the login and keep getting the same message. I'm now locked out of my DB. How do I get back in?? !!! -- Show quoteEd "Ed White" wrote: > My hard drive partition has 172GB, with only 64GB used. My DB uses about > 2.5GB, so there's way more than enough space on the HD. I'll try running the > program on another PC to see if the problem recurs. The problem is > intermittent, although it tends to occur the more often I run my program > which accesses the DB. > > I've had the problem again a couple times recently, and I checked the Task > Manager after each occurance. I found that my Available Memory is unusally > low (only about 140GB), and when I sorted the Processes by Memory Usage, I > found that sqlserver.exe is using 1,584,920K of memory (I have almost 3GB > total--I'm running XP Professional). It continues to hog the memory even > after I shut down both SSMS and VS (the only programs using SQL Server on my > PC). Last night, after closing SSMS and VS, I re-opened SSMS, and Restarted > SQL Server. The memory usage went back to normal, and the backup worked. > This morning, however, while restarting SQL Server did cause memory usage to > go back to normal, I still can't backup. > > I can think of only two reasons why this problem my have started to occur > (the backup command has worked flawlessly on my DB for over a year). First, > my DB grows in size continuously, so it just may have gotten so big that the > problem has started. Second, I recently added more memory to my PC. I used > to have 2GB, but I upgraded to 4GB. I'm using Windows XP Professional. In > order to get the PC to recognize the extra memory, my computer guy added > "/usepmtimer /3GB /userva=3072" to the boot.ini file. I don't know if this > could be contributing to the problem. > -- > Ed > > > "Charles Wang[MSFT]" wrote: > > > Hi Ed, > > Normally the error 3271 often indicates that the backup medium is full. You > > may refer to: > > MSSQLSERVER_3271 > > http://msdn2.microsoft.com/en-us/library/aa337272.aspx > > > > However I noticed that you said that there were enough disk space on your > > computer and your SQL Server 2005 was SP2. In this case, I recommend that > > you first run DBCC CHECKDB/DBCC CHECKALLOC to check if there are any errors > > in your database. If there are no errors, this pushes me think that it > > might be related to your hard drive, and I would like to check with you > > whether or not you can reproduce this issue on your other machines? You may > > copy your .mdf file to a test server, attach the file to the SQL Server > > 2005 instance on the test server, and then run BACKUP DATABASE to see if > > this issue persists. > > > > I recommend that you try the above methods and let us know the result. > > Thanks for your cooperation and have a nice day! > > > > Best regards, > > Charles Wang > > Microsoft Online Community Support > > > > ====================================================== > > When responding to posts, please "Reply to Group" via > > your newsreader so that others may learn and benefit > > from this issue. > > ====================================================== > > This posting is provided "AS IS" with no warranties, and confers no rights. > > ====================================================== > > > > > > I just figured out how to change the default DB. I looked under Security on
SSMS and found the login names. So if my default DB is my own instead of master, I would have to change the default DB back to master before I Detach. Anyway, the problem with the memory remains. -- Show quoteEd "Ed White" wrote: > HELP! I'm in real trouble now. I changed the default DB to my own DB from > master, and then Detached the DB. Now, when I pull up SSMS and try to do > anything, it says "Cannot open user default database. Login failed." The > default DB is my own, and it is Detached. I try using sp_defaultdb to change > the default DB, but it keeps saying I do not have a vaild login or do not > have permission. I use local system as my login, and I've tried multple > variations on that for the login and keep getting the same message. I'm now > locked out of my DB. How do I get back in?? !!! > -- > Ed > > > "Ed White" wrote: > > > My hard drive partition has 172GB, with only 64GB used. My DB uses about > > 2.5GB, so there's way more than enough space on the HD. I'll try running the > > program on another PC to see if the problem recurs. The problem is > > intermittent, although it tends to occur the more often I run my program > > which accesses the DB. > > > > I've had the problem again a couple times recently, and I checked the Task > > Manager after each occurance. I found that my Available Memory is unusally > > low (only about 140GB), and when I sorted the Processes by Memory Usage, I > > found that sqlserver.exe is using 1,584,920K of memory (I have almost 3GB > > total--I'm running XP Professional). It continues to hog the memory even > > after I shut down both SSMS and VS (the only programs using SQL Server on my > > PC). Last night, after closing SSMS and VS, I re-opened SSMS, and Restarted > > SQL Server. The memory usage went back to normal, and the backup worked. > > This morning, however, while restarting SQL Server did cause memory usage to > > go back to normal, I still can't backup. > > > > I can think of only two reasons why this problem my have started to occur > > (the backup command has worked flawlessly on my DB for over a year). First, > > my DB grows in size continuously, so it just may have gotten so big that the > > problem has started. Second, I recently added more memory to my PC. I used > > to have 2GB, but I upgraded to 4GB. I'm using Windows XP Professional. In > > order to get the PC to recognize the extra memory, my computer guy added > > "/usepmtimer /3GB /userva=3072" to the boot.ini file. I don't know if this > > could be contributing to the problem. > > -- > > Ed > > > > > > "Charles Wang[MSFT]" wrote: > > > > > Hi Ed, > > > Normally the error 3271 often indicates that the backup medium is full. You > > > may refer to: > > > MSSQLSERVER_3271 > > > http://msdn2.microsoft.com/en-us/library/aa337272.aspx > > > > > > However I noticed that you said that there were enough disk space on your > > > computer and your SQL Server 2005 was SP2. In this case, I recommend that > > > you first run DBCC CHECKDB/DBCC CHECKALLOC to check if there are any errors > > > in your database. If there are no errors, this pushes me think that it > > > might be related to your hard drive, and I would like to check with you > > > whether or not you can reproduce this issue on your other machines? You may > > > copy your .mdf file to a test server, attach the file to the SQL Server > > > 2005 instance on the test server, and then run BACKUP DATABASE to see if > > > this issue persists. > > > > > > I recommend that you try the above methods and let us know the result. > > > Thanks for your cooperation and have a nice day! > > > > > > Best regards, > > > Charles Wang > > > Microsoft Online Community Support > > > > > > ====================================================== > > > When responding to posts, please "Reply to Group" via > > > your newsreader so that others may learn and benefit > > > from this issue. > > > ====================================================== > > > This posting is provided "AS IS" with no warranties, and confers no rights. > > > ====================================================== > > > > > > > > > Ed,
SQL Server will not release memory while running unless the OS specifically asks for it. So the behavior you mention is expected. If you want to control how much memory SQL Server uses you can set the MAX Memory setting to xxxMB's. This is actually for the buffer pool and you can have up to 384MB's on top of that for other parts of SQL Server as well so keep that in mind. Try setting it to 1.0GB and see how that works out. Setting the /3GB is probably not a good thing in your case. Even though you have 4GB of memory installed chances are your laptop will only be able to use 3GB of it to begin with so you can starve the OS. I would take that back out along with the userva setting. -- Show quoteAndrew J. Kelly SQL MVP Solid Quality Mentors "Ed White" <ewhite@newsgroups.nospam> wrote in message news:98B4973C-E73A-4D2B-892F-D409781DF26D@microsoft.com... > My hard drive partition has 172GB, with only 64GB used. My DB uses about > 2.5GB, so there's way more than enough space on the HD. I'll try running > the > program on another PC to see if the problem recurs. The problem is > intermittent, although it tends to occur the more often I run my program > which accesses the DB. > > I've had the problem again a couple times recently, and I checked the Task > Manager after each occurance. I found that my Available Memory is > unusally > low (only about 140GB), and when I sorted the Processes by Memory Usage, I > found that sqlserver.exe is using 1,584,920K of memory (I have almost 3GB > total--I'm running XP Professional). It continues to hog the memory even > after I shut down both SSMS and VS (the only programs using SQL Server on > my > PC). Last night, after closing SSMS and VS, I re-opened SSMS, and > Restarted > SQL Server. The memory usage went back to normal, and the backup worked. > This morning, however, while restarting SQL Server did cause memory usage > to > go back to normal, I still can't backup. > > I can think of only two reasons why this problem my have started to occur > (the backup command has worked flawlessly on my DB for over a year). > First, > my DB grows in size continuously, so it just may have gotten so big that > the > problem has started. Second, I recently added more memory to my PC. I > used > to have 2GB, but I upgraded to 4GB. I'm using Windows XP Professional. > In > order to get the PC to recognize the extra memory, my computer guy added > "/usepmtimer /3GB /userva=3072" to the boot.ini file. I don't know if > this > could be contributing to the problem. > -- > Ed > > > "Charles Wang[MSFT]" wrote: > >> Hi Ed, >> Normally the error 3271 often indicates that the backup medium is full. >> You >> may refer to: >> MSSQLSERVER_3271 >> http://msdn2.microsoft.com/en-us/library/aa337272.aspx >> >> However I noticed that you said that there were enough disk space on your >> computer and your SQL Server 2005 was SP2. In this case, I recommend that >> you first run DBCC CHECKDB/DBCC CHECKALLOC to check if there are any >> errors >> in your database. If there are no errors, this pushes me think that it >> might be related to your hard drive, and I would like to check with you >> whether or not you can reproduce this issue on your other machines? You >> may >> copy your .mdf file to a test server, attach the file to the SQL Server >> 2005 instance on the test server, and then run BACKUP DATABASE to see if >> this issue persists. >> >> I recommend that you try the above methods and let us know the result. >> Thanks for your cooperation and have a nice day! >> >> Best regards, >> Charles Wang >> Microsoft Online Community Support >> >> ====================================================== >> When responding to posts, please "Reply to Group" via >> your newsreader so that others may learn and benefit >> from this issue. >> ====================================================== >> This posting is provided "AS IS" with no warranties, and confers no >> rights. >> ====================================================== >> >> >>  |
|||||||||||||||||||||||
Other interesting topics