Cupfighter.net

System Center Operations Manager is monitoring DB space used by default if you have set the database to a fixed size. If you are using autogrowth with unlimited file growth, your diskspace monitoring will kick in when you are running out of disk space (don’t forget to customize these settings for large disks: you don’t want to get warned on a 500GB volume when there is only 2 GB free space).
However, if you are using autogrow options with a limited size, then SCOM 2007 doesn’t generate alerts as also documented in the product knowledge tab for “DB Space Free (%)” Monitor:
This monitor checks the database percentage free space to ensure it is above the defined thresholds. A warning or error alert will be raised if it is below one of the defined thresholds.
Databases that have autogrow set will never generate an alert regardless of the reported free space.
If your database is filling up, you get the following error: Could not allocate space for object in database because the filegroup is full.
To prevent this error and get warned in advance, I have written a custom monitor that will detect databases with the settings mentioned above and monitors database free space when autogrow AND limited file size are on.
Read more…

Ever thought you had a good backup and it turned out it was missing when you needed a restore? Although a regular restore test is the least you can do, there is more. SQL Server stores backupinformation for all databases in msdb.dbo.backupset. A simple query shows you all databases that have no backup in the last two days:
SELECT DatabaseName = d.name
,LatetstBackUpDate = ISNULL(CONVERT(VARCHAR(30),MAX(b.backup_finish_date),120),’No backup’)
,’Days’ = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) / 24
,’Hours’ = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) % 24
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
WHERE d.name NOT IN (‘tempdb’,'model’)
GROUP BY d.name
HAVING ISNULL(MIN(DATEDIFF(hh,b.backup_finish_date,getdate())),999) >= 24 * 2 — change the 2 when you want to use a different amount of days
ORDER BY d.name

Wait, there is more! You don’t want to run this query every day by hand is it? Of course you can run it as a scheduled job on your database server, but even that can be a time-consuming task if you have a lot of servers. It would be better to run this query from System Center Operations Manager.

Read more…