|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Equal disk space needed to delete .bak file?Kind of an obscure question, but here goes.
SQL2000 SP4 I have a E: drive on my SQL server that is ~400gb. On it you'll find a ~100gb live DB file and last night's 100gb .bak file (maintenance plan is set for one day retention). The vendor is telling me that I am going to run out of space doing the SQL backups because 100gb is used by the live DB, 100gb is used by last nights .bak file, 100gb will be used to write today's .bak file, and the last 100gb will be used in the deletion process of removing the old .bak file. I can't say I know how SQL handles the removal of the old .bak file but is this a true statement that SQL will need equal free space to delete the old .bak file? SQL deletes old backups AFTER creating new ones, so you will need space for
at least two backups. Look at it this way, what happens if your database blows up during a backup. If you have an older backup, you are fine. If you are overwriting your only backup, it is time to polish up the resume. Personally, I would go out and buy a couple of USB drives and back up to there. Last week my mother purchased a 500GB model for $99. If that is not an option, you could look into one of the commercially available backup compression tools. The three major ones are SQLSafe (www.idera.com), LiteSpeed for SQL (www.quest.com), and SQL Backup (www.red-gate.com). -- Show quoteGeoff N. Hiten Senior SQL Infrastructure Consultant Microsoft SQL Server MVP <rockemh***@gmail.com> wrote in message news:04b97215-0ec7-4545-a43b-f072fb3c47a2@e6g2000prf.googlegroups.com... > Kind of an obscure question, but here goes. > > SQL2000 SP4 > > I have a E: drive on my SQL server that is ~400gb. On it you'll find > a ~100gb live DB file and last night's 100gb .bak file (maintenance > plan is set for one day retention). The vendor is telling me that I > am going to run out of space doing the SQL backups because 100gb is > used by the live DB, 100gb is used by last nights .bak file, 100gb > will be used to write today's .bak file, and the last 100gb will be > used in the deletion process of removing the old .bak file. > > I can't say I know how SQL handles the removal of the old .bak file > but is this a true statement that SQL will need equal free space to > delete the old .bak file? Right, I aggree. In your scenario I need 300gb, not 400gb as the
vendor claims. 100gb - live DB 100gb - last nights backup 100gb - to create tonights backup ----------------- 300gb Total to perform the whole operation Vendor is saying: 100gb - live DB 100gb - last nights backup 100gb - to create tonights backup 100gb - extra needed to delete last nights backup after a successful backup ------------------ 400gb Total to perform the whole operation I just don't understand why that extra 100gb is needed. Show quote On Nov 28, 10:46 am, "Geoff N. Hiten" <SQLCrafts***@gmail.com> wrote: > SQL deletes old backups AFTER creating new ones, so you will need space for > at least two backups. > > Look at it this way, what happens if your database blows up during a backup. > If you have an older backup, you are fine. If you are overwriting your only > backup, it is time to polish up the resume. Also, a backup on the same disk as the primary is pretty useless when you
think about it. -- Show quoteGeoff N. Hiten Senior SQL Infrastructure Consultant Microsoft SQL Server MVP <rockemh***@gmail.com> wrote in message news:b1987afa-2fde-4bcc-9e25-4d2632f7c009@j44g2000hsj.googlegroups.com... > Right, I aggree. In your scenario I need 300gb, not 400gb as the > vendor claims. > > 100gb - live DB > 100gb - last nights backup > 100gb - to create tonights backup > ----------------- > 300gb Total to perform the whole operation > > > Vendor is saying: > > 100gb - live DB > 100gb - last nights backup > 100gb - to create tonights backup > 100gb - extra needed to delete last nights backup after a successful > backup > ------------------ > 400gb Total to perform the whole operation > > I just don't understand why that extra 100gb is needed. > > On Nov 28, 10:46 am, "Geoff N. Hiten" <SQLCrafts***@gmail.com> wrote: >> SQL deletes old backups AFTER creating new ones, so you will need space >> for >> at least two backups. >> >> Look at it this way, what happens if your database blows up during a >> backup. >> If you have an older backup, you are fine. If you are overwriting your >> only >> backup, it is time to polish up the resume. Only if it's not getting written to tape ;)
Show quote On Nov 28, 11:28 am, "Geoff N. Hiten" <SQLCrafts***@gmail.com> wrote: > Also, a backup on the same disk as the primary is pretty useless when you > think about it. I've never seen Windows needing temp storage in order to delete a file. Perhaps your vendor somehow
confuses the windows Recycle Bin functionality somehow... -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi <rockemh***@gmail.com> wrote in message news:b1987afa-2fde-4bcc-9e25-4d2632f7c009@j44g2000hsj.googlegroups.com... > Right, I aggree. In your scenario I need 300gb, not 400gb as the > vendor claims. > > 100gb - live DB > 100gb - last nights backup > 100gb - to create tonights backup > ----------------- > 300gb Total to perform the whole operation > > > Vendor is saying: > > 100gb - live DB > 100gb - last nights backup > 100gb - to create tonights backup > 100gb - extra needed to delete last nights backup after a successful > backup > ------------------ > 400gb Total to perform the whole operation > > I just don't understand why that extra 100gb is needed. > > On Nov 28, 10:46 am, "Geoff N. Hiten" <SQLCrafts***@gmail.com> wrote: >> SQL deletes old backups AFTER creating new ones, so you will need space for >> at least two backups. >> >> Look at it this way, what happens if your database blows up during a backup. >> If you have an older backup, you are fine. If you are overwriting your only >> backup, it is time to polish up the resume.  |
|||||||||||||||||||||||
Other interesting topics