Nueva versión - Crear Database Snapshot dinámicamente

Acá les adjunto la versión mas reciente del SP para crear Snapshots de base de datos de forma dinámica.
Permite la creación de multiples snapshots para una misma BD y la posibilidad de incluir el path en donde almacenar dichos Snapshots.


CREATE PROCEDURE usp_CreateSnapshot (@SourceDB varchar(200),@DestDB varchar(200)=NULL, @Message VARCHAR(200) =NULL OUTPUT)
AS 
--Created by Adrian Miranda Cordero
--Create a snapshot dinamically 
--Following script is intented to solve the problem of creating a database snapshot from the scratch by providing two or three parameters-

DECLARE @DatabaseName as varchar(200)
DECLARE @DestDBName as varchar(200)
DECLARE @SQLQuery as Nvarchar(MAX)
DECLARE @UseSQL as varchar (205)
DECLARE @Counter as smallint
DECLARE @FileId as smallint 
DECLARE @FileName as nvarchar(max)
DECLARE @LogicalName as varchar(200)
DECLARE @Files as varchar(max)
DECLARE @Inc_File int --Consecutive for a file name when you want multiple snapshots for the same DB

SET @DatabaseName =@SourceDB
IF @DestDB IS NULL 
SET @DestDBName= @SourceDB
ELSE
SET @DestDBName=@DestDB
SET @Inc_File = 0
SET @SQLQuery =''

IF (SELECT COUNT(1) FROM sys.databases WHERE name  in ('master', 'msdb',',model','tempdb') and  name = @Databasename) > 0
SET @Message='Can not create a database snapshot based on a system database'
ELSE IF (SELECT NAME FROM sys.databases WHERE NAME = @DatabaseName) IS NULL
SET @Message= 'Database not found.'
ELSE
BEGIN
SELECT @Inc_File=COUNT(1) FROM sys.databases WHERE source_database_id=DB_ID(@DatabaseName)
SET @Inc_File = @Inc_File + 1
DECLARE @DBFiles TABLE (
ID smallint Identity(1,1),
FileId int,
Name varchar (max),
FilePath varchar (max)
)
SET @SQLQuery = 'SELECT FILE_ID,name,Physical_Name FROM sys.database_files WHERE type_desc <>''''LOG'''''; 
SET @UseSQL = 'USE ' + @DatabaseName + '; EXEC sp_executesql N''' + @SQLQuery + '''';
INSERT INTO @DBFiles Exec (@UseSQL)
SET @SQLQuery = 'CREATE DATABASE ' + ISNULL(@DestDBName,@DatabaseName) + '_Snap' + LTRIM(STR(@Inc_File)) +  ' ON '
SELECT @Counter=count(1) FROM @DBFiles
SET @FileId =1
SET @Files = ''
WHILE @FileId <= @Counter
BEGIN
SELECT @LogicalName = Name, @FileName = FilePath FROM @DBFiles where ID=@Fileid
print @FileName
SET @Files = @Files + '(Name =' + @LogicalName + ',' + 'FileName = ' + '''' + LEFT(@FileName,CHARINDEX('.',@FileName) - 1)  

+  LTRIM(STR(@Inc_File)) +  '.ss'')'
IF @FileId <> @Counter
SET @Files=@Files + ','
SET @FileId = @FileId + 1 
END
SET @SQLQuery = @SQLQuery + @Files + ' AS SNAPSHOT OF ' + @DatabaseName
EXEC (@SQLQUERY)
IF @@ERROR =0
SET @Message= 'Database Snapshot created successfully'
END

Deadlocks. Cómo identificarlos?

 

Muchos de nosotros hemos tenido que lidiar con el tema de los deadlocks. Quizás la mayoría de nosotros piensa que son temas que debe de resolver el programador, algunos dirán que debe de ser el DBA. El resultado final es que debe de ser un esfuerzo conjunto entre los dos equipos. El Desarrollador necesita del DBA para identificar y guiar al programador y el Desarrollador hará su magia para resolver los conflictos que se puedan presentar.

Como detectarlos?

Tenemos varias formas de hacerlo, la fácil y la complicada. A mi parecer la complicada es el Profiler. Por qué? Sencillo, cuantos pasos se requieren para configurar el Profiler y adaptarlo para que capture los deadlocks? Lo han intentado utilizar en sistemas altamente transaccionales? En Profilers que almacenan cerca de 25 millones de líneas en unas 4 horas de ejecución. Analizar luego esa data es complejo, se los aseguro.
El uso de Profiler con deadlocks es un tema que analizaremos posteriormente. Hoy me quiero enfocar en el mas sencillo de los dos. Utilizar los eventos de WMI de Windows es la forma mas sencilla de configurar y detectar los deadlocks.

Cuales son los pasos?

1. Ingresar a la consola de SQL Server.
2. Expandir el SQL Server Agent.
3. Sobre Alerts, presionar botón derecho. Crear nueva Alerta.
4. Seleccionar el Type WMI Event Alert
5. En el query ingresar SELECT * FROM DEADLOCK_GRAPH



Idealmente se debe de crear un JOB que permita la respuesta a esta alerta. El job lo que hará es insertar los datos recolectados por a alerta para nuestro posterior análisis.

Se debe de crear la tabla en donde vamos a almacenar los datos.

CREATE TABLE [dbo].DeadlockTrace(

[AlertTime] [datetime] NULL,

[DeadlockGraph] [varchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

El Job debe de tener la siguiente sentencia :

INSERT INTO DeadlockTrace  (AlertTime, DeadlockGraph)
VALUES (getdate(), N'$(ESCAPE_SQUOTE(WMI(TextData))))')

Recordemos asignar en la alerta en el TAB Response el Job que recién acabamos de crear.

Para tener una vista comprendible de los datos ejecutamos la siguiente sentencia.

WITH CTE AS (
  SELECT AlertTime,
    CAST(REPLACE(REPLACE(CAST(DeadlockGraph AS VARCHAR(MAX)),'</TextData>)',''),'<TextData>','') AS XML) AS DeadlockGraph
  FROM dbo].[DeadlockTrace] )
SELECT ROW_NUMBER() OVER(PARTITION BY [AlertTime] ORDER BY [AlertTime] DESC) AS DeadlockNumber,
  [AlertTime],
  [DeadlockGraph],
  [PagelockObject] = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'),
  [DeadlockObject] = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'),
  [Victim] = CASE WHEN Deadlock.Process.value('@id', 'varchar(50)') = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') THEN 1 ELSE 0 END,
  [Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'),
  [LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'),
  [Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'),
  [ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
  [HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
  [LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
  [TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
  [InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
FROM CTE
CROSS APPLY
  CTE.[DeadlockGraph].nodes('/deadlock-list/deadlock/process-list/process') AS Deadlock(Process)
ORDER BY 1 DESC

Y listo, ya podemos analizar de una manera mas sencilla los deadlocks en nuestra base de datos.