Capturar evento de Deadlock no SQL Server sem alterar parâmetros de inicialização.

Olá pessoal,

Neste post vou demonstrar como capturar eventos de Deadlock sem alterar parâmetros de inicialização do SQL Server.

Nesta demonstração estarei usando PowerShell 2.0 exutando consultas WMI.

Para iniciar vamos ver como está configurado a execução do PowerShell. Bora lá!

Abrir Windows PowerShell ISE e executar o comando Get-ExecutionPolicy:

No meu caso está restrito. Vou alterar para não restrito para esta demonstração.

Executar o comando Set-ExecutionPolicy com o parâmetro unrestricted conforme print abaixo:

Ao executar aparecerá uma mensagem solicitando a confirmação de liberação.

Clicar em “Yes”:

Vou executar novamente o comando Get-ExecutionPolicy para ver se a liberação foi realizada com sucesso:

Beleza! Tudo ok para usar o PowerShell agora…

Abaixo o Shell que vamos utilizar:

get-date
$properties = “LoginSid”, “SQLInstance”, “IsSystem”, “PostTime”, “ComputerName”, “SessionLoginName”, “SPID”, “StartTime”, “TransactionID”, “EventSequence”, “TextData”, “LoginName”
$objWql = New-Object System.Management.WqlEventQuery “SELECT * FROM DEADLOCK_GRAPH”
$objScope = New-Object System.Management.ManagementScope “root\Microsoft\SqlServer\ServerEvents\denali”
$objWatcher = New-Object System.Management.ManagementEventWatcher $objScope, $objWql
$options = New-Object System.Management.EventWatcherOptions
$timespan = New-Object System.TimeSpan(0, 0, 10)
$options.TimeOut = $timespan
$objWatcher.Options = $Options
$objWatcher.Start()
$count = 0
while ($true)
{

 trap [System.Management.ManagementException] {continue}

 $objEvent=$objWatcher.WaitForNextEvent()

 if ($objEvent)
    {
        $count = $count + 1     
        $a = Get-Date

        $print = “Deadlock Exception: ” + $a

        $date =  $a.ToShortDateString()
        $date_formated = Foreach-Object {$date -replace “/”, “_”}
        $time = $a.ToShortTimeString()
        $time_formated = Foreach-Object {$time -replace “:”, “_”}

        $path = “C:\Demo\Exception\” + $date_formated + “_” + $time_formated + “_” + $a.Second + “_” + $count + “.txt”

  $objEvent | Select-Object $properties > $path

        #$SMTPserver = “<Server de SMTP>”
        #$fileattachment = $path <caminho do arquivo que será anexado> 
        #$from = “<email de origem>”
        #$to = “<email de destino>”
        #$subject = “<titulo do email>”
        #$emailbody = “<corpo do email>”

        #$mailer = new-object Net.Mail.SMTPclient($SMTPserver)
        #$msg = new-object Net.Mail.MailMessage($from, $to, $subject, $emailbody)
        #$attachment = new-object Net.Mail.Attachment($fileattachment)
        #$msg.attachments.add($attachment)
        #$mailer.send($msg)
        $print

  Clear-Variable -name objEvent
 }
}

Atenção: informar o nome da instância que será capturado os eventos. No meu exemplo estou usando a instância DENALI.
$objScope = New-Object System.Management.ManagementScope “root\Microsoft\SqlServer\ServerEvents\<INSTÂNCIA>

Ações do Shell:

  • Consultar por WMI evento de deadlock;
  • Formatar um nome para um arquivo txt;
  • Gravar o evento ocorrido em um arquivo txt.

Obs: Deixei comentado a parte de envio de email caso queiram utilizar notificação via e-mail.

Agora vamos aos testes.

Vou colocar para rodar o Shell:

Ok! Shell rodando. Agora vou forçar a ocorrência de deadlock.

Vou criar duas tabelas para simulação:

USE DBTESTE
GO

CREATE TABLE TABELA1 (ID int, NOME VARCHAR(50))
CREATE TABLE TABELA2 (ID int, NOME VARCHAR(50))

Vou inserir registro em cada tabela:

INSERT INTO TABELA1 VALUES (1, ‘MARIA’)

INSERT INTO TABELA2 VALUES (2, ‘JOAO’)

Agora vou abrir duas “NEW QUERY” no SSMS contendo os seguintes comandos:

Agora vou executar os comandos para que ocorra o evento de deadlock:

Como podemos ver a sessão 2 foi vítima de deadlock.

Na sessão do PowerShell foi registrado o evento:

Arquivo de Deadlock gerado:

 E no arquivo TXT fica registrado tudo o que ocorreu no deadlock:

LoginSid         : {1}
SQLInstance      : DENALI
IsSystem         : 1
PostTime         : 20111007133149.000890+000
ComputerName     : XXX-XXX
SessionLoginName :
SPID             : 15
StartTime        : 20111007133149.000890+000
TransactionID    : 0
EventSequence    : 35
TextData         : <TextData><deadlock-list>20000005b4022251638e6cf78c7412c4
506ae6813d48a1b0000000000000000000000000000000000000000″>&#x
0A;UPDATE TABELA1 SET ID = 3 WHERE ID = 1     </frame>
</executionStack>     <inputbuf> — Sessão 2&#
x0A;BEGIN TRAN UPDATE TABELA2 SET ID = 4 WHERE ID = 2&#
x0A;WAITFOR DELAY &apos;00:00:10&apos; UPDATE TABELA1 S
ET ID = 3 WHERE ID = 1 COMMIT    </inputbuf>    </
process>    <process id=”process173001168″ taskpriority
=”0″ logused=”248″ waitresource=”RID: 5:1:120:0″ waittime=”9
260″ ownerId=”10751″ transactionname=”user_transaction” last
transtarted=”2011-10-07T13:31:35.627″ XDES=”0x17509a390″ loc
kMode=”U” schedulerid=”2″ kpid=”7148″ status=”suspended” spi
d=”54″ sbid=”0″ ecid=”0″ priority=”0″ trancount=”2″ lastbatc
hstarted=”2011-10-07T13:31:35.627″ lastbatchcompleted=”2011-
10-07T13:29:03.203″ lastattention=”1900-01-01T00:00:00.203″
clientapp=”Microsoft SQL Server Management Studio – Query” h
ostname=”XXX-XXX” hostpid=”4752″ loginname=”xxx\xxx
bertos” isolationlevel=”read committed (2)” xactid=”10751″ c
urrentdb=”5″ lockTimeout=”4294967295″ clientoption1=”6710907
84″ clientoption2=”390200″>     <executionStack>
<frame procname=”adhoc” line=”5″ stmtstart=”38″ sqlhandl
e=”0x0200000012d1d222020734cce49df0b6df6d8cc9045250c10000000
000000000000000000000000000000000″> UPDATE [TABELA2] se
t [ID] = @1  WHERE [ID]=@2     </frame>      <frame pro
cname=”adhoc” line=”5″ stmtstart=”180″ stmtend=”258″ sqlhand
le=”0x0200000027d92f2b0a6c2068f45529c9df534542de68e5fc000000
0000000000000000000000000000000000″> UPDATE TABELA2 SET
ID = 4 WHERE ID = 2     </frame>     </executionStack>
<inputbuf> –Sessão 1 BEGIN TRAN UP
DATE TABELA1 SET ID = 3 WHERE ID = 1 WAITFOR DELAY &apo
s;00:00:05&apos; UPDATE TABELA2 SET ID = 4 WHERE ID = 2
COMMIT     </inputbuf>    </proc
ess>   </process-list>   <resource-list>    <
ridlock fileid=”1″ pageid=”93″ dbid=”5″ objectname=”DBTESTE.
dbo.TABELA1″ id=”lock175ce9500″ mode=”X” associatedObjectId=
“72057594039238656”>     <owner-list>      <owner
id=”process173001168″ mode=”X”></owner>     </owner-lis
t>     <waiter-list>      <waiter id=”process17300
0c88″ mode=”U” requestType=”wait”></waiter>     </waite
r-list>    </ridlock>    <ridlock fileid=”1″ pagei
d=”120″ dbid=”5″ objectname=”DBTESTE.dbo.TABELA2″ id=”lock17
5ce9680″ mode=”X” associatedObjectId=”72057594039304192″>&#x
0A;    <owner-list>      <owner id=”process173000c88″ m
ode=”X”></owner>     </owner-list>     <waiter-lis
t>      <waiter id=”process173001168″ mode=”U” requestT
ype=”wait”></waiter>     </waiter-list>    </ridlo
ck>   </resource-list> </deadlock> </deadloc
k-list> </TextData>
LoginName        : sa

Solução válida para as versões do SQL: 2000, 2005, 2008, 2008 R2 e Denali

Era isso pessoal. Até a próxima.

Anúncios

Ferramenta Internals Viewer for SQL Server [CodePlex]

Neste post vou mostar a ferramenta Internals Viewer for SQL Server que está disponiel no CodePlex.

Esta ferramaneta é bem interessante e com ela é possível visualizar como o SQL Server armazena , aloca e organiza internamente os dados.

Tela para conexão:

Após conexão é possível visualizar com está as estatítiscas dos índices e suas alocações:

Agora vou inserir um registro na tabela para ver com fica armazenado:

Acessando a página alocada aí podemos ver como está armazenado:

Bom pessoal, este post foi só para dar um overview da ferramente. Em breve passo mais detalhes.

Até mais!

Objeto SEQUENCE no SQL Server Denali

Olá pessoal,

Neste post vou falar um pouco sobre o objeto SEQUENCE que foi incluído no SQL Server Denali.

Para este exemplo vou criar um banco de dados:

USE master
GO

CREATE DATABASE DBSEQUENCE
GO

Criar o objeto SEQUENCE:

USE DBSEQUENCE
GO
CREATE SEQUENCE SEQ_TESTE
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 2000
    CYCLE
    CACHE 10
GO

Para consultar o próximo valor:

Para consultar as configurações da SEQUENCE:

Vou criar uma tabela para exemplo de utilização da SEQUENCE:

CREATE TABLE TB_SEQ_TST
(
 [ID] INT,
 DESCRICAO VARCHAR(50)
)
GO

Vou fazer a inclusão de dois registros utilizando a SEQUENCE:

INSERT INTO TB_SEQ_TST (ID, DESCRICAO)
VALUES (NEXT VALUE FOR SEQ_TESTE, ‘TESTE SEQUENCE 1’)
GO
INSERT INTO TB_SEQ_TST (ID, DESCRICAO)
VALUES (NEXT VALUE FOR SEQ_TESTE, ‘TESTE SEQUENCE 2’)
GO

Vou consultar a tabela após a inclusão dos registros:

Vou consultar novamente as configurações da SEQUENCE:

Outra forma de utilização de SEQUENCE é colocar como default de uma coluna conforme exemplo abaixo:

CREATE TABLE TB_SEQ_TST2
(
 [ID] INT DEFAULT NEXT VALUE FOR SEQ_TESTE,
 DESCRICAO VARCHAR(50)
)
GO

Vou fazer a inclusão de dois registros na tabela de teste dois:

INSERT INTO TB_SEQ_TST2 (DESCRICAO)
VALUES (‘TESTE SEQUENCE 1’)
GO
INSERT INTO TB_SEQ_TST2 (DESCRICAO)
VALUES (‘TESTE SEQUENCE 2’)
GO

Vou consultar a tabela de teste dois:

E para finalizar vamos ver como está a tabela de configuração da SEQUENCE:

Feito pessoal! Até a próxima.

Criptografia a nível de banco de dados.

Olá pessoal!

Desta vez vou falar um pouco sobre criptografia a nível de banco e dados.

Nota: esta funcionalidade está disponível desde a versão SQL Server 2005 e melhorada na vesão 2008.

Para iniciar o exemplo vou criar um banco de dados:

USE master
GO

CREATE DATABASE DB_EXEMPLO
GO

Criar uma tabela para armazenar os dados:

USE DB_EXEMPLO
GO

CREATE TABLE TB_EXEMPLO
(
 ID INT,
 USUARIO VARCHAR(50),
 SENHA VARCHAR(50)
)
GO


Realizarei a inclusão de dois registros:

INSERT INTO TB_EXEMPLO VALUES
(1, ‘JOAO’, ‘SENHA1’),
(2, ‘MARIA’, ‘SENHA2’)
GO

Vou exibir como está a tabela:

Fazer um backup deste banco de dados:

BACKUP DATABASE DB_EXEMPLO TO DISK = N’D:\DB_EXEMPLO.BAK’
WITH NOFORMAT, NOINIT, NAME = N’DB_EXEMPLO BACKUP COMPLETO’, SKIP,
NOREWIND, NOUNLOAD,  STATS = 10
GO

Agora demonstrarei  que mesmo no backup dos dados ainda são visíveis.

Vou abrir o arquivo de backup em um bloco de notas:

Clicar em CTRL+F.

Digitar JOAO e clicar em buscar.

Aí está… os dados permanecem visíveis no arquivo de backup.

Agora vamos a nova funcionalidade. A MS vem chamando de TDE (Transparent Data Encryption).

Vou criar a Chave Mestre, Certificado, Chave de criptografia e alterar o banco de dados para utilizar criptografia.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘password’;
go
CREATE CERTIFICATE MeuCertificado WITH SUBJECT = ‘Desc’;
go
USE DB_EXEMPLO;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MeuCertificado;
GO
ALTER DATABASE DB_EXEMPLO
SET ENCRYPTION ON;
GO

Após ativar a criptografia vou fazer mais um backup:

BACKUP DATABASE DB_EXEMPLO TO DISK = N’D:\DB_EXEMPLO2.BAK’
WITH NOFORMAT, NOINIT, NAME = N’DB_EXEMPLO BACKUP COMPLETO’, SKIP,
NOREWIND, NOUNLOAD,  STATS = 10
GO

Vou abrir novamente o arquivo de backup no bloco de notas:

Clicar em CTRL+F.

Digitar JOAO e clicar em buscar.

Como podemos ver os dados não são mais visíveis nos arquivos de backup.

Algumas considerações sobre o uso de criptografia a nível de banco de dados:

  • Database com FILESTREAM não pode ser criptografada;
  • Database TEMPDB passa a ser criptgrafada;
  • Caso utilize replicação no banco de dados que está sendo ativado a TDE deve-se ativar criptografia no banco de dados de distribuição.
  • Não esquecer de fazer backup da chave mestre para poder fazer o restore dos backups.

Era isso pessoal. Até a próxima.

Função TRY_CONVERT no SQL Server Denali

Neste post vou descrever uma nova funcionalidade de T-SQL inserida na versão SQL Server Denali.

Esta função valida se o valor que será convertido é válido.

Abaixo exemplos:

Se o teste do valor que será convertido é válido o retorno é o próprio valor.

E se o valor não for válido o valor de retorno será NULL.

Era isso pessoal! Até a próxima!