Tag Archives: example

How to demonstrate the fill ratio of separate tempdb files of equal size in SQL Server.

This topic reminds of me this little quiz of which jug will fill first.

Pretty much all of the documentation and recommendations out there say to keep the tempdb data files the same size so that the round-robin data flow works properly, i.e. the tempdb data files fill up evenly. This means that the data for a large temp table is actually split across the files and does not reside in one file.

Below is the code necessary to prove this scenario.

I tested this process on Microsoft SQL Server 2012 – Service Pack 1.

If working with a default installation of SQL Server Express The below script should print out the code to generate four equally sized (500 Mb) tempdb data files with no auto growth.

Run the script against the instance, review the print out and then copy/paste and run it against the instance

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#sfs') IS NOT NULL
	DROP TABLE #sfs;
	
DECLARE @TempDbDirectory VARCHAR(MAX)
DECLARE @Sql VARCHAR(MAX)

CREATE TABLE #sfs (
	fileid TINYINT
	,filegroupid TINYINT
	,totalextents INT
	,usedextents INT
	,dbfilename SYSNAME
	,physfile VARCHAR(255)
	);

INSERT INTO #sfs
EXEC ('USE  tempdb; DBCC showfilestats;');

SET @TempDbDirectory = (
		SELECT REPLACE(physfile, 'tempdb.ndf', '')
		FROM #sfs
		WHERE dbfilename = 'tempdev'
		)

SET @Sql = '
USE [tempdb]
GO

DBCC SHRINKFILE (
		N''tempdev''
		,100
		)
GO

USE [tempdb]
GO

ALTER DATABASE [tempdb] MODIFY FILE (
	NAME = N''tempdev''
	,NEWNAME = N''tempdev1''
	)
GO

USE [master]
GO

ALTER DATABASE [tempdb] MODIFY FILE (
	NAME = N''tempdev1''
	,FILENAME = N''' + @TempDbDirectory + 'tempdb.ndf''
	,SIZE = 512000 KB
	,FILEGROWTH = 0
	)
GO

ALTER DATABASE [tempdb] ADD FILE (
	NAME = N''tempdev2''
	,FILENAME = N''' + @TempDbDirectory + 'tempdb2.ndf''
	,SIZE = 512000 KB
		,FILEGROWTH = 0
	)
GO

ALTER DATABASE [tempdb] ADD FILE (
	NAME = N''tempdev3''
	,FILENAME = N''' + @TempDbDirectory + 'tempdb3.ndf''
	,SIZE = 512000 KB
		,FILEGROWTH = 0
	)
GO

ALTER DATABASE [tempdb] ADD FILE (
	NAME = N''tempdev4''
	,FILENAME = N''' + @TempDbDirectory + 'tempdb4.ndf''
	,SIZE = 512000 KB
		,FILEGROWTH = 0

	)
GO

ALTER DATABASE [tempdb] MODIFY FILE (
	NAME = N''templog''
	,FILEGROWTH = 512000 KB
	)
GO
'

PRINT @Sql
Once the script has run restart the server so the changes can take effect.
Once restarted when you look in the SSMS object explorer for the properties of the tempdb you should see a window like below showing the 4 tempdb data files.
tempdbFiles
Because SQL Server has just restarted nothing should be in these files.
You can test this by running the script below. Which return results like this. As you can see GB_Used is 0 for the 4 tempdb data files.
empty Temp Db

/*
Credit for this script goes to:
DAVE TURPIN
http://www.daveturpin.com/2011/07/how-to-drop-a-tempdb-database-file/
*/

-- Is there data in the second file of tempdb?
--drop table #sfs
--drop table #fixed_drives
--drop table #output_table
--drop table #databases
--drop table #dbf
--drop table #fg


IF OBJECT_ID('tempdb..#sfs') IS NOT NULL
	DROP TABLE #sfs;

IF OBJECT_ID('tempdb..#fixed_drives') IS NOT NULL
	DROP TABLE #fixed_drives;

IF OBJECT_ID('tempdb..#output_table') IS NOT NULL
	DROP TABLE #output_table;

IF OBJECT_ID('tempdb..#databases') IS NOT NULL
	DROP TABLE #databases;

IF OBJECT_ID('tempdb..#dbf') IS NOT NULL
	DROP TABLE #dbf;

IF OBJECT_ID('tempdb..#fg') IS NOT NULL
	DROP TABLE #fg;

--------------------------
-- Save result set from showfilestats
--------------------------
CREATE TABLE #sfs (
	fileid TINYINT
	,filegroupid TINYINT
	,totalextents INT
	,usedextents INT
	,dbfilename SYSNAME
	,physfile VARCHAR(255)
	);

------------------------------
-- Save result set from sys.database_files
------------------------------
CREATE TABLE #dbf (
	[file_id] INT
	,file_guid UNIQUEIDENTIFIER
	,[type] TINYINT
	,type_desc NVARCHAR(60)
	,data_space_id INT
	,[name] SYSNAME
	,physical_name NVARCHAR(260)
	,[state] TINYINT
	,state_desc NVARCHAR(60)
	,size INT
	,max_size INT
	,growth INT
	,is_media_ro BIT
	,is_ro BIT
	,is_sparse BIT
	,is_percent_growth BIT
	,is_name_reserved BIT
	,create_lsn NUMERIC(25, 0)
	,drop_lsn NUMERIC(25, 0)
	,read_only_lsn NUMERIC(25, 0)
	,read_write_lsn NUMERIC(25, 0)
	,diff_base_lsn NUMERIC(25, 0)
	,diff_base_guid UNIQUEIDENTIFIER
	,diff_base_time DATETIME
	,redo_start_lsn NUMERIC(25, 0)
	,redo_start_fork_guid UNIQUEIDENTIFIER
	,redo_target_lsn NUMERIC(25, 0)
	,redo_target_fork_guid UNIQUEIDENTIFIER
	,back_lsn NUMERIC(25, 0)
	);

------------------------------
-- Save result set from sys.filegroups select * from sys.filegroups
------------------------------
CREATE TABLE #fg (
	[name] SYSNAME
	,data_space_id INT
	,[type] CHAR(2)
	,type_desc NVARCHAR(60)
	,is_default BIT
	,is_system BIT
	,[filegroup_id] UNIQUEIDENTIFIER
	,log_filegroup_id INT
	,is_read_only BIT
	);

-- Populate #disk_free_space with data 
CREATE TABLE #fixed_drives (
	DriveLetter CHAR(1) NOT NULL
	,FreeMB INT NOT NULL
	);

INSERT INTO #fixed_drives
EXEC master..xp_fixeddrives;

CREATE TABLE #output_table (
	DatabaseName SYSNAME
	,FG_Name SYSNAME
	,GB_Allocated NUMERIC(8, 2)
	,GB_Used NUMERIC(8, 2)
	,GB_Available NUMERIC(8, 2)
	,DBFilename SYSNAME
	,PhysicalFile SYSNAME
	,Free_GB_on_Drive NUMERIC(8, 2)
	);

SELECT NAME AS DBName
INTO #databases
FROM sys.databases
WHERE database_id <= 4
	AND state_desc = 'ONLINE';

DECLARE @dbname SYSNAME;

SELECT @dbname = (
		SELECT TOP (1) DBName
		FROM #databases
		);

DELETE
FROM #databases
WHERE DBName = @dbname;

WHILE @dbname IS NOT NULL
BEGIN
	-- Get the file group data
	INSERT INTO #sfs
	EXEC ('USE ' + @dbname + '; DBCC showfilestats;');

	INSERT INTO #dbf
	EXEC ('USE ' + @dbname + '; SELECT * FROM sys.database_files;');

	INSERT INTO #fg
	EXEC ('USE ' + @dbname + '; SELECT * FROM sys.filegroups;');

	-- Wrap it up!
	INSERT INTO #output_table (
		DatabaseName
		,FG_Name
		,GB_Allocated
		,GB_Used
		,GB_Available
		,DBFilename
		,PhysicalFile
		,Free_GB_on_Drive
		)
	SELECT @dbname AS DATABASE_NAME
		,fg.NAME AS [File Group Name]
		,CAST(((sfs.totalextents * 64.0) / 1024000.0) AS NUMERIC(8, 2)) AS GB_Allocated
		,CAST(((sfs.usedextents * 64.0) / 1024000.0) AS NUMERIC(8, 2)) AS GB_Used
		,CAST((((sfs.totalextents - sfs.usedextents) * 64.0) / 1024000.0) AS NUMERIC(8, 2)) AS GB_Available
		,sfs.dbfilename
		,sfs.physfile
		,CAST((fd.FreeMB / 1000.0) AS NUMERIC(8, 2)) AS Free_GB_on_Drive
	FROM #sfs sfs
	INNER JOIN #dbf dbf ON dbf.[file_id] = sfs.fileid
	INNER JOIN #fg fg ON fg.data_space_id = sfs.filegroupid
	INNER JOIN #fixed_drives fd ON fd.DriveLetter = SUBSTRING(sfs.physfile, 1, 1);

	SELECT @dbname = (
			SELECT TOP (1) DBName
			FROM #databases
			);

	IF @dbname IS NOT NULL
		DELETE
		FROM #databases
		WHERE DBName = @dbname;

	TRUNCATE TABLE #sfs;

	TRUNCATE TABLE #dbf;

	TRUNCATE TABLE #fg;
END

SELECT CONVERT(INT, CONVERT(CHAR, current_timestamp, 112)) AS CaptureDate
	,DatabaseName
	,FG_Name
	,GB_Allocated
	,GB_Used
	,GB_Available
	,DBFilename
	,PhysicalFile
	,Free_GB_on_Drive
FROM #output_table
ORDER BY DatabaseName
	,FG_Name
To test how the files fill you can run the script below which will create a temp table and write 6,553,599 rows of 1 into the table which should reserve over 100 Mb worth of space.

SET NOCOUNT ON; 

DECLARE @x INT

SET @x = 1

CREATE TABLE #MyTempTable (id BIGINT)

WHILE @x < 6553600
BEGIN
		;

	INSERT INTO #MyTempTable (id)
	VALUES (1)

	SET @x = @x + 1
END;
Once it has complete if you run the script above to test the tempdb fill usage again you should see the files have been filled evenly.
Image of evenly filled temp db files

 

Advertisements

How to get MS SQL Server job history using a stored procedure

In a previous article (see article) I provided simple queries to return the server’s job history. It’s only natural that when you find yourself running the same script over and over again eventually you’ll get around to turning it into a stored procedure.

Well today was the day.

You can use the script below to create the Stored Procedure usp_GetJobHistory.

To deploy the SP to your instance you’ll need to replace the text PLACEHOLDER with the name of the database you’ll be deploying to. Ideally you should have a utility or administration database rather than deploying to Master.

The SP has 5 parameters.

Parameter Datatype Options Definition
@DaysToPull INT Any Number How many days of history you want
working back from today
@Status VARCHAR One of the following: All’, ‘Succeeded’, ‘Failed’,
‘Retried’, ‘Cancelled’, NULL
Will limited the rows returned
based on the status of the entry
@SortOrder VARCHAR ASC or DESC The occurrence order i.e. oldest to newest
@JobName VARCHAR Any Text Used in a LIKE operator to find jobs containing that text
@ReturnSql BIT 0, 1 or NULL Returns the SQL you can modify or
run to return the history

Example command:

Create the SQL code to return the job history for jobs containing the work backup that failed in the past day sorted by newest to oldest.

EXEC dbo.GetJobHistory @DaysToPull = 1
,@SortOrder = ‘DESC’
,@Status = ‘Failed’
,@JobName = ‘Backup’
,@ReturnSql = 1

Tip: you could use this to create a SSRS report that could be published daily to notify stakeholders of any job failures.

-- REPLACE PLACEHOLDER
USE [PLACEHOLDER];
GO

IF OBJECT_ID('[usp_GetJobHistory]') IS NULL
	EXEC ('CREATE PROCEDURE dbo.[usp_GetJobHistory] AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[usp_GetJobHistory] @DaysToPull INT
	,@Status VARCHAR(9) = NULL
	,@SortOrder VARCHAR(4) = NULL
	,@JobName VARCHAR(256) = NULL
	,@ReturnSql BIT = NULL
AS
BEGIN
	-- =============================================
	-- Author:      Bloggins
	-- Create date: 20170420
	-- Description: <Query to retrieve job history bypassing SSMS inbuilt viewer>
	-- Website: https://techtidbytes.wordpress.com/
	-- =============================================
	SET NOCOUNT ON

	BEGIN TRY
		-- Variable Declarations 
		DECLARE @PreviousDate DATETIME
		DECLARE @Year VARCHAR(4)
		DECLARE @Month VARCHAR(2)
		DECLARE @MonthPre VARCHAR(2)
		DECLARE @Day VARCHAR(2)
		DECLARE @DayPre VARCHAR(2)
		DECLARE @FinalDate INT
		DECLARE @StatusClause AS VARCHAR(255)
		DECLARE @Sql AS VARCHAR(MAX)

		IF @SortOrder IS NULL
			OR (
				@SortOrder <> 'ASC'
				AND @SortOrder <> 'DESC'
				)
		BEGIN
			SET @SortOrder = 'ASC'
		END

		IF @Status = 'All'
			OR @Status IS NULL
			OR (
				@Status <> 'All'
				AND @Status <> 'Failed'
				AND @Status <> 'Succeeded'
				AND @Status <> 'Retried'
				AND @Status <> 'Cancelled'
				)
		BEGIN
			SET @StatusClause = '0, 1, 2, 3'
		END
		ELSE IF @Status = 'Failed'
		BEGIN
			SET @StatusClause = '0'
		END
		ELSE IF @Status = 'Succeeded'
		BEGIN
			SET @StatusClause = '1'
		END
		ELSE IF @Status = 'Retried'
		BEGIN
			SET @StatusClause = '2'
		END
		ELSE IF @Status = 'Cancelled'
		BEGIN
			SET @StatusClause = '3'
		END

		SET @PreviousDate = DATEADD(dd, - @DaysToPull, GETDATE())
		SET @Year = DATEPART(yyyy, @PreviousDate)

		SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))

		SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)), 2)

		SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))

		SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)), 2)

		SET @FinalDate = CAST(@Year + @Month + @Day AS INT)
		/*RunDuration FORMAT (DD:HH:MM:SS)*/
		SET @Sql = '
SELECT ROW_NUMBER() OVER(ORDER BY h.instance_id ' + @SortOrder + ' ) AS Row
	,h.Server AS ServerName
	,s.database_name AS DbName
	,j.name AS JobName
	,s.step_name AS StepName
	,h.step_id AS StepId
	,CASE 
		WHEN h.run_status = 0
			THEN ''Failed''
		WHEN h.run_status = 1
			THEN ''Succeeded''
		WHEN h.run_status = 2
			THEN ''Retried''
		WHEN h.run_status = 3
			THEN ''Cancelled''
		END AS RunStatus
	,MSDB.DBO.AGENT_DATETIME(h.run_date, h.run_time) AS RunTime
	,STUFF(STUFF(STUFF(RIGHT(REPLICATE(''0'', 8) + CAST(h.run_duration AS VARCHAR(8)), 8), 3, 0, '':''), 6, 0, '':''), 9, 0, '':'') AS RunDuration
	,h.sql_severity AS SqlSeverity
	,h.message AS MessageReturned
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
	AND h.step_id = s.step_id
WHERE h.run_status IN (' + @StatusClause + ')
	AND h.run_date > ' + CONVERT(VARCHAR(255), @FinalDate)

		IF @JobName IS NOT NULL
		BEGIN
			SET @Sql = @Sql + '
AND j.name LIKE ' + '''' + '%' + @JobName + '%' + '''' + ''
		END

		SET @Sql = @Sql + '
ORDER BY h.instance_id ' + @SortOrder

		IF @ReturnSql = 1
		BEGIN
			PRINT (@Sql)
		END
		ELSE
		BEGIN
			EXEC (@Sql)
		END
	END TRY

	BEGIN CATCH
		PRINT 'error!'

		DECLARE @error_number AS INTEGER
		DECLARE @error_message AS VARCHAR(400)

		SET @error_number = error_number()
		SET @error_message = left(error_message(), 400)

		PRINT 'error_message: ' + @error_message
	END CATCH
END

How to pass table valued parameters in SQL Server

With the introduction of SQL Server 2008 came the ability to define an entire table as a parameter, think of it like a table data type. This feature greatly eases the development process as constructing and parsing XML data strings is no longer necessary.

Table parameters are user defined parameters, i.e. you are creating a means of storing specific data that is passed by a stored procedure or function.

Limitations:

  • The READONLY clause must be used when passing in the table valued variable
  • Data in the table variable cannot be modified
  • The table variables cannot be used as OUTPUT parameters only input parameters.
  • When data is passed to the table variable the table variable must be passed to the stored procedure in the same batch. Table variables go out of scope as soon as the procedure or batch returns.

The following is a complete end to end example of how to create and pass data to table parameters:

IF OBJECT_ID('OrderHistory') > 0
	DROP TABLE OrderHistory;
GO

CREATE TABLE [dbo].[OrderHistory] (
	[OrderID] [int] IDENTITY(1, 1) NOT NULL PRIMARY KEY
	,[Product] [varchar](10) NULL
	,[OrderDate] [datetime] NULL
	,[SalePrice] [money] NULL
	)
GO

CREATE TYPE OrderHistoryTableType AS TABLE (
	[Product] [varchar](10) NULL
	,[OrderDate] [datetime] NULL
	,[SalePrice] [money] NULL
	)
GO

CREATE PROCEDURE usp_InsertOrder (@TableVariable OrderHistoryTableType READONLY)
AS
BEGIN
	INSERT INTO OrderHistory (
		Product
		,OrderDate
		,SalePrice
		)
	SELECT Product
		,OrderDate
		,SalePrice
	FROM @TableVariable
END
GO

DECLARE @DataTable AS OrderHistoryTableType

INSERT INTO @DataTable (
	Product
	,OrderDate
	,SalePrice
	)
VALUES (
	'Desktop'
	,GETUTCDATE()
	,599.00
	)

INSERT INTO @DataTable (
	Product
	,OrderDate
	,SalePrice
	)
VALUES (
	'Laptop'
	,GETUTCDATE()
	,299.00
	)

INSERT INTO @DataTable (
	Product
	,OrderDate
	,SalePrice
	)
VALUES (
	'Mouse'
	,GETUTCDATE()
	,9.00
	)

EXEC usp_InsertOrder @TableVariable = @DataTable

SELECT *
FROM OrderHistory

 

If you want to view other types of table type definitions in your system, or you’ve forgotten what you called a specific table parameter, you can execute the following query, which looks in the system catalog:

SELECT * FROM sys.table_types

How to export a table as an XML file using a SQL script

Below is a script that will allow you, as the title suggests, to export a table as an XML file.

By use of the find and replace function in your preferred text editor, or by way of the text editor in SSMS, input your values for the required fields, i.e. replace TableName with the name of the table you will be working off.

SERVER NAME: ServerName

DATABASE NAME: DatabaseName

SCHEMA NAME: SchemaName

TABLE NAME: TableName

SAVE LOCATION ADDRESS: AddressName

FILE NAME: FileName (Excluding .xml extension)

Then run the script below and the table will be exported as an XML file to the location and file name of your choosing.

/*
FIND AND REPLACE:

SERVER NAME: ServerName
DATABASE NAME: DatabaseName 
SCHEMA NAME: SchemaName
TABLE NAME: TableName
SAVE LOCATION ADDRESS: AddressName
FILE NAME: FileName
 */
-- Run on ServerName
USE DatabaseName;
GO

DECLARE @saveLocation AS VARCHAR(510)
DECLARE @nameOfFile AS VARCHAR(255)
DECLARE @instanceName AS VARCHAR(255)
DECLARE @cmd NVARCHAR(1020)

SET @saveLocation = 'AddressName'
SET @nameOfFile = 'FileName'
SET @saveLocation = @saveLocation + '\' + @nameOfFile + '.xml'
SET @instanceName = (
		SELECT @@servername
		)

-- Please note that the fully qualified table name is needed
SELECT @cmd = '
    bcp "SELECT * FROM [DatabaseName].[SchemaName].[TableName] row for xml auto, root(''rows''), elements" ' + 'queryout "' + @saveLocation + '" -S ' + @instanceName + ' -T -w -r -t';

EXEC xp_cmdshell @cmd;
GO

 

This script is enabled by two utilities xp_cmdshell and BCP.

For an explanation of each please view the associated links.