SQL Server Performance

Temp Tables Names

Discussion in 'SQL Server 2005 General DBA Questions' started by jn4u, Dec 14, 2007.

  1. jn4u Member

    If have found a table intempdb that’s are givning me dbcc checkdb errors. I don’t know how to besttrack this. I have the object id of the temp table
    object_id=1487071236
    USE TEMPDB
    select * from sys.all_objects where object_id=1487071236
    name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published
    #58A2E804,1487071236,NULL,1,0,U,USER_TABLE,2007-12-13 10:09:05.460,2007-12-14 10:46:09.437,0,0,0
    The name #58A2E804 …
    How does this relate to CreateTable #MyTable Names? How can I found out where this table is defined?
  2. satya Moderator

    IF this is a temp table then it should be cleared once the process is completed within your query, you coudl catch hold more information by running profiler.
  3. jn4u Member

    Well my nightly job reported….
    DBCC CHECKDB (tempdb) executedby AIR5XXXX found 48 errors and repaired 0 errors. Elapsed time: 0 hours 0minutes 0 seconds.
    Made a DBCC CHECKDB when I cometo work it reported no errors. Restarted the server just in case to get newtempdb. I went some more days. And nightly jobs started to show errors again intempdb. This time when I run the dbcc checkdb I found error.
    <Dbcc ID="125"Error="8964" Severity="16" State="1">Tableerror: Object ID 1487071236, index ID 0, partition ID 72057594953531392, allocunit ID 72057594965131264 (type LOB data). The off-row data node at page(1:908), slot 21, text ID 12844728320 is not referenced.</Dbcc>
    Then I tried to match the 1487071236with sys.all_objects found out that was user_table
    I run DBCC some cuples ofhourse later and errors are gone. Like someone drops the temp table.
    Is the only way to start trackthe use of with profiler?
  4. satya Moderator

    Sounds like the drive where TEMPDB is located had lots of issues, make sure to check with your vendor for a complete health check on the server.
    If possible try to move the TEMPDB from thsi drive.
  5. jn4u Member

    I found this in the help file for Check Database Integrity Task
    • All databases

      Generate a maintenance plan that runs maintenance tasks against all Microsoft SQL Server databases except tempdb.
    • All system databases

      Generate a maintenance plan that runs maintenance tasks against each of the SQL Server system databases except tempdb. No maintenance tasks are run against user-created databases.
    Is it any reason you this task don't test tempdb? Shall i remove the tempdb aslo from my sql loop?
  6. satya Moderator

    Yes it applies as you need not to do checks on the tempdb, but still based on the errors reports it is better to check with vendor for a health state.
  7. jn4u Member

    I will check with vendor.

Share This Page