SQL Server – Tempdb – Management – Moving Files to another Drive

Background

Based on Storage Analysis, we determined that moving TempDB to a separate disk will likely yield performance gains.

Analysis

Please review “SQL Server – What is in your TempDB?“, here, to review TempDB utilization.

It is important to do so as:

  1. You will only be able to shrink to the extent that the Storage is not in active usage

 

Outline

Here are the steps that we will take:

  1. Review the current utilization size, content, and usage pattern of your TempDB
  2. Allocate new temp data and log files on your new Physical Drive
  3. Free up system resources and user sessions that are currently using Temp-db resources
  4. Disable auto-growth on original Temp-DB data and log files
  5. Shrink original Temp-DB data and log files
  6. Review new sizes on original files
  7. If shrinkage targets are not met, repeat Temp-db usage review and shrinkage

Code

Code – Add New Files


USE [master];
GO

ALTER DATABASE [tempdb] 
ADD FILE 
(
	  NAME = N'tempdev2'
	, FILENAME = N'G:\Microsoft\SQLServer\tempdb\tempdb_data_2.ndf' 
	, SIZE = 8GB 
	, FILEGROWTH = 200MB
);

ALTER DATABASE [tempdb] 
ADD LOG FILE
(
	  NAME = N'tempdbLog2'
	, FILENAME = N'G:\Microsoft\SQLServer\tempdb\tempdb_log_2.ldf' 
	, SIZE = 8GB 
	, FILEGROWTH = 200MB
);

Shrink TempDB Data File

Code – Free up TempDB Usage

Procedure & System Cache


DBCC FREEPROCCACHE -- clean cache
DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
DBCC FREESESSIONCACHE -- clean session cache
Go

Checkpoint
Go


Disable Tempdb data file growth


ALTER DATABASE [tempdb]
MODIFY FILE
(
	  NAME = 'templog'
	, FILEGROWTH = 0MB
)
	

Shrink Tempdb data files


dbcc shrinkfile (tempdev, 1)
go

Shrink TempDB Log File

Code – Disable TempDB Log file growth


   ALTER DATABASE [tempdb]
   MODIFY FILE
   (
        NAME = 'templog'
      , FILEGROWTH = 0MB
   )

 

Code – Shrink TempDB Log File


   dbcc shrinkfile (templog, 1)
   go

 

Code – Get File Stats


Select  
		  [name]

		, physical_name

		, [SizeInMB]
			= [size] / 128.0 

                , [UsedInMB]
			= Fileproperty(name, 'SpaceUsed') / 128.0 

		, [AvailableSpaceInMB]
			= (size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int))
				/128.0 

               , PercentFull
			= Cast((Fileproperty(name, 'SpaceUsed') * 100.0) / size As Int)

	       , is_percent_growth

	       , [growth]
			= case
					when is_percent_growth=0 then
						cast(	sd.growth/256 as varchar(30)) + ' MB'
					else cast(sd.growth as varchar(30)) + '%'
			  end	

from sys.master_files sd

where  sd.database_id = db_id('tempdb')


 

Output

postMoveFileStats

 

Instrumentation

Resource Monitor

ResourceMonitor-20160506-0404PM

Explanation

Here we see that tempdb usage …

  • is distributed among our new allocation on drive (G:)
  • And, the original location on Drive E:

 

Summary

Shrinking TempDB Log files is a lot easier.

On the other hand, TempDB data files are a lot more difficult to shrink.

The reasons are multiplex:

  1. Objects that are currently utilizing the data files will likely not be moved until their contents are freed.
    • This is more likely with user tables
    • System and internal tables stay on, until the SQL Instance is restarted

References

  1. Monitoring tempdb Transactions and Space usage
    https://blogs.msdn.microsoft.com/deepakbi/2010/04/13/monitoring-tempdb-transactions-and-space-usage/

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s