How to: Check When Your SQL Server Services Started

The following script will give information about Time and Date since your server has been started. You can make use of this script to check when the server last restarted or rebooted.

USE master
GO
SELECT crdate AS Datetime_Instance_Started
FROM sysdatabases
WHERE name = 'tempdb'

The output will be like this:

Datetime_Instance_Started
-------------------------
2010-10-17 18:30:36.917

SQL Server Service Utility

Here is how this script could help you

  • Determine the status of SQL server service(s) – running or not
  • Determine if the service is installed or not

The scripts has been executed on the following servers:

  • 32 bit and 64 bit
  • SQL 2005 with SP3 – Stand alone and on multiple instances
  • SQL 2008 with SP1 and SP2 – Stand alone and on multiple instances
  • SQL 2008 R2 latest patches

Hope you find it useful. The xp_regread is a stored procedure is a undocumented procedure in MS SQL 2005 that reads the system registry and tells you which instance number maps to which instance.

/*---------------------------------------*/
/*    SQL Server Service Check Utility   */
/*---------------------------------------*/

SET NOCOUNT ON

/* ------------------------------------------ Inital Setup -----------------------------------------------------*/
CREATE TABLE #RegResult
(
	ResultValue NVARCHAR(4)
)

CREATE TABLE #ServicesServiceStatus			/*Create temp tables*/
(
	 RowID INT IDENTITY(1,1)
	,ServerName NVARCHAR(128)
	,ServiceName NVARCHAR(128)
	,ServiceStatus varchar(128)
	,StatusDateTime DATETIME DEFAULT (GETDATE())
	,PhysicalSrverName NVARCHAR(128)
)

DECLARE
		 @ChkInstanceName nvarchar(128)				/*Stores SQL Instance Name*/
		,@ChkSrvName nvarchar(128)					/*Stores Server Name*/
		,@TrueSrvName nvarchar(128)					/*Stores where code name needed */
		,@SQLSrv NVARCHAR(128)						/*Stores server name*/
		,@PhysicalSrvName NVARCHAR(128)				/*Stores physical name*/
		,@FTS nvarchar(128)							/*Stores Full Text Search Service name*/
		,@RS nvarchar(128)							/*Stores Reporting Service name*/
		,@SQLAgent NVARCHAR(128)					/*Stores SQL Agent Service name*/
		,@OLAP nvarchar(128)						/*Stores Analysis Service name*/
		,@REGKEY NVARCHAR(128)						/*Stores Registry Key information*/

SET @PhysicalSrvName = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(128))
SET @ChkSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
SET @ChkInstanceName = @@serverName

IF @ChkSrvName IS NULL								/*Detect default or named instance*/
	BEGIN
		SET @TrueSrvName = 'MSQLSERVER'
		SELECT @OLAP = 'MSSQLServerOLAPService' 	/*Setting up proper service name*/
		SELECT @FTS = 'MSFTESQL'
		SELECT @RS = 'ReportServer'
		SELECT @SQLAgent = 'SQLSERVERAGENT'
		SELECT @SQLSrv = 'MSSQLSERVER'
	END
ELSE
	BEGIN
		SET @TrueSrvName =  CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
		SET @SQLSrv = '$'+@ChkSrvName
	 	SELECT @OLAP = 'MSOLAP' + @SQLSrv	/*Setting up proper service name*/
		SELECT @FTS = 'MSFTESQL' + @SQLSrv
		SELECT @RS = 'ReportServer' + @SQLSrv
		SELECT @SQLAgent = 'SQLAgent' + @SQLSrv
		SELECT @SQLSrv = 'MSSQL' + @SQLSrv
	END 

/* ---------------------------------- SQL Server Service Section ----------------------------------------------*/

SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLSrv

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
	INSERT #ServicesServiceStatus (ServiceStatus)		/*Detecting staus of SQL Sever service*/
	EXEC xp_servicecontrol N'QUERYSTATE',@SQLSrv
	UPDATE #ServicesServiceStatus set ServiceName = 'MS SQL Server Service' where RowID = @@identity
	UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
	UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
	TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
	INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
	UPDATE #ServicesServiceStatus set ServiceName = 'MS SQL Server Service' where RowID = @@identity
	UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
	UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
	TRUNCATE TABLE #RegResult
END

/* ---------------------------------- SQL Server Agent Service Section -----------------------------------------*/

SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLAgent

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
	INSERT #ServicesServiceStatus (ServiceStatus)		/*Detecting staus of SQL Agent service*/
	EXEC xp_servicecontrol N'QUERYSTATE',@SQLAgent
	UPDATE #ServicesServiceStatus set ServiceName = 'SQL Server Agent Service' where RowID = @@identity
	UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
	UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
	TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
	INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
	UPDATE #ServicesServiceStatus set ServiceName = 'SQL Server Agent Service' where RowID = @@identity
	UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
	UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
	TRUNCATE TABLE #RegResult
END

/* ---------------------------------- SQL Browser Service Section ----------------------------------------------*/

SET @REGKEY = 'System\CurrentControlSet\Services\SQLBrowser'

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
	INSERT #ServicesServiceStatus (ServiceStatus)		/*Detecting staus of SQL Browser Service*/
	EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'sqlbrowser'
	UPDATE #ServicesServiceStatus set ServiceName = 'SQL Browser Service - Instance Independent' where RowID = @@identity
	UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
	UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
	TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
	INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
	UPDATE #ServicesServiceStatus set ServiceName = 'SQL Browser Service - Instance Independent' where RowID = @@identity
	UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
	UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
	TRUNCATE TABLE #RegResult
END

/* ---------------------------------- Integration Service Section ----------------------------------------------*/

SET @REGKEY = 'System\CurrentControlSet\Services\MsDtsServer'

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
	INSERT #ServicesServiceStatus (ServiceStatus)		/*Detecting staus of Intergration Service*/
	EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'MsDtsServer'
	UPDATE #ServicesServiceStatus set ServiceName = 'Intergration Service - Instance Independent' where RowID = @@identity
	UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
	UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
	TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
	INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
	UPDATE #ServicesServiceStatus set ServiceName = 'Intergration Service - Instance Independent' where RowID = @@identity
	UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
	UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
	TRUNCATE TABLE #RegResult
END

/* ---------------------------------- Reporting Service Section ------------------------------------------------*/

SET @REGKEY = 'System\CurrentControlSet\Services\'+@RS

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
	INSERT #ServicesServiceStatus (ServiceStatus)		/*Detecting staus of Reporting service*/
	EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@RS
	UPDATE #ServicesServiceStatus set ServiceName = 'Reporting Service' where RowID = @@identity
	UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
	UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
	TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
	INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
	UPDATE #ServicesServiceStatus set ServiceName = 'Reporting Service' where RowID = @@identity
	UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
	UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
	TRUNCATE TABLE #RegResult
END

/* ---------------------------------- Analysis Service Section -------------------------------------------------*/
IF @ChkSrvName IS NULL								/*Detect default or named instance*/
	BEGIN
	SET @OLAP = 'MSSQLServerOLAPService'
	END
ELSE
	BEGIN
	SET @OLAP = 'MSOLAP'+'$'+@ChkSrvName
	SET @REGKEY = 'System\CurrentControlSet\Services\'+@OLAP
END

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
	INSERT #ServicesServiceStatus (ServiceStatus)		/*Detecting staus of Analysis service*/
	EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@OLAP
	UPDATE #ServicesServiceStatus set ServiceName = 'Analysis Services' where RowID = @@identity
	UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
	UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
	TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
	INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
	UPDATE #ServicesServiceStatus set ServiceName = 'Analysis Services' where RowID = @@identity
	UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
	UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
	TRUNCATE TABLE #RegResult
END

/* ---------------------------------- Full Text Search Service Section -----------------------------------------*/

SET @REGKEY = 'System\CurrentControlSet\Services\'+@FTS

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
	INSERT #ServicesServiceStatus (ServiceStatus)		/*Detecting staus of Full Text Search service*/
	EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@FTS
	UPDATE #ServicesServiceStatus set ServiceName = 'Full Text Search Service' where RowID = @@identity
	UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
	UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
	TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
	INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
	UPDATE #ServicesServiceStatus set ServiceName = 'Full Text Search Service' where RowID = @@identity
	UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
	UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
	TRUNCATE TABLE #RegResult
END

/* -------------------------------------------------------------------------------------------------------------*/
SELECT   PhysicalSrverName AS 'Physical Server Name'    /*Display finding*/
		,ServerName AS 'SQL Instance Name'
		,ServiceName AS 'SQL Server Services'
		,ServiceStatus AS 'Current Service Service Status'
		,StatusDateTime AS 'Date/Time Service Status Checked'
FROM #ServicesServiceStatus

/* -------------------------------------------------------------------------------------------------------------*/
DROP TABLE #ServicesServiceStatus				/*Perform cleanup*/
DROP TABLE #RegResult

How to: Check Table Data Sizes

Sometimes I would think to have a table list to show all table size of the particular database, so that I could help me to drill down into the issue more easier. I found the scripts from SQLServerCentral.com forum.

Just connect to the appropriate databases and execute the following T-SQL scripts.

--SCRIPT TO LOOP THROUGH TABLES IN A DATABASE AND
--LIST OUT THEIR COUNTS
DECLARE @counter int
	,@tablename varchar(100)
	,@execString varchar(8000)

CREATE TABLE #temptable(
tablename varchar(100),
rows int,
reserved varchar(50),
Data varchar(50),
index_size varchar(50),
unused varchar(50))

CREATE TABLE #looptable(
	loopint int identity(1,1),
	tablename varchar(100))

INSERT INTO #looptable
SELECT name from sys.tables

SET @counter = 1

WHILE @counter <= (select max(loopint) from #looptable)
BEGIN
	SET @tablename = (select tablename from #looptable where loopint = @counter)

	SET @execString = '
	INSERT INTO #temptable
	EXEC sp_spaceused '''+@tablename+'''

	'
	EXEC(@execString)
	SET @counter = @counter + 1
END

GO
SELECT * from #temptable
ORDER BY reserved desc

GO
DROP TABLE #temptable,#looptable

SQL Server Index Type

  • SQL Server maintains indexes by using a B-tree structure
  • Clustered
    • Data is physically ordered around the keys
    • Actual data is stored at the leaf level
  • Nonclustered
    • An independent index structure that points to the underlying records
  • A heap is a table without a clustered index
    • Nonclustered indexes can exist on such a table

How to: Get Backup Status in SQL Server 2005 & 2008

When someone asks how long will that backup job runs or how much work it has already done, the best option in SQL Server 2000 was to check the output given by the stats parameter in the Backup script. But if someone forgot to include the stats parameter in the backup script, there is no way to check the backup completion status in SQL Server 2000.

But starting SQL Server 2005, this behavior has changed. One of the many wonderful DMVs to be included in SQL Server 2005 is sys.dm_exec_requests. With this DMV, we can easily track the status, percentage completed etc. of the Backup process.

Here is the script:

SELECT
	A.NAME,B.TOTAL_ELAPSED_TIME/60000 AS [Running Time],
	B.ESTIMATED_COMPLETION_TIME/60000 AS [Remaining],
	B.PERCENT_COMPLETE as [%],
	(SELECT
		TEXT
	FROM
		sys.dm_exec_sql_text(B.SQL_HANDLE))AS COMMAND
FROM
	MASTER..SYSDATABASES A,
	sys.dm_exec_requests B
WHERE
	A.DBID=B.DATABASE_ID AND B.COMMAND LIKE '%BACKUP%'
ORDER BY
	percent_complete DESC,
	B.TOTAL_ELAPSED_TIME/60000 DESC