Shrinking SQL server tempdb database to avoid corruption

In SQL Server, a database called tempdb is allocated for worktable / #temp table usage. Sometimes when the tempdb exceeds its maximum size limit, it starts behaving in weird manner. Possibilities of database corruption are very high when size of tempdb is larger than it should be, as when size is above the defined limit, the user experiences following problems:

• He finds an error message in event log saying “The log file for database “tempdb’ is full. Back up the transaction log for the database to free up some log space”.

• An error message in Query Analyzer saying “Could not allocate new page for database ‘TEMPDB’. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.”

To solve these issues and avoid SQL database corruption caused by them, shrinking tempdb database is must.

Shrinking tempdb databas : SQL Recovery

For shrinking tempdb, you will need to restart MS SQL Server. Follow below mentioned steps to restart and shrink tempdb database:

• 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.

• 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

• 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.

The best way to avoid a tempdb database from growing is not setting maximum size for it. But when it exceeds the maximum size you can shrink it using above mentioned procedure and avoid corruption and other errors and issues.

Comments are closed.