SQL Server Top 10 Secrets of Tempdb database
1. Tempdb is re-created every time when SQL Server restarts
2. Tempdb is always SIMPLE recovery mode
3. Certain database options can not be set for tempdb like OFFLINE, READONLY & CHECKSUM
4. You can not drop or backedup tempdb database
5. You can not run DBCC CHECKDB against Tempdb
6. You can not drop the guest user from Tempdb
7. Snapshot database can not be created against Tempdb
8. Shrinking Tempdb is not recommended
9. Move Tempdb database to new location after installation of sql server
10. Create a multiple Tempdb files give you better performance one file per CPU( Microsoft Best practice guide line says, It may be good for SAN), You need carefully plan this if you server really want it)
2. Tempdb is always SIMPLE recovery mode
3. Certain database options can not be set for tempdb like OFFLINE, READONLY & CHECKSUM
4. You can not drop or backedup tempdb database
5. You can not run DBCC CHECKDB against Tempdb
6. You can not drop the guest user from Tempdb
7. Snapshot database can not be created against Tempdb
8. Shrinking Tempdb is not recommended
9. Move Tempdb database to new location after installation of sql server
10. Create a multiple Tempdb files give you better performance one file per CPU( Microsoft Best practice guide line says, It may be good for SAN), You need carefully plan this if you server really want it)
How to start sql server without tempdb (Restore tempdb)
When i try to take image of sql server for installation to new server i could not restart sql server service on new server due to tempdb database files missing. In that image server where tempdb database files were placed on E:\mssql\data drive and new sql server only got C: drive and all system dbs there on c: drive files. This solution is tested and worked fine.
-- SQL Server Path
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
-- SQL Server Path
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
Step:1:You are going to start sql using single user with minimal configuration on command prompt
cmd-- > sqlservr -f -m
Now you create Folder on 'C:\MSSQL\DATA\
-- Open the second window for command prompt
c:\sqlcmd
1>
use master
go
ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME='C:\MSSQL\DATA\Tempdb.MDF')
go
ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME='C:\MSSQL\DATA\Templog.LDF')
go
>quit
Could not allocate a new page for database 'TEMPDB' because of insufficient disk space
Error: 1101, Severity: 17, State: 10.
Message:
Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Solution: Increase the data file( mdf) size. using SSMS
(OR)
USE MASTER
GO
ALTER DATABASE TEMPDB MODIFY FILE (NAME='tempdev', SIZE=1500MB)
Message:
Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Solution: Increase the data file( mdf) size. using SSMS
(OR)
USE MASTER
GO
ALTER DATABASE TEMPDB MODIFY FILE (NAME='tempdev', SIZE=1500MB)
Upgraded to SQL 2008 tempdb from earlier version like SQL2005/2000
When you upgraded to SQL 2008 from SQL 2005/2000 make sure to change tempdb database Page_verify_option set to CHECKSUM.
By default in SQL server 2008 Page_verify_option is set CHECKSUM but in SQL 2005 the default is NONE.
You can run the below query to check:
select name,Page_verify_option from sys.databases where name='tempdb'
0 - NONE
1 - TORN_PAGE_DETECTION
2 - CHECKSUM
source: http://msdn.microsoft.com/en-us/library/ms175527.aspx
By default in SQL server 2008 Page_verify_option is set CHECKSUM but in SQL 2005 the default is NONE.
You can run the below query to check:
select name,Page_verify_option from sys.databases where name='tempdb'
0 - NONE
1 - TORN_PAGE_DETECTION
2 - CHECKSUM
source: http://msdn.microsoft.com/en-us/library/ms175527.aspx
Diagnosing tempdb Disk Space Problems
-- Returns space usage of each file in tempdb
select * from sys.dm_db_file_space_usage
--Determining the Amount of Free Space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
--Determining the Longest Running Transaction
SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;
--Determining the Amount of Space Used by Internal Objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;
--Determining the Amount of Space Used by Internal and user Objects in tempdb
SELECT SUM(internal_object_reserved_page_count) as Internal_Pages,
(SUM(internal_object_reserved_page_count)*1.0/128) as Internal_space_MB,
SUM(User_object_reserved_page_count) as User_Pages,
(SUM(internal_object_reserved_page_count)*1.0/128) as User_space_MB
FROM sys.dm_db_file_space_usage;
--Determining the Total Amount of Space (Free and Used)
SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files
select * from sys.dm_db_file_space_usage
--Determining the Amount of Free Space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
--Determining the Longest Running Transaction
SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;
--Determining the Amount of Space Used by Internal Objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;
--Determining the Amount of Space Used by Internal and user Objects in tempdb
SELECT SUM(internal_object_reserved_page_count) as Internal_Pages,
(SUM(internal_object_reserved_page_count)*1.0/128) as Internal_space_MB,
SUM(User_object_reserved_page_count) as User_Pages,
(SUM(internal_object_reserved_page_count)*1.0/128) as User_space_MB
FROM sys.dm_db_file_space_usage;
--Determining the Total Amount of Space (Free and Used)
SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files
How to reduce the allocation resource contention for tempdb
Concurrency enhancements for the tempdb database
To reduce the allocation resource contention for tempdb that is experiencing heavy usage
Solution:
1. In that SQL Server Configuration Server Propertise Add trace flag ;-T1118
2.Increase the number of tempdb data files to be at least equal to the number of processors. Also, create the files with equal sizing( ( Microsoft Best practice guide line says, It may be good for SAN), You need carefully plan this one if your sql server really want it)
Always try to test before move on to production.
To reduce the allocation resource contention for tempdb that is experiencing heavy usage
Solution:
1. In that SQL Server Configuration Server Propertise Add trace flag ;-T1118
2.Increase the number of tempdb data files to be at least equal to the number of processors. Also, create the files with equal sizing( ( Microsoft Best practice guide line says, It may be good for SAN), You need carefully plan this one if your sql server really want it)
Always try to test before move on to production.
The transaction log for database tempdb is full
If you get the error message for Tempdb Transaction log is full.
Msg 9002, Level 17, State 4, Procedure sp_helpdb, Line 19
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Solution: 1
-- First check Tempdb Tran log file size using dbcc sqlperf(logspace)
-- tempdb 999.9922 99.99628 0
USE MASTER
GO
ALTER DATABASE TEMPDB MODIFY FILE (NAME='templog', SIZE=1500MB)
Solution:2
ALTER DATABASE Tempdb
ADD LOG FILE
( NAME = tempdblog2,
FILENAME = 'E:\MSSQL.1\MSSQL\DATA\tempdblog2.ldf',
SIZE = 10MB,
MAXSIZE = 100MB,
FILEGROWTH = 10MB)
Msg 9002, Level 17, State 4, Procedure sp_helpdb, Line 19
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Solution: 1
-- First check Tempdb Tran log file size using dbcc sqlperf(logspace)
-- tempdb 999.9922 99.99628 0
USE MASTER
GO
ALTER DATABASE TEMPDB MODIFY FILE (NAME='templog', SIZE=1500MB)
Solution:2
ALTER DATABASE Tempdb
ADD LOG FILE
( NAME = tempdblog2,
FILENAME = 'E:\MSSQL.1\MSSQL\DATA\tempdblog2.ldf',
SIZE = 10MB,
MAXSIZE = 100MB,
FILEGROWTH = 10MB)
Move the tempdb database to new location to speed up the system
-- Find out the current default location of the tempdb database when you installed in SQL 2005
sp_helpdb tempdb
tempdev 1 E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf
templog 2 E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf
-- Move to new location for fastest disk
USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\MSSQL\Data\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\MSSQL\Data\templog.ldf')
go
-- Find out the current default location of the tempdb database when you installed in SQL 2005
sp_helpdb tempdb
tempdev 1 E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf
templog 2 E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf
-- Move to new location for fastest disk
USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\MSSQL\Data\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\MSSQL\Data\templog.ldf')
go