Category Archives: tsql

How to schedule a job to restore the last backup made of a SQL Server database

This post provides you with a script that will generate a restore script for a database using the latest full backup file that exists in a directory. (No need to state the filename explicitly)

You need to provide the following at the start of the script:

  • The target database i.e. the database you will restore to
  • The directory where the backup file is saved

If you are using the excellent Ola Hallengren maintenance solution (see link) the directory path will look something like below. If you’re not using Ola’s solution, you should be.

\\ServerWhereBackupsAreSaved\DriveName\InstanceName\TargetDatabase\Full\

This restore script is designed to work with Ola’s solution as it segregates the backup directory structure such that each database has an allocated folder and each full backup file is named with the date and time of the file creation.

The restore script determines which backup file is the latest backup file based on the max name. So for the script to work it is assumed you have an appropriate backup strategy (i.e. using Ola’s solution) were backup types are segregated into different folders, backup names have a date reference and the backup location is dedicated to backups and nothing else, i.e. no trash files in the location.

Some use cases for this solution might be:

  • Restoring a nightly backup to another instance for reporting purposes
  • Restoring backups to a development environment
  • Restoring backups to another server to test the backups

You can use the logic in a stored procedure or as the T-SQL in a job step and schedule accordingly.

/*
You need to reference the following:
* The target database i.e. the database you will restore to.
* The directory where the backup file is saved.
If you are using the Ola Hallengren backup scripts the directory path will look 
something like below.
\\ServerWhereBackupsAreSaved\DriveName\InstanceName\TargetDatabase\Full\

Find & Replace the follow text for the target database and directory:

TARGET_DATABASE
DIR_PATH
*/
/*
Declare Variables
*/
DECLARE @DatabaseToRestore AS VARCHAR(MAX);
DECLARE @DirToSearch AS VARCHAR(MAX);
DECLARE @ShellCommand AS VARCHAR(MAX);
DECLARE @BackupFile AS VARCHAR(MAX);
DECLARE @Sql AS VARCHAR(MAX);

/*
Set User Variables
*/
SET @DatabaseToRestore = 'TARGET_DATABASE';
SET @DirToSearch = 'DIR_PATH';
SET @ShellCommand = 'dir ' + @DirToSearch;

/*
Create Temp Table To Hold xp_cmdshell Output
*/
IF OBJECT_ID('tempdb..#DirList') IS NOT NULL
	DROP TABLE #DirList;

CREATE TABLE #DirList (
	Id INT identity(1, 1)
	,line NVARCHAR(1000)
	);

/*
Enable Advanced Options To Enable xp_cmdshell Temporarily
*/
EXEC master.dbo.sp_configure 'show advanced options'
	,1;

RECONFIGURE
WITH OVERRIDE;

EXEC master.dbo.sp_configure 'xp_cmdshell'
	,1;

RECONFIGURE
WITH OVERRIDE;

/*
Run The Shell Command To Capture And Write Dir Info To Temp Table
*/
SET @Sql = '
INSERT INTO #DirList (line)
EXEC xp_cmdshell ' + '''' + @ShellCommand + '''' + ';';

EXEC (@Sql);

EXEC master.dbo.sp_configure 'xp_cmdshell'
	,0;

/*
Disable Advanced Options And xp_cmdshell Again
*/
RECONFIGURE
WITH OVERRIDE;

EXEC master.dbo.sp_configure 'show advanced options'
	,0;

RECONFIGURE
WITH OVERRIDE;

/*
Get The Last Backup File Name And Save To A Variable
*/
WITH CTE
AS (
	SELECT SUBSTRING(line, 37, 100) [FileName]
	FROM #DirList
	WHERE Id > (
			SELECT MIN(Id)
			FROM #DirList
			WHERE line LIKE '%<DIR>%..%'
			)
		AND Id < (
			SELECT MAX(Id) - 2
			FROM #DirList
			)
	)
SELECT @BackupFile = [FileName]
FROM CTE
WHERE [FileName] = (
		SELECT MAX(FileName)
		FROM CTE
		);

/*
Create The Restore Script
*/
SET @BackupFile = @DirToSearch + @BackupFile
SET @Sql = '
ALTER DATABASE ' + QUOTENAME(@DatabaseToRestore) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE ' + QUOTENAME(@DatabaseToRestore) + ' FROM DISK = ' + '''' + @BackupFile + '''' + '
WITH NORECOVERY
,REPLACE;
RESTORE DATABASE ' + QUOTENAME(@DatabaseToRestore) + ' WITH RECOVERY;
'

/*
To Directly Execute The Script Uncomment The EXEC Statement And Delete The Select Statement
*/
/*
EXEC (@Sql)
*/
SELECT @Sql

 

How to move and or rename Database files in SQL Server

An example use case for the process below could be you need to move database files to a new drive. Another example might be your organisation intends to run a legacy database along side a new updated database with both sharing the same database name in the same instance with the files located in the same directory with the same names. Obviously this cannot be done and requires the database names to differ and the files to be renamed or not exist in the same directory.

For example AdventureWorks might become AdventureWorks_Legacy while a new and improved AdventureWorks database retains the original database name. The associated database file names would also need to be changed/moved to reflect this.

Someone might also want to do something like this for test purposes but obviously having test resources in a live environment would not be recommended if avoidable.

The first step to moving and renaming the files is to copy and modify the script below. Note the script below assumes you want to move and change the names of the files. To avoid any database conflicts you only need to do one or the other.

/* 
Find & Replace DbName with the name of the Database you are working with
*/
USE [DbName];

/*
Changing Physical names and paths
Replace 'C:\...\NewDbName.mdf' with full path of new Db file to be used
*/
ALTER DATABASE DbName MODIFY FILE (
	NAME = ' DbName '
	,FILENAME = 'C:\...\NewDbName.mdf'
	);

/*
Replace 'C:\...\NewDbName_log.ldf' with full path of new Db log file to be used
*/
ALTER DATABASE DbName MODIFY FILE (
	NAME = ' DbName _log'
	,FILENAME = 'C:\...\NewDbName_log.ldf'
	);

/*
Changing logical names
*/
ALTER DATABASE DbName MODIFY FILE (
	NAME = DbName
	,NEWNAME = NewDbName
	);

ALTER DATABASE DbName MODIFY FILE (
	NAME = DbName_log
	,NEWNAME = NewDbName_log
	);
Once the script has been set up as desired follow the steps below:
  1. Open Microsoft SQL Server Management Studio (SSMS).
  2. Connect to the server that houses the Db you are working with.
  3. Run the modified script
  4. Right click on the Db in SSMS and select Tasks > Take Offline
  5. If you are moving the database files log into the server that houses the database files and copy and move the MDF and LDF files to the location you specified in first two alter commands. If the script specifies new names rename the copied files to match the names given in the script exactly.
  6. Go back to SSMS and right click on the Db and select Tasks > Bring Online.
  7. If you have moved the files once the database is back online and confirmed working as expected the unused original files can be deleted.
  8. Now you can rename the Db to the new name if you wish using SSMS.

How to demonstrate the space usage of a Null Varchar(Max) column

An empty Varchar(Max) column uses a negligible amount of disk space. The script below demonstrates this by creating the table TestTb which contains one column named NullColumn that has a Varchar(Max) data type. When the table is created the column NullColumn is populated with 100,000 rows of Null.

The two readings below show the table when it has just been created and the table with 100,000 rows entered.

Results

IF OBJECT_ID('dbo.TestTb', 'U') IS NOT NULL
	DROP TABLE dbo.TestTb;

CREATE TABLE TestTb (NullColumn VARCHAR(MAX));
GO

sp_spaceused 'TestTb';

DECLARE @i AS INT;

SET @i = 0;

WHILE @i < 100000
BEGIN
	INSERT INTO TestTb (NullColumn)
	VALUES (NULL)

	SET @i = @i + 1
END;
GO

sp_spaceused 'TestTb';

IF OBJECT_ID('dbo.TestTb', 'U') IS NOT NULL
	DROP TABLE dbo.TestTb;

 

How to determine why a T-SQL command is unreasonably slow

If you’ve ever found yourself in the situation were a command executing against a small table is nowhere near instant there can be numerous reasons for this but the most common causes are locks and waits.

The first step in identifying the problem is to execute the script below in a new query window while the troublesome command is running.

/* Queries Not Running */
SELECT ROW_NUMBER() OVER (
		ORDER BY r.total_elapsed_time DESC
		) AS Rn
	,st.TEXT AS SqlText
	,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.status <> 'running';

/* Queries Running */
SELECT ROW_NUMBER() OVER (
		ORDER BY r.total_elapsed_time DESC
		) AS Rn
	,st.TEXT AS SqlText
	,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.status = 'running';

 

This script will return two lists of the currently active sessions along with the stats associated with their execution. The first list will contain all the active sessions that are not running. The second list will contain all the active sessions that are running and will likely not contain the troublesome query you’re dealing with.

Identify your session based on the SqlText field. Be sure you’ve identified the session correctly as you may decide you want to kill the process later and killing the wrong one could cause you a lot of trouble.

  • status : If the status is not running look to the other fields in the returned result set to help identify the problem. If the session is in the running result set but you are unhappy with the performance it is likely the T-SQL needs to be optimized to make it run faster. This is a very broad topic and there are tons of articles and guides on the internet dealing with it.
  • blocking_session_id : If another session is blocking yours from executing, e.g. it has locked a table your command needs to write to, then this field will include the Id of the session causing the table to be locked. You can use EXEC sp_who2 to assess if the underlying command/query is experiencing a problem. If you are familiar with the blocking session you may know that you are able to kill the session without incurring any negative consequences. You can use the following code snippet to kill the blocking session.
    KILL blocking_session_id /*replace by the actual Id*/

    NOTE: Before you kill anything if it’s a command that has been running for a very long time it will likely take at least the same amount of time to roll back and unlock the table. You might be better off waiting for the session to finish on its own.

  • wait_type : If no blocking session is available, then the query is waiting for something, e.g. server resources etc. More details about wait types can be found HERE
  • wait_time : This stat value is measured in milliseconds. Short wait times are fine, specially in PAGEIOLATCH wait types (access to physical files) but longer wait times indicate a more serious problem.
  • last_wait_type : Indicates if the last wait type was different. This is quite helpful in analyzing if the query was blocked for the same reason before.

 

How to rename and/or remove tables in SQL Server with T-Sql generated by Excel formulas

This post deals with using an Excel file to generate T-Sql code to rename and/or remove tables given a scenario like the following. (To generate T-Sql to remove tables using T-sql see this post.)

Say someone sends you a list via an email or text file of tables they want renamed or removed from a database . You could go into SSMS object explorer and rename or delete each table in the list one by one. Or you could write the T-Sql statements individually but chances are you can speed things up using Excel.

With Excel you can input the schema and table name into a given cell and the T-Sql code will be generated to rename and drop the table using formulas.

To do this you can just download the Excel file template here. Download

Rename And Drop Script Generator

The template is setup assuming you are intending on the dropping the table sometime in the future but first you will be renaming it.

A good approach for removing objects is to rename the objects first. This makes it easier to put the environment back the way it was if there are any problems encountered. After a set period of time if there is no negative impact on the overall environment you can script out the object then drop it. (Obviously do this in a test environment first if possible)

To aid further in a cleanup project the Excel file also acts as a form that can be used to track progress as it contains the columns RenameDate, RestoreDate and DropDate. It also contains the column RestoreOriginalName. This column holds the formula to create the T-Sql code to renamed the tables back if there are any problems encountered.

You can adjust the formula in cell D2 to somethings other than _DELETE_ if you want to change the prefix so the tables will be renamed something else. If you just want to remove the tables you’ll have to run the script from column D before you can drop the tables using the script from column F.

Remember to drag the formula down for as many table entries as you have and it will generate the T-Sql needed.

You can create the Excel file manually yourself without downloading it.

To do so open a new Excel file and in an empty sheet name the first 9 columns as below:

A1: DatabaseName
B1: SchemaName
C1: TableName
D1: RenameForDeletion
E1: RestoreOriginalName
F1: DropTable
G1: RenameDate
H1: RestoreDate
I1: DropDate

For D2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘”&C2&”‘)) BEGIN exec sp_rename ‘”&B2&”.”&C2&”‘, ‘_DELETE_”&C2&”‘ END ELSE BEGIN SELECT ‘TABLE [“&A2&”].[“&B2&”].[“&C2&”] DOES NOT EXIST’ AS [RenameFailed] END;”

For E2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘_DELETE_”&C2&”‘)) BEGIN exec sp_rename ‘”&B2&”._DELETE_”&C2&”‘, ‘”&C2&”‘ END ELSE BEGIN SELECT ‘TABLE [“&A2&”].[“&B2&”].[“&C2&”] DOES NOT EXIST’ AS [RenameFailed] END;”

For F2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘_DELETE_”&C2&”‘)) BEGIN DROP TABLE [“&B2&”].[_DELETE_”&C2&”] END;”

To test that the scripts generated work you can create the mock database and table using the script below. The Excel file is loaded with these values by default.

CREATE DATABASE [TidBytez];
GO

USE [TidBytez]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer] ([ID] [int] NULL) ON [PRIMARY]
GO

 

How to tell if you are a member of a SQL Server group or create a list of group members using T-SQL

The following scripts will help you determine if you are a member of a group or role or create a list of group members in SQL Server without having to use SQL Server Management Studio. This is a particularly handy script in determining who might have access to the server through Active Directory groups.

/*
The code below indicates whether the current user is a member 
of the specified Microsoft Windows group or SQL Server database role.
A result of 1 = yes
,0 = no
,null = the group or role queried is not valid.
*/

SELECT IS_MEMBER('[group or role]')


/*
The code below will create a list of all the logins that are members 
of a group.
*/

EXEC master..xp_logininfo 
@acctname = '[group]',
@option = 'members'

 

How to get the default error log path for SQL Server with T-SQL

Below is a script to get the default error log path for SQL Server and set it as a variable. 

USE MASTER;
GO

DECLARE @LogPath AS VARCHAR(MAX)
DECLARE @ErrorLogPath TABLE (
	LogDate DATETIME
	,ProcessInfo VARCHAR(255)
	,PathText VARCHAR(MAX)
	);

INSERT INTO @ErrorLogPath
EXEC xp_readerrorlog 0
	,1
	,N'Logging SQL Server messages in file';

SET @LogPath = (
		SELECT REPLACE(REPLACE(REPLACE(PathText, 'Logging SQL Server messages in file ', ''), '''', ''), 'ERRORLOG.', '')
		FROM @ErrorLogPath
		);

SELECT @LogPath AS DefaultLogPath;
GO

 

How to search for SQL Server objects that exist anywhere across an instance using T-SQL

You’re probably never going to be familiar with every database object, i.e. Table, View, Stored Procedure and Function, that exists in a large production database, even if you were the one who designed it. So everyone who maintains an environment be it a call center back end or sales system back end or CRM back end etc. etc. needs to be able to locate objects quickly.

Redgate offer a fantastic free tool to do this within SQL Server Management Studio through a GUI called SQL Search.

As great as this is though sometimes you might want to search through object definitions programmatically.

To clarify I’m defining object definition as being column names of a table or view or the command that makes up a stored procedure or function.

Below is the Store Procedure I’ve written to do this called SearchObjectDefinition. To work this Stored Procedure also requires the User Defined Function (UDF) called Split which I used in the tutorial “How to pass a multi-value parameter to a stored procedure from a SSRS Report“.

Below are a few use cases for SearchObjectDefinition:

--List All Instance Tables, Stored Procedures, Views and Functions
EXEC dbo.SearchObjectDefinition

--List All Stored Procedures, and Functions in the Databases 
--TestDatabaseOne and TestDatabaseTwo
EXEC dbo.SearchObjectDefinition @ObjectType = 'Sp, Fn'
	,@DatabaseName = 'TestDatabaseOne, TestDatabaseTwo'

--List All Instance Tables, Stored Procedures, Views and Functions 
--where Object Definition contains the word Insert
EXEC dbo.SearchObjectDefinition @strFind = 'insert'

--List All Instance Tables where Object Name is Customers and 
--Column name contains the word Phone
EXEC dbo.SearchObjectDefinition @ObjectType = 'tb'
	,@ObjectName = 'Customers'
	,@strFind = 'Phone'

As always be sure to deploy the following Function and Store Procedure in a utility database not the master database as this is bad practice.

Split Function:

--USE [DatabaseName];
--GO

IF OBJECT_ID('[Split]') IS NULL
	EXEC ('CREATE FUNCTION dbo.[Split](@i INT) RETURNS @RtnValue TABLE (j INT) AS BEGIN INSERT INTO @RtnValue (j) SELECT 1 RETURN END');
GO

ALTER FUNCTION [dbo].[Split] (
	@List NVARCHAR(2000)
	,@SplitOn NVARCHAR(5)
	)
RETURNS @RtnValue TABLE (
	Id INT identity(1, 1)
	,Value NVARCHAR(100)
	)
AS
BEGIN
	WHILE (Charindex(@SplitOn, @List) > 0)
	BEGIN
		INSERT INTO @RtnValue (value)
		SELECT Value = ltrim(rtrim(Substring(@List, 1, Charindex(@SplitOn, @List) - 1)))

		SET @List = Substring(@List, Charindex(@SplitOn, @List) + len(@SplitOn), len(@List))
	END

	INSERT INTO @RtnValue (Value)
	SELECT Value = ltrim(rtrim(@List))

	RETURN
END

SearchObjectDefinition Stored Procedure:

--USE [DatabaseName];
--GO

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

ALTER PROCEDURE [dbo].[SearchObjectDefinition] (
	@ObjectType AS VARCHAR(20) = NULL
	,@ObjectName AS SYSNAME = NULL
	,@DatabaseName AS SYSNAME = NULL
	,@strFind AS VARCHAR(MAX) = NULL
	)
AS
BEGIN
	SET NOCOUNT ON;
	SET @strFind = ISNULL(@strFind, '')
	SET @ObjectName = ISNULL(@ObjectName, '')

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

	DECLARE @DatabaseTable TABLE (DbName SYSNAME)
	DECLARE @DbName AS SYSNAME
	DECLARE @Sql AS VARCHAR(MAX)

	CREATE TABLE #Result (
		DbName SYSNAME NULL
		,ObjectType VARCHAR(2)
		,ObjectName SYSNAME
		,ObjectDefinition VARCHAR(MAX)
		)

	IF @DatabaseName IS NOT NULL
	BEGIN
		INSERT INTO @DatabaseTable (DbName)
		SELECT Value
		FROM dbo.Split(@DatabaseName, ',')
	END

	IF @DatabaseName IS NULL
	BEGIN
		INSERT INTO @DatabaseTable (DbName)
		SELECT NAME
		FROM master.dbo.sysdatabases
		WHERE NAME NOT IN (
				'tempdb'
				,'master'
				,'msdb'
				,'model'
				)
		ORDER BY NAME ASC
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL PROCEDURES  
	IF @ObjectType LIKE '%Sp%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
			USE ' + QUOTENAME(@DbName) + ';
			
			INSERT INTO #Result (
				DbName
				,ObjectType
				,ObjectName
				,ObjectDefinition
				)
			SELECT ''' + @DbName + ''' AS DbName
				,''Sp'' AS ObjectType
				,OBJECT_NAME(OBJECT_ID) AS ObjectName
				,OBJECT_DEFINITION(OBJECT_ID) AS ObjectDefinition
			FROM ' + QUOTENAME(@DbName) + '.sys.procedures
			WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'' + ''' + @strFind + ''' + ''%''
			AND Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
			'

			EXEC (@Sql)
		END
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL VIEWS   
	IF @ObjectType LIKE '%Vw%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
		USE ' + QUOTENAME(@DbName) + ';	
		
		INSERT INTO #Result (
			DbName
			,ObjectType
			,ObjectName
			,ObjectDefinition
			)
		SELECT ''' + @DbName + ''' AS DbName
			,''Vw'' AS ObjectType
			,OBJECT_NAME(OBJECT_ID) AS ObjectName
			,OBJECT_DEFINITION(OBJECT_ID) AS ObjectDefinition
		FROM ' + QUOTENAME(@DbName) + '.sys.VIEWS
		WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'' + ''' + @strFind + ''' + ''%''
		AND Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
			'

			EXEC (@Sql)
		END
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL FUNCTION 
	IF @ObjectType LIKE '%Fn%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
		USE ' + QUOTENAME(@DbName) + ';	
		
		INSERT INTO #Result (
			DbName
			,ObjectType
			,ObjectName
			,ObjectDefinition
			)
		SELECT ''' + @DbName + ''' AS DbName
			,''Fn'' AS ObjectType
			,ROUTINE_NAME AS ObjectName
			,ROUTINE_DEFINITION AS ObjectDefinition
		FROM ' + QUOTENAME(@DbName) + '.INFORMATION_SCHEMA.ROUTINES
		WHERE ROUTINE_DEFINITION LIKE ''%'' + ''' + @strFind + ''' + ''%''
			AND ROUTINE_NAME LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
			AND ROUTINE_TYPE = ''FUNCTION''
		ORDER BY ROUTINE_NAME
			'

			EXEC (@Sql)
		END
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL TABLES OF DATABASE.  
	IF @ObjectType LIKE '%Tb%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
		USE ' + QUOTENAME(@DbName) + ';	
			
		INSERT INTO #Result (
			DbName
			,ObjectType
			,ObjectName
			,ObjectDefinition
			)
		SELECT ''' + @DbName + ''' AS DbName
			,''Tb'' AS ObjectType
			,t.NAME AS ObjectName
			,c.NAME AS ObjectDefinition
		FROM ' + QUOTENAME(@DbName) + '.sys.tables AS t
		INNER JOIN ' + QUOTENAME(@DbName) + '.sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
		WHERE c.NAME LIKE ''%'' + ''' + @strFind + ''' + ''%''
		AND t.Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
		ORDER BY [ObjectDefinition] ASC
			'

			EXEC (@Sql)
		END
	END

	SELECT DbName
		,ObjectType
		,ObjectName
		,ObjectDefinition
	FROM #Result
	ORDER BY DbName ASC
		,ObjectType ASC
		,ObjectName ASC

	DROP TABLE #Result
END

 

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