SQL Sysadmin : Clear Backup History (nibble delete)

It is best practice to remove old backup history records from MSDB. Else they will eventually cause MSDB to bloat and Management Studio to become unresponsive when dealing with BACKUPs and RESTOREs.

Note : Only do this when required backups are safely archived away.

Last year I blogged about this and included a script to trim backup history back to a month's data.
SQL Solace : Removing Database Backup History

If housekeeping has not been performed on backup history then many thousands of backup records may exist. For example ;
Say 47 log backups are taken each day, in addition to 1 full backup.
This happens for 10 databases, every day for 5 years. The number of log records is therefore -
48 backup records * 10 databases * 365 days * 5 years = 876000 records

If you find yourself in this situation, the following script will help.
It uses the nibble delete principle and cleans up backup history 1 day at a time, starting with the oldest record.

USE MSDB
GO
DECLARE @OldestBackupDate DATETIME
DECLARE @DaysToLeave INT
DECLARE @DaysToDeleteAtOnce INT
DECLARE @DeleteDate DATETIME

DECLARE @Counter INT
DECLARE @CounterText VARCHAR(30)

SELECT  @OldestBackupDate = MIN(backup_start_date) FROM msdb..backupset  
SELECT  @OldestBackupDate
SET @DaysToLeave = 30
SET @DaysToDeleteAtOnce = 1

SELECT @Counter = DATEDIFF(DAY,@OldestBackupDate,GETDATE())

WHILE @Counter >= @DaysToLeave  
BEGIN   
 SET @CounterText = CONVERT(VARCHAR(30),DATEADD(DAY, -@Counter,GETDATE()),21) 
 SELECT @DeleteDate = CONVERT(VARCHAR(30),DATEADD(DAY, -@Counter,GETDATE()),21) 
 RAISERROR (@CounterText , 10, 1) WITH NOWAIT   
 EXEC sp_delete_backuphistory @DeleteDate ;
 SELECT @Counter = @Counter - @DaysToDeleteAtOnce  
END 

It reports progress to screen, like this -

2006-06-17 16:27:59.970

Backup history older than Jun 17 2006 4:27PM has been deleted.

2006-06-18 16:28:42.067

Backup history older than Jun 18 2006 4:28PM has been deleted.

2006-06-19 16:30:08.853

Backup history older than Jun 19 2006 4:30PM has been deleted.

2006-06-20 16:31:32.653

Referential Integrity via Information_Schema views

Tables with Primary Keys -

-- Tables with Primary Keys defined
-- Note : Multiple rows are returned when the PK involves more than one column
SELECT  TC.TABLE_NAME
      ,CU.COLUMN_NAME
      ,TC.CONSTRAINT_NAME
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
      INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
              ON TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
             AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'

Tables with Foreign Keys -

                                   
-- Tables with Foreign Keys defined
-- Note : Multiple rows are returned when the FK involves more than one colum
SELECT  TC.TABLE_NAME
      ,CU.COLUMN_NAME
      ,TC.CONSTRAINT_NAME
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
      INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
              ON TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
             AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'

How the keys are linked -

                                
-- How Referential Integrity is enforced
-- i.e. data being present in related table before insert is allow
SELECT  UNIQUE_CONSTRAINT_NAME AS PRIMARY_KEY_CONSTRAINT
      ,CONSTRAINT_NAME        AS FOREIGN_KEY_CONSTRAINT
FROM   INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Greater detail about the FK to PK Relationships.
Includes Table and Column information.

                                
-- How Referential Integrity is enforced
-- Expand to show referenced columns
SELECT   CONSTRAINTSLINK.CONSTRAINT_NAME        AS FOREIGN_KEY_CONSTRAINT
        ,FOREIGNKEY.TABLE_NAME                  AS REFERENCINGTABLE
        ,FOREIGNKEY.COLUMN_NAME                 AS REFERENCINGCOLUMN
        ,CONSTRAINTSLINK.UNIQUE_CONSTRAINT_NAME AS PRIMARY_KEY_CONSTRAINT
        ,PRIMARYKEY.TABLE_NAME                  AS REFERENCEDTABLE
        ,PRIMARYKEY.COLUMN_NAME                 AS REFERENCEDCOLUMN
FROM     INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS CONSTRAINTSLINK
        INNER JOIN (SELECT TC.TABLE_NAME
                           ,UC.COLUMN_NAME
                           ,TC.CONSTRAINT_NAME
                    FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
                           INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE UC
                                   ON TC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
                                  AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY') PRIMARYKEY
          ON CONSTRAINTSLINK.UNIQUE_CONSTRAINT_NAME = PRIMARYKEY.CONSTRAINT_NAME
        INNER JOIN (SELECT TC.TABLE_NAME
                           ,UC.COLUMN_NAME
                           ,TC.CONSTRAINT_NAME
                    FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
                           INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE UC
                                   ON TC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
                                  AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY') FOREIGNKEY
          ON CONSTRAINTSLINK.CONSTRAINT_NAME = FOREIGNKEY.CONSTRAINT_NAME
                                              
ORDER BY  CONSTRAINTSLINK.CONSTRAINT_NAME
        ,FOREIGNKEY.TABLE_NAME
        ,FOREIGNKEY.COLUMN_NAME

Rename SQL installation

Am taking no credit for this.
Script from a colleague, it lets you rename a sql server, i.e. tell SQL about the fact that the server has been renamed!

DECLARE  @machine  SYSNAME,
        @instance SYSNAME

SELECT @instance = CASE
                  WHEN CHARINDEX('\',@@SERVERNAME) = 0 THEN ''
                  ELSE SUBSTRING(@@SERVERNAME,CHARINDEX('\',@@SERVERNAME),
                                   (LEN(@@SERVERNAME) + 1) - CHARINDEX('\',@@SERVERNAME))
                  END

SELECT @machine = CONVERT(NVARCHAR(100),SERVERPROPERTY('MACHINENAME')) + @instance;


EXEC SP_DROPSERVER
 @@SERVERNAME;

EXEC SP_ADDSERVER
 @machine ,'local'

NB : Remember to restart the SQL Server service after running the script.

TSQL 2005 - ALL

SQL Function to evaluate results of a subquery >

IF 130 > ALL (SELECT Rate FROM HumanResources.EmployeePayHistory)
 PRINT 'No employee is paid more than 130.'
ELSE
 PRINT 'There are employees paid more than 130.'

TSQL : Users & Logins

TSQL for querying systems objects for database users & logins (2000 & 2005) -

--sql 2000 -- list database users
select name 
from sysusers
where islogin = 1
and uid not in (0,1,2,3,4) -- exclude internal sql acounts

--sql 2005 -- list database users
select name 
from sys.database_principals
where type = 'S' -- sql login
and principal_id not in (0,1,2,3,4) -- exclude internal sql acounts

-- sql 2000 -- list orphanned users for current database
select name 
from sysusers
where islogin = 1
and uid not in (0,1,2,3,4) 
and sid not in (select sid from sys.syslogins) -- exclude mapped logins

--sql 2005 -- list orphanned users for current database
select name 
from sys.database_principals
where type = 'S' -- sql login
and principal_id not in (0,1,2,3,4) -- exclude internal sql acounts
and sid not in (select sid from sys.server_principals) -- exclude mapped logins

-- code to demonstrate sql users present in db, but not mapping to sql server logins

select * from sys.sysusers
      join sys.syslogins
  on sys.sysusers.name = sys.syslogins.name
    and sys.sysusers.sid <> sys.syslogins.sid

--sql 2000 - users that need remapping to login following RESTORE
select 'sp_change_users_login ''update_one'',''' + name + ''','''+ name + '''' 
from sysusers where islogin = 1
and uid not in (0,1,2,3,4) 
and sid not in (select sid from sys.syslogins) -- exclude mapped logins

--sql 2005 - users that need remapping to login following RESTORE
select 'sp_change_users_login ''update_one'',''' + name + ''','''+ name + '''' 
from sys.database_principals
where type = 'S' -- sql login
and principal_id not in (0,1,2,3,4) -- exclude internal sql acounts
and sid not in (select sid from sys.server_principals) -- exclude mapped logins

TSQL : Blocking & Locking

Tasks waiting right now -

SELECT * FROM Sys.dm_os_waiting_tasks

Tasks being blocked right now -

SELECT * FROM Sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL

Bit difficult to test when your system is running fine, but a query for clearly seeing the details of blocking issues -

SELECT PROCESS_BLOCKING.SPID                 'Holding ID',
RTRIM(PROCESS_BLOCKING.STATUS)        'Status',
'Lock Type' = CASE SYSLOCKINFO.RSC_TYPE
WHEN 1 THEN NULL
WHEN 2 THEN 'DATABASE'
WHEN 3 THEN 'FILE'
WHEN 4 THEN 'INDEX'
WHEN 5 THEN 'TABLE'
WHEN 6 THEN 'PAGE'
WHEN 7 THEN 'KEY'
WHEN 8 THEN 'EXTENT'
WHEN 9 THEN 'RID'
WHEN 10 THEN 'APPLICATION'
ELSE NULL
END,
SUSER_SNAME(PROCESS_BLOCKING.SID)     'Holding User',
SUSER_SNAME(PROCESS_WAITING.SID)     'Waiting User',
PROCESS_WAITING.SPID                 'Waiting ID',
'Database' = CASE
WHEN SYSLOCKINFO.RSC_DBID = 0 THEN '[NULL]'
ELSE DB_NAME(SYSLOCKINFO.RSC_DBID)
END,
SYSLOCKINFO.RSC_OBJID            'Object ID',
RTRIM(PROCESS_BLOCKING.HOSTNAME)      'Holding Host',
RTRIM(PROCESS_WAITING.HOSTNAME)      'Waiting Host',
RTRIM(PROCESS_BLOCKING.PROGRAM_NAME)  'Holding Program',
RTRIM(PROCESS_WAITING.PROGRAM_NAME)  'Waiting Program',
PROCESS_BLOCKING.CMD                  'Holding Command',
PROCESS_WAITING.CMD                  'Waiting Command',
PROCESS_BLOCKING.CPU                  'CPU Time',
PROCESS_BLOCKING.PHYSICAL_IO          'I/O',
PROCESS_BLOCKING.MEMUSAGE             'Mem Usage'
FROM   MASTER.DBO.SYSLOCKINFO
JOIN MASTER.DBO.SYSPROCESSES PROCESS_BLOCKING
ON SYSLOCKINFO.REQ_SPID = PROCESS_BLOCKING.SPID
JOIN MASTER.DBO.SYSPROCESSES PROCESS_WAITING
ON SYSLOCKINFO.REQ_SPID = PROCESS_WAITING.BLOCKED
AND PROCESS_BLOCKING.SPID = PROCESS_WAITING.BLOCKED
WHERE  PROCESS_BLOCKING.SPID <> @@SPID

Find all internal sql objects (including undocumented ones)

Note is_ms_shipped clause below -

SELECT * FROM sys.all_objects
WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC')
AND [is_ms_shipped] = 1
ORDER BY [name];

To find functions, use type of  -
FN SQL Scalar Function
IF Inline Table Valued Function
TF SQL Table Valued Function

To find views, use type of  -
V View

To find procedures, use type of -
P SQL Stored Procedure
PC CLR Stored Procedure
X Extended Stored Procedure