A seasoned SQL Server DBA has many skills, expertise and knowledge. Unfortunately, this also means that his/her time is always on demand by many different groups within the IT organization. Maximizing your productivity and efficiency, especially on many of those pesky daily tasks, is essential not only to the business but to the DBA as well. Automation is the key to happiness!
IT automation is the linking of disparate systems and software in such a way that they become self-acting or self-regulating. In other words, letting technology do things for you. The benefits of automation are clear and here a few examples:
- Move from a reactive mode to proactive one
- Minimize service downtime
- Maximize productivity and optimization of your SQL servers
- Minimize resolution time
- More time to do others tasks to improve the business
You can automate everything if you want, but before you do, consider some of these things:
- What tasks do you not want to spend time doing?
- Which automated responses could minimize downtime or service degradation?
- What is the risk of not automating? What is the risk of automating?
- In case of an automated failure, can you be alerted or can roll back changes if necessary?
In the following 3 examples, we will discuss how we can incorporate a monitoring solution like SQL Diagnostic Manager along with your scripts, to achieve better automation in your SQL Server environment.
The first example allows you to automate Transaction Log backup when they are getting full, based on your criteria.
The second example allows you to kill a long running blocking session, based on the number of seconds it has been active.
The third example monitors all the databases that do not have current backup, executes a backup and emails you the list of databases that were backed up.
Example 1: Backup When Transaction Log Is Getting Full
This response will execute the script to do a Transaction Log backup using Idera’s SQL Safe backup. The script will execute when the metric Log Full (Percent) is with a critical status, only from Monday thru Friday, only on RRIOS-L instance, it will send me an email and run the backup script generated by SQLSafe Backup.
First, you must set an Alert for this. Right click on the Instance, and click on Configure Alerts. Select the metric called Log Full (Percent) and change the default thresholds if desired.
If you want to send emails, you must also configure it the Action Providers.
In SQL Diagnostic Manager, select Tools -> Alerts Actions and Responses…
Click on the Action Providers tab
Enable the services you want. For this example, Email (SMTP) Provider and SQL Script Action Provider.
Click on Email (SMTP) Provider, and hit Edit.
Fill in the required fields and test it.
Next, in SQL Diagnostic Manager, select Tools -> Alerts Actions and Responses…
Click the Add button to Add a new Action and Response. Enter a descriptive name, in this case Backup Log Full.
Select the conditions:
Where the SQL Server Instance is in specified list (optional)
Where metric is in specified list
Where metric severity has changed
Where refreshed occurred during a specific time frame
Select your actions:
Enable Send an email to
Click on to add the email address(es) and to format your email with the variables available from SQL Diagnostic Manager.
Enable Run the following SQL Script (not shown in image below. Scroll down the list)
Enter your script, in this case a script generated by SQLSafe Backup. You could use any script you want to accomplish the same task.
Log Backup Script
DECLARE @ResultCode INT
EXEC @ResultCode = [master].[dbo].[xp_ss_backup]
@database = N'$(Database)',
@filename = N'C:\Backup\%instance%_%database%_%backuptype%_%timestamp% (%ordinal% of %total%).safe',
@compressionlevel = N'isize',
@server = N'rrios-l',
@backuptype = N'Log'
IF(@ResultCode != 0)
RAISERROR('One or more operations failed to complete.', 16, 1);
Example 2: Killing a Blocking Session
This response is useful when you have a long blocking session (in seconds) that might be impeding application or uses from finishing their transactions. This action will kill any session with a blocking more than x seconds as specified in your critical alert threshold.
Follow the same steps as in Example 1 to change the thresholds for Blocking Sessions Wait Time (Seconds). You can do 300 seconds (5 minutes) or whatever you feel is critical in your environment.
Enter the information needed as shown in the image below.
Then enter the SQL Script to kill the running session.
-- KILL SESSION
declare @session varchar(5)
DECLARE @CMD1 varchar(8000)
declare @start_space int
declare @next_space int
declare @diff int
set @start_space = charindex(' ','$(AlertSummary)',0)
set @next_space = charindex(' ','$(AlertSummary)',@start_space+1)
set @diff = @next_space - @start_space
Set @session = substring('$(AlertSummary)',@start_space, @diff)
SELECT @CMD1 = 'KILL ' + (@session)
EXEC (@CMD1)
This script will execute after an Alert is refreshed based on your settings for raising alerts. In my case, every minute.
Example 3: Backup Databases That Do Not Have a Current Backup
This response will require additional configuration. It requires the use of Custom Counters. You need to create a custom counter to do a count of databases without current backup (in my case 1 day or more) then the script list all the databases without a current backup executes a backup command of each of those databases. The script then emails you the results of the databases that were backed up. The script will only run from 12:30 am until 2:30 am, every day (if needed) and for any SQL Server instance.
Create a Custom Counter
Go to the Administration Tab and select Custom Counters
Click Add and Select Custom SQL Script (must return a single value).
Enter the SQL Script below:
select [Database Name] = d.name, isnull(cast(datediff(dd,max(s.backup_finish_date),getdate()) as nvarchar(20)),'999') AS [Days since most recent backup] INTO TEMPDB.dbo.backupdata from master..sysdatabases d left join msdb..backupset s on d.name = s.database_name group by d.name order by d.name -- select count (*) select count (*) [Database Name] from TEMPDB.dbo.backupdata where [Days since most recent backup]>0 AND [Database Name] != 'tempdb' Drop Table TEMPDB.dbo.backupdata
Next, select Use Collected Value, Scale 1, give it a name and Category (optional) and description (optional). Click Next.
On the Configure Alert Thresholds enter 0 for Warning and 1 for Critical
Click Next and Finish.
This counter will run to check if any databases do not have a current backup. If the number is greater than one, it will change the status to critical.
Create a Response to This Alert
Follow the steps above to create a Response.
Fill the required information as shown in the image below.
Enter the following Script attached and make changes as described in the script. SQLSafeBackup- Automation-script
This script will use SQLSafe Backup’s store procedures to execute the back process. You could use your own script to do this natively in SQL Server.
You must change the following in order for the script to work:
— ************************ CHANGE THESE 2 VALUES ************************
–Enter the UNC or folder path for the backup location
Set @Backup_Path=’C:\Backup\’
–Enter the file name be sure to use the %database% variable to keep the archive names unique
–or leave the name as it is which is preferred
Set @Backup_FileName= ‘%instance%_%database%_%backuptype%_%timestamp% (%ordinal% of %total%).safe’
–***********************************************************************
The path is required. The filename can be left as it is or can be changed if desired.
Optional: This script will email you a list of the databases that were backed up. You must configure Database Mail in your SQL Server prior to running this script and change these values accordingly.
— SEND EMAIL WITH NAMES OF DATABASES BACKED UP
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘idera’,
@recipients = ‘user@mail.com’,
@query = ‘select [Database Name] from TEMPDB.dbo.backupdata where [Days since most recent backup]>0 AND [Database Name] != ”tempdb”’,
@subject = ‘Backup completed for databases without recent backups on $(Instance) at $(Timestamp)’,
@body = ‘The following databases did not have a recent backup and a backup script was executed. Check SQLSafe Backup for more details.';
I hope this gives you a few ideas and allows you to fully automate many of your DBA tasks.