How to move Tempdb
If you come across following errors in log file,
Source: MSSQLSERVER
Event ID: 17052
Description: The LOG FILE FOR DATABASE 'tempdb' IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free
up SOME LOG SPACE
Get the logical filenames for tempdb and use it in NAME parameter in the below query.
sp_helpdb tempdb
or Just run the below command
Use tempdb;
sp_helpfile
We found that the location of my tempdb database files tempdev and templog is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\, which is the default location.
then use this query to move tempdb
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'D:\MSSQL\Data\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'D:\MSSQL\Data\templog.ldf')
go
Then we need to Restart SQL SERVER.
Now your tempdb will be started from the new drive location and you may delete the old files.
Move Tempdb – Don’ts
Don’t try and move tempdb using a backup and restore method as you will receive an error
Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Don’t try and detach tempdb using sp_detach_db @dbname=’tempdb’ as you will again see an error
Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
/////////////////////////////////////////
How to find Tempdb Contention
Here is the query to check for page contention in tempDB.
Select session_id,
wait_type,
wait_duration_ms,
blocking_session_id,
resource_description,
ResourceType = Case
When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 1 % 8088 = 0 Then 'Is PFS Page'
When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 2 % 511232 = 0 Then 'Is GAM Page'
When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 3 % 511232 = 0 Then 'Is SGAM Page'
Else 'Is Not PFS, GAM, or SGAM page'
End
From sys.dm_os_waiting_tasks
Where wait_type Like 'PAGE%LATCH_%'
And resource_description Like '2:%'
Good Articles
http://michaeljswart.com/2013/01/pagelatch-contention-on-21103/
http://www.sqlsoldier.com/wp/sqlserver/breakingdowntempdbcontention
http://www.sqlservercentral.com/blogs/robert_davis/2010/03/05/Breaking-Down-TempDB-Contention/
http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/175/tempdb-page-contention.aspx
/////////////////////////////////////////////////////////////////////////////
Query 1 - Script to Check TempDB Speed
There is a dynamic management function (DMF) sys.dm_io_virtual_file_stats for all of the TempDB data files and lists out how fast they’re responding to write and read requests:
SELECT files.physical_name, files.name,
stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files
ON stats.database_id = files.database_id
AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'
We’re looking for two things:
Are writes being evenly distributed between data files?
Are writes finishing in 20ms or less?
If the answer is no to either of those questions, we’ve got some performance tuning work to do.
Query 2: Code to Find the Size and Growth Percentage of the tempdb Data and Log Files
SELECT
name AS FileName,
size*1.0/128 AS FileSizeinMB,
CASE max_size
WHEN 0 THEN 'Autogrowth is off.'
WHEN -1 THEN 'Autogrowth is on.'
ELSE 'Log file will grow to a maximum size of 2 TB.'
END,
growth AS 'GrowthValue',
'GrowthIncrement' =
CASE
WHEN growth = 0 THEN 'Size is fixed and will not grow.'
WHEN growth > 0 AND is_percent_growth = 0
THEN 'Growth value is in 8-KB pages.'
ELSE 'Growth value is a percentage.'
END
FROM tempdb.sys.database_files;
GO
//////////////////
Great Articles
http://support.microsoft.com/kb/2154845
http://www.mssqltips.com/sqlservertip/1432/tempdb-configuration-best-practices-in-sql-server/
http://www.mssqltips.com/sqlservertip/1388/properly-sizing-the-sql-server-tempdb-database/
http://dbadiaries.com/tempdb-best-practices
//////////////////
Unable to Shrink Tempdb Data files have grown large.
You may receive this error message.
File ID of database ID cannot be shrunk as it is either being shrunk by another process or is empty
In any case we can restart SQL instance to re-initialize tempdb.
However, its not easy to restart for critical application databases.
Restarting the SQL services is very difficult sometime,
last option that can be tried that you can reinitialize by increasing the data file size to 1MB and then start shrink activity.
USE tempdb;
SELECT
name,
size * 8 / 1024
FROM sys.database_files;
Find out the size of the data files and add 1 MB.
ALTER DATABASE tempdb
MODIFY FILE
(NAME = 'tempdev', SIZE = <current database size + 1 MB>);
ALTER DATABASE tempdb
MODIFY FILE
(NAME = 'tempdev2', SIZE = <current database size + 1 MB>);
After execute the command, DBCC SHRINKFILE should works again without any error message.
DBCC SHRINKFILE ('tempdev', 1024)
Still unable to shrink...?
The next obvious step would be to check for any open transaction on tempdb.
SELECT * FROM sys.dm_exec_requests WHERE database_id = 2
No open transactions! Alright, any process holding locks on tempdb?
select * from sys.dm_tran_locks
where resource_database_id= 2
No locks! There is sys.dm_db_session_space_usage DMV which helps to track the number of page allocation and deallocation by each session on the instance.
select * from sys.dm_db_session_space_usage
where user_objects_alloc_page_count<> 0
We can clear the user sleeping session in case we see something here.
The another key thing is, any session would use tempdb for creating some temporary objects. Since the database was not shrinking, obviously some user defined tables would be there on it. Execute this...
SELECT * FROM tempdb..sys.all_objects
where is_ms_shipped = 0
The is_ms_shipped column would be 1 for all the system objects. Hence, we can get the detail of user objects temporarily created...
In case we get objects list with #tablename in name column. We need to run below.
DBCC FREEPROCCACHE
Note: Its not recommended to clear the procedure cache, it will force the SPROCs to be recompiled and thereby effects performance. Anyhow, If tempdb is more of a concern than the stored procedures being recompiled. We can go ahead and clear the Procedure Cache using the above mentioned DBCC.
Still if there is no luck
Finally
You can also use DBCC FREESYSTEMCACHE (‘ALL’) to clear the plan cache for the SQL server instance, but it would result decrease in the query performance temporarily.
DBCC FREESYSTEMCACHE (‘ALL’)
GO
You can also use DBCC FREESESSIONCACHE to flush the distributed query connection cache used by distributed queries against the SQL server instance.
DBCC FREESESSIONCACHE
GO
After execute the command, DBCC SHRINKFILE should works again without any error message.
DBCC SHRINKFILE ('tempdev', 1024)
Thanks
No comments:
Post a Comment