A member of my user group recently asked me about the best practices for monitoring and automating database maintenance.
Automate Everything
If I do something once, I automate it. I learned this valuable lesson at last year’s PASS Summit during a panel discussion. Any time I find an error situation, I try to determine which conditions might have caused that situation and I create a repeatable script that I can put on every server to check for a problem and email me.
Which Parts of Everything Should I Automate?
First off, you should check out this excellent article by Jonathan Kehayias about How to Automate Common DBA Tasks. It covers almost everything that we do on a day to day basis to make sure we have early warnings about any problems. I understand that there a products out there that might cover a lot of this functionality. But, if you’re stuck waiting for a purchase order, these scripts can hold you off for a while.
There are some WMI scripts in Jonathan’s article. I’m not using those yet. Why? I’d like to be using PowerShell instead. With the push to pick up PowerShell for the entire admin/IT pro space, I would prefer to migrate things to PowerShell than to add new features into VBScript and WMI applications.
Magical Backups
The final part of this cavalcade of T-SQL is my backup solution. This is no substitute for a commercial backup system, but it’s a start.
The BackupControl Table
First up we have BackupControl table:
CREATE TABLE [dbo].[BackupControl](
[id] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [sysname] NOT NULL,
[BackupRoot] [nvarchar](255) NULL,
[BackupInterval] [tinyint] NULL,
CONSTRAINT [PK_BackupControl] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] ;
This is a pretty simple table. There’s an arbitrary primary key so that we don’t keep referencing a lovely heap table. In addition, we have the name of the database, the root path where we want to do our backups, and then the interval for backups, in minutes.
The Jobs
I’ve scheduled a job to run nightly at a specific time, we’ll say 1:00 AM. This job takes nightly backups, removes backup history, and then deletes old backup files.
Backing up Databases
This is a simple step, but I’ve gone and made it complicated by using a lot of code. The reason for using a lot of code is that sometimes it’s necessary to backup databases A, B, and C to the G: drive and then backup databases D, R, and Q to the P: drive. You’ll also notice in this script that I use a maintenance database which has been cunningly named Maintenance. That’s so that I can hold all of my routine scripts in a single database, rather than having to copy them to every database on the server.
DECLARE @current_time AS NVARCHAR(50) ;
DECLARE @backup_root AS NVARCHAR(50) ;
DECLARE @sql AS NVARCHAR(MAX) ;
DECLARE @newline AS NVARCHAR(2) ;
DECLARE @s AS NVARCHAR(1) ;
DECLARE @now AS DATETIME ;
SET @current_time = '' ;
SET @backup_root = 'H:\BACKUPS\OLTP\DEFAULT'
SET @sql = '' ;
SET @newline = NCHAR(13) + NCHAR(10) ;
SET @s = '''' ;
SET @now = GETDATE() ;
SET @current_time = CAST(DATEPART(yyyy, @now) AS NVARCHAR(4)) + N'.'
+ RIGHT(N'00' + CAST(DATEPART(mm, @now) AS NVARCHAR(2)), 2) + N'.'
+ RIGHT(N'00' + CAST(DATEPART(dd, @now) AS NVARCHAR(2)), 2) + N'.'
+ RIGHT(N'00' + CAST(DATEPART(hh, @now) AS NVARCHAR(2)), 2) + N'.'
+ RIGHT(N'00' + CAST(DATEPART(mi, @now) AS NVARCHAR(2)), 2) ;
SELECT @sql = @sql + N'BACKUP DATABASE ' + DatabaseName + N' TO DISK = ' + @s
+ COALESCE(BackupRoot, @backup_root + DatabaseName + N'\')
+ DatabaseName + N'.' + @current_time + N'.bak' + @s
+ N' WITH FORMAT ;' + @newline
FROM Maintenance.dbo.BackupControl ;
EXEC sp_executesql @sql ;
After this runs, I run a stored procedure to clean up the backup history – more info about why this is important over here, on Brent Ozar’s blog. The code is in a step that is set to run in msdb:
DECLARE @oldest_date AS DATETIME;
SET @oldest_date = DATEADD(mm, -1, GETDATE());
EXEC sp_delete_backuphistory @oldest_date;
And, finally, I run a little chunk of PowerShell to delete only backup files:
Get-Item "H:\BACKUPS\OLTP\DEFAULT" | Get-ChildItem | Get-ChildItem |? {$_.CreationTime -lt (Get-Date).AddDays(-2)} | Del
What About My Transaction Logs?
I thought you might ask that… Okay, you probably didn’t because you know I’m using rhetorical devices.
Just like our lovely script to take daily backups, we have a script to take regular transaction log backups. The cool part is that we’re going to tell it to run on a schedule and we don’t have to worry about whether or not the timings sync up perfectly. How can we do that?
At the start of the script, we grab the current time into a local variable. Then we go ahead and pull a list of all of the databases from our BackupControl table that meet the following conditions:
- The BackupInterval isn’t NULL
- The modulus of the BackupInterval and the current time is 0 (if you want to run the backup every 7 mintues, it will get run at 12:07, 12:14, 12:21, etc)
- The database recovery mode is not set to simple.
The code to do this is remarkably similar to the full backup script above:
DECLARE @current_time AS NVARCHAR(50) ;
DECLARE @newline AS NVARCHAR(2) = NCHAR(13) + NCHAR(10) ;
DECLARE @now AS DATETIME = GETDATE() ;
DECLARE @sql AS NVARCHAR(MAX) = '' ;
DECLARE @s AS NVARCHAR(1) = '''';
SET @current_time = CAST(DATEPART(yyyy, @now) AS NVARCHAR(4)) + N'.'
+ RIGHT(N'00' + CAST(DATEPART(mm, @now) AS NVARCHAR(2)), 2) + N'.'
+ RIGHT(N'00' + CAST(DATEPART(dd, @now) AS NVARCHAR(2)), 2) ;
SELECT @sql += N'BACKUP LOG ' + DatabaseName + N' TO DISK = ' + @s + BackupRoot
+ DatabaseName + N'.' + @current_time + N'.log.bak' + @s + @newline
FROM dbo.BackupControl AS bc
WHERE BackupInterval IS NOT NULL
AND (DATEPART(mi, @now) % BackupInterval) = 0
AND DATABASEPROPERTYEX(DatabaseName, 'Recovery') <> 'SIMPLE' ;
EXEC sp_executesql @sql ;
What Else Should I Automate?
Automate everything. If you’ve done it once, automate it. If you don’t want to deal with a problem, figure out how to detect it before it’s a problem and automate that. Through the Dynamic Management Views and built-in functions, it’s possible to collect a wealth of information about SQL Server that you can use to detect your pain points. Turn those pain points around and you’ve got a solid monitoring solution.
