Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / DB Engine / SQL Server / March 2008

Tip: Looking for answers? Try searching our database.

Database backup issue - Space requirements

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jim Underwood - 29 Feb 2008 17:30 GMT
I am trying to figure out how disk space is used when overwriting backup
files.  Here is a description of our situation...

We have a 700+GB reporting database (SQL 2000) that we backup weekly to a 1
TB drive.  This has worked fine for quite a while but we have seen the
server crash twice in the last month while performing the backup.  The
backup is set to backup to 5 files which overwrite the previous weeks backup
(the old backup has already been backed up to tape at this point).   The
backups are run via a scheduled job, but when the server has crashed we see
no evidence that it ever ran in the job history.  The dba that set up the
job never set it up to go to a log file so we don't have that to look at
yet.  The only thing I do have are a great deal of messages in the SQL
Server log that occured while the last backup was running.   The following
messages began appearing a couple hours after the backup began and continued
until the server was rebooted.

SQL Server has encountered 3 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [V:\MSSQL\Data\tempdev_2_Data.NDF] in
database [tempdb] (2).  The OS file handle is 0x00000598.  The offset of the
latest long IO is: 0x000004cab00000
Time out occurred while waiting for buffer latch type 4,bp 0x129ffc0, page
3:2305080), stat 0x40d, object ID 2:1682105033:0, EC 0x2015A3F0 : 0,
waittime 300. Not continuing to wait.
Waiting for type 0x4, current count 0x100022, current owning EC 0x2015A3F0.

Just some additional info on what I mean by "the server crashed".  This
server is both a database server and a webserver which runs and admin
website used to monitor database extracts.  At some point the server stopped
responding to all forms of remote control, and the actual screen in our
datacenter went black.  No one could log onto the server or see anything.
Enterprise manager seemd to be connecting on, although it was extremely
slow, and the website was working as well.  When the 4 hour backup had run a
full 12 hours over the normal time we had the datacenter do a hard power
down and restart the server.  Everything came up fine.

Getting back to the backups, and my question...

I know when I run backups on large databases I will see the zero byte
file(s) created and the size doesn't appear to change until the backup is
complete.  I'm wondering if the data is somehow being cached or stored in
temporary files until all the data is available then it written all at once.
I can't imagine SQL Server is caching 750GB, and if it uses temporary files
on the disk I would think my backups should have been failing for months.
It was suggested by our server admins that it may be overwriting the
previous backups but not updating the file system tables to show this, which
means we would have corrupt backup files on the server.

For the time being I have the job set up to delete the previous backups
prior to beginning the new backup, which I hope alleviates the problem.
Regardless of whether this works or not, I want to understand what is
happening and what my disk space requirements are going forward.

Can anyone explain how the disk space would be utilized in this situation?

Thanks in advance.
- Jim
jason - 29 Feb 2008 19:35 GMT
What is this 1TB drive? Is it USB? The IO errors are because of a disk
bottleneck where the data resides but the lockup and black screen is another
issue. It sounds like a drive or hardware issue.

Signature

Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio

>I am trying to figure out how disk space is used when overwriting backup
>files.  Here is a description of our situation...
[quoted text clipped - 64 lines]
> Thanks in advance.
> - Jim
Jim Underwood - 29 Feb 2008 20:34 GMT
Thanks for the response.

The backup drive is a local raid5 drive atttached internally.  We did just
upgrade the firmware on wednesday to eliminate that as a potential cause of
the problem.  However, I'm actually more interested in how SQL Server
handles the space when backing up a database over existing backup files.
The hardware issues are somethign of a lesser priority for me, since we have
folks far better equipped than I to handle those issues.

Should I have double the space available to handle temporary files and the
actual backup files?  Does SQL Server overwrite the existing files but not
show the file changes in windows until it completes (I assume this would
make the files corrupt if the backup gets interrupted)?

> What is this 1TB drive? Is it USB? The IO errors are because of a disk
> bottleneck where the data resides but the lockup and black screen is
[quoted text clipped - 71 lines]
>> Thanks in advance.
>> - Jim
Russell Fields - 29 Feb 2008 20:42 GMT
Jim,  The backups do not overwrite existing files. - RLF

> Thanks for the response.
>
[quoted text clipped - 90 lines]
>>> Thanks in advance.
>>> - Jim
Jim Underwood - 29 Feb 2008 21:45 GMT
Russel, can you elaborate?  We specify the name of the backup files, so the
files are replaced by the new backup.  When the backup is complete the same
file names now contain the new backup and old backup is gone.

I should have noted the command we are using to perform the backup...

BACKUP DATABASE MyDB
TO DISK='O:\MSSQL\Backup\MyDB\MyDB_Backup-Set_01.BAK',
  DISK='O:\MSSQL\Backup\MyDB\MyDB_Backup-Set_02.BAK',
  DISK='O:\MSSQL\Backup\MyDB\MyDB_Backup-Set_03.BAK',
  DISK='O:\MSSQL\Backup\MyDB\MyDB_Backup-Set_04.BAK',
  DISK='O:\MSSQL\Backup\MyDB\MyDB_Backup-Set_05.BAK'
WITH INIT

> Jim,  The backups do not overwrite existing files. - RLF
>
[quoted text clipped - 95 lines]
>>>> Thanks in advance.
>>>> - Jim
Roy Harvey (SQL Server MVP) - 29 Feb 2008 21:51 GMT
The WITH INIT option forces the overwrite.

I do not know the answer to your question about when the old file
space is released.  Based on the events you reported I would assume
you should continue to delete the files before the next run, as you
are doing now.

Roy Harvey
Beacon Falls, CT

>Russel, can you elaborate?  We specify the name of the backup files, so the
>files are replaced by the new backup.  When the backup is complete the same
[quoted text clipped - 109 lines]
>>>>> Thanks in advance.
>>>>> - Jim
Russell Fields - 01 Mar 2008 01:20 GMT
Jim,

As best I can tell from experimentation, if you use NOINIT there is some
hiding and renaming of files going on.  If the backup completes
successfully, it deletes the old backup and replaces it.  But it does not
directly overwrite.  Experiment with a 500 MB database:

BACKUP DATABASE [Test] TO
DISK = N'D:\Backup\Test.BAK'
WITH NOFORMAT, NOINIT,  NAME = N'Test-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Run to completion, it creates George.BAK.  Wait a minute, then run it again,
let it get about 70 percent done, then cancel the second backup.  The file
date of George.BAK is updated with the time of the second backup, but the
contents are still from the original backup. You can verify by:

RESTORE DATABASE [Test] FROM
DISK = N'D:\Backup\Test.BAK'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10

It restores successfully from the first backup.

However, if you use INIT, then the second backup will overwrite the
original, but when you cancel the backup you will not be left with a useful
file.

If you do the second backup to a different backup file  Test1.BAK, it will
appear as 0 bytes and eventually grow in size.  If you cancel this backup,
Test1.BAK will disappear.

THEREFORE, my answer should be qualified by whether you use INIT or NOINIT.

RLF

> Russel, can you elaborate?  We specify the name of the backup files, so
> the files are replaced by the new backup.  When the backup is complete the
[quoted text clipped - 113 lines]
>>>>> Thanks in advance.
>>>>> - Jim
Greg D. Moore (Strider) - 01 Mar 2008 04:01 GMT
> Russel, can you elaborate?  We specify the name of the backup files, so
> the files are replaced by the new backup.  When the backup is complete the
> same file names now contain the new backup and old backup is gone.

If I understand what you're doing, I think you risk having something bad
happen.

Imagine this (I can because it's happened to me).  1/2 way through the
backup, it fails due to a data-corruption issue.

You know have a database with potential issues and no good backup since
you've overwritten 1/2 of your one good one.

Always keep at least one good backup when createing the next one.

I'm also not clear why you have 5 files written to the same drive.
Different physical drives can improve performance, but here I think it's
just complicating things.

> I should have noted the command we are using to perform the backup...
>
[quoted text clipped - 109 lines]
>>>>> Thanks in advance.
>>>>> - Jim

Signature

Greg Moore
SQL Server DBA Consulting           Remote and Onsite available!
Email: sql  (at)  greenms.com          http://www.greenms.com/sqlserver.html

Andrew J. Kelly - 01 Mar 2008 15:39 GMT
Greg,

> I'm also not clear why you have 5 files written to the same drive.
>>Different physical drives can improve performance, but here I think it's
>>just complicating things.

There are several reasons. Even when all the files are on a single array you
can still get increased performance assuming the I/O bus is not saturated
for both backups and restores.  But one of the biggest reasons is that by
default SQL Server will use 1 processor per data file regardless of the
setting of MAXDOP. With a single file you may only get 1 processor used and
the time it takes to backup can be much longer.

Signature

Andrew J. Kelly    SQL MVP
Solid Quality Mentors

>> Russel, can you elaborate?  We specify the name of the backup files, so
>> the files are replaced by the new backup.  When the backup is complete
[quoted text clipped - 133 lines]
>>>>>> Thanks in advance.
>>>>>> - Jim
Greg D. Moore (Strider) - 01 Mar 2008 16:40 GMT
> Greg,
>
[quoted text clipped - 8 lines]
> regardless of the setting of MAXDOP. With a single file you may only get 1
> processor used and the time it takes to backup can be much longer.

In my experience doing some testing with multiple files for backups, the
only time I really saw any improvement was when writing to different disks.
In fact I have a hard time seeing how multiple files to the same disk would
actually improve things because at first pass I'd expect it to create move
travel overhead for the diskheads.

And as for multiple CPUs, I'd buy that if I ever saw backups having an
appreciable impact on CPU usage.

However, now you've got me wondering and I'll have to play with this again
in SQL 2005. (I had done previous testing in SQL 2000).

(and I will say with that testing, 5 files was just under 5x faster than 1
file when writing to different disks.  VERY useful and handy when trying to
backup and then restore a 70GB+ DB once a day. :-)

Signature

Greg Moore
SQL Server DBA Consulting           Remote and Onsite available!
Email: sql  (at)  greenms.com          http://www.greenms.com/sqlserver.html

Andrew J. Kelly - 01 Mar 2008 21:53 GMT
Again you will only see a noticeable difference if the I/O channels are not
saturated.  If you have a good controller and the array has lots of spindles
you should notice a difference, especially if you can utilize more
processors in parallel.

Signature

Andrew J. Kelly    SQL MVP
Solid Quality Mentors

>> Greg,
>>
[quoted text clipped - 24 lines]
> file when writing to different disks.  VERY useful and handy when trying
> to backup and then restore a 70GB+ DB once a day. :-)
Andrew J. Kelly - 01 Mar 2008 00:49 GMT
Jim,

This error is referring to tempdb so I suspect there is more to it than you
think. Is tempdb on the same physical array as the db you are trying to
backup?  Are you using compression software?  If not you probably should
consider it as it will make your backup files much smaller and probably
eliminate any issue with disk space. But in the mean time you might want to
try and delete the old backup before you attempt the new one since you
already have it on tape. Due to the I/O error I suspect your issue is
hardware related. Either the storage can't handle the load or there is a
problem somewhere. The errors of I/O taking longer than 15 minutes are sure
signs of one of the two.  Using something such as LiteSpeed can dramatically
speed up an operation such as this.

Signature

Andrew J. Kelly    SQL MVP
Solid Quality Mentors

>I am trying to figure out how disk space is used when overwriting backup
>files.  Here is a description of our situation...
[quoted text clipped - 64 lines]
> Thanks in advance.
> - Jim
TheSQLGuru - 01 Mar 2008 15:33 GMT
Perhaps you are using too many files simulaneously and that could be causing
the RAID5 volume to become overwhelmed with write requests.  Try backing up
to just one or perhaps 2 files instead of 5.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

>I am trying to figure out how disk space is used when overwriting backup
>files.  Here is a description of our situation...
[quoted text clipped - 64 lines]
> Thanks in advance.
> - Jim
Jim Underwood - 03 Mar 2008 14:47 GMT
Thank you all for your feedback.  You've answered my question, and given me
many things to consider when looking at a long term solution, as well as
when setting up future backups.  As to why things are set up the way they
are, I can't fully say.  Our last DBA determined that this would be ideal to
have 5 smaller files rather than one big file (the database was 400 GB at
the time).  The drive the backups are going to is only 5 spindles, one raid
group.

>I am trying to figure out how disk space is used when overwriting backup
>files.  Here is a description of our situation...
[quoted text clipped - 64 lines]
> Thanks in advance.
> - Jim
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.