Tag Archives: start SQL Server in minimal configuration mode

Shrink tempdb database in SQL Server

Yesterday I had to shrink the tempdb database on SQL Server.  I follow SQL Server tempdb best practices , but rarely have I shrunk tempdb before.  Despite the discussion on whether or not, or how many files to separate tempdb into, I expanded upon the instructions on how to shrink the tempdb database in SQL Server.

Of the options available, Method 1 Use Transact-SQL commands, has always been my preferred method. This is due to the fact that all three ways require restarting SQL Server, so I favor the one that allows me complete control over the size of the tempdb files.  Nevermind I cannot guarantee tempdb is not in use in my environment like I should have for methods 2 and 3.

When, I follow Method 1 for shrinking tempdb, I:

1.Stop SQL Server.  You can do this from SQL Server Management Studio (SSMS) by right-clicking on your server instance and selecting Stop .

2.  Start SQL Server in minimal configuration mode.  At a command prompt, type the following command to start SQL Server:

sqlservr -c -f

In minimum configuration mode tempdb has a size of 1 MB for the data file and 0.5 MB for the log file.

Note If you are unable to start SQL Server, you may need to navigate to “Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\”

If you use a SQL Server named instance, you must change to the appropriate folder (Program Files\Microsoft SQL Server\MSSQL$instance name\Binn) and use the -s switch (-s%instance_name%).

3.Connect to SQL Server by using Query Analyzer, and run the following Transact-SQL commands:

Syntax
ALTER DATABASE tempdb MODIFY FILE
(NAME = ‘tempdev’, SIZE = target_size_in_MB)
— Target size for the data file

ALTER DATABASE tempdb MODIFY FILE
(NAME = ‘templog’, SIZE = target_size_in_MB)
— Target size for the log file

Example
ALTER DATABASE tempdb MODIFY FILE
(NAME = ‘tempdev’, SIZE = 2048)
— Set the data file size to 2 GB.

ALTER DATABASE tempdb MODIFY FILE
(NAME = ‘templog’, SIZE = 1024)
— Set the log file size to 1 GB.

4. Stop SQL Server by pressing Ctrl+C at the Command line.

5. Start your SQL Server service in SSMS by right-clicking your SQL Server and clicking Start .

6. Log into SQL Server and verify the size of the tempdb.mdf and templog.ldf files.  You can do this in SSMS by expanding the System Databases folder, Right-click tempdb , choose Properties , and click Files in the left pane.  The tempdb file sizes will be in the right pane.