Post reply

Shrinking the tempdb DB

  • January 12, 2011 at 4:12 pm

    #232192

    Hi All,

    I have a tempdb database in a production environment which is 135gb. The data file is most of it. Its size is 135 gb. To break it down

    Most of it may be 99.99% is unallocated space of the 135 gb.

    Only a very small part is used in the data and other files.

    The db is in simple recovery model.

    I want to free the space to the OS. How do I do that?

    I tried using the shrink though the management studio and used the shrink action to be release unused space option.

    It did not help.

    Please suggest.

  • January 12, 2011 at 4:24 pm

    #1272717

    You can't shrink TempDB if it's in use.

    Check this

    http://support.microsoft.com/kb/307487

    Thank You,

    Best Regards,

    SQLBuddy

  • January 12, 2011 at 8:45 pm

    #1272777

    You could use some DMVs to find out what exactly is consuming space in tempdb among these : - user objects, internal objects, and version stores

    Below DMV's will give you a good idea on that

    sys.dm_db_file_space_usage

    sys.dm_db_session_space_usage

    sys.dm_db_task_space_usage

    For more info on querying these DMV's, refer below link

    http://msdn.microsoft.com/en-us/library/ms176029.aspx

    I am quite sure, you will get the answer to your problem if you refer below thread.

    http://www.sqlservercentral.com/Forums/Topic705182-146-1.aspx

    Do let us know if you are still unable to shrink tempdb.

    M&M

  • January 12, 2011 at 9:18 pm

    #1272785

    Thanks for the reply.

    I was able to restart sql server in single user mode.

    I could not find query analyzer in sql server 2008 to connect. I lam able to connect to the management studio by logging in as

    one of the administrators. It does not allow me to open a new query window. Its says cannot access as its in single user mode.

    My sql server service runs under the local system account. Am I missing anything on the permissions?

    I am unable to run the dbcc shrinkdatabase file command as I could not find the query analyzer.

    I am using the developer edition 2008

  • January 13, 2011 at 3:29 am

    #1272903

    Suri, if you were able to restart the SQL Server in single-user mode, your problem should already been gone away, as SQL Server drops and re-creates the tempdb when a restart occurs.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 13, 2011 at 10:41 am

    #1273180

    I originally specified a file size under the files tab in the properties window for the DB. Now when I look at the report disk usage, I see that lot of space is unallocated. I want to release the unallocated space to the OS.

    My restart in single user mode did not help. Am I missing something?

    Thanks,

    Suri

  • January 13, 2011 at 10:45 am

    #1273184

    was a restart or did you just place the database in single user mode.

    just placing the databse in single user mode does not restart the service thus restarting tempdb....

    you would have to actually restart the instance to get tempdb to start over at a smaller size..

    of course, if you have tempdb "preallocated" to a huge amount, it will always start at that size....

  • January 13, 2011 at 11:48 am

    #1273208

    If you restart your instance, then you should start with a new TempDB database with the default size. But this should be your last option.

    Thank You,

    Best Regards,

    SQLBuddy

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply