SQL Server: Wait for long backups, recovery, or restores

USE master;
GO

IF ( EXISTS ( SELECT * FROM sys.procedures p WHERE p.object_id = OBJECT_ID('dbo.uspAdminWaitForProcess') ) ) 
DROP PROCEDURE dbo.uspAdminWaitForProcess;
GO

CREATE PROCEDURE dbo.uspAdminWaitForProcess (
   @CommandName NVARCHAR(450) = 'BACKUP DATABASE' -- 'DB STARTUP' 'RESTORE DATABASE' 'KILLED/ROLLBACK'
  ,@DatabaseName SYSNAME = NULL
  ,@strPause NCHAR(8) = '00:00:01'
)
AS

SET NOCOUNT ON;

DECLARE @dtStart DATETIME;
DECLARE @dtEnd DATETIME;
SET @dtStart = GETDATE();
SET @dtEnd = @dtStart;

IF ( @CommandName IS NULL )
BEGIN

  SELECT 
     CommandName = er.command
    ,DatatbaseName = DB_NAME(er.database_id)
    ,PercentComplete = CONVERT(FLOAT,er.percent_complete)
    ,SessionId = er.session_id
    ,TotalElapsedTimeSeconds = CONVERT(FLOAT,er.total_elapsed_time)/1000.0
    ,EstimatedTimeSeconds = CONVERT(FLOAT,er.estimated_completion_time)/1000.0
    ,WaitResource = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.wait_resource))),'')
    ,WaitTime = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.wait_time))),'')
    ,WaitType = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.wait_type))),'')
    ,SessionBlockingId = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.blocking_session_id))),'')
    ,Reads = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.reads))),'')
    ,Writes = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.writes))),'')
    ,CPUTime = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.cpu_time))),'')
  FROM sys.dm_exec_requests er
  WHERE 
    (
      @DatabaseName IS NULL
      OR DB_NAME(er.database_id) = @DatabaseName
    )
  ORDER BY DB_NAME(er.database_id),er.command;

  RETURN 0;
END

DECLARE @dtRuntime DATETIME;
DECLARE @strCommandName NVARCHAR(450);
DECLARE @strDatatbaseName SYSNAME;
DECLARE @fltPercentComplete FLOAT;
DECLARE @fltEstimatedTimeSeconds FLOAT;
DECLARE @fltTotalElapsedTimeSeconds FLOAT;

DECLARE @intSessionId INT;
DECLARE @intSessionBlockingId NVARCHAR(450);
DECLARE @strWaitResource NVARCHAR(450);
DECLARE @strWaitTime NVARCHAR(450);
DECLARE @strWaitType NVARCHAR(450);
DECLARE @intReads NVARCHAR(450);
DECLARE @intWrites NVARCHAR(450);
DECLARE @intCPUTime NVARCHAR(450);

DECLARE @fltEstimatedTimePrior FLOAT;
DECLARE @fltEstimatedTimePriorDiffSec FLOAT;

DECLARE @fltEstimatedTimeMIN FLOAT;
DECLARE @fltEstimatedTimeHRS FLOAT;
DECLARE @fltEstimatedTimeDAY FLOAT;

DECLARE @fltTotalElapsedTimeMIN FLOAT;
DECLARE @fltTotalElapsedTimeHRS FLOAT;
DECLARE @fltTotalElapsedTimeDAY FLOAT;

DECLARE @strMessage NVARCHAR(4000);

SET @fltEstimatedTimePrior = NULL;

WHILE ( EXISTS (
  SELECT *
  FROM sys.dm_exec_requests er
  WHERE 
    er.command = @CommandName 
    AND (
      @DatabaseName IS NULL
      OR DB_NAME(er.database_id) = @DatabaseName
    )
))
BEGIN

  SELECT 
     @dtRuntime = GETDATE()
    ,@strCommandName = er.command
    ,@strDatatbaseName = DB_NAME(er.database_id)
    ,@fltPercentComplete = CONVERT(FLOAT,er.percent_complete)
    ,@intSessionId = er.session_id
    ,@fltTotalElapsedTimeSeconds = CONVERT(FLOAT,er.total_elapsed_time)/1000.0
    ,@fltEstimatedTimeSeconds = CONVERT(FLOAT,er.estimated_completion_time)/1000.0
    ,@strWaitResource = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.wait_resource))),'')
    ,@strWaitTime = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.wait_time))),'')
    ,@strWaitType = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.wait_type))),'')
    ,@intSessionBlockingId = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.blocking_session_id))),'')
    ,@intReads = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.reads))),'')
    ,@intWrites = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.writes))),'')
    ,@intCPUTime = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.cpu_time))),'')
  FROM sys.dm_exec_requests er
  WHERE 
    er.command = @CommandName 
    AND (
      @DatabaseName IS NULL
      OR DB_NAME(er.database_id) = @DatabaseName
    );

  SET @fltEstimatedTimeMIN = @fltEstimatedTimeSeconds / 60.0
  SET @fltEstimatedTimeHRS = @fltEstimatedTimeSeconds / 60.0 / 60.0; 
  SET @fltEstimatedTimeDAY = @fltEstimatedTimeSeconds / 60.0 / 60.0 / 24.0;

  SET @fltTotalElapsedTimeMIN = @fltTotalElapsedTimeSeconds / 60.0
  SET @fltTotalElapsedTimeHRS = @fltTotalElapsedTimeSeconds / 60.0 / 60.0; 
  SET @fltTotalElapsedTimeDAY = @fltTotalElapsedTimeSeconds / 60.0 / 60.0 / 24.0;

  IF ( 
    COALESCE(@fltEstimatedTimeSeconds,0) IS NOT NULL
    AND
    COALESCE(@fltEstimatedTimePrior,0) IS NOT NULL
    )
    SET @fltEstimatedTimePriorDiffSec = COALESCE(@fltEstimatedTimeSeconds,0) - COALESCE(@fltEstimatedTimePrior,0);
  ELSE
    SET @fltEstimatedTimePriorDiffSec = NULL;

  IF ( @dtRuntime IS NULL OR @strCommandName IS NULL OR @strDatatbaseName IS NULL )
    BREAK;

  SET @strMessage = '';
  SET @strMessage = @strMessage + CONVERT(NVARCHAR(50),@dtRuntime,121 );
  SET @strMessage = @strMessage + ': ' + LEFT(@strCommandName,50);
  SET @strMessage = @strMessage + ': ' + LEFT(@strDatatbaseName,50);
  SET @strMessage = @strMessage + ': Finished ' + CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,7),@fltPercentComplete)) + '%'
  SET @strMessage = @strMessage + ': Left ' + CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,3),@fltEstimatedTimeMIN)) + ' min'
  SET @strMessage = @strMessage + ': Left ' + CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,3),@fltEstimatedTimeHRS)) + ' hrs'
  SET @strMessage = @strMessage + ': Left ' + CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,3),@fltEstimatedTimeDAY)) + ' day'

  -- IF ( @fltEstimatedTimePriorDiffSec IS NOT NULL ) SET @strMessage = @strMessage + ': Diff ' + igAdministration.dbo.FormatString(@fltEstimatedTimePriorDiffSec,'#,##0.000') + ' sec'

  SET @strMessage = @strMessage + ': Runtime ' + CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,3),@fltTotalElapsedTimeMIN)) + ' min'
  SET @strMessage = @strMessage + ': Runtime ' + CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,3),@fltTotalElapsedTimeHRS)) + ' hrs'
  SET @strMessage = @strMessage + ': Runtime ' + CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,3),@fltTotalElapsedTimeDAY)) + ' day'

  IF ( @intSessionId IS NOT NULL ) SET @strMessage = @strMessage + ': SessionId ' + CONVERT(NVARCHAR(450),@intSessionId)
  IF ( @strWaitResource IS NOT NULL ) SET @strMessage = @strMessage + ': Wait Resource ' + @strWaitResource
  IF ( @strWaitTime IS NOT NULL ) SET @strMessage = @strMessage + ': Wait Time ' + @strWaitTime
  IF ( @strWaitType IS NOT NULL ) SET @strMessage = @strMessage + ': Wait Type ' + @strWaitType
  IF ( @intSessionBlockingId IS NOT NULL ) SET @strMessage = @strMessage + ': Blocking Session ' + @intSessionBlockingId

  IF ( @intReads IS NOT NULL ) SET @strMessage = @strMessage + ': Reads ' + @intReads
  IF ( @intWrites IS NOT NULL ) SET @strMessage = @strMessage + ': Reads ' + @intWrites
  IF ( @intCPUTime IS NOT NULL ) SET @strMessage = @strMessage + ': CPU Time ' + @intCPUTime

  SET @fltEstimatedTimePrior = @fltEstimatedTimeSeconds;

  RAISERROR('%s',0,1,@strMessage) WITH NOWAIT;
  WAITFOR DELAY @strPause;
END
GO

-- USAGE:
-- EXEC dbo.uspAdminWaitForProcess @CommandName='RESTORE DATABASE';

Clear Database Tuning Advisor Tables

When using the SQL Server Database Tuning Advisor (DTA) the UI can get clustered with all the completed sessions. You could right-click each session and delete them. Or, you could just run this SQL:

USE msdb
GO

-- -------------------------------------------------------------------------------
-- Clear Database Tuning Advisor Tables
-- -------------------------------------------------------------------------------

TRUNCATE TABLE DTA_output;
TRUNCATE TABLE DTA_progress;
TRUNCATE TABLE DTA_reports_indexcolumn;
TRUNCATE TABLE DTA_reports_partitionscheme;
TRUNCATE TABLE DTA_reports_querycolumn;
TRUNCATE TABLE DTA_reports_querydatabase;
TRUNCATE TABLE DTA_reports_queryindex;
TRUNCATE TABLE DTA_reports_querytable;
TRUNCATE TABLE DTA_reports_tableview;
TRUNCATE TABLE DTA_tuninglog;
GO

DELETE FROM DTA_reports_table;
DELETE FROM DTA_reports_partitionfunction;
DELETE FROM DTA_reports_database;
DELETE FROM DTA_reports_column;
DELETE FROM DTA_reports_index;
DELETE FROM DTA_input;
DELETE FROM DTA_reports_query;
GO

Wipe all objects in a database

Have you ever wanted to wipe out a whole database? All the objects?

Tested on SQL Server 2008 R2:

IF ( EXISTS ( SELECT * FROM sys.procedures p WHERE p.object_id = OBJECT_ID('[dbo].[ResetWarehouse]') ) )
DROP PROCEDURE [dbo].[ResetWarehouse];
GO

CREATE PROCEDURE [dbo].[ResetWarehouse] (
  @booDebug BIT = 0
)
AS
SET NOCOUNT ON;
DECLARE @svr SYSNAME = @@SERVERNAME;
IF ( @svr != 'YOUR_DEV_SERVER' )
BEGIN
  RAISERROR('WRONG SERVER: %s IS NOT YOUR_DEV_SERVER',25,1,@svr) WITH LOG;
  RETURN -1;
END

-- =============================================
-- Author: Brad Simpson
-- Create date: 2008-08-25@10:24
-- Description:	clean up
-- =============================================

SET @booDebug = COALESCE(@booDebug,0);
DECLARE @SQL as NVARCHAR(max);
DECLARE @tblObjects TABLE (
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
  ,ObjectId INT NOT NULL
  ,SchemaName SYSNAME NOT NULL
  ,ObjectName SYSNAME NOT NULL
  ,ObjectType SYSNAME NOT NULL
  ,ObjectIdParent INT NULL
  ,SchemaParent SYSNAME NULL
  ,ObjectParent SYSNAME NULL
  ,ObjectTypeParent SYSNAME NULL
  ,CountLoops INT NOT NULL DEFAULT(0)
);

WITH typeDesc ( OrderBy,type_desc ) AS (
SELECT 01,'FOREIGN_KEY_CONSTRAINT'
UNION ALL SELECT 02,'UNIQUE_CONSTRAINT'
UNION ALL SELECT 03,'DEFAULT_CONSTRAINT'
UNION ALL SELECT 04,'PRIMARY_KEY_CONSTRAINT'
UNION ALL SELECT 05,'SQL_INLINE_TABLE_VALUED_FUNCTION'
UNION ALL SELECT 06,'SQL_SCALAR_FUNCTION'
UNION ALL SELECT 07,'SQL_TABLE_VALUED_FUNCTION'
UNION ALL SELECT 08,'SQL_STORED_PROCEDURE'
UNION ALL SELECT 09,'USER_TABLE'
UNION ALL SELECT 10,'VIEW'
)
INSERT INTO @tblObjects ( [ObjectId],[SchemaName],[ObjectName],[ObjectType],[ObjectIdParent],[SchemaParent],[ObjectParent],[ObjectTypeParent] )
SELECT
   ObjectId = o.object_id
  ,SchemaName = s.name
  ,ObjectName = o.name
  ,ObjectType = o.type_desc
  ,ObjectIdParent = po.object_id
  ,SchemaParent = ps.name
  ,ObjectParent = po.name
  ,ObjectTypeParent = po.type_desc
FROM
  sys.schemas s
  INNER JOIN sys.objects o ON s.schema_id = o.schema_id
  LEFT OUTER JOIN sys.objects po ON o.parent_object_id != 0 AND o.parent_object_id = po.object_id
  LEFT OUTER JOIN sys.schemas ps ON po.schema_id = ps.schema_id
  INNER JOIN typeDesc td ON o.type_desc = td.type_desc
WHERE
  s.name NOT IN ('dbo','sys','INFORMATION_SCHEMA')
  AND NOT ( s.name = 'dbo' AND o.name = 'ResetWarehouse' AND o.type_desc = 'SQL_STORED_PROCEDURE' )
ORDER BY td.OrderBy,o.type_desc,s.name,o.name;

DECLARE @ID INT;
DECLARE @ObjectId INT;
DECLARE @SchemaName SYSNAME;
DECLARE @ObjectName SYSNAME;
DECLARE @ObjectType SYSNAME;
DECLARE @ObjectIdParent INT;
DECLARE @SchemaParent SYSNAME;
DECLARE @ObjectParent SYSNAME;
DECLARE @ObjectTypeParent SYSNAME;
WHILE ( EXISTS ( SELECT * FROM @tblObjects ) )
BEGIN
  SELECT TOP(1)
     @ID = o.ID
    ,@SQL = ''
    ,@ObjectId = o.ObjectId
    ,@SchemaName = o.SchemaName
    ,@ObjectName = o.ObjectName
    ,@ObjectType = o.ObjectType
    ,@ObjectIdParent = o.ObjectIdParent
    ,@SchemaParent = o.SchemaParent
    ,@ObjectParent = o.ObjectParent
    ,@ObjectTypeParent = o.ObjectTypeParent
  FROM @tblObjects o
  ORDER BY o.CountLoops,o.ID;

  --IF ( @ObjectIdParent IS NULL )
  --  RAISERROR('Object: %s.%s: %s',0,1,@SchemaName,@ObjectName,@ObjectType) WITH NOWAIT;
  --ELSE
  --  RAISERROR('Object: %s.%s: %s ON %s.%s: %s',0,1,@SchemaName,@ObjectName,@ObjectType,@SchemaParent,@ObjectParent,@ObjectTypeParent) WITH NOWAIT;

  SELECT @SQL = CASE @ObjectType
    WHEN 'DEFAULT_CONSTRAINT'     THEN 'ALTER TABLE ' + QUOTENAME(@SchemaParent) + '.' + QUOTENAME(@ObjectParent) + ' DROP CONSTRAINT ' + QUOTENAME(@ObjectName) + ';'
    WHEN 'FOREIGN_KEY_CONSTRAINT' THEN 'ALTER TABLE ' + QUOTENAME(@SchemaParent) + '.' + QUOTENAME(@ObjectParent) + ' DROP CONSTRAINT ' + QUOTENAME(@ObjectName) + ';'
    WHEN 'PRIMARY_KEY_CONSTRAINT' THEN 'ALTER TABLE ' + QUOTENAME(@SchemaParent) + '.' + QUOTENAME(@ObjectParent) + ' DROP CONSTRAINT ' + QUOTENAME(@ObjectName) + ';'
    WHEN 'UNIQUE_CONSTRAINT'      THEN 'ALTER TABLE ' + QUOTENAME(@SchemaParent) + '.' + QUOTENAME(@ObjectParent) + ' DROP CONSTRAINT ' + QUOTENAME(@ObjectName) + ';'

    WHEN 'SQL_INLINE_TABLE_VALUED_FUNCTION' THEN 'DROP FUNCTION ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'
    WHEN 'SQL_SCALAR_FUNCTION'              THEN 'DROP FUNCTION ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'
    WHEN 'SQL_TABLE_VALUED_FUNCTION'        THEN 'DROP FUNCTION ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'

    WHEN 'SQL_STORED_PROCEDURE' THEN 'DROP PROCEDURE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'
    WHEN 'VIEW'                 THEN 'DROP VIEW '      + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'
    WHEN 'USER_TABLE'           THEN 'DROP TABLE '     + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'
    ELSE NULL END

  UPDATE o
  SET o.CountLoops = COALESCE(o.CountLoops,1) + 1
  FROM @tblObjects o
  WHERE o.ID = @ID;

  SET @SQL = NULLIF(LTRIM(RTRIM(@SQL)),'');
  IF ( @SQL IS NOT NULL )
  BEGIN
    BEGIN TRY
      --RAISERROR('%s',0,1,@SQL) WITH NOWAIT;
      IF ( @booDebug = 0 ) EXEC sp_executesql @SQL;
      DELETE o FROM @tblObjects o WHERE o.ID = @ID;
    END TRY
    BEGIN CATCH
      SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage;
      RETURN 0;
    END CATCH
  END  
  DELETE FROM o FROM @tblObjects o WHERE o.CountLoops > 100;
END
GO