Backup error

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
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 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...
>
|||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...[vbcol=seagreen]
>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:
|||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...
>
|||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.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Ed White" <ewhite@.newsgroups.nospam> wrote in message
news:5229C645-EF08-4777-8C12-F5D8E9F9E14D@.microsoft.com...[vbcol=seagreen]
> 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:
|||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.
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.
> ================================================== ====
>
>
|||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:
[vbcol=seagreen]
> 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:
|||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.
Ed
"Ed White" wrote:
[vbcol=seagreen]
> 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: