Querying msdb for job history


I recently had the need to create an SSRS report showing the status of some jobs from the 10 last days, so I wrote this script that I thought could come in handy to others as well. The contents should be pretty self explanatory except maybe for the RowNum column which is there to separate each iteration of the job:

WITH cte_jobhistory AS (
		--> Creating a cte only to make the columns run_date and run_time into a "datetime-friendly" format  
		--  so it can be used for filtering
		SELECT 
			RunDateString = SUBSTRING(CAST(run_date as VARCHAR(20)), 1, 4) + '-' +  SUBSTRING(CAST(run_date as VARCHAR(20)), 5, 2) + '-' + SUBSTRING(CAST(run_date as VARCHAR(20)), 7, 2),
			RunTimeString = SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 1, 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 3, 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 5, 2),
			*
		FROM msdb.dbo.sysjobhistory
		)
SELECT 
	a.name,
	RowNum = ROW_NUMBER() OVER (PARTITION BY a.name, b.step_id ORDER BY instance_id DESC),
	b.step_id,
	b.step_name,
	b.message,
	RunStatus = CASE b.run_status 
		WHEN 0 THEN 'Failed'
		WHEN 1 THEN 'Succeded' 
		WHEN 2 THEN 'Retry' 
		WHEN 3 THEN 'Cancelled' 
		WHEN 4 THEN 'In Progress' 
		END,
	b.run_duration,
	b.RunDateString,
	b.RunTimeString
FROM msdb.dbo.sysjobs a
	INNER JOIN cte_jobhistory b
		ON a.job_id = b.job_id
WHERE CONVERT(datetime, b.RunDateString) > GETDATE()-10
ORDER BY a.name ASC, b.instance_id DESC
Advertisements

Updated: create point-in-time restore script automatically


The script I created for generating a point-in-time restore t-sql code has been updated and the last version should support sql server 2005, 2008 and 2012 versions.

There is one prerequisite:
– Only backups made to file on disk is supported

Here is the final script:

/*
Generate Point-in-time restore script, given only DBName and Point-in-time timestamp
The script WILL NOT do any changes to your databases, it will only generate sql code 
that you need to manually execute.
 
Created by Henning Frettem, www.thefirstsql.com
*/
SET NOCOUNT ON
GO
 
DECLARE
  @DBName varchar(200),
  @PointInTime datetime,
  @Filename varchar(200),
  @tab varchar(1) = char(9),
  @cr varchar(2) = char(13)+char(10),
  @Full_BackupStartDate datetime,
  @Diff_BackupStartDate datetime,
  @Log_BackupStartDate datetime,
  @SQL varchar(max) = ''
 
SET @DBName  = 'MyDatabase'
SET @PointInTime = '2013-05-21 15:40:00'
 
BEGIN TRY
  --> Performing some checks
  IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName)
    RAISERROR(N'The database has never been backed up', 16, 1)
  IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND type = 'D' AND backup_start_date <= @PointInTime)
    RAISERROR(N'No full backup exists prior to the specified PointInTime', 16, 1)
  ELSE IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND type = 'L' AND backup_start_date > @PointInTime)
    RAISERROR(N'No backup of the log exist after the specified PointInTime, do a tail log backup first', 16, 1)
 
  --> Getting the filename and the date of the last full backup prior to the PointInTime
  SELECT TOP 1 
      @Filename = b.physical_device_name,
      @Full_BackupStartDate = backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'D'
    AND a.backup_start_date <= @PointInTime
  ORDER BY a.backup_start_date DESC
 
  SET @SQL = 'USE master' + @cr + 
    'GO' + @cr + @cr +
    'ALTER DATABASE ' + QUOTENAME(@DBName) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE' + @cr + 
    'GO' + @cr + @cr
  
  PRINT @SQL
  SET @SQL = ''
 
  --> Restore the last full backup if it exists
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE DATABASE ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', REPLACE, STATS=5, NORECOVERY' + @cr + 'GO' + @cr,
    @Diff_BackupStartDate = a.backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'D'
    AND a.backup_start_date = @Full_BackupStartDate
  ORDER BY a.backup_start_date DESC   

  PRINT @SQL
  SET @SQL = ''
   
  --> Restore the last differential backup if it exists
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE DATABASE ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', NORECOVERY' + @cr + 'GO',
    @Diff_BackupStartDate = a.backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'I'
    AND a.backup_start_date > @Full_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date DESC
  
  PRINT @SQL
  SET @SQL = ''
   
  IF @Diff_BackupStartDate IS NULL
    SET @Diff_BackupStartDate = @Full_BackupStartDate
     
  --> Generate all log restores except the last one
  SELECT
    @SQL = @SQL + 'RESTORE LOG ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', NORECOVERY' + @cr + 'GO' + @cr,
    @Log_BackupStartDate = a.backup_start_date   
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @Diff_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date
 
  PRINT @SQL
  SET @SQL = ''

  --> Generate last log restore script with the stopat command and recovery
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE LOG ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', RECOVERY, STOPAT = ''' + CONVERT(varchar(20), @PointInTime, 120) + '''' + @cr + 'GO' + @cr
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @PointInTime
  ORDER BY a.backup_start_date ASC
 
  PRINT @SQL
 
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE()
END CATCH

Create a point-in-time restore automatically – SQL 2005 version


A new version has been created! Check it out here: Updated: create point-in-time restore script automatically

This is a 2005-version of the script originally posted here: Create a full Point-In-Time restore script automatically! – 2008 version

/*
Generate Point-in-time restore script, given only DBName and Point-in-time timestamp
Works only on SQL2005 databases!!

Script for generating restore script for full backup originally created by Michael 
Valentine Jones, SQLTeam.com

The rest is created by Henning Frettem, www.thefirstsql.com
*/
SET NOCOUNT ON
GO

DECLARE 
  @DBName varchar(200),
  @PointInTime datetime,
  @Filename varchar(200),
  @tab varchar(1),
  @cr varchar(2),
  @Full_BackupStartDate datetime,
  @Diff_BackupStartDate datetime,
  @Log_BackupStartDate datetime,
  @SQL nvarchar(max)
  
SET @DBName = 'MyDatabaseName'
SET @PointInTime = '2011-11-22 09:45:00'
SET @tab = char(9)
SET @cr = char(13)+char(10)
SET @SQL = ''


BEGIN TRY
  --> Performing some checks
  IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND type = 'D' AND backup_start_date <= @PointInTime)
    RAISERROR(N'No full backup exists prior to the specified PointInTime, restore is not possible at the given PointInTime.', 16, 1)
  ELSE IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND type = 'L' AND backup_start_date > @PointInTime)
    RAISERROR(N'No backup of the log exist after the specified PointInTime, do a tail-log backup before running this script again', 16, 1)

  --> Getting the filename and the date of the last full backup prior to the PointInTime
  SELECT TOP 1 
    @Filename = b.physical_device_name,
    @Full_BackupStartDate = backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'D'
    AND a.backup_start_date <= @PointInTime
  ORDER BY a.backup_start_date DESC

  --> Create temp-tables for file header information
  DECLARE @header table (
	  BackupName varchar(128) NULL,
	  BackupDescription varchar(255) NULL,
	  BackupType smallint NULL,
	  ExpirationDate datetime NULL,
	  Compressed tinyint NULL,
	  Position smallint NULL,
	  DeviceType tinyint NULL,
	  UserName varchar(128) NULL,
	  ServerName varchar(128) NULL,
	  DatabaseName varchar(128) NULL,
	  DatabaseVersion int NULL,
	  DatabaseCreationDate datetime NULL,
	  BackupSize numeric(20,0) NULL,
	  FirstLSN numeric(25,0) NULL,
	  LastLSN numeric(25,0) NULL,
	  CheckpointLSN numeric(25,0) NULL,
	  DatabaseBackupLSN numeric(25,0) NULL,
	  BackupStartDate datetime NULL,
	  BackupFinishDate datetime NULL,
	  SortOrder smallint NULL,
	  CodePage smallint NULL,
	  UnicodeLocaleId int NULL,
	  UnicodeComparisonStyle int NULL,
	  CompatibilityLevel tinyint NULL,
	  SoftwareVendorId int NULL,
	  SoftwareVersionMajor int NULL,
	  SoftwareVersionMinor int NULL,
	  SoftwareVersionBuild int NULL,
	  MachineName varchar(128) NULL,
	  Flags int NULL,
	  BindingID uniqueidentifier NULL,
	  RecoveryForkID uniqueidentifier NULL,
	  Collation varchar(128) NULL,
	  FamilyGUID uniqueidentifier NULL,
	  HasBulkLoggedData bit NULL,
	  IsSnapshot bit NULL,
	  IsReadOnly bit NULL,
	  IsSingleUser bit NULL,
	  HasBackupChecksums bit NULL,
	  IsDamaged bit NULL,
	  BeginsLogChain bit NULL,
	  HasIncompleteMetaData bit NULL,
	  IsForceOffline bit NULL,
	  IsCopyOnly bit NULL,
	  FirstRecoveryForkID uniqueidentifier NULL,
	  ForkPointLSN numeric(25,0) NULL,
	  RecoveryModel varchar(60) NULL,
	  DifferentialBaseLSN numeric(25,0) NULL,
	  DifferentialBaseGUID uniqueidentifier NULL,
	  BackupTypeDescription varchar(60) NULL,
	  BackupSetGUID uniqueidentifier NULL,
	  Seq int NOT NULL identity(1,1)
    )

  --> Create temp-table for db file information
  DECLARE @filelist TABLE  (
	  LogicalName varchar(128) NULL,
	  PhysicalName varchar(260) NULL,
	  Type char(1) NULL,
	  FileGroupName varchar(128) NULL,
	  Size numeric(20,0) NULL,
	  MaxSize numeric(20,0) NULL,
	  FileID bigint NULL,
	  CreateLSN numeric(25,0) NULL,
	  DropLSN numeric(25,0) NULL,
	  UniqueID uniqueidentifier NULL,
	  ReadOnlyLSN numeric(25,0) NULL,
	  ReadWriteLSN numeric(25,0) NULL,
	  BackupSizeInBytes bigint NULL,
	  SourceBlockSize int NULL,
	  FileGroupID int NULL,
	  LogGroupGUID uniqueidentifier NULL,
	  DifferentialBaseLSN numeric(25,0) NULL,
	  DifferentialBaseGUID uniqueidentifier NULL,
	  IsReadOnly bit NULL,
	  IsPresent bit NULL,
	  Seq int NOT NULL identity(1,1)
    )

  --> Get header and filelist information from the backup file
  INSERT INTO @header
  EXEC ('RESTORE HeaderOnly FROM DISK = ''' + @Filename + '''')

  INSERT INTO @filelist
  EXEC ('RESTORE FilelistOnly FROM DISK = ''' + @Filename + '''')

  --> Generate the full backup restore script
  SELECT
    @SQL = @SQL + 
      CASE
        WHEN a.Seq = 1 THEN 
          @cr + 'RESTORE DATABASE [' + c.DatabaseName + ']' +
          @cr + 'FROM DISK =' + @cr + @tab + '''' +
          @Filename + '''' + @cr + 'WITH' 
        ELSE ''
      END
      + @cr + @tab + 'MOVE ''' + a.LogicalName + ''' TO ''' + a.PhysicalName + ''',' 
      +
      CASE 
        WHEN a.Seq = b.Seq THEN
          @cr + @tab + 'REPLACE, STATS = 5, NORECOVERY'
        ELSE ''
      END
  FROM
    @filelist a
    CROSS JOIN
      (SELECT Seq = MAX(b1.Seq) FROM @filelist b1 ) b
    CROSS JOIN
      (SELECT DatabaseName = MAX(c1.DatabaseName) FROM @header c1) c
  ORDER BY
    a.Seq
    
  SELECT @SQL = @SQL + @cr + 'GO' + @cr + @cr 
    
  --> Restore the last differential backup if it exists
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE DATABASE [' + @DBName + '] FROM DISK = ''' + b.physical_device_name + ''' WITH NORECOVERY' + @cr + 'GO',
    @Diff_BackupStartDate = a.backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'I'
    AND a.backup_start_date > @Full_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date DESC
  
  IF @Diff_BackupStartDate IS NULL
    SET @Diff_BackupStartDate = @Full_BackupStartDate
    
  --> Generate all log restores except the last one
  SELECT 
    @SQL = @SQL + 'RESTORE LOG [' + @DBName + '] FROM DISK = ''' + b.physical_device_name + ''' WITH NORECOVERY' + @cr + 'GO' + @cr,
    @Log_BackupStartDate = a.backup_start_date   
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @Diff_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date
  
  --> Generate last log restore script with the stopat command and recovery
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE LOG [' + @DBName + '] FROM DISK = ''' + b.physical_device_name + ''' 
      WITH RECOVERY, STOPAT = ''' + CONVERT(varchar(20), @PointInTime, 120) + '''' + @cr + 'GO' + @cr
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @PointInTime
  ORDER BY a.backup_start_date ASC

  PRINT @SQL

END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE()
END CATCH

Mirroring between two stand-alone servers using certificates


Mirroring is usually done between two servers inside the same network where both servers are a part of a domain and have a trusted authentication mechanism provided by Active Directory. But what if this is not the case? What if you have your mirroring partners in separate networks or in separate data centers even, where there is no trusted authentication possible?

There is still hope, and the solution is to use SQL Server certificates. Here are the scripts you need to set up mirroring between a PRINCIPAL and a MIRROR server using certificates.

PLEASE NOTE: you can not simply copy and paste these scripts in to SQL Server Management Studio, press F5 and expect everything to work. You need to run each individual statement by itself, and some times you will need to run scripts on the mirror before progressing on the principal (i.e you need to create and do a backup of the certificate on the mirror before importing it to the principal, obviously)

--> This is the setup of the PRINCIPAL (master) in a mirroring environment

USE master
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

--> Create a name not using "master" or "principal", remember: the roles can 
--  switch but the certificates remain the same. 
CREATE CERTIFICATE SQLSrv1_mirroring_cert 
   WITH SUBJECT = 'SQLSrv1 certificate',
      START_DATE = '2009-01-01 00:00:00',
      EXPIRY_DATE = '2030-01-01 00:00:00'
GO

CREATE ENDPOINT Mirroring_Endpoint
   STATE = STARTED
   AS TCP (
      LISTENER_PORT = 7024, 
      LISTENER_IP = ALL
   ) 
   FOR DATABASE_MIRRORING ( 
      AUTHENTICATION = CERTIFICATE SQLSrv1_mirroring_cert, 
      ENCRYPTION = REQUIRED ALGORITHM AES, 
      ROLE = ALL
   )
GO

--> Backup certificate and transfer to SQLSrv2
BACKUP CERTIFICATE SQLSrv1_mirroring_cert TO FILE = 'C:\SQLSrv1_mirroring_cert.cer';
GO

--> Create a login for the other server process
CREATE LOGIN SQLSrv2_mirroring_login WITH PASSWORD = 'password';
GO

--> Create a user for the new login 
CREATE USER SQLSrv2_mirroring_user FOR LOGIN SQLSrv2_mirroring_login;
GO

--> Associate the certificate with the user.
CREATE CERTIFICATE SQLSrv2_cert
   AUTHORIZATION SQLSrv2_mirroring_user
   FROM FILE = 'C:\SQLSrv2_mirroring_cert.cer'
GO

--> Grant the user access to the endpoint 
GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [SQLSrv2_mirroring_login]
GO

--> Create a FULL BACKUP of each database to be mirrored
BACKUP DATABASE mojo TO DISK = 'D:\SQLbackup\mojo_20090211.bak'

--> Restore these databases to the MIRROR using NORECOVERY

--> Set up partnering between the mirror and the principal (use a fully qualified name or an IP address)

--> Set up partnering between the principal and the mirror (use a fully qualified name or an IP address)
ALTER DATABASE mojo SET PARTNER ='tcp://sqlsrv2:7024'
GO
--> This is the setup of the MIRROR (slave) in a mirroring environment

USE master
GO

--> Make sure a master key is set 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

--> 
CREATE CERTIFICATE SQLSrv2_mirroring_cert 
   WITH SUBJECT = 'SQLSrv2 certificate',
      START_DATE = '2009-01-01 00:00:00',
      EXPIRY_DATE = '2030-01-01 00:00:00'
GO

CREATE ENDPOINT Mirroring_Endpoint
   STATE = STARTED
   AS TCP (
      LISTENER_PORT = 7024, 
      LISTENER_IP = ALL
   ) 
   FOR DATABASE_MIRRORING ( 
      AUTHENTICATION = CERTIFICATE SQLSrv2_mirroring_cert, 
      ENCRYPTION = REQUIRED ALGORITHM AES, 
      ROLE = ALL
   )
GO

--> Backup certificate and transfer to SQLSrv1
BACKUP CERTIFICATE SQLSrv2_mirroring_cert TO FILE = 'C:\SQLSrv2_mirroring_cert.cer';
GO

--> Create a login for the other server process
CREATE LOGIN SQLSrv1_mirroring_login WITH PASSWORD = 'password'
GO

--> Create a user for the new login 
CREATE USER SQLSrv1_mirroring_user FOR LOGIN SQLSrv1_mirroring_login;
GO

--> Associate the certificate with the user.
CREATE CERTIFICATE SQLSrv1_mirroring_cert
   AUTHORIZATION SQLSrv1_mirroring_user
   FROM FILE = 'C:\SQLSrv1_mirroring_cert.cer'
GO

--> Grant the user access to the endpoint 
GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [SQLSrv1_mirroring_login]
GO

--> Copy a backup of the database(s) to be mirrored

--> Restore them using NORECOVERY
RESTORE DATABASE mojo
   FROM DISK='d:\SQLBackups\mojo_20090211.bak'
   WITH NORECOVERY
GO

--> Set up partnering between the mirror and the principal (use a fully qualified name or an IP address)
ALTER DATABASE mojo SET PARTNER ='tcp://sqlsrv1:7024'
GO

How to monitor replication latency using code?


I while back I had the need for monitoring quite a few replicated databases, so I had to make a procedure to automate the monitoring process. This procedure will insert a tracer token for each publication and monitor how long it takes for the token to by committed at each subscriber.

CREATE PROCEDURE [dbo].[proc_replication_latency_monitor]
AS

-- Procedure created by Henning Frettem, www.thefirstsql.com

BEGIN

SET NOCOUNT ON

DECLARE 
  @PublishedDBName varchar(200),
  @SQL nvarchar(2000),
  @PublicationPK int, 
  @PublicationName varchar(200),
  @PublicationDatabase varchar(200),
  @TracerTokenID int,
  @Parmameters nvarchar(500)
  
DECLARE @Publications table (
  PublicationPK int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  PublicationDatabase varchar(200),
  PublicationName varchar(200),
  TracerTokenID int,
  TracerTokenPostTime datetime
)

DECLARE @Latency table (
  PublicationPK int,
  DistributorLatency bigint,
  Subscriber sysname,
  SubscriberDB sysname,
  SubscriberLatency bigint,
  OverallLatency bigint
)

--> Cursor for fetching all publications in all databases
DECLARE curPublishedDatabases CURSOR LOCAL FAST_FORWARD FOR 
  SELECT [name] FROM sys.databases WHERE is_published > 0
  
OPEN curPublishedDatabases
FETCH curPublishedDatabases INTO @PublishedDBName
WHILE @@FETCH_STATUS = 0
  BEGIN 
    SET @SQL = N'SELECT ''' + @PublishedDBName + ''', [name] FROM ' + @PublishedDBName + '.dbo.syspublications'
    INSERT INTO @Publications (PublicationDatabase, PublicationName)
    EXEC sp_executesql @SQL
    
    FETCH NEXT FROM curPublishedDatabases INTO @PublishedDBName
  END

CLOSE curPublishedDatabases
DEALLOCATE curPublishedDatabases 

--> Cursor for posting tracer tokens
DECLARE curPublications CURSOR LOCAL FAST_FORWARD FOR 
  SELECT PublicationPK, PublicationDatabase, PublicationName FROM @Publications

OPEN curPublications
FETCH curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName
WHILE @@FETCH_STATUS = 0
  BEGIN 
    SET @Parmameters = N'@PublicationName varchar(200), @TracerTokenID_OUT int OUTPUT';
    SET @SQL = N'EXEC ' + @PublicationDatabase + '.sys.sp_posttracertoken @publication = @PublicationName, @tracer_token_id = @TracerTokenID_OUT OUTPUT'
    EXEC sp_executesql @SQL, @Parmameters, @PublicationName = @PublicationName, @TracerTokenID_OUT = @TracerTokenID OUTPUT
  
    UPDATE @Publications SET 
      TracerTokenID = @TracerTokenID,
      TracerTokenPostTime = GETDATE()
    WHERE PublicationPK = @PublicationPK

    FETCH NEXT FROM curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName
  END
CLOSE curPublications
DEALLOCATE curPublications

--> Wait two minutes for all tokens to be commited at all subscribers
WAITFOR DELAY '000:02:00.000'

--> Then check the results for each posted token 
DECLARE curTokens CURSOR LOCAL FAST_FORWARD FOR 
  SELECT PublicationPK, PublicationDatabase, PublicationName, TracerTokenID FROM @Publications

OPEN curTokens
FETCH curTokens INTO @PublicationPK, @PublicationDatabase, @PublicationName, @TracerTokenID
WHILE @@FETCH_STATUS = 0
  BEGIN 
    --> Insert token history for each token 
    SET @SQL = N'EXEC ' + @PublicationDatabase + '.dbo.sp_helptracertokenhistory @publication = ''' + @PublicationName + ''', @tracer_id = ' + CAST(@TracerTokenID AS VARCHAR(50)) + ''
    INSERT INTO @Latency
      (DistributorLatency, Subscriber, SubscriberDB, SubscriberLatency, OverallLatency)
    EXEC sp_executesql @SQL

    --> Make sure that the PublicationPK is added to the token history
    UPDATE @Latency SET PublicationPK = @PublicationPK WHERE PublicationPK IS NULL
  
    --> Clean up the tracer token
    SET @SQL = N'EXEC ' + @PublicationDatabase + '.sys.sp_deletetracertokenhistory @publication = ''' + @PublicationName + ''', @tracer_id = ' + CAST(@TracerTokenID AS VARCHAR(50)) + ''
    EXEC sp_executesql @SQL

    FETCH NEXT FROM curTokens INTO @PublicationPK, @PublicationDatabase, @PublicationName, @TracerTokenID
  END
CLOSE curTokens
DEALLOCATE curTokens

SELECT 
  b.PublicationDatabase,
  b.PublicationName,
  a.Subscriber,
  a.SubscriberDB,
  a.OverallLatency,
  b.TracerTokenPostTime 
FROM @Latency a
  INNER JOIN @Publications b
    ON a.PublicationPK = b.PublicationPK

END

Finding the most active 15 minute time period


A guy over at sqlteam.com wanted to find the most active 15-minute period for a series of different stores his company runs and I thought this would be a nice topic for a blog post. I modified the problem a little to fit a sample table I had lying around and the result wasn’t too hard to come by.

Basically I have a table with sample call records for a telephone company and I want to find the most active 15-minute period where the most calls are started. First we have to generate a table with some sample data:

create table calls (
	call_id int identity(1, 1) primary key clustered,
	a_number bigint,
	b_number bigint,
	start_time datetime,
	duration int)
 go

--> Function to generate random numbers, used to create sample data
create function randomnumber (@min bigint, @max bigint, @rand float)
returns bigint
as
begin
	return CAST(((@Max + 1) - @Min) * @Rand + @Min AS bigint)
end
go

--&gt; Generate 500  rows of sample data
declare 
	@end int = 500,
	@current int = 1
	
while (@current <= @end)
	begin
		insert into calls (a_number, b_number, start_time, duration)
		select 
			dbo.randomnumber (22000000, 22999999, RAND()),
			dbo.randomnumber (22000000, 22999999, RAND()),
			DATEADD(MINUTE, dbo.randomnumber (1, 300, RAND()),GETDATE()),
			dbo.randomnumber (3, 3600, RAND())
			
		select @current = @current + 1
	end
go

Now once the test table with sample data has been established, we can run the actual query:

DECLARE @dummydate datetime = '2010-06-23 00:00:00'

;WITH cte AS (
	SELECT 
		[Mod] = DATEDIFF(MILLISECOND, @dummydate, start_time) % (15 * 60 * 1000), --&gt; 15 mins to milliseconds
		*
	FROM calls
	)
SELECT TOP 10 
	Period = DATEADD(MILLISECOND, -[Mod], start_time), 
	CountOfTransID = COUNT(call_id)
FROM cte
GROUP BY DATEADD(MILLISECOND, -[Mod], start_time)
ORDER BY COUNT(call_id) DESC

Last backups in a glance


As most other DBA’s I’m lazy and I try to make things as easy as possible for myself without missing out on anything. And the most important task of my day is to check the latest backups on all servers and I don’t think it can be done any easier than this. Unfortunately I don’t have the luxury of having all my servers registered in Management Studio because of security regulations but if you do, running this as a multi-server query should make things *reeeally* easy for you:

--> Latest backups
USE master
GO

SET NOCOUNT ON
GO

SELECT
  database_name,
  backup_finish_date = CASE
    WHEN db_backup_finish_date &gt; log_backup_finish_date THEN db_backup_finish_date
    ELSE log_backup_finish_date
    END
FROM (
  SELECT
    database_name = a.[name],
    db_backup_finish_date = 
      COALESCE(MAX(db_backup.backup_finish_date), '1900-01-01 00:00:00'),
    log_backup_finish_date = 
      COALESCE(MAX(log_backup.backup_finish_date), '1900-01-01 00:00:00')
  FROM sys.databases a
    LEFT OUTER JOIN msdb..backupset AS db_backup
      ON a.[name] = db_backup.database_name
        AND db_backup.[Type] = 'D'
    LEFT OUTER JOIN msdb..backupset AS log_backup
      ON db_backup.database_name = log_backup.database_name
        AND log_backup.[Type] = 'L'
  GROUP BY a.[name]
  ) AS dt