Thursday, September 14, 2017

Using Synonyms with Availability Groups

I'm a big fan of using synonyms to point to objects in other databases, but if you've tried to use them in conjunction with Availability Groups you may have encountered a slight issue: upon failover, the synonyms are no longer valid because the instance name is part of the synonym definition.  In order for the synonyms to work again, they have to point to the instance they now reside in - assuming the databases were in the same instance, respectively.

The next challenge was to automate the of detection of when the primary has changed, therefore triggering an update of the synonyms.  I put the following tSQL into a SQL Agent job and set it to run every five seconds.  This will be Step 1 in the job, as it will have two steps total.

IF  (   SELECT role_desc
FROM   sys.dm_hadr_availability_replica_states
INNER JOIN sys.dm_hadr_name_id_map 

               ON  ys.dm_hadr_availability_replica_states.group_id = sys.dm_hadr_name_id_map.ag_id
WHERE  sys.dm_hadr_name_id_map.ag_name = 'AGName'
AND is_local = 1
) = 'secondary'
('This is secondary replica. Do not countinue this job.', 11, 1);

('This is primary replica and you can go to next step.');

Now, if no failover has occurred, the Step 1 above will fail - as will the job as a whole obviously.  If in fact a failover has occurred it will go on to Step 2, which has the below syntax.  You'll note the syntax of the query in the cursor: it will query sys.synonyms to see if there are any pointing to the incorrect (not local) instance.  If they are all correct, no records will be returned and it will stop.  If there are synonyms that need updating, it will fall into the cursor and drop/recreate them using REPLACE syntax to point locally (@@SERVERNAME).

, @Definition VARCHAR(MAX)
@Schema VARCHAR(50);


, SCHEMA_NAME(schema_id)
FROM   sys.synonyms
WHERE  base_object_name LIKE '%OtherInstanceName%';

OPEN loccur;

INTO @ObjectName
   , @Schema
, @Definition;

'Converting: Synonym, ' + @ObjectName;

SET @SQL = 'DROP SYNONYM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@ObjectName);

SET @SQL = 'CREATE SYNONYM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@ObjectName) + ' FOR ' + REPLACE(@Definition, 'OtherInstanceName', @@SERVERNAME);

INTO @ObjectName
, @Schema
, @Definition;

CLOSE loccur;

Tuesday, August 29, 2017

Monitor and stop long-running SQL Server Agent Jobs

In recent days I've been having a periodic issue with the distribution cleanup job for replication. The vast majority of the time it runs in a few seconds.  There are times at night when it may take a few minutes which is not an issue.  But recently I've had a couple cases where the job ran for 13 or 14 hours and that subsequently led to prolonged blocking of other replication activities such as the distribution agent SPIDs, etc.  That in turn led to all sorts of other issues that I won't detail here.

To address the situation, I would essentially have to stop the SQL Server Agent job called 'Distribution clean up: distribution'.  Oddly enough, subsequent runs would be right back on track.

To mitigate the issue as quickly as possible (perhaps there are other solutions out there) I decided to put together a simple monitoring job to check how long the cleanup job has been running.  If it has been running for more than 10 minutes, stop the job.

Here is the code I used.  It has to run in msdb and I scheduled it to run every 10 seconds.  You could use this syntax to monitor any jobs - perhaps an index maintenance job is running abnormally long on the weekend and bloating TempDB or something - but you wouldn't know right away.  This will at least stop the activity until there is time to further investigate.

The syntax:

DECLARE @Duration_secs INT;

SELECT @Duration_secs = DATEDIFF(ss, ja.start_execution_date, GETDATE())
FROM   msdb.dbo.sysjobs jobs
       LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = jobs.job_id
            AND ja.start_execution_date IS NOT NULL
WHERE = 'Distribution clean up: distribution'
AND stop_execution_date IS NULL;

IF @Duration_secs > 600 -- 10 minutes
dbo.sp_stop_job N'Distribution clean up: distribution';

Thursday, August 18, 2016

VMware Host Performance and Configuration Reports

So you are a DBA and you are in a virtual environment - VMware in particular.  You are curious to know the health of the VMware hosts in terms of CPU and RAM, but you really don't know how to get the data you need and you're not certain if the information you are asking for is entirely accurate.  Well, chances are you have access to the VMware databases themselves - if that is the case, you can create these reports based on a blog post from Jonathan Kehayias: "Querying the VMware vCenter Database (VCDB) for Performance and Configuration Information".

I have created five reports that are based on Jonathan's queries and you can download the RDL for the SSRS reports below - enjoy!

Host Configuration
CPU Ready Values (Daily)
CPU Ready Values (Weekly)
Memory Ballooned (Daily)
Memory Ballooned (Weekly)

Hopefully you find these useful,
Allen (@SQLTrooper)

Thursday, January 21, 2016

SSMSBoost - A Productivity Tool

SSMSBoost Tips and Trips    (

Today I typed up a little "tips and tricks" document for the various teams I work with and figured heck, why not share it with everyone?  As you can see, I had to obfuscate some of the database/connection information with white boxes.  Hope the tips come in handy!

Tip 1: Locate Object – when you have hundreds or sometimes thousands of tables to comb through, this feature makes it very simple to locate an object with a couple clicks.  Simply hover over the object (normally a table), right-click and click “Locate object in Object Explorer”.  All the menus will expand and send you right to the object.


Tip 2: Easily switch connections – I often switch between connections, which natively is a little cumbersome (Right-click > Connection > Change Connection, etc.).  With SSMSBoost, you can define your Preferred Connections.  For each connection, as you can see below you can also choose a default database that it will connect to rather than master by default.

The color coding is nice as well.  I choose to set all production servers to have a red Status Bar (see Tip 1 image).  That way I know when I’m connected to production and when I’m not.  Once you have configured your Preferred Connections, you’ll note the Connections drop-down in the SSMSBoost ribbon will contain them all for quick switching between instances:

Tip 3: Fatal Actions Guard – Let’s face it – we’ve all likely ran a query and forgot to put a where clause, or perhaps you clicked F5 to truncate a table in dev… BUT you were in production.  At least this feature gives you one last chance to change your mind if you really didn’t mean it.

Preview of the popup that will appear upon execution:

Tip 4: Results Grid aggregates – Some people do a lot of calculations with their data, whether it’s sales data or whatever.  You end up saving the query results to Excel and about five minutes later you have the totals, etc.  With SSMSBoost, you can have your totals in seconds.  Below I’m selecting 10 records from a sales related table.  Say I want to get the SUM, MIN, MAX and Count of the SALEPRICE.  All I do is slide my mouse down the column highlighting the cells and a pop-up will appear with that information:

Tip 5: Format SQL – Not every developer has a tSQL formatting tool.  SSMSBoost does come with one if you want to use it.  Your teams could all configure the tool for the same sort of formatting for consistency sake.


Tip 6: History – How many times have you wanted to retrieve a query from a few days ago?  Maybe SSMS crashed, or you wanted to revert back to another version.  You can customize the History feature to suit your needs.  In the example below I searched for UPDATE – I know I ran an update a few days ago, but I don’t recall the exact syntax.  Not a problem:

Friday, October 16, 2015

Custom Backup Exec SSRS Report

I'm not a Backup Exec administrator, but recently one of my co-workers wanted a custom SSRS report to track our backups, which also allows him to receive this report on a regular basis via a subscription.  This is what I came up with - perhaps you too will find it useful.

          WHEN 200 THEN 'Backup'
          WHEN 300 THEN 'Duplicate'
        END AS TaskType
       ,(ElapsedTimeSeconds / 3600.0) AS ElapsedTimeHours
       ,CASE FinalJobStatus
          WHEN THEN 'Success w/ Exception'
          WHEN THEN 'Success w/ Exception'
          WHEN THEN 'Canceled'
          WHEN 19 THEN 'Success'
          ELSE 'Unknown'
        END AS FinalJobStatus
       ,(TotalDataSizeBytes / 1024.0 / 1024.0 / 1024.0) AS TotalDataSizeGB
FROM    BEDB.dbo.vwJobHistorySummary
WHERE   TaskTypeID IN (200, 300)
        AND ActualStartTime > DATEADD(MONTH, -1, GETDATE())
ORDER BY ActualStartTime DESC

RDL: click here.