| Author |
Topic |
|
dpcamp
Starting Member
10 Posts |
 Posted - 2007-05-14 : 16:54:41
|
| Our SQL server runs a backup everynight and is supposed to leave only 3 days of backups on the server and overwrite the oldest one every night. It was doing this for years, and all of a sudden if the server does not have space for the backup it wont overwrite the oldest backup, it will just fail. Right now the work around is to manually delete the oldest backup and re-run the job, but considering a normal day in IT sometimes I wont have the time to do that, or will just plain forget, and that really isn't a good excuse if our SQL server crashes!Any help would be appreciated. Thanks! |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-05-14 : 17:17:56
|
| Your job should contain a step with the command "EXECUTE xp_sqlmaint...". One of the parameters is "-DelBkUps", which should indicate the number of days to retain the files.e4 d5 xd5 Nf6 |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-05-14 : 17:19:19
|
| ...or perhaps your drive space has shrunk or you database size has increased to the point where it cannot maintain four copies of the backup. It will not delete the old backup until the new backup is completed.e4 d5 xd5 Nf6 |
|
|
|
dpcamp
Starting Member
10 Posts |
Posted - 2007-05-14 : 17:22:58
|
here's the code. It looks like it has both lines :EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0A97A0E1-CEC6-495D-BEED-C2DAC958ED2F -Rpt "d:\program files\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Plan14.txt" -DelTxtRpt 1WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 2DAYS -BkExt "BAK"' |
|
|
|
dpcamp
Starting Member
10 Posts |
Posted - 2007-05-14 : 17:25:05
|
| well it appears its holding 2 days. is that 2 days plus the most current? so 3 total? because then that would be 30 gig and theres only 25 gig free on that hard drive. should I change 2DAYS to 1DAY ? |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-05-14 : 22:29:17
|
| My advice would be to increase your drive space rather than compromise your disaster recovery plan. But that's just me...e4 d5 xd5 Nf6 |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-05-14 : 22:30:26
|
| ...by the way, does your backup schedule include dumping the transaction logs? What recovery model are you using? Just suspicious about something...e4 d5 xd5 Nf6 |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-14 : 23:15:23
|
| Maintenance plan does backup first then delete old files. To keep 3 day's backup, you need disk space for 4 days'. |
|
|
|
dpcamp
Starting Member
10 Posts |
Posted - 2007-05-15 : 10:59:58
|
| It is supposed to be dumping transaction logs every 3 hours, but I just noticed it is not doing that! |
|
|
|
dpcamp
Starting Member
10 Posts |
Posted - 2007-05-15 : 11:15:35
|
| I just looked at the transaction log:Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'SQLSRVR' as 'DOMAIN\Admin' (trusted)Starting maintenance plan 'DB Maintenance Plan1' on 5/15/2007 8:13:51 AMBackup can not be performed on database 'SQLDATABASE'. This sub task is ignored.Deleting old text reports... 0 file(s) deleted.End of maintenance plan 'DB Maintenance Plan1' on 5/15/2007 8:13:51 AMSQLMAINT.EXE Process Exit Code: 1 (Failed) |
|
|
|
dpcamp
Starting Member
10 Posts |
Posted - 2007-05-15 : 11:22:32
|
| Ok, I just found that The database revoery model was simple, should that be changed to Bulk logged or full?? |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-05-15 : 12:19:54
|
| Look up the definitions of Simple, Bulk-logged, and Full recovery modes in Books Online to decide what is right for you.e4 d5 xd5 Nf6 |
|
|
|
|
Backup will not overwrite