xp_sqlmaint
-
Here's the situation:
I try to keep 3 days backups and 2 days logs on the server. That way I can pretty much recover on the fly.
The problem that I run into is that if we get energetic departments, they fill up the trans logs in several databases, that then crunches the free diskspace down so that backups can't be taken, so then the logs don't shrink and the old logs and backups don't get deleted then the disk gets even tighter. At which point the SQLServer virtually crashes.
I keep getting a Msg 22029 on the lines below.
EXECUTE master.dbo.xp_sqlmaint N'-DelTxtRpt 3DAYS -DelBkUps 2DAYS -BkExt "BAK"' EXECUTE master.dbo.xp_sqlmaint N'-DelTxtRpt 3DAYS -DelBkUps 1DAYS -BkExt "TRN"'
I'm trying to get the above scripts to work on the 9002 event along with an error message sent to me. This will buy me the time to get diskpace added or do other adjustments. (Especially not come in Monday morn to screaming users.
) Any Ideas
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
I do not have a solution for what you want, but we use SQL LiteSpeed, for back-up. You use a lot less diskspace for the database backup and transaction log backup. Maybe that is worth looking at?
-
A possible solution is to schedule transaction log backups more frequently, using your maintenance plan. Or, set up an alert to automatically trigger a transaction log backup when the log files gets to a specified size.
Regards
Peter
-
The problem is that there are 7 db's on the one server and 22 on the other (note: avoid NetVision at all costs for now) not inlucing the SQL db's. I want to do it generically across the board for all DB's without trrying to track log files.
My boss is talking the Sql OFA Backup Express, but that doesn't buy me anything. The odds of getting a decent utility is slim and none.
I know I can come up with a procedure to do a BackUp log Trunc Log, but I'd rather take the risk of losing days past than current. Currently my tran log backups are done hourly 24/7.
The M$ programmer that developed this problem needs to be shot. It boils down to "We have no room, so we won't delete anything until we have room!"
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Sorry Jim, I missed that you were trying to get the maintenance plan to delete without doing the backup.
I have never tried to do it, but I think you will at least have to specify the ' -BkUpDB "C:\somefolder"' or ' -BkUpLog "C:\somefolder" ' clauses to say where the backup files are.
Is it possible that you can share a folder on a different server and modify the current maintenance plan jobs to backup to that folder instead. This could also buy you some time until you can add disk space to your existing servers.
Regards
Peter
-
here's come ideas you can put together to be as simple or complicated as you want...
xp_fixeddrives - check to see how much remaining free space
check size of last backup batch (if you backed up 21 dbs in 1 maintenance cycle, how much space did it take?)
given if you have enough remaining space to take another backup, proceed normally. If not, execute something like xp_cmdshell 'del c:\mssql\backup\old backup.bak'
you could also deermine to break down the "1 maintenance plan fits all" and create maybe 1 maintenance for large or highly active DBs" - make sure this doesn't overlap with other normal backups and eats up your space. sqlmaint backups by nature BACKUP FIRST and DELETE LAST so you can't change this behavior.
you could also create routines to inspect the size of a log file and if it has grown to a threshold, do a backup on the fly and shrink it. There have been several postings on how to do this by doing only backups when necessary.
you could also adjust from keeping 3 days to keeping 2. Ask yourself what is the value of keeping that many backups online? If you really had a failure, you would most likely only use the most recent backup and not one from 2 days ago. For DR, you should copy these files to another location anyway and maybe not have ANY online backups. This is a common solution when disk space is an issue as well.
-
I generally breakup my Maint Plans into system DBs, User DBs (tran log BU), and IS DBs (simple recovery and SW control DBs).
The 3 day is to allow for weekends and to give a window to grab EOM copies for baselining/report databases. We do run Backup Express and write the files to tape. But the tapes are off-site within 24-36 hours of their creation.
I was trying to avoid doing a "Backup log %DB% TRUNCATE_ONLY", but if that is the solution...it is easy enough to setup.
As for "xp_cmdshell 'del c:\mssql\backup\old backup.bak'" I have named sub-folders and of course named_dattimestamp.bak files. I don't want to blow them all out. I'm just trying to get to the oldest set and logically delete them. You can't really do it by date. So then it becomes a matter of read from the sub-folders, then build the cmdshell line.
Too bad M$ won't allow the maint to clean up.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
By the way...
If you want to find out the name of the third to last database backup file created, so you can delete it, you can do it with:
declare @file_name varchar(100)
select top 1 @file_name = '\\BackupSharedDrive' + substring(physical_device_name,
charindex('\', physical_device_name), 4000) from msdb.dbo.BackupMediaFamily bmf
left join msdb.dbo.BackupSet bs on bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'databasename' and type = 'D'
and bmf.media_set_id not in
(select top 2 bmf.media_set_id from msdb.dbo.BackupMediaFamily bmf
left join msdb.dbo.BackupSet bs on bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'databasename' and type = 'D'
ORDER BY bs.backup_start_date desc
)
ORDER BY bs.backup_start_date desc
Just change the \\backupSharedDrive and databasename as appropriate.
Regards
Peter
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply