Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Resize the current allocated space for Tempdb

Beantwortet Resize the current allocated space for Tempdb

  • Thursday, January 24, 2013 4:33 AM
     
      Has Code
    Sign In to Vote
    0

    Hi,

    I want to reduce the current size of tempdb. It grew to 8gb. Initially set to 8MB - it grew to 8gb. As it stands most of the space in temp db is now free. Due to contranints I cannot restart sql server which would reset the temp db mdf file size. 

    I tried couple of options as below :

    USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, SIZE=<size of the data file>);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, SIZE=<size of the log file>);
GO

    I want to reduce the currently allocated space for tempdb without having to restart sql server services. Is there a possible way to achieve this ?

    Please guide..

    Thanks,

    EVA05

     

All Replies

  • Thursday, January 24, 2013 4:58 AM
     
     
    Sign In to Vote
    0

    Few links on the similar issue, have a look

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/7fd4c213-5314-4a0e-9c44-b93d97a390d2/

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/0aa22872-4010-4009-b448-0c5f1107b0d7/

    Regards
    Satheesh

     
  • Thursday, January 24, 2013 5:01 AM
     
     
    Sign In to Vote
    0

    Hi,

    The only way to do this "online" is to be certain that there is no activity occuring in tempdb and the only way to be sure of that is to be running in single user mode which kind of defeats the purpose.

    If you attempt to shrink a tempdb files while tempdb is being used it may lead to corruption errors and would require a restart to re-build tempdb anyway.

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

    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

     
  • Thursday, January 24, 2013 5:04 AM
     
      Has Code
    Sign In to Vote
    0

    Try this it worked for me:

    USE [tempdb] 
GO 
DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY) 
GO
    Regards,Eshwar.

    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

     
  • Thursday, January 24, 2013 5:17 AM
     
     
    Sign In to Vote
    0

    I have tried all of the above options. It does not reduce the space allocated to the tempdb mdf file. I want to reduce the mdf file size of the temp db which is currently 8gb. Although very less space is actually used now- it grew to 8gb because at some point it encountered the usage. I know if I restart services it will automatically reduce the mdf file size of tempdb. But as restarting services is not an option I am looking at other ways to achieve this.

    Please guide..

    Thanks

    EVA05

     
  • Thursday, January 24, 2013 6:56 AM
     
     
    Sign In to Vote
    0

    Click on tempdb and set new size to 8 MB.

    It just re sized of file

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA

    Check the tempdb.mdf .it will sized 8 MB.

    It shows in SSMS 8Gb.

    -- Bhavesh Thakkar

    Sr.Database Administrator
    My Blog
    Linked In Profile


    • Edited by Database Experts Thursday, January 24, 2013 7:08 AM
    • Proposed As Answer by Database Experts Thursday, January 24, 2013 8:21 AM
    • Unproposed As Answer by eva05 Thursday, January 24, 2013 2:36 PM
    •  
     
  • Thursday, January 24, 2013 2:37 PM
     
     
    Sign In to Vote
    0
     can you tell exact step sequence that would allow to reduce the mdf file size for tempdb.  I could not locate the option to set new size when I click tempdb or right click tempdb

    EVA05

     
  • Thursday, January 24, 2013 8:36 PM
     
     Answered
    Sign In to Vote
    1
    A shrink will only work if there is nothing locking the data, the only way to ensure that is to stop processes using tempdb and the only way to be certain of that is to restart or single user mode. There is no magic behind this the shrink will either work cleanly or it won't and you will need to take the outage.

    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!