Unable to Reduce the TempDB size

Nagaraj S

11y957

Dear Members,

I would like to know if there is anyway to clear the TempDB in my SQL Server 2008.But with several options whichever resulted in my Google search, but nothing seem to be worked.

Issue Details:

Actually i am running a query which would take probably more than 3 hours to complete an operation ( I am doing the import process for than 1.5 lakh data). Due to this my TempDB increases more than DB size and it causes serve issue in my application.

Please anyone give me the clue to solve my issue..

Thanks

Nagaraj.S

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nagaraj S11y ago
@Nishant Mittal

Thanks for your reply. Restarting the SQL Service might not be solution for this issue, since DB Size reaches its Maximum Drive size even before end of the query execution. I am working on it, if i find any other solution will reply you back.
0
Nishant Mittal11y ago
One more ..hope below link will help you

http://dba.stackexchange.com/questions/19231/tempdb-is-getting-full-very-quickly-in-microsoft-sql-server-2008
0

Nishant Mittal11y ago

This I got it on Microsoft Site..

By default, the tempdb database is configured to autogrow as needed. Therefore, this database may unexpectedly grow in time to a size larger than the desired size. A simple restart of SQL Server resets the size of tempdb to its last configured size.

In SQL Server 2005 and later versions, you can use any of the following methods to alter the size of tempdb.

Method	Requires restart?	More information
ALTER DATABASE	Yes	Gives complete control on the size of the default tempdb files (tempdev and templog).
DBCC SHRINKDATABASE	No	Operates at database level.
DBCC SHRINKFILE	No	Lets you shrink individual files.
SQL Server Management Studio	No	This is basically a way to shrink database files through the GUI.

Hope it will solve your issue