Archive | SSMS RSS for this section

Script fun!

I wanted to post some of my newer scripts in hopes that they give people a new way to think about doing less manual work.

Most of these came about due to server migrations and not wanting to repeat tasks for each database on servers with a large number of databases.

;with BUS as (
SELECT *, row_Number() over (partition by database_name order by backup_start_date desc) as rn
FROM msdb.dbo.backupset
WHERE type=’D’ –D = Full; I = Diff; L = Log
and database_name not in (‘model’, ‘msdb’, ‘master’)
) select database_name, bus.media_set_id, ‘RESTORE DATABASE [‘ + database_name + ‘] FROM DISK = N”’ + physical_device_name + ”’ WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 1′
from bus
inner join msdb.dbo.backupmediafamily bmf on bus.media_set_id = bmf.media_set_id
where rn = 1

*note*:  Change the D to an I and run again to get the diff listing
select ‘RESTORE DATABASE [‘ + name + ‘] WITH NORECOVERY’,*
from sys.databases
where name not in (‘master’, ‘tempdb’, ‘model’, ‘msdb’)
and state_desc = ‘Restoring’

select ‘ALTER DATABASE [‘ + name + ‘] SET COMPATIBILITY_LEVEL = 110’,*
from sys.databases
where name not in (‘master’, ‘tempdb’, ‘model’, ‘msdb’)
and [compatibility_level] < 110

Anyway — I hope this helps you think about new ways to do things more efficiently!

Deadlocks — Are you watching them?

One of the strangest things I think is that SQL Server does not alert you to deadlocks by default.  I recently deployed some deadlock monitoring out so that I could be more aware of what exactly is happening on my servers.  Those of you with third party monitoring tools would already have similar functionality — this is mostly for those that are not spending a lot of money on monitoring software.  The foundation for this is available at

Things to watch for:

Third party applications seem to just throw deadlocks all of the time.  Some of them, if you google, simply suggest turning on snapshot isolation so that you get versioning.  Please make sure your tempdb situation is under control before taking this advice.

Sharepoint still sucks and the crawls seem to throw a lot of deadlocks.

And now to the fun part, the scripting!

I put this table in master, feel free to put it somewhere else.  I do most of my administration through my central management server so it makes deployment very simple.  As always, never deploy anything without knowing exactly what it is doing and always always always test on a development server first.

USE [master]

/****** Object: Table [dbo].[DeadlockEvents] Script Date: 10/30/2013 2:06:06 PM ******/


CREATE TABLE [dbo].[DeadlockEvents](
[AlertTime] [datetime] NULL,
[DeadlockGraph] [xml] NULL



Next up create a SQL Agent Job to that will be called by the alert you are going to create in a little bit.
USE [msdb]

/****** Object: Job [[Monitor]] Capture Deadlock Graph] Script Date: 10/30/2013 2:06:33 PM ******/
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 10/30/2013 2:06:34 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'[Monitor] Capture Deadlock Graph’,
@description=N’Job for responding to DEADLOCK_GRAPH events’,
@category_name=N'[Uncategorized (Local)]’,
@notify_email_operator_name=N’SQL Server Notifications’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Insert graph into DeadlockEvents] Script Date: 10/30/2013 2:06:34 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Insert graph into DeadlockEvents’,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’INSERT INTO DeadlockEvents
(AlertTime, DeadlockGraph)
VALUES (getdate(), N”$(ESCAPE_SQUOTE(WMI(TextData)))”)

EXEC msdb.dbo.sp_send_dbmail
@body = N”$(ESCAPE_SQUOTE(WMI(TextData)))”,
@subject = ”DeadLock Occurred On Server”,
@importance = ”High” ;’,
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
GOTO EndSave


And now you will create the alert:
USE [msdb]

/****** Object: Alert [DeadlockWMI] Script Date: 10/30/2013 2:07:17 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’DeadlockWMI’,
@job_name=N'[Monitor] Capture Deadlock Graph’

Note:  This uses WMI.

Finally — you will want to turn on a little check box in SQL Agent, but since we like to make as many changes through queries [CMS] as possible:

USE [msdb]
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1

Now the final step is that you do need to restart SQL Agent in order to get these working properly.  Make sure you do not skip this step.

Extra fun — what about blocking?

Here is the alert:

USE [msdb]

/****** Object: Alert [BlockingWMI] Script Date: 11/13/2013 7:28:29 AM ******/
EXEC msdb.dbo.sp_add_alert @name=N’BlockingWMI’,
@wmi_query=N’SELECT * FROM Blocked_Process_report’,
@job_name=N’Put Your Job Here’

A small addition for blocked processes — you will want to use sp_configure and determine what triggers a blocked process:

blocked process threshold (s) is the configuration item, so if you want it to trigger a blocked process after twenty seconds of blocking then do:

sp_configure ‘blocked process threshold (s)’, 20

Questions? Comments?  Leave a message!

Replication – Adding an Article

I have encountered this problem multiple times and it seems that a lot of people think that you have to recreate the publication / subscription in order to add articles.  This is *NOT* the case.

It is actually a very simple task!

Right-click on your publication and click ‘Properties’

Go to the articles listing and uncheck the ‘Show only checked articles in the list’ box.

Now you can add a new table to your replication.

Next: The key step — go to the Agents tab inside of replication monitor for the publication in question.  Right click on the Snapshot agent and select ‘Start Agent’.  This will snapshot the table and send it across!

Pretty easy!

Questions? Comments?  Leave a note!

SQL Policy Management – Compatibility Level

After implementing Policy Management company-wide I decided to expand the built in set of policies that comes from the Microsoft offering.

Implementing a check against compatibility level differences is a little beyond the normal scripts but still relatively easy once you figure out some of the minor issues.


Create a new condition.

You want to use the facet type of “Database”.

In field you will want to paste:

ExecuteSql(‘Numeric’, ‘
SELECT compatibility_level
FROM sys.databases WHERE [name] = ”model”’)  since model is where the server compatibility level should be configured.

Operator will be “=” and the value will be @CompatibilityLevel.

Name your condition and save.

Next up — create your Policy.

Name it whatever you want and then use your newly created check condition.

Check against targets ‘online user databases’ and evaluate on demand.  Save it and then evaluate against some test servers!

Comments?  Questions?  Drop a message!

Short Tip of the Day [number two]! – Script Multiple Objects SSMS

Ever wanted to script multiple objects through SSMS at the same time?

Frustrated by your inability to shift-click multiple items?

Click on the object type [jobs, stored procedures, tables, etc] and press F7.  This will bring up the summary view which is shift-clickable!


Comments? Questions?  Drop a message!