Kill all connections to SQL database

Ever try to rename a database and not be able to grab an exclusive lock? I find this system SPROC does the trick for me.

SQL Agent Job Reporting - alerts for jobs that haven't ran for a while

When SQL Agent jobs run they produce a wealth of information in msdb. You can query that data and tell what jobs have failed, when, and with what severity.

Unfortunately if a job is scheduled wrong there's not a lot you can do. If an important weekly job never even starts you can forget about that wealth of error information.

So I came up with a way to know about all this:

  1. Pick a database and create a table in it containing the jobs you want to be sure run at certain intervals. Said database would have access to query msdb on the instance housing the SQL Agent Jobs.
  2. Populate that table with data - including the number of hours you can tolerate between runs
  3. Create a procedure that queries the new table and e-mails an alert regarding any SQL Agent Jobs that haven't ran within the acceptable number of hours.
  4. Schedule the procedure to run daily and look for the e-mail. If it's empty that's a good thing!
Step 1 - Pick a database and create a table

I picked "RCBdatabase" because it's a database in the same instance as where the jobs I care about are. Therefore it can easily access the all-important msdb system database which has the data I'm going after.

My table will have a uniqueidentifier for the job_id, a friendly name, contact information for someone I can frantically e-mail, and the maximum amount of time, in hours, that I can tolerate for the delays between the job being ran.

Step 2 - Populate that table

This'll do for now. In a nutshell you're inserting every job and specifying they all should run at least within the last 168 hours (or one week - 7 times 24). The "recipientsbysemicolon" column just identifies a business owner and mayhap can be leveraged automatically later on. Note the use of the LEFT JOIN so I'm only grabbing jobs that have a recent history.

Step 3 - Create a procedure

There's a great code sample out on msdn here.

Step 4 - Schedule the procedure to run

Couple of things worth calling out:

  • msdb.dbo.agent_datetime is a godsend because these msdb tables store the date/time data as, what I would consider, inconvenient data types.
  • COALESCE is used liberally. If the job has never ran or isn't scheduled you'll want to know about that too.
  • Check your job history retention settings. If you have the default settings configured you may be limited to querying only the last 1000 rows or so which can fill up fast.
This won't tell you when jobs fail - only if there isn't results information (i.e. neither success nor failure). The point to this is that if, say, a schedule associated with an important job gets deleted or disabled you won't merrily go on thinking all is right with the world.


Get Overview of Indexes on a Table

SSMS is always giving me index suggestions for slow queries , but doesn't take into account anything else running on the system, how useful the index would be beyond just that query, or whether there's an existing index that would do a "good enough" job with substantially less upkeep.
    Before adding indexes I have to remember:
  • Is there an existing index with the same left edge key column(s)?
  • What columns are included in the leaf level?
  • Could I tweak existing indexes to take advantage of index intersection?
I'll need to revisit this because the display format is not...quite right, but it sure beats going into index properties in Object Explorer.



Scheduling SQL statements in the future

Almost ashamed to admit this, but when running queries off-hours on short notcie that can't be tested beforehand I resort to WAITFOR rather than drinking enough coffee to get me out of bed at whatever god awful hour.

Say I want to take a quick-and-dirty backup of a table that will be undergoing massive DML changes in the middle of the night.

If I know they want a copy at a minute past midnight I can run something like this from a query window up to 23 hours 59 minutes in advance of when they would actually want the statement to execute::


WAITFOR is awesome. Allows you to set an exact time to execute, time to wait, and numerous other options. More information is available here, so check it out. Note that I'm not affiliated or endorsed by this link in any way (standard disclaimer).

I realize there's other, better, ways to do this like scheduling SQL Agent Jobs, but I find that often when the chips are down and there's no dress rehearsal good ole WAITFOR is sometimes the simplest option when you need something that just works.


Performing a tail log backup with T-SQL

Always needing to migrate non-production databases, so I figured I would do a quick post about the simple syntax for tail log backups.

Quick refresher - a tail log backup backs up the transaction log and puts the database in "recovery" mode so that it's inaccessible to further manipulation.  The idea is that you're backing up the most recent changes to the database and not allowing further changes.  If hit with a catastrophic database failure you would want to attempt to take a tail log backup in order to get the absolute latest version of the database.

So easy:

Be sure your Management Studio is active in the master database - otherwise your own SQL Server Management Studio session may prevent you from performing it.

Note:  Not applicable to databases in "Simple" recovery mode - their transaction logs are in auto-truncate anyway, so you don't have as many options with restore points.