I have been recently worked for some client who has huge tempdb size (SQL Server 2000). As you probably know, you cannot reduce physical size of tempdb without restart MSSQLServices. Using the command DBCC SHRINKFILE to shrink the individual tempdb files you must run DBCC SHRINKFILE command while no other activity occurs in the tempdb database. To make sure that other processes cannot use tempdb while DBCC SHRINKFILE executes, you must restart SQL Server in the single user
mode.
So I would like to show some stepS we ended up. The client completely understands the problem cause tempdb database to be growing and I hope it is not short time solution.
1) Stop SQL Server. Open a command prompt, and then start SQL Server by
typing the following command:
"sqlservr -c -f" (remove the quotation)
The -c and -f parameters force 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 issue the following
TSQL commands for the tempdb database files that you need to shrink
use tempdb
go
dbcc shrinkfile (tempdev, 'target size in MB') --for the primary data file
or
dbcc shrinkfile (templog, 'target size in MB')--for the log file
As MS says
"An advantage of DBCC SHRINKFILE is that it can reduce the size of a file to
a size smaller than its original size. A limitation of DBCC SHRINKFILE is that you cannot make the database smaller than the size of the model database."
Subscribe to:
Post Comments (Atom)
3 comments:
Uri,
I'm confused.
At the start of your post, you said that the conventional process for resizing tempdb is to resize the tempdb files manually in the tempdb schema and restart the SQL Server instance (service). The implication is that the outage caused by restarting the SQL Server instance is unfavorable. This part I follow.
In the next part, you describe a solution that uses DBCC SHRINKFILE on the tempdb files (data and log) after first restarting SQL Server instance in single-user mode. While this approach may also achieve your goal functionally, it still requires a SQL Server instance restart (an outage) from production mode to single-user mode. More importantly, the duration of the overall outage will be longer, as single user mode for the SQL Server instance must be in effect while the tempdb files are being resized. When the tempdb files are resized, the SQL Server instance must be restarted again back to regular (not single user) mode, incurring yet another outage. The longer overall outage and greater complexity is what I don't understand as being favorable.
My take is that if you are going to take a hit for an SQL Server instance outage, do all of the preparation for the outage (manually resizing the tempdb files as needed) prior to the outage, restart the SQL Server instance once (minimizing the overall outage period), and then the new tempdb file sizes take effect. I have used this approach both for resizing existing tempdb files (the standard tempdev and templog files) and to add supplemental equal-sized DB files to tempdb - per Microsoft KB article 328551 - PRB: Concurrency enhancements for the tempdb database (link: http://support.microsoft.com/kb/328551), the article "Working with tempdb in SQL Server 2005" (link: http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx#_tempdb_Space_Usage), and other publications.
Another KB article "How to shrink the tempdb database in SQL Server" (link: http://support.microsoft.com/default.aspx?scid=KB;EN-US;307487) also describes both of these procedures, including the single user mode restart.
Perhaps I have missed something in your procedure. If so, please explain the gaps I missed.
Thanks,
Scott R.
Scott
I'll try to explain. You are right, that we can simple restart the SQL Server instance and tempdb will be rebuilt and its size is 1 MB for the data file and 0.5 MB for the log file. But we did have many y activities in tempdb such as you cannot run ALTER DATABASE...(DBCC SHRINKFILE) to resize the file therefore I used -m configuration.Sometimes you do not have time to wait till end of the work day.
Post a Comment