Clean your MSSQL TempDB file and more

If you write an odd query on your SQL server, you may have made your tempdb large. This can cause problems by filling your disk. While Tempdb stores temporary tables and cached information that helps with the performance of your server, you may want to shrink the table and allow it to rebuild – thus making your server perform better. Here’s a query that may help.

The apostrophe ‘ may need to be changed if you copy and paste this query.

use tempdb
GO

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE (‘ALL’)
DBCC FREESESSIONCACHE
DBCC SHRINKDATABASE(tempdb, 10);
dbcc shrinkfile (‘tempdev’)
dbcc shrinkfile (‘templog’)
GO

SELECT name, size
FROM sys.master_files
WHERE database_id = DB_ID(N’tempdb’);
GO

About TCAT Shelbyville IT Department

The Tennessee College of Applied Technology - is one of 46 institutions in the Tennessee Board of Regents System, the seventh largest system of higher education in the nation. This system comprises six universities, fourteen community colleges, and twenty-six Applied Technology Colleges.
This entry was posted in Technology. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s