TEMP DATABASE

TEMPDB !!  Did we knew Everything ??

Does it feel bad when you thought you know everything about something!! And ... Hold On...

The myth just got busted!!

It happened for me :) and it was nothing other then our own SQL Temp DB..!!  Hope the below helps every one of my blog reader in some ways (Will keep it short)!!

What’s Tempdb??

<< General Overview>>
The tempdb is a temporary workspace. Among other uses, SQL Server uses the tempdb for: 

·         Storage of explicitly created temporary tables.

·         Worktables that hold intermediate results created during query processing and sorting.

·         Materialized static cursors.

<< Lets go deep >>

Every thing doesn't get logged : SQL Server records only enough information in the tempdb transaction log to roll back a transaction .REDO operations are just never needed ... Why ??.. This is because tempdb is re-created every time you restart SQL Server; therefore, it does not have any transactions to roll forward or roll back. This feature helps in 2 ways!!

·         Increases the performance of INSERT statements

·         Saves us Log Space

Shrinking is not fun !! : Tempdb is the only DB which doesn't like to be shrinked so easy. To shrink tempdb you need to make sure it doesn't have any active transactions going on.

2005 Management Studio is Screwed !!: Never believe the tempDB size displayed with 2005 Management studio ( 2008 is good ). It does not show the correct size of tempdb files after a shrink operation. The 'Currently allocated space' value is always pulled from sys.master_files DMV and this value never gets updated after shrink operation:

To find the correct size use the below query :

use tempdb

select (size*8) as FileSizeKB from sys.database_files

Shrink the tempdb in SQL Server !!

Lets Start -- were you aware of below??

Have you come across a situation, of you shrinking the tempdb (With any of 3 options mentioned below) and ... Its just doesn't work !! Well!! Did you know the below

"If you run DBCC SHRINKDATABASE/DBCC SHRINKFILE, no other activity should be occurring with the tempdb database." 


If you have any active connection with the tempDB it simply won’t shrink or will fail with any of below errors:

Server: Msg 2501, Level 16, State 1, Line 1 Could not find table named '1525580473'. Check sysobjects.

-or-

Server: Msg 8909, Level 16, State 1, Line 0 Table Corrupt: Object ID 1, index ID 0, page ID %S_PGID. The PageId in the page header = %S_PGID.

So make sure these:

·         For other processes not to use tempdb while DBCC SHRINKDATABASE is run, start SQL Server in single user mode (Safest).

           OR

·         Check if you have any active Transactions in tempdb (Using DBCC Opentran), if not go ahead keeping fingers crossed.

Back to Point

TempDB can be shrunk with any of below 3 options (Choose your best!!)

Method 1

This method requires you to restart SQL Server.

1.    Stop SQL Server. Open a command prompt, and then start SQL Server by typing the following command:

sqlservr -c -f 

The -c and -f parameters cause SQL Server to start in a minimum configuration mode with a tempdb size of 1 MB for the data file and 0.5 MB for the log file.

2.    Connect to SQL Server with Query Analyzer, and then run the following Transact-SQL commands:

ALTER DATABASE tempdb MODIFY FILE

(NAME = 'tempdev', SIZE = target_size_in_MB)

--Desired target size for the data file

ALTER DATABASE tempdb MODIFY FILE

(NAME = 'templog', SIZE = target_size_in_MB)

--Desired target size for the log file

    

3.    Stop SQL Server by pressing Ctrl-C at the command prompt window, restart SQL Server as a service, and then verify the size of the Tempdb.mdf and Templog.ldf files.

Limitations:

·         Only operates on the default tempdb logical files, tempdev and templog. 

·         Additional files can be shrunk with other (Below) 2 methods once SQL Service starts

·         All tempdb files are re-created during start up; therefore, they are empty and can be removed using (ALTER DATABASE .... Remove FILE Option)

Method 2

Use the DBCC SHRINKDATABASE command to shrink the tempdb database as a whole. DBCC SHRINKDATABASE receives the parameter target_percent, which is the desired percentage of free space left in the database file after the database is shrunk. If you use DBCC SHRINKDATABASE, you may have to restart SQL Server.

1.    Determine the space currently used in tempdb by using the sp_spaceused stored procedure. Then, calculate the percentage of free space left for use as a parameter to DBCC SHRINKDATABASE; this calculation is based on the desired database size.

Note In some cases you may have to execute sp_spaceused @updateusage=true to recalculate the space used and to obtain an updated report. Refer to SQL Server Books Online for more information about the sp_spaceused stored procedure.

Consider this example:

Assume that tempdb has two files, the primary data file (Tempdb.mdf), which is 100 MB in size and the log file (Tempdb.ldf), which is 30 MB. Assume that sp_spaceused reports that the primary data file contains 60 MB of data. Also assume that you want to shrink the primary data file to 80 MB. Calculate the desired percentage of free space left after the shrink, 80 MB - 60 MB = 20 MB. Now, divide 20 MB by 80 MB = 25% and that is your target_percent. The transaction log file is shrunk accordingly, leaving 25% or 20 MB of space free after the database is shrunk.

2.    Connect to SQL Server with Query Analyzer, and then run the following Transact-SQL commands:

dbcc shrinkdatabase (tempdb, 'target percent')

   -- This command shrinks the tempdb database as a whole

·         The target size for data and log files cannot be smaller than the size specified when the database was created ( OR redefined with ALTER DATABASE or DBCC Shrinkfile)

·         DBCC SHRINKDATABASE is the calculation of the target_percentage parameter and its dependency on the current space used.

Method 3

Use the command DBCC SHRINKFILE to shrink the individual tempdb files. DBCC SHRINKFILE provides more flexibility than DBCC SHRINKDATABASE because you can use it on a single database file without affecting other files that belong to the same database. DBCC SHRINKFILE receives the target size parameter, which is the desired final size for the database file. 

1. Determine the desired size for the primary data file (tempdb.mdf), the log file (templog.ldf), and/or additional files added to tempdb. Make sure that the space used in the files is less than or equal to the desired target size.
2. Connect to SQL Server with Query Analyzer, and then run the following Transact-SQL commands for the specific database files that you need to shrink:

•    use tempdb
•    go
•    dbcc shrinkfile (tempdev, 'target size in MB')
•    go

   -- This command shrinks the primary data file

•    dbcc shrinkfile (templog, 'target size in MB')
•    go

-- This command shrinks the log file, look at the last paragraph.

     

Advantage

·         Can reduce the size of a file to a size smaller than its original size. 

·         Can be issues on any of the data or log files.

Limitations

·          You cannot make the database smaller than the size of the model database. 

Look out for my next ... !! Something more on 

TempDB !!