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?
--
EdEd,
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...
> > 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.
--
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
>>|||"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...
--
Tibor 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?
--
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
> >>
> >>
>|||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...
> 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.
--
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:
> 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.
--
Ed
"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.
--
Andrew 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.
>> ======================================================>>|||Hi Ed,
Thanks for your response.
Since /3GB switch had been enabled and your SQL Server just used 1.5GB
memory, your SQL Server was not in a heavy memory pressure status. Also as
you said, while restarting SQL Server did cause memory usage to go back to
normal, you still could not backup. So it is hard to get a conclusion that
this issue was memory related according these appearances.
If you are concerned with /3GB switch, you may remove it from boot.ini file
and then try using AWE to see if it helps:
Managing Memory for Large Databases
http://technet.microsoft.com/en-us/library/ms191481.aspx
Currently for further research, I recommend that you try my former
suggestions first and let us know the results. 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.
======================================================|||Hi Charles,
I took Andrew's advice and removed the /3GB and userva settings from the
boot.ini. After doing this, Task Manager still shows the same amount of
memory available (2.75GB). I did this a few days ago, and I have not had the
error message recur since. So far, this seems to have solved the problem.
If the problem reappears, I'll let you know, but I'm confident that, at least
for now, it appears to be fixed.
--
Ed
"Charles Wang[MSFT]" wrote:
> Hi Ed,
> Thanks for your response.
> Since /3GB switch had been enabled and your SQL Server just used 1.5GB
> memory, your SQL Server was not in a heavy memory pressure status. Also as
> you said, while restarting SQL Server did cause memory usage to go back to
> normal, you still could not backup. So it is hard to get a conclusion that
> this issue was memory related according these appearances.
> If you are concerned with /3GB switch, you may remove it from boot.ini file
> and then try using AWE to see if it helps:
> Managing Memory for Large Databases
> http://technet.microsoft.com/en-us/library/ms191481.aspx
> Currently for further research, I recommend that you try my former
> suggestions first and let us know the results. 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.
> ======================================================>
>
>
>