KPH:Online

This article and the code examples within were written in 2014 for SQL Server 2008 R2. Please test the code to make it works as expected if using a newer version of SQL Server

I work with Microsoft SQL Server everyday and have been developing scripts to monitor such things as database growth and the amount of free HD space on each of the systems.

The code I have records HD space stores the DBID of each database next to each recording so I can track growth over time. “Sounds fine so far” you say. So did I…

When developing some code to report on the data collected I noticed that there were several databases where the sizes had changed after a period of having zero size.

Further investigation showed that MSSQL had reused IDs that belonged to databases I’d previously deleted. Doh! I needed a better way of differentiating between the databases I was monitoring.

I looked at what information MSSQL stores for each database on a system

SELECT * FROM master..sysdatabases

This gave me a list of databases on the system along with some information about them such as database name, the date it was created, it’s ID, the path of the MDF file, status etc. I wanted to find something unique from the list that wouldn’t change.

The DBID doesn’t change, and neither does the name on any of my systems but they could be reused if the DB was deleted. But how could I make sure that a database called ‘Geoff’ with a DBID of 7 is the same db as the one I checked earlier and not an imposter! If I also check the ‘crdate’ column I get the date the database was created. Between all three sets of data I can create a unique identifier for each database that would change if the database was deleted and recreated.

SELECT (name+CAST(dbid as varchar(3))+CAST(crdate as varchar(20)))
FROM master..sysdatabases

This is unique but not very useful to work with due to some very long or awkward strings generated. When scripting on Linux I can use an MD5 hash as a simple way to see if a file has changed. Getting the MD5 hash of this string sounds much better than using the string itself. For this I used the HashBytes function.

SELECT CONVERT(NVARCHAR(32),HashBytes('MD5', name+CAST(dbid as varchar(3))+CAST(crdate as varchar(20))),2) as MD5Hash
FROM master..sysdatabases
WHERE name = 'Geoff'

A45756E1D7B9E615FCD7EEEDB0CC518D

This now means I can use the hash as a unique identifier and be confident it will be different even if the database name and id are somehow, by coincidence, the same.

I created a couple of functions that enable you to get the hash of a database by either it’s ID or name. I can then store this elsewhere and use the hash in future logging. Hopefully they’ll be of use to somebody:

USE [KPHOnline]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        kphonline.co.uk
-- Create date:     02/06/2014
-- Description:    Returns a unique id for a DB since DBID isn't reliable enough. Generates an MD5 hash of the Concatanated DB Name, ID and Created Date
-- =============================================

ALTER FUNCTION [dbo].[fn_DBA_ReturnDBHashfromDBID]
(
 @pDBID int
)

RETURNS VARCHAR(32)
AS
BEGIN

DECLARE @vHash VARCHAR(32)

SELECT @vHash = CONVERT(NVARCHAR(32),HashBytes('MD5', name+CAST(dbid as varchar(3))+CAST(crdate as varchar(20))),2)
FROM master..sysdatabases
WHERE DBID = @pDBID

RETURN @vHash

END

and

USE [KPHOnline]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_DBA_ReturnDBHashfromDBName]    Script Date: 03/06/2014 22:45:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        kphonline.co.uk
-- Create date:     02/06/2014
-- Description:    Returns a unique id for a DB since DBID isn't reliable enough. Generates an MD5 hash of the Concatanated DB Name, ID and Created Date
-- =============================================

ALTER FUNCTION [dbo].[fn_DBA_ReturnDBHashfromDBName]
(
 @pDBName varchar(128)
)

RETURNS VARCHAR(32)
AS
BEGIN

DECLARE @vHash VARCHAR(32)

SELECT @vHash = CONVERT(NVARCHAR(32),HashBytes('MD5', name+CAST(dbid as varchar(3))+CAST(crdate as varchar(20))),2)
FROM master..sysdatabases
WHERE name = @pDBName

RETURN @vHash

END

I’m sure there may be better way of solving the problem but this approach wordked for me when I needed it :)

Categories:

SQL Server

← Older posts

Newer posts →