SQL Server Forum / DB Engine / SQL Server / March 2008
Database backup issue - Space requirements
|
|
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
|
|
|