| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2010-01-08 : 08:31:57
|
| SQL Server 2005 SP3 version 9.0.4035I have a backup maintenance plan that has two steps:1. "Maintenance Cleanup Task" which deletes files older than 23 hours.2. "Back up Database Task" The cleanup task was set to older than one day and it didn't drop the day before's bak files. But older files were deleted. Therefore, I had two bak files everyday in the folder one belong to previous day and the other belong to the day before. I set the task to "delete files older than 23 hours" yesterday and in the folder there were only 2 bak files both dataed 1/6/2010 8:01 PM. The bak file sizes were 7.6 GB and 5 MB. The job starts at 8:00 PM every day. Today, I noticed the old small bak file is not in the folder but the big one stays there in addition to the new backup which were created last night. I have the same setting in other servers and don't see this issue there. What could be the reason? Why the Cleanup task does not delete the file?Canada DBA |
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2010-01-08 : 09:41:19
|
| Have you tried restarting the SQL Agent.Just a thought as i've had a similar problem, restarted the sql agent and never had a problem since, but this was after i'd altered the time to keep the BK files from two weeks to one. |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2010-01-08 : 12:20:18
|
| I don't recall if I had restarted the agent but will give it a try.Canada DBA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2010-01-08 : 12:35:11
|
| Restarted the Agent. Will post the result tomorrow.Canada DBA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2010-01-13 : 10:40:36
|
| It is weird. I have two databases and the maintenance plan backups both but the clean up task drops only one of them (the smaller one). I checked the folder every morning and noticed that only one of the BAK file belong to the previous night. But for the other BAK file (which is about 7.7 GB) I have two in the folder one for previous night and one for the night before. Note that the clean up task is set to delete files older than 23 hours. How come it doesn't work for one and works for the other?Canada DBA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-13 : 11:14:15
|
Tangential issue, but can you be certain that your Tape Backup (or whatever) task will backup your files within your 23 hour window?Our Tape Backup routine is completely separate from our SQL Backup to Disk routine, and can go AWOL for a while if something external screws up, so we like to keep our SQL backups for several days just in case the Tape event doesn't fire on time.We also like to keep "a reasonable amount" of backup files on disk because they are a whole pile easier to restore than getting them back from Tape - our Tape Restore process makes "urgent request" look like an oxymoron!If you are getting short on disk space this is probably no help, and you may well have other factors that mean I'm talking out of the wrong end  |
|
|
|
CanadaDBA
583 Posts |
Posted - 2010-01-14 : 08:37:17
|
It's SQL backup on disk and starts at 8pm every night. We have tape backups as well but it is being done by our SysEng team. I transfer and restore the BAK files to another server every night. That's why I need only one BAK from each database. Today the backup folder shows as below. As you can see BAK files belong to Jan 12 were deleted last night but Jan 11th bak file is still in there!!Name Size Type Date Modified AttributesAAA_backup_201001112000.bak 7,726,169 KB BAK File 1/11/2010 8:01 PM AAAA_backup_201001132000.bak 7,752,793 KB BAK File 1/13/2010 8:01 PM ABBB_backup_201001132000.bak 4,911 KB BAK File 1/13/2010 8:01 PM A I'll post the backup folder content tomorrow, too.Canada DBA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-14 : 08:53:07
|
| "I transfer and restore the BAK files to another server every night."That works for me Perhaps the filenames are not in the MSDB database?, or there is some perceived referential integrity that is not allowing the file to be deleted? (sub-servient TLog backups or somesuch)I have no experience of this in MSDB, so I'm only guessing. |
|
|
|
CanadaDBA
583 Posts |
Posted - 2010-01-14 : 09:50:43
|
| Well, your post just brought me to something which might be related. The SQL Server was installed on this machine by somebody else and I heard it was uninstalled and installed several times at the time of building and setting up the machine. After a year, when I applied SQL SP3, it didn't change the version to 9.0.4035. Also it caused the C: drive being filled up by error log and stop the server working. We had to get Microsoft support in order to fix the issues.Now, I am suspect that this behavior has the same root cause. As I said above, I have the same backup settings on other servers and they are working fine.Canada DBA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-14 : 10:02:56
|
| "when I applied SQL SP3, it didn't change the version to 9.0.4035."Three places you can get the version number from:select @@VERSIONEXEC xp_msver 'ProductVersion'and the top of the ErrorLog file (in SQL LOG folder, usually)If they all agree, and you think they are lying!!, then its down to the TimeStamp on the DLLs I think ... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
CanadaDBA
583 Posts |
Posted - 2010-01-14 : 14:14:25
|
| Agree Tara. Actually I was thinking to write my stand alone jobs. This way I would be aable to get better error messages. I remember there were some useful codes on your blog. :)Canada DBA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2010-01-14 : 14:20:48
|
Thanks Kristen for the posts. All these three show same number which is correct. select @@VERSIONEXEC xp_msver 'ProductVersion'and the top of the ErrorLog file (in SQL LOG folder, usually) What is "its down to the TimeStamp on the DLLs"? How can I check that?Although this is not related to my issue but good to learn.Canada DBA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-14 : 14:45:18
|
| The various SQL versions have associated with them timestamps on their DLLs / EXEs.The DLL timestamp is important for Client Tool Version Detection - for some tools that may be the only means of telling the version.By way of example see the [currently] latest MS service pack article for a list of timestamps:http://support.microsoft.com/kb/975977/en-us |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
CanadaDBA
583 Posts |
Posted - 2010-01-15 : 10:31:10
|
Today the backup folder contains:Name Size Type Date Modified AttributesAAA_backup_201001112000.bak 7,726,169 KB BAK File 1/11/2010 8:01 PM AAAA_backup_201001142000.bak 7,764,057 KB BAK File 1/14/2010 8:01 PM ABBB_backup_201001142000.bak 4,911 KB BAK File 1/14/2010 8:01 PM A As you see the backups belong to yesterday (1/13/2010) are deleted but the 11th is in there. I am going to delete the 11th backup and will post the folder's content tomorrow.Canada DBA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2010-01-19 : 15:12:50
|
There must be something wrong with SQL Server. After deleting the backup belong to Jan 11th, SQL server kept the 14th backup and by monitorimng the folder I noticed every night it drops the previous night backups but not 14th. See the following directory list for today:Name Size Type Date Modified AttributesAAA_backup_201001142000.bak 7,764,057 KB BAK File 1/14/2010 8:01 PM AAAA_backup_201001182000.bak 7,779,417 KB BAK File 1/18/2010 8:01 PM ABBB_backup_201001182000.bak 4,911 KB BAK File 1/18/2010 8:01 PM A Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-20 : 02:15:17
|
| Does it delete the orphan one the following night? or do you have to delete that manually?If it deletes it on the following night it sounds like the time is just the wrong side of some cutoff (relative to the time of the scheduled job would be my guess)Or take Tara's approach and nuke them based on date/time ... |
|
|
|
CanadaDBA
583 Posts |
Posted - 2010-01-20 : 11:05:28
|
| Every night it deletes the previous night backup. I don't delete anything manually. But it doesn't touch the 14th. For example, today I have the 14th and 19th backups in the folder. If I delete the 14th, it would not delete the 19th backup and on 21st it starts to keep the 19th and drops the 20th backup!!I think the best is replace the clean up task with a code that drops the files in the folder.Thanks Tara and Kristen!Canada DBA |
|
|
|
|
Clean up task doesn't work