The location of ‘Tempdb’ database files tempdev and templog is at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data which is the default location. Due to the large filesize of tempdb.mdf, We need to change the location of this file to another drive (X).
First, check the location of TempDB Data and Log Files. Execute the below mentioned script to get the logical name for the temp data and log file and also the location of TempDB data and log file.
1 2 3 4 5 |
-- To find logical name for the temp data and log file USE TempDB GO EXEC sp_helpfile GO |
OR
1 2 3 4 5 6 7 8 9 10 |
-- To find logical name for the temp data and log file Use master GO SELECT name AS [LogicalName], physical_name AS [Location], state_desc AS [Status] FROM sys.master_files WHERE database_id = DB_ID(N'tempdb'); GO |
Now need to change the location of TempDB Data and Log files using ALTER DATABASE statements. Execute the below scripts to change the temp database to the new drive.
1 2 3 4 |
USE master GO ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' ) Go |
Example
1 2 3 4 5 6 |
USE master GO ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'x:\datatempdb.mdf') GO ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'x:\datatemplog.ldf') GO |
Now restart SQL services, the change will take place.
DMV ‘sys.dm_exec_requests’ provides details on all of the processes running in SQL Server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT qs.Session_ID, Blocking_Session_ID, qs.Status, Wait_Type, Wait_Time, Wait_Resource, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text, GetDate() SnapshotDateTime, --Open_Transaction_Count, ss.PROGRAM_NAME, ss.HOST_NAME, ss.Login_Name FROM sys.dm_exec_requests AS qs INNER JOIN sys.dm_exec_sessions ss ON qs.session_id = ss.session_id CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE Wait_Time > 0 ORDER BY Wait_Time DESC |
“Please let us know if there…