Stored Procedure

MSSQL Exchange names 2 databases

/ MSSQL Server /

Using the following Stored Procedure you can swap the names of two databases. For example, to bring a recently updated database online, after which the old database can be filled again. To exchange database names, a third name is required. This is the name of database2 with the addition ‘_OLD’.

/****** Object:  StoredProcedure [dbo].[DbNameExchange]    Script Date: 12/01/2012 09:15:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ==============================================================
-- Author:		Tim van Kooten Niekerk
-- Create date: 2012-12-01-A
-- Description:	Exchange database names between 2 databases...
-- ==============================================================

CREATE PROCEDURE [dbo].[DbNameExchange]
    @vDatabase1 nvarchar(256),
    @vDatabase2 nvarchar(256)
    
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @H1 AS int
  DECLARE @vDatabaseT AS nvarchar(256)
  DECLARE @vQuery1 AS nvarchar(512)

  -- T.b.v. wisselen van database namen is er een derde naam nodig...
  SET @vDatabaseT = @vDatabase2 + N'_OLD'

  -- Check rename action prefered state before rename action 1/3...
  IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 1
     AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 1
     AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 0
  BEGIN
    -- Geef de secundaire db een tijdelijke naam...
    BEGIN TRY
      SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase2 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;WAITFOR DELAY ''00:00:05'';ALTER DATABASE [' + @vDatabase2 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabase2 + '] MODIFY NAME = [' + @vDatabaseT + '];ALTER DATABASE [' + @vDatabaseT + '] SET MULTI_USER'
      EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
    END TRY
    BEGIN CATCH
    END CATCH
    -- Check rename action prefered state before rename action 2/3...
    IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 1
      AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 0
      AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 1
    BEGIN
      -- Geef de primaire db de naam van de secundaire db...
      BEGIN TRY
	    SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase1 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;WAITFOR DELAY ''00:00:05'';ALTER DATABASE [' + @vDatabase1 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabase1 + '] MODIFY NAME = [' + @vDatabase2 + '];ALTER DATABASE [' + @vDatabase2 + '] SET MULTI_USER'
        EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
      END TRY
      BEGIN CATCH
      END CATCH
  	  -- Check rename action prefered state before rename action 3/3...
      IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 0
        AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 1
        AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 1
      BEGIN
        -- Geef de db met de tijdelijke naam de naam van de primaire db...
        BEGIN TRY
	      SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabaseT + '] MODIFY NAME = [' + @vDatabase1 + '];ALTER DATABASE [' + @vDatabase1 + '] SET MULTI_USER'
          EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
        END TRY
	    BEGIN CATCH
        END CATCH
	    IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 1
          AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 1
          AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 0
	    BEGIN
	      PRINT 'INFO: Rollout actie correct uitgevoerd...'
	    END
	    ELSE BEGIN
          PRINT 'ERROR: Rollout actie is niet volledig uitgevoerd, probeer het nomaals...'
	      BEGIN TRY 
	        SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabaseT + '] MODIFY NAME = [' + @vDatabase1 + '];ALTER DATABASE [' + @vDatabase1 + '] SET MULTI_USER'
            EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
          END TRY
	      BEGIN CATCH
          END CATCH
          -- Niet gelukt, dan in ieder geval multi_user activeren...
		  SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET MULTI_USER'
          EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
	    END
	  END
    ELSE BEGIN
	    -- Something went wrong trying to rollback...
	    PRINT 'ERROR: Fout geconstateerd, probeer terug te draaien...'
        SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabaseT + '] MODIFY NAME = [' + @vDatabase2 + '];ALTER DATABASE [' + @vDatabase2 + '] SET MULTI_USER'
        EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
        SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase1 + '] SET MULTI_USER'
        EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
  	  END
    END
    ELSE BEGIN
    -- Something went wrong trying to rollback...
    PRINT 'ERROR: Fout geconstateerd, probeer terug te draaien...'
    SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase2 + '] SET MULTI_USER'
    EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
    END
    -- Unprepare statements...
    EXEC sp_unprepare @H1
  END
  ELSE BEGIN
    PRINT 'ERROR: Startsituatie niet correct...'
  END
END

GO

MSSQL Query SQL Server Logs

/ MSSQL Server /

De volgende uitwerking maakt gebruik van extended stored procedure xp_readerrorlog om in de SQL Server Log records te zoeken naar specifieke term(en). Alleen records van de laatste 3 dagen worden getoond (indien aanwezig in het logbestand).

DECLARE @iLogArchive int, @iLogType int 
DECLARE @vSearchString1 nvarchar(50), @vSearchString2 nvarchar(50) 
DECLARE @vSortOrder nvarchar(4) 
DECLARE @dtFrom datetime, @dtTo datetime 
SET @dtFrom = (GETDATE() -3) 
SET @dtTo = (GETDATE()) 
SET @iLogArchive	= 0		-- 0=Current, 1=Archive #1, enz.. 
SET @iLogType		= 1		-- 1=SQL Server, 2=SQL Agent 
SET @vSortOrder		= N'asc'	-- asc=Ascending, desc=Descending  
-- Set searchstrings and start procedure... 
SET @vSearchString1 	= N'Fail' 
SET @vSearchString2 	= N'' 
EXEC master.sys.xp_readerrorlog @iLogArchive, @iLogType, @vSearchString1, @vSearchString2, @dtFrom, @dtTo, @vSortOrder

MSSQL Query Linked Server

/ MSSQL Server /

Voor uitvoer van Stored Procedures op een linked server moet de optie RPC onder Server Options op True worden gezet. Hieronder een tweetal methodes m.b.t. uitvoeren SP’s op een linked server. Het eerste voorbeeld maakt gebruik van de four-part name. Het tweede voorbeeld gebruikt OPENQUERY.

EXEC [LINKEDSERVERNAME].[master].sys.sp_who2
SELECT * FROM OPENQUERY([LINKEDSERVERNAME],'EXEC master.sys.sp_who')

Hieronder een tweetal methodes om een query uit te voeren op een tabel in een linked server.

SELECT * FROM [LINKEDSERVERNAME].[master].sys.databases
SELECT * FROM OPENQUERY([LINKEDSERVERNAME], 'SELECT * FROM sys.databases')

Met de onderstaande opdracht maak je een linked server aan die terugvalt op de actuele security context.

sp_addlinkedserver 'SERVERNAME', 'SQL Server'
sp_addlinkedsrvlogin 'SERVERNAME', 'True', NULL

MSSQL Read output SP in temptable

/ MSSQL Server /

De onderstaande gegevens plaatst de gegevens uit een stored procedure in een tijdelijke tabel. Deze specifieke uitwerking plaatst de output van sp_who2 in een tijdelijke tabel met de naam #tWho2. Nu kun je de gegevens filteren d.m.v. queries op de tijdelijke tabel.

-- Oude tijdelijke tabel verwijderen...
DROP TABLE #tWho2
GO

-- Nieuwe tijdelijke tabel aanmaken met alle velden...
CREATE TABLE #tWho2 (
	SPID char(5),
	[Status] nvarchar(max),
	[Login] nvarchar(max),
	HostName nvarchar(max),
	BlkBy char(5),
	DBName nvarchar(max),
	Command nvarchar(max),
	CPUTime nvarchar(max),
	DiskIO nvarchar(max),
	LastBatch nvarchar(max),
	ProgramName nvarchar(max),
	SPID2 char(5),
	REQUESTID char(5)
)
GO

-- Gegevens uit stored procedure inlezen in tijdelijk tabel...
INSERT INTO #tWho2 EXEC sp_who2
GO

MSSQL Log space used percentage

/ MSSQL Server /

Met onderstaande T-SQL kun je de vulling in procent van het transaction log bestand van een specifieke database opvragen en monitoren. Dit is alleen handig in specifieke situaties. Bij een correct database onderhoudsplan zal dit niet nodig zijn.

CREATE TABLE #tTLSpace 
( 
DBName sysname, 
TLSize decimal(18,5), 
TLUsed decimal(18,5), 
status INT 
)
GO

INSERT INTO #tTLSpace 
       exec ('DBCC SQLPERF(logspace)') 
GO

DECLARE @dTLUsed decimal
SELECT @dTLUsed = TLUsed from #tTLSpace WHERE DBName = 'DATABASE'
IF ( @dTLUsed > 90)
BEGIN
  PRINT @dTLUsed
  PRINT 'TransactionLog bestand 90 procent vol...'
END
GO

DROP TABLE #tTLSpace 
GO