Find SQL Server Agent job history retention with T-SQL

In SQL Server, the Job Agent has its own retention that you can set. This retention determines how much execution history of SQL Server Agent jobs is being kept. But if you have a lot of instances across your domain, or if you reinstall a new server, how do you determine if this value is set correctly?

 
Doing it old-school: via the GUI
You can check the SQL Server agent retention via the GUI, by right-clicking on the SQL Server Agent, and clicking on properties. In the window that popped-up, click on History, and you’ll see something like this:

 
T-SQL / Registry
The way I wanted to check it is with T-SQL. Because I want to run the statement against a 40+ instances, I use a multi-server query for that. The reason I do that, is that this returns all the results in a single table, which makes reviewing and comparing the results a lot easier.

But there doesn’t seem to be a way to query the set values. But with a little bit of searching, I found out that you use the stored procedure below to change one of the values:

EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows = 999

 
So by dissecting this procedure, I found out SQL Server just reads the value from a registry key. I took out the piece that I need to read the retention values:

DECLARE @jobhistory_max_rows INT = NULL,
        @jobhistory_max_rows_per_job INT = NULL


EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                        N'JobHistoryMaxRows',
                                        @jobhistory_max_rows OUTPUT,
                                        N'no_output'
SELECT @jobhistory_max_rows = ISNULL(@jobhistory_max_rows, -1)


EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                        N'JobHistoryMaxRowsPerJob',
                                        @jobhistory_max_rows_per_job OUTPUT,
                                        N'no_output'


SELECT @jobhistory_max_rows, @jobhistory_max_rows_per_job

 
But when I wanted to look up the values in the registry, to see if it returned the correct results, I couldn’t find the actual keys. I thought I was losing my mind, but there’s a catch…

 
xp_instance_regread vs xp_regread
There are 2 stored procedures you can use to read registry keys from your SQL Server:

DECLARE @jobhistory_max_rows INT = NULL

EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                        N'JobHistoryMaxRows',
                                        @jobhistory_max_rows OUTPUT,
                                        N'no_output'

SELECT @jobhistory_max_rows

 
or

DECLARE @jobhistory_max_rows INT = NULL

EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                        N'JobHistoryMaxRows',
                                        @jobhistory_max_rows OUTPUT,
                                        N'no_output'

SELECT @jobhistory_max_rows

 
The difference between the two is perfectly explained in this Stack Overflow thread:

xp_regread reads the literal registry path that you specify. xp_instance_regread “converts” the path you specify so that it matches the instance of SQL Server that you’re currently using.

 
So if you run multiple instances on your machine, you want to use the instance version of the stored procedure. If you’re just running 1 (default) instance, you can use both versions.

So eventually, I searched for the instance folder, and I did find the registry keys:

 
Conclusion
Especially for very large environments, it’s a good idea to write system- and/or server-checks in T-SQL. That way, you can script them in SCOM (or another monitoring tool), or if you use SQL Server Policy Based Management (and I would really like to recommend this Pluralsight course from a very good friend of mine: Boris Hristov (Blog | @BorisHristov)!), or just a multi-server query.

How to determine SQL Server uptime?

Determining the SQL Server uptime can be difficult. Because SQL Server is a Windows service that can be stopped and started without restarting the OS, the uptime of your SQL Server can be completely different compared to your server uptime. So how do you determine both uptimes from within SQL Server?

 
tempdb
One of the ways to determine the last restart of SQL Server, is by looking at the tempdb. Because the tempdb is recreated on SQL Server startup, you could get an indication of the uptime of your SQL Server, by querying the creation date:

SELECT create_date AS START_TIME_INSTANCE FROM sys.databases WHERE name = 'tempdb'

 
SQL Server error log
In the SQL Server error log, the startup time is stored on a regular basis, together with a process ID. This information can be retrieved in 2 ways. You can look for either the process id event:

DECLARE @XREL TABLE
    (LogDate DATETIME,
     ProcessInfo VARCHAR(100),
     Text VARCHAR(MAX))

/* Insert current log */
INSERT INTO @XREL
EXEC xp_readerrorlog
 
/* Insert previous log */
INSERT INTO @XREL
EXEC xp_readerrorlog 1
 
SELECT TOP 1 *
FROM @XREL AS X
WHERE X.TEXT LIKE '%Server process ID is%'
ORDER BY LogDate DESC

 
or look for the informational message regarding the process ID:

DECLARE @XREL TABLE
    (LogDate DATETIME,
     ProcessInfo VARCHAR(100),
     Text VARCHAR(MAX))

/* Insert current log */
INSERT INTO @XREL
EXEC xp_readerrorlog
 
/* Insert previous log */
INSERT INTO @XREL
EXEC xp_readerrorlog 1
 
SELECT TOP 1 *
FROM @XREL AS X
WHERE X.Text LIKE '%This instance of SQL Server has been using a process %'
ORDER BY LogDate DESC

 
In the first query, you can look at the LogDate. In the second query, you need to extract the datetime from the Text column.

 
sysprocesses
Another way to find out the startup time, is by looking at the view sys.sysprocesses. This contains information about running processes. And when you look at SPID 1 (system process), you’ll find the startup time of SQL Server:

SELECT
  login_time AS START_TIME_INSTANCE
FROM sys.sysprocesses
WHERE spid = 1

 
sys.dm_os_sys_info
The last possibility for SQL Server uptime I want to share is querying the sys.dm_os_sys_info view. Looking at MSDN, this view contains “a miscellaneous set of useful information about the computer, and about the resources available to and consumed by SQL Server.” Also, the instance startup time:

SELECT sqlserver_start_time AS START_TIME_INSTANCE
FROM sys.dm_os_sys_info

 
Server startup
Not only SQL Server uptime can be important, but also the server uptime (the hardware on which SQL Server runs). But if you run Windows 8 or Windows Server 2012, this isn’t always accurate. But you can retrieve the accurate with T-SQL:

SELECT
  DATEADD(MILLISECOND, (sample_ms * -1), GETDATE()) AS BOOT_TIME_MACHINE
FROM sys.dm_io_virtual_file_stats(DB_ID(N'tempdb'), 2)

 
Dashboard Report
Another way to retrieve the server startup time (without T-SQL, and without using the event viewer in the OS or other tools), is to use the SQL Server Dashboard Report. You can view this report, by right-clicking on the servers name in SQL Server Management Studio (SSMS), and select Reports -> Standard Reports -> Server Dashboard. If you look at the report, in the left table you’ll see the Server Startup time.

SQL Sentry Plan Explorer: You can’t live without it

Every data professional out there will run into slow running queries, or performance issues you can’t explain at some point. At that moment, it’s difficult to explain the problem without looking at an execution plan. SQL Server Management Studio (SSMS) has build-in functionality to look at these execution plans. But this isn’t always as useful as we would like it to be. But there is a great free tool that’ll help you with query-tuning and pinpointing the issue in bad performing queries.

 
Download
SQL Sentry Plan Explorer is free, and available on the website of SQL Sentry. Even though it says it’s a trial version, it won’t expire after a certain period. The only thing that’s “trial” in this version, is that some functionality is blocked in the free version. But all the good stuff is available in the free version.

 
Integration in SSMS
When you start the install, the install doesn’t ask you to shut down SSMS. But I recommend you do. If you don’t close SSMS, you won’t see the SSMS add-in menu. It will show after the setup is finished, and you start a new instance of SSMS.

 
Creating a query, and opening it in Plan Explorer
As an example, I’ve created a really bad query on the Adventureworks2012 database:

USE AdventureWorks2012
GO


DECLARE @MinPrice INT = -1;


WITH Shipping AS
(
SELECT
  PV.ProductID AS ProductID,
  UM.Name AS ShippingPer,
  CASE
    WHEN UM.Name = 'Each' THEN PV.StandardPrice
    WHEN UM.Name = 'Dozen' THEN PV.StandardPrice / 12
    ELSE @MinPrice
  END AS ShippingCostPerUnit
FROM Purchasing.ProductVendor AS PV
INNER JOIN Production.UnitMeasure AS UM ON UM.UnitMeasureCode = PV.UnitMeasureCode
)


SELECT
  P.ProductID,
  P.ProductNumber,
  P.Name,
  S.ShippingCostPerUnit,
  Quantity.TotalQuantity,
  P.ListPrice,
  dbo.ufnGetProductListPrice(P.ProductID, GETDATE()) AS XYZ,
  Locations.TotalLocations,
  P.ListPrice + S.ShippingCostPerUnit AS TotalCostProduct,
  Quantity.TotalQuantity * P.ListPrice AS TotalValueStock,
  ((Quantity.TotalQuantity * P.ListPrice) / Locations.TotalLocations) AS AverageValuePerLocation
FROM Production.Product AS P
INNER JOIN Shipping AS S ON S.ProductID = P.ProductID
CROSS APPLY
(
  SELECT SUM(Quantity) AS TotalQuantity
  FROM Production.ProductInventory
  WHERE ProductID = P.ProductID
  GROUP BY ProductID
) AS Quantity
CROSS APPLY
(
  SELECT COUNT(LocationID) AS TotalLocations
  FROM Production.ProductInventory --WITH(INDEX(0))
  WHERE ProductID = P.ProductID
) AS Locations
WHERE P.ListPrice <> 0
ORDER BY P.ProductID, P.ProductNumber, P.Name, TotalLocations ASC

 
If you run this query in SSMS, and you include the actual execution plan (Ctrl + M), it will show you the execution plan in a separate result window. In this window, you’ll have the option to right-click, and choose “View with SQL Sentry Plan Explorer”:

 
If you click this, you’ll open Plan Explorer, and it will show you the execution plan:

 
So, is that all?
I can almost hear you think: So what’s the difference between Plan Explorer and the default SSMS windows, besides the fancy colors? Just take a look at all the extra opportunities you get with Plan Explorer. For example, how does your join diagram look? Can you pull that from SSMS? No? Well I can do that with Plan Explorer:

 
Your most expensive operation in the query? Yes, you could do that by looking at the percentages shown in your queryplan. But can you show me why they are that expensive? Again, I can do that with Plan Explorer:

 
Can you do you job without it?
If I ask myself this question, I think I can honestly answer this with: yes. Yes, I can do my job without it. But this makes it SO much easier to pinpoint the problem, and to get a quick overview of the query performance. Normally I look at the queryplan in SSMS first, and then immediately open up a Plan Explorer window, to take a closer look at the problems.

So if you write queries on a daily basis, and you’re responsible for, or interested in, qery performance: download it today, and try it out yourself. I’ll promise you, you won’t regret downloading it!
If you want to read more about SQL Sentry Plan Explorer, don’t forget to check out these blog posts:

Julie Koesmarno: Analysing Execution Plans With SQL Sentry Plan Explorer
Mickey Stuewe: On sabbatical
Chris Yates: SQL Sentry Plan Explorer – Don’t Leave Home Without It

Restoring or Moving a database with CDC enabled

When you have CDC enabled on your database, and you want to move it on another instance or restore it somewhere, you need to take some extra steps to make sure CDC is transferred correctly. When asking myself the question how this works, I decided to do some testing.

 
Create test resources
In order to test this, we need to create some test resources. I’ve created a new database, added a table and enabled CDC:

CREATE DATABASE [CDC_DB]
 ON PRIMARY
(NAME = N'CDC_DB', FILENAME = N'C:\Databases\#Backup\CDC_DB.mdf')
 LOG ON
(NAME = N'CDC_DB_log', FILENAME = N'C:\Databases\#Backup\CDC_DB_log.ldf')
GO

USE CDC_DB
GO

CREATE TABLE dbo.Customer
  (CustomerID INT CONSTRAINT PK_Customer PRIMARY KEY IDENTITY(1,1),
   FirstName VARCHAR(50),
   LastName VARCHAR(50))
GO

EXEC sys.sp_cdc_enable_db
GO

EXEC sys.sp_cdc_enable_table
  @source_schema = 'dbo',
  @source_name = 'Customer',
  @supports_net_changes = 1,
  @role_name = NULL,
  @index_name = 'PK_Customer'
GO


INSERT INTO dbo.Customer
  (FirstName, LastName)
VALUES
  ('John', 'Doe')
GO

Now that we have the resources, we can take a backup of the database:

BACKUP DATABASE CDC_DB TO DISK = 'C:\Databases\#Backup\CDC_DB.bak'

 
Restoring the backup
To show you the default restore doesn’t work, let’s restore the backup next to the original database:

RESTORE DATABASE CDC_DB_RESTORE FROM DISK='C:\Databases\#Backup\CDC_DB.bak'
WITH
   MOVE 'CDC_DB' TO 'C:\Databases\#Backup\CDC_DB.mdf',
   MOVE 'CDC_DB_log' TO 'C:\Databases\#Backup\CDC_DB_log.ldf'

If we now try to select data from the change table of CDC, we get an error message:

 
But CDC was in the old database, so why wasn’t it restored? If you look for a solution, Books Online / MSDN doesn’t say anything about this. But luckily other community members blogged about this before (see this and this article). There is an option you can add to your restore:

USE master
GO

DROP DATABASE CDC_DB_RESTORE
GO

RESTORE DATABASE CDC_DB_RESTORE FROM DISK='C:\Temp\#BackupTest\CDC_DB.bak'
WITH
   MOVE 'CDC_DB' TO 'C:\Temp\#BackupTest\CDC_DB.mdf',
   MOVE 'CDC_DB_log' TO 'C:\Temp\#BackupTest\CDC_DB_log.ldf'
, KEEP_CDC
GO

 
If you add “KEEP_CDC” to your backup statement, it will also restore CDC. But that’s not your only option.

 
Detach and Attach

Another option you have is to detach your database, copy the files to another location, and attach the database again. Let’s detach the database first:

USE master
GO
ALTER DATABASE [CDC_DB_RESTORE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC master.dbo.sp_detach_db @dbname = N'CDC_DB_RESTORE'
GO

 
Now that’s done, we can copy the files to another directory if needed. You could also just attach it on the same instance:

USE master
GO
CREATE DATABASE [CDC_DB_RESTORE] ON 
( FILENAME = N'C:\Temp\#BackupTest\CDC_DB.mdf' ),
( FILENAME = N'C:\Temp\#BackupTest\CDC_DB_log.ldf' )
 FOR ATTACH
GO

 
This also allows you to use CDC like nothing happened. The database is still CDC enabled, the change tables are there, and the captured data is still in the change table. But there’s one thing missing when you move your CDC database to another instance: the capture and cleanup jobs.

 
CDC Jobs
So how do we recover the jobs? Script them from one instance, and create them on the other instance? There’s a better way to do that. You can just run the statement below to add the jobs to your instance:

USE CDC_DB_RESTORE
GO
EXEC sys.sp_cdc_add_job 'capture'
GO
EXEC sys.sp_cdc_add_job 'cleanup'
GO

 
And if you move your database, you can also clean your instance, and run the statement below to remove the leftover jobs:

USE CDC_DB_RESTORE
GO
EXEC sys.sp_cdc_drop_job @job_type = N'capture'
GO
EXEC sys.sp_cdc_drop_job @job_type = N'cleanup'
GO

 
It is possible, but…
Moving or restoring a database with CDC gives you a few challenges, and even though there are some issues, it’s not impossible. But before you start, you need to think about the approach you need to take, in order to move/restore a database without losing CDC. Depending on your environment and possibilities, you might want to use a specific option, or perhaps can’t use the option you would like. There is a way around, but I would recommend you to test it first, before you run this on production databases!

Using FOR XML in a CROSS APPLY

A few blog posts ago, I showed how easy it is to generate JSON, JavaScript or arrays with the FOR XML clause. But in the examples, the output is transformed to text. Selecting this output is the same as a normal select. But what if you really want to output XML? Does this work the same? Let’s create the same example table again, and rebuild it to output XML.

 
Creating resources
Just as in my last blog post, let’s use the airline example:

CREATE TABLE #TMP_AirlineTickets
  (ID INT IDENTITY(1,1),
   Airline VARCHAR(10),
   Departure_Airport_Code VARCHAR(5),
   Price DECIMAL(10,4))


INSERT INTO #TMP_AirlineTickets
  (Airline, Departure_Airport_Code, Price)
VALUES
  ('BA', 'RTM', 10.00),
  ('KLM', 'AMS', 125.00),
  ('BA', 'LHR', 15.00),
  ('KLM', 'BCN', 50.00),
  ('KLM', 'BHX', 75.00)

 
When you write a query with a CROSS APPLY on this table, it works like it’s supposed to:

SELECT AT.Airline, CA.Departure_Airport_Code
FROM #TMP_AirlineTickets AT
CROSS APPLY
	(
		SELECT Departure_Airport_Code
		FROM #TMP_AirlineTickets A
		AND A.Airline = AT.Airline
	) CA
GROUP BY AT.Airline, CA.Departure_Airport_Code

 
There is a DISTINCT or GROUP BY needed in this query, to return only the unique records. As we’ll see later on, DISTINCT doesn’t work for this query, so a GROUP BY is used. This results in a dataset where every Airline code is joined to every airport they fly to:

 
Generating XML in CROSS APPLY
But in the previous post, we got a string as output. But now, we want to return XML instead of a normal resultset. But what happens if we put the FOR XML clause in this query? If we do so, it throws an exception:

SELECT AT.Airline, CA.Departure_Airport_Code
FROM #TMP_AirlineTickets AT
CROSS APPLY
	(
		SELECT Departure_Airport_Code
		FROM #TMP_AirlineTickets A
		AND A.Airline = AT.Airline
		FOR XML PATH('')
	) CA
GROUP BY AT.Airline, CA.Departure_Airport_Code

 

 
Apparently column 1 of the CROSS APPLY result has no column name. Looking at the result that comes from the CROSS APPLY, the column name is generated automatically:

 
Giving a column alias to the column in the select doesn’t work. But you can also provide a column list to a cross apply:

SELECT AT.Airline, CONVERT(XML, CA.Airport_XML) AS Airport_XML
FROM #TMP_AirlineTickets AT
CROSS APPLY
    (
        SELECT Departure_Airport_Code AS DAC
        FROM #TMP_AirlineTickets A
        AND A.Airline = AT.Airline
        FOR XML PATH('')
    ) CA (Airport_XML)
GROUP BY AT.Airline, CA.Airport_XML

 
By adding a column list to the CROSS APPLY, we can now use the column name Airport_XML in the outer select. By doing that, we get the airline name, and the departure airport code list:

 
Returning XML
So when you want to generate XML in a query, it’s possible to do that with a CROSS APPLY. It’s just a easy as writing a normal query, but it makes you a lot more flexible when you need to output XML from SQL Server. With this, there is no need for CLR’s or external applications to generate the XML you need, but you can just do it with T-SQL.

Using OUTPUT clause to track changes

Whenever something changes in a database, you would like to know what has changed. This change might brake your database or application later, so all schema or server changes are monitored. But how about data changes? Do you monitor those? You don’t have to feel bad if you don’t, because most people don’t track those changes. But you can track these changes in a very easy way, by using the output clause in your query. Let’s take a look at how that works.

 
Create resources
As example, let’s take a look at a bank account. If you have a bank account, there’s some money in it, and you spend money which will be subtracted from your account balance. First of all, let’s make two tables: one for the account balance, and one for logging the changes:

CREATE TABLE #Account
  (ID INT IDENTITY(1,1),
   Balance FLOAT)


CREATE TABLE #Logging
  (DEL_ID INT,
   DEL_Balance FLOAT,
   UPD_ID INT,
   UPD_Balance FLOAT,
   Balance_Difference FLOAT)

 
To keep it simple, the account table has just one column, and that column contains the current balance of the account. Let’s insert a balance to start with:

INSERT INTO #Account
  (Balance)
VALUES
  (250.25)

 
If we run an update query now, the account balance will change. But do we still know what the original balance was? Not without logging. So let’s see how the output clause can change this:

UPDATE #Account
SET Balance = 125.00
OUTPUT Deleted.ID,
       Deleted.Balance,
       Inserted.ID,
       Inserted.Balance,
	   Inserted.Balance - Deleted.Balance
INTO #Logging

 
The balance table now only contains the new value. But by using the output clause, that logged the changes we made, we can check the logging table, and find the old value. When looking at the output clause, you can use the temporary objects called Deleted and Inserted. The Deleted table contains the old values (before the change), and the Inserted contains the new values (the values after the change). When checking the logging table, you’ll see both the old and the new values:

 
So as you can see, the logging table contains both the old and the new version of the record. And this not only works for update queries, but also for Inserts, Deletes and Merge-statements. This gives you the opportunity to log changes in the queries you think need it. Just think about what you want to log, create a log table, and add an output clause to your query. It’s not always the case, but with this, it’s as easy as that.

 
If you want to read more about CRUD, don’t forget to check out these blog posts:

Julie Koesmarno: On sabbatical
Mickey Stuewe: Going Beyond The INSERT Statement
Chris Yates: C-R-U-D The Basics

Follow

Get every new post delivered to your Inbox.

Join 60 other followers