Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dbthj
Posting Yak Master
143 Posts |
 Posted - 2011-09-06 : 14:23:37
|
| I'm looking for details of a backup that took place last month. I have a handy-dandy query that gives information on backups and it gets the start-date and stop-date from msdb.dbo.backupset. On one particular instance, I see the backup history going back to 8/30/2011. I know for a fact there were many backups taken before then. What kind of process could account for lost history in msdb.dbo.backupset? All other instances in our shop (we have many) keep history forever. |
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2011-09-06 : 14:24:02
|
| sql 2008 R2. Clustered |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-06 : 14:43:30
|
| Maintenance plan cleanup task?sp_delete_backuphistory ? |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-09-06 : 16:12:40
|
| Most likely you have the default settings for SQL Server Agent. On the History page for SQL Server Agent properties - there are 2 check boxes. The first one (Limit size of job history log) is a server wide setting. What this option does is purges out any history once you reach the maximum size regardless of the per job setting.The second option is not really a setting - it allows you to run a manual cleanup of history based on time.If you have the first item checked, that would explain why you see history for some jobs - but not for others. My recommendation is to create a maintenance plan with one sub-plan (Daily) and one task (History Cleanup). Define how long you want to keep history - I set this to 6 months - and set those options.Once that is done - uncheck both options on the History page for SQL Server Agent and you will always have 6 months of history available for all jobs, maintenance plans and backups.Jeff |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2011-09-08 : 12:38:02
|
| Kristen hit it. There was a maint plan set up by a junior dba that had a history cleanuptask set to delete job history and backup/restore history older than a week. Don't want to do that. History is goodness. I find backup history VERY useful for tracking the history of database size. The backup size is recorded in msdb..backupset and a query can give you size and date history to paste into a spreadsheet. A simple line graph gives you a picture to mail to the people you want to get more disk space from - or the people who should have a purge program - or the people who will be unhappy when the application fails due to a full disk.Try this query. I run it a lot -- recent backup history LOOK FOR THE "-8" THIS SAYS JUST SHOW LAST 7 DAYS. -- YOU CAN CHANGE THAT TO -1 or -555 or whatever. SELECT bs.database_name AS DatabaseName,convert(decimal(9,2),bs.backup_size/1024/1024) as BackupSizeMB, DATEDIFF ( minute , bs.backup_start_date ,bs.backup_finish_date ) as Elap_Minutes , case datepart(weekday,bs.backup_finish_date) when 1 then 'Sunday' when 2 then 'Monday' when 3 then 'Tuesday' when 4 then 'Wednesday' when 5 then 'Thursday' when 6 then 'Friday' when 7 then 'Saturday' end as weekday, bms.physical_device_name AS FullBackupName, case UPPER(bs.type) when 'D' then 'Full' when 'I' then 'Incremental' when 'L' then 'log' when 'F' then 'FileOrFilegroup' end as BackupType, bs.backup_start_date, bs.backup_finish_date --INTO #Backups -- use this and it will automagically create the table FROM msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupmediafamily bms ON bs.media_set_id = bms.media_set_id INNER JOIN master.dbo.sysdatabases s ON bs.database_name = s.name WHERE CONVERT(VARCHAR(20), bs.backup_finish_date, 111) > CONVERT(VARCHAR(20), (GETDATE() - 8), 111) -- -1 is yesterday and -8 is last week -- and -- s.name NOT IN ('master', 'msdb', 'model', 'pubs', 'Northwind','adventureWorks', 'adventureworksdw') -- and physical_device_name like 'gx%' -- Galaxy. Galaxy might also have garbage names. -- and physical_device_name like 'VNB%' -- Netbackup and UPPER(bs.type) in ('d', 'i' ) -- full and incremental only <<===== add 'l' if you want log backups --and bs.database_name like '%xxxyyxxx%' order by -- bs.database_name asc , bs.backup_finish_date desc; |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-08 : 17:04:35
|
"Don't want to do that. History is goodness."Send the Junior DBA to the Stores department for a Long Weight, or a Sky Hook ...  I've been meaning to log, daily, the size of all individual database objects, but your quick-query from MSDB is most welcome, thanks. It tells me that all my databases have not grown much in size, except for MSDB which, in the last year, has grown from 13MB to 231MB - how ironic!!In case your stats. are not leaning towards the disk sub-system that you crave for I recommend this book: How to Lie with Charts |
|
|
|
|
|
|
|
|
Backup History Dropped from msdb.dbo.backupset