Post reply

TempDB Usage Query

  • January 22, 2012 at 10:23 am

    #267005

    Hi all

    I recently noticed out TempDB drives are filling up. I ran a query to look at the amount of free space as found below, and noticed there was plenty of space available. I did notice however than a DBCC DBREINDEX job was running.

    http://www.sqlservercentral.com/blogs/steve_jones/2009/11/30/what-s-using-space-in-tempdb/

    When I looked at the running code for this I could see code like the following being run:

    insert [dbo].[PURCHPARMLINE] select * from [dbo].[PURCHPARMLINE]

    Is this the DBCC code repopulating the table after the reindex has completed?

    Also I guess I am going to have to mointor TempDB to find out what caused the initial large growth in the TempDB.

    Thanks

  • January 22, 2012 at 10:52 am

    #1436894

    It seems like a user query.. you can check the columns and data to relate about what this query does.

  • January 22, 2012 at 11:29 am

    #1436899

    I am not sure it was a user query. The SPID was the same SPID number as the DBCC DBREINDEX.

  • January 22, 2012 at 12:43 pm

    #1436907

    Kwisatz78 (1/22/2012)

    When I looked at the running code for this I could see code like the following being run:

    insert [dbo].[PURCHPARMLINE] select * from [dbo].[PURCHPARMLINE]

    Is this the DBCC code repopulating the table after the reindex has completed?

    No, that's the actual reindex itself, that's just how it appears in the DMVs. It's not a user query

    Are you using Sort_In_Temp_DB? Online index rebuilds? Snapshot or read committed snapshot isolation?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 23, 2012 at 8:07 am

    #1437119

    Hi Gail

    I looked at the code that a previous DBA implemented and it looks to be a DBCC DBREINDEX with no options supplied, reading Microsoft articles I am unsure on what the default is. I don't believe that online rebuilds are being performed though reading the article here.

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

    Also I have found out how to set a database to use snapshot article but not how to check if my database is currently using snapshot isolation, is there a select I can do?

    Many thanks

  • January 23, 2012 at 8:23 am

    #1437135

    select snapshot_isolation_state, snapshot_isolation_state_desc, is_read_committed_snapshot_on

    from sys.databases

    where name = <your DB name>

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

  • January 23, 2012 at 9:12 am

    #1437189

    Ahh thanks, the snapshot_isolation_state_dec = OFF and is_read_committed_snapshot_on = 1

  • January 23, 2012 at 9:18 am

    #1437194

    So you are running read committed snapshot. That means that you'll have the version store in TempDB. Depending on the types of transactions and frequency, that can get very large, especially if you have long-running transactions. You'll need to make sure you have enough space for TempDB's normal use (temp tables, work tables, sort spills, etc) as well as the version store.

    You may find this useful - http://msdn2.microsoft.com/en-us/library/ms345124.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 23, 2012 at 9:48 am

    #1437209

    wow - ok thanks.

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

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