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
Note : This Script will work only with SQL Server 2005+ environments.
