Sql Backing up Tables: run

Showing posts with label run. Show all posts

Sunday, March 25, 2012

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.
> ======================================================>
>
>
>

Backup Error

We have been using the Domain administrator's account to
run the backup on our server. This morning the backup
failed on one of our SQL server's saying it was Unable to
determine if the owner of the job has server access. We
have another SQL server using the same account which
worked fine. Why would this account suddenly not work for
the backup'Are you using a 3rd party tool to backup the database such as NTBackup or
Arcserve or using SQLAgent to schedule a BACKUP DATABASE command
--
HTH
Ryan Waight, MCDBA, MCSE
"Me" <anonymous@.discussions.microsoft.com> wrote in message
news:063001c393f6$67d954c0$a001280a@.phx.gbl...
> We have been using the Domain administrator's account to
> run the backup on our server. This morning the backup
> failed on one of our SQL server's saying it was Unable to
> determine if the owner of the job has server access. We
> have another SQL server using the same account which
> worked fine. Why would this account suddenly not work for
> the backup'|||You could try setting the job owner to "sa". This has helped me a couple of times.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Me" <anonymous@.discussions.microsoft.com> wrote in message
news:063001c393f6$67d954c0$a001280a@.phx.gbl...
> We have been using the Domain administrator's account to
> run the backup on our server. This morning the backup
> failed on one of our SQL server's saying it was Unable to
> determine if the owner of the job has server access. We
> have another SQL server using the same account which
> worked fine. Why would this account suddenly not work for
> the backup'|||And the article to accompany Tibor's answer can be found here
PRB: 8198 Error Message Returned from Job Owned by Windows NT Authenticated
User
PSS ID Number: 241643
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ukioT4IlDHA.1284@.TK2MSFTNGP09.phx.gbl...
> You could try setting the job owner to "sa". This has helped me a couple
of times.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Me" <anonymous@.discussions.microsoft.com> wrote in message
> news:063001c393f6$67d954c0$a001280a@.phx.gbl...
> > We have been using the Domain administrator's account to
> > run the backup on our server. This morning the backup
> > failed on one of our SQL server's saying it was Unable to
> > determine if the owner of the job has server access. We
> > have another SQL server using the same account which
> > worked fine. Why would this account suddenly not work for
> > the backup'
>sql

Backup Error

Each time I run a backup the following error appears in
SQL Server's error log: Error: 4035, Severity: 10, State: 1
Can anyone help in explaining why I keep getting the above
error? Your help will be greatly appreciated.
Hi,
Can you run the backup command from query analyzer an check the status of
backup
Backup database <dbname> to disk='c:\backup\dbname.bak' with init
Which version of SQL Server you are running?
Thanks
Hari
MCDBA
"y_shoge@.yahoo.co.uk" <anonymous@.discussions.microsoft.com> wrote in message
news:24d5401c4601f$c79722c0$a301280a@.phx.gbl...
> Each time I run a backup the following error appears in
> SQL Server's error log: Error: 4035, Severity: 10, State: 1
> Can anyone help in explaining why I keep getting the above
> error? Your help will be greatly appreciated.
|||Hi,
I have run the backup command from query analyser and
still getting the same error.
I am running SQL Server 2000 Enterprise SP3.
Thanks
>--Original Message--
>Hi,
>Can you run the backup command from query analyzer an
check the status of
>backup
>Backup database <dbname> to disk='c:\backup\dbname.bak'
with init
>Which version of SQL Server you are running?
>
>--
>Thanks
>Hari
>MCDBA
>"y_shoge@.yahoo.co.uk"
<anonymous@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
>news:24d5401c4601f$c79722c0$a301280a@.phx.gbl...
State: 1[vbcol=seagreen]
above
>
>.
>
|||Hi,
I feel this is just a informational message.
Can you validate the backup file by restoring it to a diffrent database.
After that run a DBCC CHECKDB on that
new database and confirm things are fine.
Thanks
Hari
MCDBA
"y_shoge@.yahoo.co.uk" <anonymous@.discussions.microsoft.com> wrote in message
news:251cb01c46024$c778ac00$a401280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> I have run the backup command from query analyser and
> still getting the same error.
> I am running SQL Server 2000 Enterprise SP3.
> Thanks
> check the status of
> with init
> <anonymous@.discussions.microsoft.com> wrote in message
> State: 1
> above
|||Thanks Hari,
I have restored the backup file to another database and
ran the DBCC Checkdb against the restored database, it
returned no errors. Everything appears to be fine. I
guess I will have to ignore the error message for now.
Thank you very much for your help.
>--Original Message--
>Hi,
>I feel this is just a informational message.
>Can you validate the backup file by restoring it to a
diffrent database.
>After that run a DBCC CHECKDB on that
>new database and confirm things are fine.
>--
>Thanks
>Hari
>MCDBA
>"y_shoge@.yahoo.co.uk"
<anonymous@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
>news:251cb01c46024$c778ac00$a401280a@.phx.gbl...
in
>
>.
>

Thursday, March 22, 2012

Backup error

Backup Error

Each time I run a backup the following error appears in
SQL Server's error log: Error: 4035, Severity: 10, State: 1
Can anyone help in explaining why I keep getting the above
error? Your help will be greatly appreciated.Hi,
Can you run the backup command from query analyzer an check the status of
backup
Backup database <dbname> to disk='c:\backup\dbname.bak' with init
Which version of SQL Server you are running?
Thanks
Hari
MCDBA
"y_shoge@.yahoo.co.uk" <anonymous@.discussions.microsoft.com> wrote in message
news:24d5401c4601f$c79722c0$a301280a@.phx
.gbl...
> Each time I run a backup the following error appears in
> SQL Server's error log: Error: 4035, Severity: 10, State: 1
> Can anyone help in explaining why I keep getting the above
> error? Your help will be greatly appreciated.|||Hi,
I have run the backup command from query analyser and
still getting the same error.
I am running SQL Server 2000 Enterprise SP3.
Thanks
>--Original Message--
>Hi,
>Can you run the backup command from query analyzer an
check the status of
>backup
>Backup database <dbname> to disk='c:\backup\dbname.bak'
with init
>Which version of SQL Server you are running?
>
>--
>Thanks
>Hari
>MCDBA
>"y_shoge@.yahoo.co.uk"
<anonymous@.discussions.microsoft.com> wrote in message
> news:24d5401c4601f$c79722c0$a301280a@.phx
.gbl...
State: 1[vbcol=seagreen]
above[vbcol=seagreen]
>
>.
>|||Hi,
I feel this is just a informational message.
Can you validate the backup file by restoring it to a diffrent database.
After that run a DBCC CHECKDB on that
new database and confirm things are fine.
--
Thanks
Hari
MCDBA
"y_shoge@.yahoo.co.uk" <anonymous@.discussions.microsoft.com> wrote in message
news:251cb01c46024$c778ac00$a401280a@.phx
.gbl...[vbcol=seagreen]
> Hi,
> I have run the backup command from query analyser and
> still getting the same error.
> I am running SQL Server 2000 Enterprise SP3.
> Thanks
> check the status of
> with init
> <anonymous@.discussions.microsoft.com> wrote in message
> State: 1
> above|||Thanks Hari,
I have restored the backup file to another database and
ran the DBCC Checkdb against the restored database, it
returned no errors. Everything appears to be fine. I
guess I will have to ignore the error message for now.
Thank you very much for your help.
>--Original Message--
>Hi,
>I feel this is just a informational message.
>Can you validate the backup file by restoring it to a
diffrent database.
>After that run a DBCC CHECKDB on that
>new database and confirm things are fine.
>--
>Thanks
>Hari
>MCDBA
>"y_shoge@.yahoo.co.uk"
<anonymous@.discussions.microsoft.com> wrote in message
> news:251cb01c46024$c778ac00$a401280a@.phx
.gbl...
in[vbcol=seagreen]
>
>.
>

Backup error

Tuesday, March 20, 2012

Backup Device size

I am looking for a way to retrieve the size of my backup devices. When I run sp_helpdevice, the backup devices with a cntrltype = 2 (which happen to be the only ones I am interested in), all return a size = 0. Any idea how to get the size using T-SQL?
Message posted via http://www.sqlmonster.com
How about the file_size in the BackupFile system table?
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:a46bfcae27494128849870935f2de9b3@.SQLMonster.c om...
>I am looking for a way to retrieve the size of my backup devices. When I
>run sp_helpdevice, the backup devices with a cntrltype = 2 (which happen to
>be the only ones I am interested in), all return a size = 0. Any idea how
>to get the size using T-SQL?
> --
> Message posted via http://www.sqlmonster.com
|||That does not provide the size of the backup files (as in *.bak), but instead provides the files that are being backed up. I am looking for the size of the *.bak files.
Message posted via http://www.sqlmonster.com
|||OK then how about Backup_Size in sysbackuphistory?
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:5d56319a8f704287b2cbe0cc361395fc@.SQLMonster.c om...
> That does not provide the size of the backup files (as in *.bak), but
> instead provides the files that are being backed up. I am looking for the
> size of the *.bak files.
> --
> Message posted via http://www.sqlmonster.com
|||I am running SQL 2000 and do not seem to be able to find sysbackuphistory.
Message posted via http://www.sqlmonster.com
|||I am sorry I meant the BackupSet system table.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:13e45dbcb76d4537a615b957827d5d8a@.SQLMonster.c om...
>I am running SQL 2000 and do not seem to be able to find sysbackuphistory.
> --
> Message posted via http://www.sqlmonster.com
sql

Backup Device size

I am looking for a way to retrieve the size of my backup devices. When I run
sp_helpdevice, the backup devices with a cntrltype = 2 (which happen to be
the only ones I am interested in), all return a size = 0. Any idea how to ge
t the size using T-SQL?
Message posted via http://www.droptable.comHow about the file_size in the BackupFile system table?
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:a46bfcae27494128849870935f2de9b3@.SQ
droptable.com...
>I am looking for a way to retrieve the size of my backup devices. When I
>run sp_helpdevice, the backup devices with a cntrltype = 2 (which happen to
>be the only ones I am interested in), all return a size = 0. Any idea how
>to get the size using T-SQL?
> --
> Message posted via http://www.droptable.com|||That does not provide the size of the backup files (as in *.bak), but instea
d provides the files that are being backed up. I am looking for the size of
the *.bak files.
Message posted via http://www.droptable.com|||OK then how about Backup_Size in sysbackuphistory?
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:5d56319a8f704287b2cbe0cc361395fc@.SQ
droptable.com...
> That does not provide the size of the backup files (as in *.bak), but
> instead provides the files that are being backed up. I am looking for the
> size of the *.bak files.
> --
> Message posted via http://www.droptable.com|||I am running SQL 2000 and do not seem to be able to find sysbackuphistory.
Message posted via http://www.droptable.com|||I am sorry I meant the BackupSet system table.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:13e45dbcb76d4537a615b957827d5d8a@.SQ
droptable.com...
>I am running SQL 2000 and do not seem to be able to find sysbackuphistory.
> --
> Message posted via http://www.droptable.com

Sunday, March 25, 2012

Backup error

Backup Error

Backup Error

Thursday, March 22, 2012

Backup error

Backup Error

Backup error

Tuesday, March 20, 2012

Backup Device size

Backup Device size

Sql Backing up Tables

Blog Archive

About Me