SQL DBA - Delete OLD Backup files Using TSQL - SQL server 2005

Step 1 :

            USE MASTER

GO

Step 2 :

EXEC SP_CONFIGURE 'show advanced options',1

GO

Step 3 :

EXEC SP_CONFIGURE 'XP_CMDSHELL',1

GO

RECONFIGURE

GO

Step 4:

TSQL TO Delete All the Backup Files

DECLARE @DBbackuppath varchar(8000)

DECLARE @DBName varchar(255)

DECLARE @Command varchar(255)

DECLARE @Backup_finish_date datetime

DECLARE DATABASES_CURSOR CURSOR FOR

SELECT Physical_device_name,Backup_finish_date,database_name FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE msdb..backupset.type = 'D' -- add a condition if u need a condition on Backup_finish_date

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBbackuppath,@Backup_finish_date,@DBName

WHILE @@FETCH_STATUS = 0

BEGIN

      FETCH NEXT FROM DATABASES_CURSOR INTO @DBbackuppath,@Backup_finish_date,@DBName

      set @DBbackuppath = 'del '+@DBbackuppath

      exec master..xp_cmdshell  @DBbackuppath

      END

CLOSE DATABASES_CURSOR

DEALLOCATE DATABASES_CURSOR

SQL DBA : Killing All the Session in a DB

USE MASTER

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_KillDatabaseProcesses]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sp_KillDatabaseProcesses]

GO

CREATE PROCEDURE dbo.sp_KillDatabaseProcesses

( @databaseName varchar(100))

AS

DECLARE @databaseId int,

        @sysProcessId int,

        @cmd varchar(1000)

EXEC ('USE MASTER')

SELECT @databaseId = dbid FROM master..sysdatabases

  WHERE [name] = @databaseName

DECLARE sysProcessIdCursor CURSOR FOR

   SELECT spid FROM [master]..[sysprocesses] WHERE [dbid] = @databaseId

OPEN sysProcessIdCursor

FETCH NEXT FROM sysProcessIdCursor INTO @sysProcessId

WHILE @@fetch_status = 0

  BEGIN

    SET @cmd = 'KILL '+ convert(nvarchar(30),@sysProcessId)

    PRINT @cmd

    EXEC(@cmd)

    FETCH NEXT FROM sysProcessIdCursor INTO @sysProcessId

END

DEALLOCATE sysProcessIdCursor            

GO