SQL Server Performance


Discussion in 'SQL Server 2005 General DBA Questions' started by klehonia, Aug 6, 2010.

  1. klehonia New Member

    Hello everyone, my system is sql server 2005 standard edition and i noticed that database tempdb is growing continuously. Running a script that identifies the size of indexes and tables i found that system encrypted table sysobjvalues is allocated the 95% of the size of the database (about 4,2 GB) and always grows up. I know that the only way to read this table is to enable dac but the point is, can i truncate this table so as to reclaim the space or i have to restart sql server to empty tempdb? What exactly is written there? I will face a disk space problem.
    Thank you in advance.
  2. Luis Martin Moderator

    Did you run DBCC checktable on sysobjvalues?
  3. klehonia New Member

    I run checkdb on tempdb database and nothing found.
  4. ashish287 New Member

    you can run dbcc shrinkfile(templog, 10) (or whatever size you want your templog to be reduiced upto)
  5. klehonia New Member

    Database tempdb is in simple recovery model, the tempdev file grows up.
  6. Luis Martin Moderator

    If you application has a lot of sort, tempdb grow. If you are lack of disk, then stop and start sql service, to reduce tempdb to original size.
  7. satya Moderator

    Do you have immediate threat of free disk space here?
    If not leave the size of tempdb like that which can be used by SQL Server as and when it is required, it is a best practice that not to restart the SQL Server for the sake of tempdb and when you do it the size will be default and again you have to increase the same. Further information on how best you can keep up the TEMPDB see http://sqlserver-qa.net/blogs/perftune/archive/tags/tempdb/default.aspx post.

Share This Page