question

Lakshmi avatar image
Lakshmi asked

Reducing size of tempdb in SQL Server 2005

I am trying to reduce the size of temdb file from 50Gb to 25 Gb and need help on how to do it. Please help.
sql-server-2005sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
If tempdb is growing to this size because of normal workload on your server, then regularly shrinking it isn't going to help, however here's a Microsoft support document that gives you the options: [How to shrink the tempdb database in SQL Server][1] [1]: http://support.microsoft.com/kb/307487
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I am not trying to shrink the tempdb file. i am trying to reduce the initial size as it was sized incorrectly.
0 Likes 0 ·
That's covered in Method 1 in the document. You'll have to resize the database, then restart SQL.
0 Likes 0 ·
Sharma avatar image
Sharma answered
Change Temp Database and log file size and restart the Service. ALTER DATABASE tempdb MODIFY FILE (NAME = 'temp_Data', SIZE = target_size_in_MB) ALTER DATABASE tempdb MODIFY FILE (NAME = 'temp_log', SIZE = target_size_in_MB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Srishiva avatar image
Srishiva answered
it is better to use shrink file for the tempdb
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If you refer to the document in my answer you will see that all methods may require a server restart (including DBCC SHRINKFILE) and attempting to shrink the database whilst it is in use can run the risk of corruption, or at best a failed shrink operation. I would rather plan my downtime, than have data corruption and be forced to restart the server now.
2 Likes 2 ·
@Srishiva : Why?
1 Like 1 ·
kev riley:for sql server restart we need down time for oltp databases.
0 Likes 0 ·
Kevin is correct. You will not be able to shrink tempdb while it is in use. TempDB is rebuilt each time the service is restarted. Sounds like your best bet is to resize your initial tempdb allocations, and then restart the service. Hopefully you have a regular maintenance schedule where you can fit the restart in.
0 Likes 0 ·
As Kevin says, shrinking TempDB has been documented to cause corruption and you should not do so. Alter the file sizes and restart SQL. See http://support.microsoft.com/kb/307487 for details on the corruption issue.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.