Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 How to shrink the Temp db

Author  Topic 

pankaj2910
Starting Member

31 Posts

Posted - 2014-09-01 : 06:57:08
Hi,

I am new in SQL server, I tried to shrink the tempdb
using the following queries but not successful :

1.) DBCC SHRINKFILE(tempdev, 200)
msg : Msg 8985, Level 16, State 1, Line 1
Could not locate file 'tempdev' for database 'master' in sys.database_files. The file either does not exist, or was dropped.


2.) DBCC SHRINKDATABASE(tempdb, 20)
msg: DBCC SHRINKDATABASE: File ID 1 of database ID 2 was skipped because the file does not have enough free space to reclaim.
DBCC SHRINKDATABASE: File ID 2 of database ID 2 was skipped because the file does not have enough free space to reclaim.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Please help me to shrink the tempdb.

pankajrocks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-09-01 : 08:23:29
read this KB
http://support.microsoft.com/kb/307487


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-09-01 : 09:50:47
The question would arise why do you want to shrink tempdb( data and log file). Are you facing a space issue. If so this link 'http://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx' would help you to troubleshoot space issue by finding out queries which are filling tempdb. Your acctual aim should be to find out what is causing tempdb to grow out f proportion. Shrinking ( if you succeed ) is temporary solution after shrinking data and log files would again increase because queries are utilizing tempdb.

Hope this helps

Regards
Shanky
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page
   

- Advertisement -