Days pass by and my logs apply and I think that I’m ok until I find myself with data that is fading away…

Oh my goodness, are we back on the log-shipping journey? We are! After the last post, I found a bunch of things that I wanted to fix and one of them was alerting. How do I know if my logs are applying? How do I know if something bad is happening?

I don’t want emails that tell me everything is ok, only when things are bad and I want them to be helpful emails. Not only did I want an alert, I wanted an email with actual information that I can use to make my decisions. Decisions like, can I just apply a few logs to get caught up or did everything burn down and I need to pull a full backup plus all the logs to be up and running again?

This was a task for some super fancy alerts on my agent job.

First, I need to come up with the query of information I wanted to display in my email:

SELECT Max(CreatedDate) AS LastRestore, Origin AS [Database]
FROM RestoreFile
WHERE IsApplied = 1
HAVING DATEDIFF(hh,Max(CreatedDate),GETDATE()) > 3

This query will give me the LastRestored file time and for which Database it was restored. It will help me identify which database is having problems and how much I need to try to fix it. So, cool query, but how do I get that to email me? This next part saves me so much time with digging on my agent job issues.


SET @xml = CAST(( SELECT [LastRestore] AS 'td','',[Database] AS 'td'
FROM (SELECT Max(CreatedDate) AS LastRestore, Origin AS [Database]
FROM RestoreFile
WHERE IsApplied = 1
HAVING DATEDIFF(hh,Max(CreatedDate),GETDATE()) > 3) A 

SET @body ='<html><body><H3>Last Applied Log</H3>
<table border = 1> 
<th> Last Restore </th> <th> Database </th></tr>'    

SET @body = @body + @xml +'</table></body></html>'

FROM RestoreFile
WHERE IsApplied = 1 
HAVING DATEDIFF(hh,Max(CreatedDate),GETDATE()) > 3) > 0
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default', -- replace with your SQL Database Mail Profile 
@body = @body,
@body_format ='HTML',
@recipients = '', -- replace with your email address
@subject = 'Transaction Logs Are Old' ;

A few things are happening here. First I am declaring my @xml and my email @body. Then I create the query that is going to return my formatted table in my email and shove that into XML. Next, I start building the body of the email with a title for my table, and column names too (using HTML). Then I combine it with the XML.

The “If ” statement allows me to only send the email if it meets conditions in the query. This query is similar to the one I created above, but it doesn’t have to be. Also, the count has to be greater than 0 to send the email, otherwise it will skip sending.

Finally, I get to the part that calls sp_send_dbmail. This allows me to set the profile for the sender, add in my XML\HTML built body and tell the proc to use that HTML formatting. I add my email recipients and a subject for the email.

I started testing it and it was awesome…until one day when I got an error from what is reading my databases because one wasn’t in Standby. I decided to add another step that would also check that all the databases were in Standby. It is almost exactly the same, with the exception of the query, which looks like this:

SELECT name as [Database]
FROM sys.databases
WHERE is_read_only <> 1 -- Read_only should equal 1 if the database is in standby. 
AND database_id > 5 -- 5 is my database that is controlling the log-shipping and lower are system databases.

I have these emails also forward me a text so I know that something is wrong. It is awesome and makes me worry so much less.

The song for this post is Imagine Dragons’ Wrecked.

Listen to the AG that’s tricking you…


Another AG (Availability Group) Post? Yes, I learned something new and it must be cataloged. When you are failing AG’s back and forth really fast and a major indexing job kicks off in the middle, it can cause a transaction to have to rollback.  This rollback may take a REALLY long time, even if you were only on the node for 10 minutes and a large transaction had only been running for about 5 minutes. When I failed back to my preferred primary node and the AG Dashboard didn’t go completely green, I got worried.  Why in the world would it not go green? I just failed to the preferred secondary and applied a patch (see? I learned.) and then was failing back. It had been green when I started, green when I failed over to the secondary and now one of my biggest databases was not synchronizing on the primary….*sigh*

I panicked. In this situation I would normally pull the database out of the AG and then re-add it.  I didn’t have that option because it is a HUGE database and didn’t have that much time and space to move it around. I knew a large transaction had kicked off (thank you alert email that I created to warn me about such things) but thought that surely the rollback would have cleared quickly.  That lead me to looking for rolling back transactions.

I ran this on the alarming secondary node:

SELECT R.session_id, R.command ,R.status, R.percent_complete
FROM sys.dm_exec_requests R
WHERE R.command IN ('killed/rollback','rollback')

To my surprise, there were no results.  Nothing was killed or rolling back; or was it? I ran the query again, but this time without the where clause.

SELECT R.session_id, R.command ,R.status, R.percent_complete
FROM sys.dm_exec_requests R

I saw one command listed as “UNKNOWN TOKEN” that had a percent complete at about 5%. That percent was rising. I theorized that this was my rolling back process and when it finished, my AG would be healthy again.  The system isn’t used overnight. We had started the maintenance in the late afternoon and it was the secondary node in trouble, so I had time to test my theory.  It was an agonizing 8 hours as I kept checking on the percent_complete all evening.  It finally completed and the AG went green.

My lesson learned: When my AG isn’t healthy and I have already resumed data movement, before I pull the database out of the AG,I need to check for processes that have a percent complete on the secondary node. Being patient is really hard but necessary with AG’s.

The song that goes with this post Listen to the Man.

It’s different for Availability Groups when service packs get broke…

Last week I did a few things wrong.  The good news is I learned from it and now can prevent myself from repeating it.

So I have this AG, it is kind of important, hence the AG part but after 5pm I have two hours that it can be down, or so I thought.  We recently added new functionality that requires it to be up all the time.  I have been applying SQL 2014 SP2 to all of my 2014 servers all of which have been in AGs and it has been super easy, less than 10 minutes of work and only a reboot of downtime. (I am a little old fashioned and always reboot after a service pack.) My mind said, “Sweet, you can get this done and no one will notice and if you do both at the same time and delay the reboot on one of them, there will be no down time.”  That was my first mistake.  My second one was starting a few minutes early. I was excited to have it done because it was my last round of service packs. My final mistake was not realizing that SSISDDB is considered a system database and should NEVER be in an availability group. It may be super awesome because you can keep your SSIS catalog completely in sync. You could maybe do it if you plan to remove it before doing service packs or any kind of upgrades to the server, bur as far as setting it and forgetting it, you are in trouble if you do it.

Here are a few of the fun errors that we saw.

“Script level upgrade for database ‘master’ failed because upgrade step ‘SSIS_hotfix_install.sql’ encountered error 942, state 4, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.”

“Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.”

Huge thank you to all the people that have blogged about Trace Flag 902.  It allowed us to start up SQL Server and find the errors and pull SSISDB out of the AG and get the service packs to finish running and everything was happy and great.  Here is the list of steps from the Microsoft KB article:

Enable trace flag 902 on the instance of SQL Server. To do this, follow these steps:

  1. Open SQL Server Configuration Manager.
  2. In SQL Server Configuration Manager, click SQL Server Services.
  3. Double-click the SQL Serverservice.
  4. In the SQL Server Properties dialog box, click the Advanced tab.
  5. On click the Advanced tab, locate the Startup Parameters item.
  6. Add ;-T902 to the end of the existing string value, and then click OK.

Lessons Learned:

  1. Don’t start earlier than you say you are going to start.
  2. Don’t do both sides of an AG at the same time.  Do the Passive one first and make sure everything is up and working and the AG is healthy before you do the primary node. Verify the fail-over works to the passive node so that you have no down time.
  3. Don’t get too comfortable with Service Packs and Cumulative Updates. They are still a big change even though they are pretty stable.
  4. Don’t have any system databases in the AG while applying patches and know that SSISDB is a part of the system databases.
  5. An awesome team that can back you up and help you trouble shoot can make all the difference.  It is amazing to have a boss that believes in you and is encouraging to help you keep going even when you want to give up and go cry in the closet.

Also for those of you following along at home and what to know what song goes with this post: It’s Different for Girls

Happy Service Packing!

Needle and the thread Agents running out of it’s head…the disabled agent jobs are still running!

I learned an important lesson this weekend about updating system tables directly. I am getting ready to migrate to a new SQL Server and am setting up agent jobs to match what is on the old server.  I scripted them out and then I ran them on the new server. Then I saw that many of them were enabled.  Rookie mistake! I was in a hurry and googled for an Agent job disable script and found this one:

UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;

“Awesome”, I thought to myself and kept going.  But over the weekend our on-call started getting alerts of jobs failing from the new server.  How was this possible? They all showed disabled?


Well, it turns out that SQL Agent didn’t know they were disabled because I didn’t use the proper Stored Procedure to update the jobs. SQL Agent thought it was supposed to run them, so it kept running them even though they were marked as disabled. We shut down SQL Agent until I could figure out why they were running and that is one of the fixes.  By “rebooting” the agent, it sees all the jobs are disabled and updates.  For Reference, here is the proper way to update jobs to disabled or enabled From HowardH on SQLServerCentral:

--generate disable
SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 0' FROM msdb..sysjobs
--generate enable
SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 1' FROM msdb..sysjobs WHERE enabled = 0

I added a where clause to the enable script so that if you run them together, you will get all the jobs you should re-enable next time.

Please don’t stop the music…while I add the last day of the month to a table


I have this table that Finance uses to tell what the last day of any given month is in their reports. Through people coming and going, the keeper of this table didn’t keep it and the updates to it stopped.  Eventually, reports that used it stop moving forward as the dates didn’t exist.  I decided to create a job to update this table so it wouldn’t matter if someone forgot to update it.  It automatically will update each month (thank you SQLAgent).  But I was still left with the small problem of determining the last day of the month.  I found that the table had the last day of the month before it, so I am using that table to get the my next month end date.  The current table has two columns, the MonthEndDate and the Date that entry was created. I turned this select into an insert and ta-da we have the last date of the next month.

I add a day to the month end day so I get the first day of the next month.  Then I add a month to that and subtract to the day before the first of that month.

For Example:

MAX(MonthEndDate) = ‘5/30/2014’

Add a day and you get ‘6/1/2014’

Add a month and you get ‘7/1/2014’

Subtract a day and you get ‘6/30/2014’

SELECT DATEADD(day,-1,(DATEADD(m,1,(DATEADD(day, 1,MAX(MonthEndDate)))))),GETDATE()

FROM [dbo].[MonthEndDates]

*Update* Todd commented and showed me that you can just do this in SQL 2012:


That is super awesome!  Thank you for sharing Todd!


Conditional Formatting in Reporting Services

I’m certainly no Reporting Services master, and far from a Business Intelligence Developer, but I do like to tinker around with reports for the bosses from time to time

I like Excel for it’s conditional formatting feature, especially in building heat-map type charts.

So I was looking for a way to replicate the functionality in SSRS and figured I’d pass it on:

Just as an example, boss comes and asks “Hey, what’s the busiest time of day for our order-entry website?”

This is easily accomplished by changing the Properties of the cell ( in my case textbox2 ) Fill > BackgroundColor > and click on Expression…



From here it is pretty intuitive… I select a color from the values field and using the SWITCH function can apply green for < 100,  yellow for 100 – 500, red for 500-1000, etc.

I end up using the more colors link down right to select a visually appealing color range.


This technique can also be used to create alternating background colors that makes a report more readable as used here…


It’s not magic, but logic. However, as Andrea says “Have a magical day!”

Free tools are priced right…

Isn’t it great when you work for a company that buys you all the nifty, expensive tools and gives you a handsome Christmas bonus to boot?  I remember the days of having a dozen instances of Quest Spotlight on SQL Server monitors, LiteSpeed, Redgate, you name it.  But these days, times seem to be a bit tighter.  I’m always looking for a good deal on SQL tools and wouldn’t you know… free tools are prices right!

What is your favorite free SQL tool?  Here are a couple of mine:

ClearTrace      Many Thanks to Bill Graziano for a fantastic way to quickly analyze SQL Profiler Trace files.  Slick as a whistle, find your expensive batches or statements by Application Name, Login Name, Normalized SQL Text or Host Name. I am worthless as a DBA without it… I am a hero with it!  He even has an online version now.

SMSS Tools Pack     This is new to me, but my colleague just passed it forward.  Thank you Mladen Prajdic for the add-in to Management Studio.  One of the features I already use is Windows Connection Coloring, a handy tool to know what type of server you are connected to (for me red=prod, orange =staging and test, green=dev and local).  Just a little indication to keep you from getting yelled at by mistakenly connecting to the wrong environment!  Query Execution History is a lifesaver when you’ve spent a bunch of time writing code and close your window without saving the script.  SQL Code Snippets are fantastic.

Hope you have a chance to give these tools a shot.  Let me know what your favorite free SQL tool is, I’d like to get some in my Christmas Stocking!

~Todd Carrier