SQL Script to know Temporary Tables and Their Size

Leave a comment (0) Go to comments

Check my previous Post "How to Optimize tempdb ? ", where I have already answered

  • What is Stored in tempdb ?
  • A more about tempdb Internals
  • How to Monitor tempdb
  • How to Optimize tempdb
  • tempdb Space Allocation
  • What sort of problem you may have with tempdb
  • How to Monitoring tempdb Space
  • Tips for Optimizing tempdb
  • How Many Physical Files Should tempdb Have ?
  • How to Move tempdb ?

The Following script will help us to understand, how many temporary tables resides in tempdb and what’s the size for those tables.

There might be a case in where developers has access, where developer keep creating tables in tempdb for backup or temporary storage, but they do forget to drop those which might result tempdb oversized, In that case, we want to delete some temporary tables create in tempdb , which consomes the most storage.

/*--------------------------------------------------------------------------------------------------------------------------------- Description : This stored procedure will send out alert email if there is a blocking which lasted more than specified duration)
-- Copyright 2011 - DBATAG
-- Description : SQL Script to find Temporary Tables and Their Size 
-- Author : DBATAG
-- Created on : 09/01/2011
-- Version : 1.0
-- Dependencies :
-- Table Procedure Permissions
--                   -                          - View Server State
----------------------------------------------------------------------------------------------------------------------------*/
SELECT TBL.name AS ObjName
      ,pstats.row_count AS StatRowCount
      ,pstats.used_page_count * 8 AS UsedSizeKB
      ,pstats.reserved_page_count * 8 AS RevervedSizeKB
FROM tempdb.sys.partitions AS pt
     INNER JOIN tempdb.sys.dm_db_partition_stats AS pstats
         ON pt.partition_id = pstats.partition_id
            AND pt.partition_number = pstats.partition_number
     INNER JOIN tempdb.sys.tables AS TBL
         ON pstats.object_id = TBL.object_id
ORDER BY TBL.name;

OUTPUT Screen Shot


SQL Script to know Temporary Tables and Their Size Temporary Tables Size information Temporary Tables and Their Size

Note : This Script will work only with SQL Server 2005+ environments.

EOF - SQL Script to know Temporary Tables and Their Size, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.