| Author |
Topic |
|
methodology
Starting Member
31 Posts |
 Posted - 2007-08-01 : 09:47:16
|
| sql 2005 stnd on a server of decent spec.dbase in question is only about 5GB on a 450GB partition.at the begining of the month I run:BACKUP LOG [objectstore] TO DISK ='D:\Backups\Prod\backup_objectstore.BAK' WITH NOFORMAT , INIT , NAME = N'objectstore backup'and then every 10 minutes (within working hours) for the rest of the month I run:BACKUP LOG [objectstore] TO DISK ='D:\Backups\Prod\backup_objectstore.BAK' WITH NOFORMAT , NOINIT , NAME = N'objectstore backup'.The amount of data that gets backed up is the same through out the month and the loading on the server as a whole also stays constant throughout the month - NOTHING increases throughout the month that would affect this server in any way, yet at the begining of the month the backup takes 10 seconds, and at the end, it gets up to 5-6 minutes.why?THanksAlastair Jones."A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-01 : 10:08:13
|
What is your retention time?You add a new backupset to the backupfile every time, thus increasing the size every day with the size of the database. With good compression you add 2.5 GB to the backupfile every day.And nothing gets overwritten.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-08-01 : 10:09:05
|
| You are adding to the file each time you run a transaction log backup, so the file is probably very large by the end of the month. It would contain around 1,000 log backups by the end of the month.It is better to create a new time-stamped file for each backup, (example: DBNAME_tlog_yyyymmddhhmm.TRN), and automatically delete them when you no longer need them.You can do this with a maintenance plan, or you could use a stored procedure like this one:Backup databases stored procedurehttp://weblogs.sqlteam.com/tarad/archive/2007/02/26/60120.aspx CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-01 : 10:21:06
|
| "It is better to create a new time-stamped file for each backup ..."backing up by appending to a single file leaves you at risk if there is a corruption in the file.You should take a Full backup more than once a month for the same reason.If you have space / time constraints consider:Full backup once a weekDifferential backup dailyTLog backup every 10 minutes (as you have at present)Kristen |
|
|
|
methodology
Starting Member
31 Posts |
Posted - 2007-08-01 : 10:28:26
|
| and if I want to keep a months worht of data available on the server?full backup week 1 INITfull backup week 2, 3, 4 with NOINITDaily Diffs throughout - NOINITten minute TL?"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-01 : 10:33:14
|
| "and if I want to keep a months worht of data available on the server?"Delete the "older" ones after a month. The Maintenance Wizard can do this for you."NOINIT"As suggested above, better to use unique filenames (e.g. "DatabaseName_YYMMDD_HHMMSS.BAK"), rather than append and risk there being a corruption in the file.What happens if you have a failure one minute after the new month's backup is made?The INIT has just cleared out the file, and you have no backups to revert to. (Well, I suppose you have a copy of the file on tape, but the Tlog backup you made 10 minutes ago is presumably not yet on tape, and has now gone in the INIT!)Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-08-01 : 10:40:15
|
quote:
Originally posted by methodology
and if I want to keep a months worht of data available on the server?full backup week 1 INITfull backup week 2, 3, 4 with NOINITDaily Diffs throughout - NOINITten minute TL?"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.
No.Forget putting more than one backup in the same file. Just backup to a new time-stamped file each time, whether it is a full, differential, or transaction log backup. Retain each type of file for as long as you feel necessary.By the way, the method you proposed would not give you a full month of data. After the INIT at the beginning of the month, you would only have your most recent backup.CODO ERGO SUM |
|
|
|
methodology
Starting Member
31 Posts |
Posted - 2007-08-01 : 10:48:27
|
| OKso theres no hidden SQL uber level wierdness going on, its just the nature of the backup schedule thats screwing itself up.I get itthanks allbeers owed."A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-01 : 11:15:37
|
"its just the nature of the backup schedule thats screwing itself up"Yeah, I imagine that appending backups causes SQL Server to "read" through the existing backup file to find out what is there, where the end of the file is, or possibly even to copy the whole file to make a new one and then delete the "old" one.If you append a backup every 10 minutes for a month I guess that's quite a lot for SQL Server to "thumb through" towards the end of the month  Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-01 : 11:33:56
|
| And will take forever to restore the db with this backup plan. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-01 : 12:05:09
|
| "And will take forever to restore the db with this backup plan."Is that because to get, say, the 10th TLog backup SQL Server has to "walk through" the file to find it?So it gets progressively longer the more backups are appended to the file?If so that's worth knowing, thanks.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-01 : 14:19:33
|
| If you need restore db at the end of month, how many logs have to apply? Get file is not that bad with restore statement, but not that good to list them in gui. |
|
|
|
|
Backup takes longer at month end than begining