Category Archives: SQL Server 2005 Scripts

Table size in all DBs

create Procedure Mu_TableSizeas if object_id(‘TableStorage’) is  null  –drop table TableStorageCREATE TABLE  TableStorage( [ServerName] [nvarchar](128) NOT NULL, [DatabaseName] [nvarchar](128) NOT NULL, [TableName] [nvarchar](128) NOT NULL, [RowCount] [bigint] NULL, [TotalSpace] [bigint] NULL, [DataSpace] [bigint] NULL, [IndexSpace] [bigint] NULL, [UnusedSpace] [bigint] NULL, [DateInfoCollected] [datetime] NULL) declare @command1 varchar(500), @command2 varchar(500), @command3 varchar(500), @replacechar … Continue reading

Posted in SQL Server 2005 Scripts | 100 Comments

Indexusage stats

The following script is written by Greg_Linwood, it very useful when you want to check your index usage based on the table…  create procedure [dbo].[usp_helpindexusagestats] @tablename varchar(1000) as set nocount on   –collect output from system supplied [sp_helpindex] if object_id(‘tempdb..#helpindex’) > … Continue reading

Posted in SQL Server 2005 Scripts | 56 Comments

Database size free and used

set nocount on declare @cmd varchar(500) declare @db varchar(128) create table #results(FileType    varchar(4) NOT NULL,                       [Name]      sysname NOT NULL,                       Total       numeric(9,2) NOT NULL,                       Used        numeric(9,2) NOT NULL,                       [Free]      numeric(9,2) NOT NULL,                       dbname      sysname NULL) create … Continue reading

Posted in SQL Server 2005 Scripts | 66 Comments

Report Blocker and Blockee (Waiter) SQL Statements

Report Blocker and Waiter SQL Statements Description Sample stored procedure that reports real time blocker and waiter SQL statements. This stored procedure, which requires Microsoft SQL Server 2005, lists real time blocks, including the blocked statement and the blocker. Script … Continue reading

Posted in SQL Server 2005 Scripts | 29 Comments

List Real-Time Blocker and Waiter Statements

List Real-Time Blocker and Waiter StatementsDescriptionSample script that lists real-time blocker and waiter SQL statements. This script requires Microsoft SQL Server 2005.  Script Code  select t1.resource_type ,db_name(resource_database_id) as [database] ,t1.resource_associated_entity_id as [blk object] ,t1.request_mode ,t1.request_session_id — spid of waiter ,(select … Continue reading

Posted in SQL Server 2005 Scripts | 20 Comments

Compare Signal Waits and Resource Waits

Compare Signal Waits and Resource Waits Description Sample script that compares signal waits and resource waits. Signal waits are the time spent in the runnable queue waiting for the CPU, while resource waits are the time spent waiting for the … Continue reading

Posted in SQL Server 2005 Scripts | 14 Comments

List Currently-Executing Statements

List Currently-Executing Statements Description Sample script that lists currently-executing statements. A status equal to runnable indicates that the user waiting on the CPU. Note that each scheduler has its own runnable queue. This script, contributed by Microsoft’s Tom Davidson, requires … Continue reading

Posted in SQL Server 2005 Scripts | 39 Comments