sql solace: May 2007

Wednesday, 30 May 2007

Source Code 'Prettifier'

I cannot promise to always use it, but theres a new tool on the simple talk site to make source code more presentable for display.

http://www.simple-talk.com/prettifier/

Tuesday, 29 May 2007

USP_KillUserProcess

CREATE PROCEDURE [dbo].[usp_KillUserProcesses] @user varchar(100)
AS

BEGIN
 
SET NOCOUNT ON

 -- variable declaration and initial values
 DECLARE @chv_killprocessestring varchar(8000)
 DECLARE @ins_initialprocesses smallint
 DECLARE @ins_remainingprocesses smallint

 SET @ins_initialprocesses = 0
 SET @ins_remainingprocesses = 0
 SET @chv_killprocessestring = ''

 -- Fetch number of processes to kill 
 SELECT  @ins_initialprocesses = COUNT(*)
 FROM master..sysprocesses 
 WHERE   loginame = @user
 AND  SPID <> @@spid

 -- Check there are processes to kill
 IF @ins_initialprocesses > 0 
 
 BEGIN
  -- Build command string of processes to kill >
  SELECT @chv_killprocessestring = coalesce(@chv_killprocessestring,',' ) + 'KILL ' + convert(varchar, spid)+ '; '
  FROM   master..sysprocesses 
   WHERE   loginame = @user
  AND  SPID <> @@spid

  -- Kill processes (run command created above) >
  EXEC (@chv_killprocessestring)

  -- Fetch number of processes remaining >
  SELECT  @ins_remainingprocesses = COUNT(*)
  FROM master..sysprocesses 
   WHERE   loginame = @user
   AND  SPID <> @@spid

  IF @ins_remainingprocesses = 0
  BEGIN
  -- Report on processes killed if killed them all >
  PRINT LTRIM(RTRIM(UPPER(@user))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10), @ins_initialprocesses)) + ' connection(s)'
  END

  IF @ins_remainingprocesses <> 0
  BEGIN
  -- Report on processes remaining if could not end them all >
  PRINT LTRIM(RTRIM(UPPER(@user))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10), @ins_initialprocesses-@ins_remainingprocesses)) + ' connection(s), '+ RTRIM(CONVERT(VARCHAR(10), @ins_remainingprocesses)) + ' processes remaining.'
  END
 END 

IF @ins_initialprocesses = 0 
 BEGIN
  -- Report there are no processes for that user>
  PRINT @user + ' - No processes to kill'
 END
END

USP_KillHostProcesses

CREATE PROCEDURE [dbo].[USP_KillHostProcesses] @HOSTNAME varchar(100)
AS

SET NOCOUNT ON

-- variable declaration and initial values
DECLARE  @chv_killprocessestring VARCHAR(8000)  
DECLARE  @ins_initialprocesses SMALLINT
DECLARE  @ins_remainingprocesses SMALLINT  

SET @ins_initialprocesses = 0  
SET @ins_remainingprocesses = 0  
SET @chv_killprocessestring = ''

-- Fetch number of processes to kill

SELECT @ins_initialprocesses = COUNT(* )
FROM   MASTER..SYSPROCESSES
WHERE  HOSTNAME = @HOSTNAME
AND  SPID <> @@spid
select *  FROM   MASTER..SYSPROCESSES

-- Check there are processes to kill
IF @ins_initialprocesses > 0
BEGIN
  -- Build command string of processes to kill >
  SELECT @chv_killprocessestring = COALESCE(@chv_killprocessestring,',') + 'KILL ' + CONVERT(VARCHAR,SPID) + '; '
  FROM   MASTER..SYSPROCESSES
  WHERE  HOSTNAME = @HOSTNAME

  -- Kill processes (run command created above) >
  EXEC( @chv_killprocessestring)
  
  -- Fetch number of processes remaining >
  SELECT @ins_remainingprocesses = COUNT(* )
  FROM   MASTER..SYSPROCESSES
  WHERE  HOSTNAME = @HOSTNAME
  
  IF @ins_remainingprocesses = 0
    BEGIN
      -- Report on processes killed if killed them all >
      PRINT LTRIM(RTRIM(UPPER(@HOSTNAME))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10),@ins_initialprocesses)) + ' process(s)'
    END
  
  IF @ins_remainingprocesses <> 0
    BEGIN
      -- Report on processes remaining if could not end them all >
      PRINT LTRIM(RTRIM(UPPER(@HOSTNAME))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10),@ins_initialprocesses - @ins_remainingprocesses)) + ' process(s), ' + RTRIM(CONVERT(VARCHAR(10),@ins_remainingprocesses)) + ' processes remaining.'
    END
END

IF @ins_initialprocesses = 0
BEGIN
  -- Report there are no processes for that DB >
  PRINT @HOSTNAME + ' - No processes to kill'
END

GO

Sunday, 27 May 2007

USP_FixOrphans - Fix Orphanned Users Procedure (for ALL databases)

Script to fix orphanned users after a restore operation.
This builds on USP_FixUsers and works for all databases.

Save it to master database or common tools db.

Usage : EXEC Usp_FixOrphans 'databasename' or EXEC Usp_FixOrphans '/all'

ALTER PROCEDURE USP_FixOrphans(@chvparameterdbname VARCHAR(255))
AS
/*


Fix Orphanned Users


Stored Procedure


Procedure to reattach users after restores



1
27/05/2007
sqlsolace
Original Version



*/
SET NOCOUNT ON

-- check for parameters
IF @chvparameterdbname IS NULL
OR LTRIM(RTRIM(@chvparameterdbname)) = ''
BEGIN
PRINT 'USP_FixOrphans : Parameter missing : must provide a database name or /all'

RETURN
END

SET @chvparameterdbname = LTRIM(RTRIM(@chvparameterdbname))

-- check for reconnecting all dbs
IF @chvparameterdbname = '/all'
BEGIN
PRINT 'USP_FixOrphans : Executing for all databases'

SET @chvparameterdbname = ''
END
ELSE
BEGIN
PRINT 'USP_FixOrphans : Executing for ' + @chvparameterdbname
END

IF OBJECT_ID('tempdb..##ServerLogins') IS NOT NULL
BEGIN
DROP TABLE ##SERVERLOGINS
END

IF OBJECT_ID('tempdb..##DBUsers') IS NOT NULL
BEGIN
DROP TABLE ##DBUSERS
END

IF OBJECT_ID('tempdb..##DBList') IS NOT NULL
BEGIN
DROP TABLE ##DBLIST
END

IF OBJECT_ID('tempdb..##DBResults') IS NOT NULL
BEGIN
DROP TABLE ##DBRESULTS
END

DECLARE  @DBName VARCHAR(128);

DECLARE  @SQLCmd VARCHAR(2000);

DECLARE  @NumberOfDBs INT;

DECLARE  @chvOrphanName VARCHAR(255);

DECLARE  @chvCurrentDatabaseName VARCHAR(255);

-- Get the SQL Server logins
-- Create login table
CREATE TABLE ##SERVERLOGINS (
[SID]        VARBINARY(85)   NULL,
[LOGIN_NAME] VARCHAR(128)   NULL);

-- Populate login table
INSERT INTO ##SERVERLOGINS
SELECT SID,
CAST(LOGINNAME AS VARCHAR(128))  AS [LOGIN_NAME]
FROM   MASTER.DBO.SYSLOGINS;

-- Create list of databases
CREATE TABLE ##DBLIST (
[DBNAME] VARCHAR(128))

IF @chvparameterdbname = ''
BEGIN
-- perform for all dbs on server
INSERT INTO ##DBLIST
SELECT   NAME
FROM     MASTER..SYSDATABASES
WHERE    DBID > 4
ORDER BY NAME;
END
ELSE
BEGIN
INSERT INTO ##DBLIST
SELECT   NAME
FROM     MASTER..SYSDATABASES
WHERE    NAME = @chvparameterdbname
ORDER BY NAME;
END

SELECT @NumberOfDBs = COUNT(* )
FROM   ##DBLIST

-- Create the output table for the Database User ID's
CREATE TABLE ##DBUSERS (
[DATABASE_USER_ID] VARCHAR(128),
[SERVER_LOGIN]     VARCHAR(128),
[DATABASE_ROLE]    VARCHAR(128),
[DATABASE_NAME]    VARCHAR(128));

CREATE TABLE ##DBRESULTS (
[DATABASE_NAME]   VARCHAR(128),
[TOTAL_USERS]     SMALLINT,
[USERS_OK]        SMALLINT,
[USERS_ORPHANNED] SMALLINT,
[USERS_FIXED]     SMALLINT,
[USERS_NOLOGIN]   SMALLINT);

-- Declare a cursor to loop through all the databases on the server
DECLARE CSRDB CURSOR  FOR
SELECT   DBNAME
FROM     ##DBLIST
ORDER BY DBNAME

-- Open the cursor and get the first database name
OPEN CSRDB

FETCH NEXT FROM CSRDB
INTO @DBName

-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN

-- populate ##DBUsers table for database list
SELECT @SQLCmd = 'INSERT ##DBUsers ' + ' SELECT CAST(su.[name] AS VARCHAR(128)) AS [database_user_id], ' + ' CAST(COALESCE (u.[login_name], ''* Orphaned *'') AS VARCHAR(128))AS [server_login], ' + ' CAST(COALESCE (sug.name, ''Public'') AS VARCHAR(128)) AS [database_role],' + ' CAST(''' + @DBName + ''' AS VARCHAR(128)) AS [database_name]' + ' FROM [' + @DBName + '].[dbo].[sysusers] su' + ' LEFT OUTER JOIN ##ServerLogins u' + ' ON su.sid = u.sid' + ' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm ' + ' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug ' + ' ON sm.groupuid = sug.uid)' + ' ON su.uid = sm.memberuid ' + ' WHERE su.hasdbaccess = 1' + ' AND su.[name] != ''dbo'' '

--print @SQLCmd
EXEC( @SQLCmd)

-- Get the next database name
FETCH NEXT FROM CSRDB
INTO @DBName

-- End of the cursor loop
END

-- Close and deallocate the CURSOR
CLOSE CSRDB

DEALLOCATE CSRDB

-- database name and total users
INSERT INTO ##DBRESULTS
(DATABASE_NAME,
TOTAL_USERS,
USERS_OK,
USERS_ORPHANNED)
SELECT ##DBLIST.DBNAME,
ALLCOUNT,
OKCOUNT,
ORPHANNEDCOUNT
FROM   ##DBLIST
INNER JOIN (SELECT   COUNT(* )     AS ALLCOUNT,
DATABASE_NAME
FROM     ##DBUSERS
GROUP BY DATABASE_NAME) ALLUSERS
ON ALLUSERS.DATABASE_NAME = ##DBLIST.DBNAME
INNER JOIN (SELECT   COUNT(* )     AS OKCOUNT,
DATABASE_NAME
FROM     ##DBUSERS
WHERE    SERVER_LOGIN <> '* Orphaned *'
GROUP BY DATABASE_NAME) OK
ON OK.DATABASE_NAME = ##DBLIST.DBNAME
INNER JOIN (SELECT   COUNT(* )     AS ORPHANNEDCOUNT,
DATABASE_NAME
FROM     ##DBUSERS
WHERE    SERVER_LOGIN = '* Orphaned *'
GROUP BY DATABASE_NAME) ORPHANNED
ON ORPHANNED.DATABASE_NAME = ##DBLIST.DBNAME

--select * from ##DBResults

-- cursor through users we can repair
DECLARE CURREPAIRORPHANEDUSERS CURSOR FORWARD_ONLY FOR
SELECT DATABASE_NAME,
DATABASE_USER_ID
FROM   ##DBUSERS
INNER JOIN ##SERVERLOGINS
ON ##DBUSERS.DATABASE_USER_ID = ##SERVERLOGINS.LOGIN_NAME
WHERE  SERVER_LOGIN = '* Orphaned *'

OPEN CURREPAIRORPHANEDUSERS

FETCH NEXT FROM CURREPAIRORPHANEDUSERS
INTO @chvCurrentDatabaseName,
@chvOrphanName

WHILE (@@FETCH_STATUS = 0)
BEGIN
BEGIN
IF @chvOrphanName = 'dbo'
BEGIN
SET @SQLCmd = 'USE [' + @chvCurrentDatabaseName + '] EXEC SP_CHANGEDBOWNER ''Sa'''
END
ELSE
BEGIN
SET @SQLCmd = 'USE [' + @chvCurrentDatabaseName + '] EXEC SP_CHANGE_USERS_LOGIN ''update_one'',' + '''' + @chvOrphanName + ''',' + '''' + @chvOrphanName + ''''
END
END

PRINT @SQLCmd

EXEC( @SQLCmd)

FETCH NEXT FROM CURREPAIRORPHANEDUSERS
INTO @chvCurrentDatabaseName,
@chvOrphanName
END

CLOSE CURREPAIRORPHANEDUSERS

DEALLOCATE CURREPAIRORPHANEDUSERS

-- repopulate the ##DBUsers table so can compare what the script has achieved
DELETE ##DBUSERS

DECLARE CSRDB CURSOR  FOR
SELECT   DBNAME
FROM     ##DBLIST
ORDER BY DBNAME

-- Open the cursor and get the first database name
OPEN CSRDB

FETCH NEXT FROM CSRDB
INTO @DBName

-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN

-- populate ##DBUsers table for database list
SELECT @SQLCmd = 'INSERT ##DBUsers ' + ' SELECT CAST(su.[name] AS VARCHAR(128)) AS [database_user_id], ' + ' CAST(COALESCE (u.[login_name], ''* Orphaned *'') AS VARCHAR(128))AS [server_login], ' + ' CAST(COALESCE (sug.name, ''Public'') AS VARCHAR(128)) AS [database_role],' + ' CAST(''' + @DBName + ''' AS VARCHAR(128)) AS [database_name]' + ' FROM [' + @DBName + '].[dbo].[sysusers] su' + ' LEFT OUTER JOIN ##ServerLogins u' + ' ON su.sid = u.sid' + ' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm ' + ' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug ' + ' ON sm.groupuid = sug.uid)' + ' ON su.uid = sm.memberuid ' + ' WHERE su.hasdbaccess = 1' + ' AND su.[name] != ''dbo'' '

--print @SQLCmd
EXEC( @SQLCmd)

-- Get the next database name
FETCH NEXT FROM CSRDB
INTO @DBName

-- End of the cursor loop
END

-- Close and deallocate the CURSOR
CLOSE CSRDB

DEALLOCATE CSRDB

-- database name and total users
UPDATE ##DBRESULTS
SET    USERS_FIXED = NEWRESULTS.OKCOUNT - USERS_OK,
USERS_NOLOGIN = NEWRESULTS.ORPHANNEDCOUNT
FROM   ##DBRESULTS
INNER JOIN (SELECT ##DBLIST.DBNAME AS DBNAME,
OKCOUNT,
ORPHANNEDCOUNT
FROM   ##DBLIST
INNER JOIN (SELECT   COUNT(* )     AS OKCOUNT,
DATABASE_NAME
FROM     ##DBUSERS
WHERE    SERVER_LOGIN <> '* Orphaned *'
GROUP BY DATABASE_NAME) OK
ON OK.DATABASE_NAME = ##DBLIST.DBNAME
INNER JOIN (SELECT   COUNT(* )     AS ORPHANNEDCOUNT,
DATABASE_NAME
FROM     ##DBUSERS
WHERE    SERVER_LOGIN = '* Orphaned *'
GROUP BY DATABASE_NAME) ORPHANNED
ON ORPHANNED.DATABASE_NAME = ##DBLIST.DBNAME) NEWRESULTS
ON ##DBRESULTS.DATABASE_NAME = NEWRESULTS.DBNAME

SELECT DATABASE_NAME,
TOTAL_USERS,
USERS_OK        AS INITIAL_USERS_OK,
USERS_ORPHANNED AS INITIAL_USERS_ORPHANNED,
USERS_FIXED,
USERS_NOLOGIN   AS USERS_NO_MATCHING_LOGIN
FROM   ##DBRESULTS

GO

Wednesday, 23 May 2007

USP_KillSleepingProcesses - Procedure to kill sleeping processes

USP_KillSleepingProcesses - Procedure to kill sleeping processes
Pass the database name as the parameter or '/all' for all dbs.

exec USP_KillSleepingProcesses 'WebDb' -- or '/all'
GO

ALTER PROCEDURE [Utils].[usp_KillSleepingProcesses] @database varchar(100)
AS

SET NOCOUNT ON

-- Check validity of supplied database name
IF DB_ID(@database) > 4 OR LOWER(LTRIM(RTRIM(@database))) = '/all'
BEGIN
  -- variable declaration and initial values
  DECLARE  @chv_killprocessestring VARCHAR(8000)  
  DECLARE  @ins_initialprocesses SMALLINT
  DECLARE  @ins_remainingprocesses SMALLINT  

  SET @ins_initialprocesses = 0  
  SET @ins_remainingprocesses = 0  
  SET @chv_killprocessestring = ''
  
  -- Fetch number of processes to kill
 IF LOWER(LTRIM(RTRIM(@database))) = '/all'
        BEGIN
        SELECT @ins_initialprocesses = COUNT(* )
        FROM   MASTER..SYSPROCESSES
                      WHERE  STATUS = 'sleeping'
       AND  SPID <> @@spid
       -- avoid system processes
       AND spid > 50
        END
      ELSE
        BEGIN
        SELECT @ins_initialprocesses = COUNT(* )
        FROM   MASTER..SYSPROCESSES
                      WHERE  DBID = DB_ID(@database)
                      AND STATUS = 'sleeping'
        AND  SPID <> @@spid
  -- avoid system processes
  AND spid > 50

        END
  
  -- Check there are processes to kill
  IF @ins_initialprocesses > 0
    BEGIN
      -- Build command string of processes to kill >
      IF LOWER(LTRIM(RTRIM(@database))) = '/all'
        BEGIN
          SELECT @chv_killprocessestring = COALESCE(@chv_killprocessestring,',') + 'KILL ' + CONVERT(VARCHAR,SPID) + '; '
          FROM   MASTER..SYSPROCESSES
          WHERE  STATUS = 'sleeping'
     AND  SPID <> @@spid
           -- avoid system processes
           AND spid > 50
        END
      ELSE
        BEGIN
          SELECT @chv_killprocessestring = COALESCE(@chv_killprocessestring,',') + 'KILL ' + CONVERT(VARCHAR,SPID) + '; '
          FROM   MASTER..SYSPROCESSES
          WHERE  DBID = DB_ID(@database)
                 AND STATUS = 'sleeping'
     AND  SPID <> @@spid
           -- avoid system processes
     AND spid > 50

        END
      
      -- Kill processes (run command created above) >
      EXEC( @chv_killprocessestring)
      
      -- Fetch number of processes remaining >
      IF LOWER(LTRIM(RTRIM(@database))) = '/all'
        BEGIN
     SELECT @ins_remainingprocesses = COUNT(* )
     FROM   MASTER..SYSPROCESSES
     WHERE  STATUS = 'sleeping'
       AND  SPID <> @@spid
        -- avoid system processes
       AND spid > 50
  END
   ELSE
  BEGIN
     SELECT @ins_remainingprocesses = COUNT(* )
     FROM   MASTER..SYSPROCESSES
     WHERE  DBID = DB_ID(@database)
       AND  STATUS = 'sleeping'
    AND  SPID <> @@spid
    -- avoid system processes
    AND  spid > 50
  END
      
      IF @ins_remainingprocesses = 0
        BEGIN
          -- Report on processes killed if killed them all >
          PRINT LTRIM(RTRIM(UPPER(@database))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10),@ins_initialprocesses)) + ' process(s)'
        END
      
      IF @ins_remainingprocesses <> 0
        BEGIN
          -- Report on processes remaining if could not end them all >
          PRINT LTRIM(RTRIM(UPPER(@database))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10),@ins_initialprocesses - @ins_remainingprocesses)) + ' process(s), ' + RTRIM(CONVERT(VARCHAR(10),@ins_remainingprocesses)) + ' processes remaining.'
        END
    END
    
  IF @ins_initialprocesses = 0
    BEGIN
      -- Report there are no processes for that DB >
      PRINT @database + ' - No processes to kill'
    END
END

ELSE

BEGIN
  -- Supplied database name not valid or was a system db, hence report that >
  PRINT 'Invalid Database name or System Database supplied'
END
GO

Tuesday, 22 May 2007

USP_KillAllProcesses - Procedure to Kill connections to (processes running in) a database

I thought this would be useful to run before restore jobs.
It uses sysprocesses to find and kill any open processes for the supplied database name or all if you pass '/all'

ALTER PROCEDURE [Utils].[usp_KillAllProcesses] @database varchar(100)
AS
SET NOCOUNT ON
-- Check validity of supplied database name
IF db_id(@database) > 4 OR LOWER(LTRIM(RTRIM(@database))) = '/all'
BEGIN
 
 -- variable declaration and initial values
 DECLARE @chv_killprocessestring varchar(8000)
 DECLARE @ins_initialprocesses smallint
 DECLARE @ins_remainingprocesses smallint

 SET @ins_initialprocesses = 0
 SET @ins_remainingprocesses = 0
 SET @chv_killprocessestring = ''

 -- Fetch number of processes to kill 
 -- Fetch number of processes to kill
 IF LOWER(LTRIM(RTRIM(@database))) = '/all'
        BEGIN
    SELECT  @ins_initialprocesses = COUNT(*)
    FROM master..sysprocesses 
    WHERE status <> 'background' 
    -- to get the user process
    AND status in ('runnable','sleeping') 
    -- to avoid the current spid
    AND spid <> @@spid
    -- avoid system processes
    AND spid > 50
  END
ELSE
        BEGIN
    SELECT  @ins_initialprocesses = COUNT(*)
    FROM master..sysprocesses 
    WHERE   dbid = db_id(@database) 
    -- to avoid the sql process
    AND status <> 'background' 
    -- to get the user process
    AND status in ('runnable','sleeping') 
    -- to avoid the current spid
    AND spid <> @@spid
    -- avoid system processes
    AND spid > 50
  END

 -- Check there are processes to kill
 IF @ins_initialprocesses > 0 
 
 BEGIN
  -- Build command string of processes to kill >
IF LOWER(LTRIM(RTRIM(@database))) = '/all'
        BEGIN
     SELECT @chv_killprocessestring = coalesce(@chv_killprocessestring,',' ) + 'KILL ' + convert(varchar, spid)+ '; '
     FROM   master..sysprocesses 
     WHERE  status <> 'background' 
     -- to get the user process
     AND status in ('runnable','sleeping') 
     -- to avoid the current spid
     AND spid <> @@spid
     -- avoid system processes
     AND spid > 50
  END
ELSE
  BEGIN
     SELECT @chv_killprocessestring = coalesce(@chv_killprocessestring,',' ) + 'KILL ' + convert(varchar, spid)+ '; '
     FROM   master..sysprocesses 
     WHERE  dbid = db_id(@database)
     -- to avoid the sql process
     AND status <> 'background' 
     -- to get the user process
     AND status in ('runnable','sleeping') 
     -- to avoid the current spid
     AND spid <> @@spid
     -- avoid system processes
     AND spid > 50
  END


  -- Kill processes (run command created above) >
  EXEC (@chv_killprocessestring)

      -- Fetch number of processes remaining >
      IF LOWER(LTRIM(RTRIM(@database))) = '/all'
        BEGIN
     SELECT  @ins_remainingprocesses = COUNT(*)
     FROM    master..sysprocesses 
     WHERE   status <> 'background' 
     -- to get the user process
     AND status in ('runnable','sleeping') 
     -- to avoid the current spid
     AND spid <> @@spid
     -- avoid system processes
     AND spid > 50
  END
   ELSE
  BEGIN
     SELECT  @ins_remainingprocesses = COUNT(*)
     FROM master..sysprocesses 
     WHERE   dbid = db_id(@database)
     -- to avoid the sql process
     AND status <> 'background' 
     -- to get the user process
     AND status in ('runnable','sleeping') 
     -- to avoid the current spid
     AND spid <> @@spid
     -- avoid system processes
     AND spid > 50
  END


  IF @ins_remainingprocesses = 0
  BEGIN
  -- Report on processes killed if killed them all >
  PRINT LTRIM(RTRIM(UPPER(@database))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10), @ins_initialprocesses)) + ' connection(s)'
  END

  IF @ins_remainingprocesses <> 0
  BEGIN
  -- Report on processes remaining if could not end them all >
  PRINT LTRIM(RTRIM(UPPER(@database))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10), @ins_initialprocesses-@ins_remainingprocesses)) + ' connection(s), '+ RTRIM(CONVERT(VARCHAR(10), @ins_remainingprocesses)) + ' processes remaining.'
  END
 END 

IF @ins_initialprocesses = 0 
 BEGIN
  -- Report there are no processes for that DB >
  PRINT @database + ' - No processes to kill'
 END
END

ELSE

BEGIN 
 -- Supplied database name not valid or was a system db, hence report that >
 PRINT 'Invalid Database name or System Database supplied'    
END
GO

Monday, 21 May 2007

{Off Topic} Internet Usage Summary

I was bemused by this proxy server summary >

category_name	connections
Adult/Sexually Explicit	4121
Advertisements & Popups	1083293
Alcohol & Tobacco	1563
Arts	6326
Blogs & Forums	39031
Business	303674
Chat	18648
Company & Intranet	354799
Computing & Internet	463939
Criminal Activity	119
Downloads	25369
Education	18288
Entertainment	114408
Fashion & Beauty	1858
Finance & Investment	289405
Food & Dining	7432
Gambling	13935
Games	19954
Government	56587
Hacking	43
Health & Medicine	9693
Hobbies & Recreation	12207
Hosting Sites	5093
Illegal Drugs	48
Infrastructure	658618
Intimate Apparel & Swimwear	4591
Intolerance & Hate	59
Job Search & Career Development	20044
Kids Sites	1289
Motor Vehicles	94384
News	192035
Peer-to-Peer	243
Personals & Dating	10650
Philanthropic & Professional Orgs.	6037
Phishing & Fraud	66
Photo Searches	17370
Politics	1638
Proxies & Translators	1273
Real Estate	136073
Reference	587250
Religion	1250
Ringtones/Mobile Phone Downloads	279
Search Engines	627024
Sex Education	23
Shopping	250154
Society & Culture	14953
Spam URLs	87
Sports	76104
Spyware	10192
Streaming Media	289529
Tasteless & Offensive	1098
Travel	167507
Uncategorized	1281753
Violence	101
Weapons	394
Web-based E-mail	373831

Saturday, 19 May 2007

OPENXML - Data Retrieval 2 (via CTE)

A further OPENXML example to read data from wddxPacket -

I am using recursive functionality of a sql CTE (common table expression) here -


DECLARE @xmlstring VARCHAR(MAX)
DECLARE @idoc int
DECLARE @doc xml

SET @xmlstring ='
    
    
      
        
          a string
        
        
          -12.456
        
        
          1998-06-12T04:32:12
        
        
          
        
        
          
            10
            second element
          
        
        
          
            
              a string
            
            
              -12.456
            
          
        
      
    
  '


SET @doc = CONVERT(XML,@xmlstring)
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc


;with recursiveCTE(level,id,parentid,nodetype,localname,[text], sortcolumn)
as
(

 SELECT '1',a.id,a.parentid,a.nodetype,a.localname,a.[text] , CAST(a.id AS VARBINARY(900))
    FROM   OPENXML (@idoc, 'wddxPacket/data/struct', 1) a

    UNION ALL

 SELECT '2',b.id,b.parentid,b.nodetype,b.localname,b.[text] , CAST(sortcolumn + CAST(b.id AS BINARY(4)) AS VARBINARY(900))
    FROM   OPENXML (@idoc, 'wddxPacket/data/struct', 1) b
    INNER JOIN recursiveCTE c on c.id = b.parentid  

)

select * from recursiveCTE
order by sortcolumn

Friday, 18 May 2007

OPENXML - Data Retrieval

Simple OPENXML example to read data from wddxPacket

Note : I have escaped the single quote (') character by replacing it with 2 single quotes ('') to make this work!

DECLARE @xmlstring VARCHAR(MAX)
DECLARE @idoc int
DECLARE @doc xml

SET @xmlstring ='
    
    
      
        
          a string
        
        
          -12.456
        
        
          1998-06-12T04:32:12
        
        
          
        
        
          
            10
            second element
          
        
        
          
            
              a string
            
            
              -12.456
            
          
        
      
    
  '


SET @doc = CONVERT(XML,@xmlstring)
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *
FROM   OPENXML (@idoc, 'wddxPacket/data/struct', 1)

Wednesday, 9 May 2007

List SQL Agent Jobs and owners


SELECT server_name = @@servername ,
    job_name    = jobs.[name],
    job_enabled = CASE WHEN jobs.Enabled = 0 THEN 'No' ELSE 'Yes' END,
    job_owner   = logins.[name]
FROM msdb.dbo.sysjobs jobs
INNER JOIN master.dbo.syslogins logins
ON jobs.owner_sid = logins.sid
ORDER BY jobs.[name]

Tuesday, 8 May 2007

Blogging : Ad Code Convertor

Convertor to correctly escape HTML code.
Useful for adding code to blogger etc >

http://www.eblogtemplates.com/blogger-ad-code-converter/

Saturday, 5 May 2007

TSQL : List tables, Indexes & Columns

List tables, Indexes & Columns via system objects -

select sys.objects.object_id,
sys.objects.name as tablename,
sys.indexes.name as indexname,
sys.columns.name as columnname
from sys.objects
inner join sys.indexes 
on sys.objects.object_id = sys.indexes.object_id
inner join sys.index_columns 
on sys.index_columns.object_id = sys.indexes.object_id
and sys.index_columns.index_id = sys.indexes.index_id
inner join sys.columns
on sys.columns.object_id = sys.index_columns.object_id
and sys.columns.column_id = sys.index_columns.column_id
where sys.objects.type_desc  = 'USER_TABLE'
and sys.indexes.is_primary_key = 0

Friday, 4 May 2007

SQL Sysadmin : Recycle SQL Server Error Logs

Schedule this as appropriate (weekly?) so that SQL logs do not become too cumbersome (taking ages to load in management studio).

Database : master

Command :

EXEC sp_cycle_errorlog
go

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

Thursday, 3 May 2007

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