SQL Skip Rules


As vezes entender os problemas de uma instalação ou remoção pode ser complicado.

Existem cenários em que um Reboot naquele momento não é uma opção, ou um host está totalmente degradado e você precisa forçar a remoção de um SQL já migrado e em estado Offline.

Se você executar o Setup normalmente ele vai fazer uma série de checagens como: necessidade de reboot, acesso ao registro remoto, WMI, .NET, etc.

Dependendo do que você precisa, algumas opções podem ser desconsideradas na instalação/remoção, vou colocar o arquivo onde contém todas as explicações de todas as opções possíveis.

ATENÇÃO, esta informação não é documentada, caso precise usar, faça por conta e risco.

Para usar qualquer uma das opções ou mais de uma opção:

  • SETUP /SkipRules=NoRebootPackage /Action=RemoveNode

Para ele não considerar mais de uma regra adicione um espaço entre elas.

  • SETUP /SkipRules=NoRebootPackage WmiServiceStateCheck /Action=RemoveNode

Abaixo vou listar todas as regras do documento para facilitar a pesquisa, mas aqui tem o documento para download com a explicação de cada uma delas.

Regra
ThreadHasAdminPrivilegeCheck
HasSecurityBackupAndDebugPrivilegesCheck
RebootRequiredCheck
WmiServiceStateCheck
AclPermissionsFacet
MediaPathLength
SetupCompatibilityCheck
NoRebootPackage
NoRebootPackageDownLevel
ServerCorePlatformCheck
ServerCore64BitCheck
ThreadHasAdminPrivilegeCheck
RebootRequiredCheck
WmiServiceStateCheck
AclPermissionsFacet
MediaPathLength
SetupCompatibilityCheck
NoRebootPackage
NoRebootPackageDownLevel
ServerCorePlatformCheck
ServerCore64BitCheck
Bids2008InstalledCheck
DenaliCTPbyCTPSxS
AclPermissionsFacet
FacetDomainControllerCheck
SSMS_IsInternetConnected
FacetWOW64PlatformCheck
FusionRebootCheck
Bids2008InstalledCheck
DenaliCTPbyCTPSxS
AclPermissionsFacet
FacetDomainControllerCheck
SSMS_IsInternetConnected
FacetWOW64PlatformCheck
IsFirewallEnabled
ServerCoreBlockUnsupportedSxSCheck
DEV10RTMDetected
ASSPIExistingFarmUnconfiguredWarningCheck
ASSPIInstanceNameNotInUseCheck
ASSPINewFarmConfiguredWarningCheck
ASSPIRequiresCompleteFarmCheck
ASSPIRequiresMossBitsCheck
ASSPIRequiresO14MossEnterpriseCheck
ASSPIRequiresO14MossSP1Check
SlipstreamMediaInfoCheck
PowerShellCheck
InternetConnectionToNETFX4DownloadSite
ServerCoreBlockUnsupportedFeaturesCheck
ServerCoreNetFxCheck
RS_IsDotNet3_5Installed
FAT32FileSystemCheck
InstanceClashRule
StandaloneInstall_HasClusteredOrPreparedInstanceCheck
BlockCrossLanguageInstall
BlockMixedArchitectureInstall
RS_DoesCatalogExist
RS_DoesCatalogTempDBExist
ASIsValidEditionForDeploymentModeCheck
FusionRebootCheck
Bids2008InstalledCheck
DenaliCTPtoCTPUpgrade
AclPermissionsFacet
FacetDomainControllerCheck
Cluster_IsOnlineIfClustered
SSMS_IsInternetConnected
FacetWOW64PlatformCheck
Cluster_DNS_Consistency_Rule
Cluster_IsWMIServiceOperational
ServerCoreBlockUnsupportedSxSCheck
YukonUpgradeSidRule
BlockCrossLanguageUpgrade
KatmiCTPupgradeToKilimanjaroBlockRule
KatmaiSlipstreamBuildToBuildUpgradeRule
RS_ValidDSN
RS_ValidDatabaseVersion
RS_ValidServerCollation
RS_NoCustomRenderingExtensions
RS_NoCustomSecurityExtensions
RS_NoCustomAuthExtensions
RS_ReportServerUnsupportedSecurityMode
RS_ReportManagerUnsupportedSecurityMode
RS_ReportServerClientCertificateRequired
RS_ReportManagerClientCertificateRequired
RS_RSServiceRunning
RSSHP_IsO12Installed
RSSHP_ReportServerVDirNotSupported
Engine_SqlServerServiceDisabled_Id
Engine_IsAweEnabledForX86
Engine_IsMinusHEnabledForX86
Engine_IsLPIMEnabledForX86
Engine_IsLPIMEnabledForX64
Engine_SqlEngineHealthCheck
Engine_AllSystemDatabasesAccessibleCheck
Engine_ResourceDLLUpdateRestartCheck
BlockMixedArchitectureUpgrade
Engine_ServiceAccountOnDomainCheck
Cluster_MultipleGroupsUpgradeRule
FeatureUpgradeMatrixCheck
IncompleteUpgradeCheck
FailedUpgradeCheck
LocalOnly_SqlFeatureStateCheck
LocalOnly_AsFeatureStateCheck
RsFeatureStateCheck
Cluster_FeatureDownGradeCheck
AS_IsServiceHealthy_Id
AS_UpgradeValidateMixedState
FAT32FileSystemCheck
DEV10RTMDetected
FacetWOW64ClusterUpgradeCheck
SlipstreamMediaInfoCheck
ServerCoreBlockUnsupportedFeaturesCheck
ServerCoreNetFxCheck
RSSHP_UpgradePreDenali
PowerShellCheck
RS_IsDotNet3_5Installed
KatmaiRTMUpgradeBlockRule
KJRTMUpgradeBlockRule
YukonSP3UpgradeBlockRule
SideBySideUninstallRule
BlockMismatchedArchitectureUninstall
UninstallClusterBlockCheck
SlipstreamMediaInfoCheck
Cluster_IsOnlineIfClustered
FacetWOW64PlatformCheck
Cluster_IsWMIServiceOperational
ServerCoreBlockUnsupportedSxSCheck
AS_IsClusterServiceOffline
Engine_SqlResourceIsOfflineIfCurrentNodeActive
SlipstreamMediaInfoCheck
ServerCoreNetFxCheck
PowerShellCheck
RS_IsDotNet3_5Installed
FacetDomainControllerCheck
Engine_SqlServerServiceDisabled_Id
Engine_IsAweEnabledForX86
Engine_IsMinusHEnabledForX86
Engine_IsLPIMEnabledForX86
Engine_IsLPIMEnabledForX64
Engine_SqlEngineHealthCheck
Engine_OnlySupportedFeaturesUsedCheckSystem
Engine_OnlySupportedFeaturesUsedCheckUser
ServerCoreBlockUnsupportedSxSCheck
EditionUpgradeMatrixCheck
EditionDownGradeCheck
Cluster_EditionDownGradeCheck
BlockMixedArchitectureUpgrade
SlipstreamMediaInfoCheck
UcpEditionDowngradeRule
Engine_IsAlwaysOnFeatureEnabled
MultiSubnetClusterUpgradeCheck
ServerCoreBlockUnsupportedFeaturesCheck
ServerCoreNetFxCheck
Engine_SqlServerServiceDisabled_RebuildDatabase
Engine_ResourceGroupIsActiveOnCurrentNode
Engine_SqlResourceIsOfflineIfCurrentNodeActive
FusionRebootCheck
Cluster_IsOnlineIfClustered
Cluster_IsWMIServiceOperational
Cluster_IsUserAdmin
Cluster_RemoteRegistryServiceFacet
Cluster_IsDomainController
SSMS_IsInternetConnected
IsDomainNetworkTopOfBindings
IsFirewallEnabled
Cluster_DNS_Consistency_Rule
FacetWOW64SetupCheck
ServerCoreBlockUnsupportedSxSCheck
ClusterPrepare_HasClusteredInstanceCheck
ClusterSupportCheck
ClusterFeaturesCheckForBI
Engine_FilestreamRequiredHotfixesCheck
Engine_ResourceDLLUpdateRestartCheck
FAT32FileSystemCheck
InstanceClashRule
SlipstreamMediaInfoCheck
ServerCoreBlockUnsupportedFeaturesCheck
ServerCorePlatformCheck
ServerCore64BitCheck
ServerCoreBlockUnsupportedSxSCheck
ServerCoreBlockUnsupportedFeaturesCheck
ServerCoreNetFxCheck
DEV10RTMDetected
PowerShellCheck
RS_IsDotNet3_5Installed
FusionRebootCheck
Cluster_IsMachineClustered
Cluster_IsWMIServiceOperational
Cluster_IsUserAdmin
Cluster_IsOnline
Cluster_IsDTCInstalled
Cluster_IsDTCRunning
Cluster_IsDTCClustered
Cluster_VerifyForErrors
Cluster_VerifyForWarnings
Cluster_Prepped_Instance
Cluster_RemoteRegistryServiceFacet
Cluster_IsDomainController
Cluster_DNS_Consistency_Rule
FacetWOW64SetupCheck
ServerCoreBlockUnsupportedSxSCheck
Cluster_NumberOfNodes
Cluster_FailedPreppedInstance
Cluster_ClusteredInstance
Cluster_IsDomainController
SqlFeatureStateCheck
AsFeatureStateCheck
IsSqlInstanceIdConsistentCheck
IsAsInstanceIdConsistentCheck
IsSqlVersionConsistentCheck
IsAsVersionConsistentCheck
IsSqlEditionConsistentCheck
IsAsEditionConsistentCheck
SlipstreamMediaInfoCheck
Cluster_SharedDiskFacet
ServerCoreBlockUnsupportedFeaturesCheck
ServerCoreNetFxCheck
Engine_FilestreamConfigurationCheck
MultiSubnetClusterOSCheck
MultiSubnetClusterSkuCheck
FusionRebootCheck
Cluster_IsMachineClustered
Cluster_IsWMIServiceOperational
Cluster_IsUserAdmin
Cluster_IsOnline
Cluster_IsDTCInstalled
Cluster_IsDTCRunning
Cluster_IsDTCClustered
Cluster_VerifyForErrors
Cluster_VerifyForWarnings
Cluster_RemoteRegistryServiceFacet
Cluster_IsDomainController
SSMS_IsInternetConnected
IsDomainNetworkTopOfBindings
IsFirewallEnabled
Cluster_DNS_Consistency_Rule
FacetWOW64SetupCheck
ServerCoreBlockUnsupportedSxSCheck
ClusterSupportCheck
ClusterFeaturesCheckForBI
Cluster_SharedDiskFacet
PowerShellCheck
DEV10RTMDetected
SlipstreamMediaInfoCheck
ServerCoreBlockUnsupportedFeaturesCheck
ServerCoreNetFxCheck
RS_IsDotNet3_5Installed
InstanceClashRule
FAT32FileSystemCheck
Engine_ResourceDLLUpdateRestartCheck
Engine_FilestreamRequiredHotfixesCheck
FusionRebootCheck
Cluster_IsMachineClustered
Cluster_IsWMIServiceOperational
Cluster_IsUserAdmin
Cluster_IsOnline
Cluster_IsDTCInstalled
Cluster_IsDTCRunning
Cluster_IsDTCClustered
Cluster_VerifyForErrors
Cluster_VerifyForWarnings
Cluster_RemoteRegistryServiceFacet
Cluster_IsDomainController
SSMS_IsInternetConnected
IsDomainNetworkTopOfBindings
IsFirewallEnabled
Cluster_DNS_Consistency_Rule
FacetWOW64SetupCheck
ServerCoreBlockUnsupportedSxSCheck
Cluster_NumberOfNodes
SqlFeatureStateCheck
AsFeatureStateCheck
AddNodeEditionBlock
Engine_FilestreamRequiredHotfixesCheck
Engine_ResourceDLLUpdateRestartCheck
FAT32FileSystemCheck
InstanceClashRule
DEV10RTMDetected
SlipstreamMediaInfoCheck
MultiSubnetClusterOSCheck
MultiSubnetClusterSkuCheck
ServerCoreBlockUnsupportedFeaturesCheck
ServerCoreNetFxCheck
PowerShellCheck
RS_IsDotNet3_5Installed
FusionRebootCheck
Cluster_IsMachineClustered
Cluster_IsWMIServiceOperational
Cluster_IsUserAdmin
Cluster_IsOnline
Cluster_RemoteRegistryServiceFacet
ServerCoreBlockUnsupportedSxSCheck
FusionRebootCheck
SysPrepFeatureCheck
Bids2008InstalledCheck
SSMS_IsInternetConnected
AclPermissionsFacet
FacetDomainControllerCheck
FacetWOW64PlatformCheck
ServerCoreBlockUnsupportedSxSCheck
BlockMixedArchitectureInstall
BlockCrossLanguageInstall
FAT32FileSystemCheck
Unconfigured_SqlFeatureStateCheck
Unconfigured_AsFeatureStateCheck
Unconfigured_RsFeatureStateCheck
PowerShellCheck
SlipstreamMediaInfoCheck
InternetConnectionToNETFX4DownloadSite
ServerCoreBlockUnsupportedFeaturesCheck
ServerCoreNetFxCheck
ServerCorePlatformCheck
ServerCore64BitCheck
ServerCoreBlockUnsupportedSxSCheck
ServerCoreBlockUnsupportedFeaturesCheck
ServerCoreNetFxCheck
PowerShellCheck
RS_IsDotNet3_5Installed
SSMS_IsInternetConnected
IsFirewallEnabled
AclPermissionsFacet
FacetDomainControllerCheck
FacetWOW64PlatformCheck
ServerCoreBlockUnsupportedSxSCheck
EditionUpgradeMatrixCheck
EditionDownGradeCheck
FAT32FileSystemCheck
InstanceClashRule
ServerCoreBlockUnsupportedFeaturesCheck
ServerCoreNetFxCheck
Anúncios

Habilitar o Dark Theme no SSMS 2016


Para quem já baixou o SSMS 2016/2017 deste a versão de julho o pessoal da Microsoft liberou os temas Blue e Light.

Como o SSMS é baseado no Visual Studio essa possibilidade cosmética já estava demorando para aparecer.

O problema é já que liberaram esses 2 temas por que não liberar o Dark?

Abri um connect a alguns meses pedindo esse tema (se você ainda não votou, aproveita e acessa o link e vote) de acordo com a resposta inicial o tema existe mas está desativado pois ainda estão trabalhando nele.

Caso você esteja interessado em saber como está o andamento do que eles tem até agora você pode habilitar o tema Dark para o SSMS 2016 (13.0.16000.28)

  1. Abra o notepad em modo de administração;
  2. Navegue até o diretório c:\program files (x86)\Microsoft SQL Server\130\tools\binn\managementstudio
  3. Abra o arquivo SSMS.pkgundef
  4. Dentro do arquivo localize a entrada “Remove Dark theme”
  5. Comente com // todas as linhas abaixo dessa entrada até o “Remove dacfx…” Atualização!!! Não precisam ser todas as linhas, apenas a linha diretamente abaixo do “Remove Dark theme”
  6. Salve o arquivo e reinicie o SSMS 2016

Até agora eles só trocaram as cores das barras de menu e do editor de query…

É perceptível que falta muita coisa como “Object Explorer”, “Grid Result”, etc.

SQL Server no Linux


Todos já devem estar sabendo dessa “novidade”

O link para o blog: https://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux

Se você quiser se cadastrar para “testar” o preview o link é: https://www.microsoft.com/en-us/server-cloud/sql-server-on-linux.aspx

Honestamente não sei se isso vai ser bom ou ruim,,, vejo muitas aplicações interessantes, mas também vejo muita dor de cabeça pela frente…

pode ser que eles usem isso para acabar com a versão Enterprise, forçando as empresas a usarem a nuvem caso queiram HA ou features avançadas? ou é uma forma de baratear o produto já que “em teoria” existem versões gratuitas de Linux? ainda mais pensando que ele vai ser distribuído por Docker,,,

Bom,,, de qualquer forma, mais uma coisa para relembrar,,, como faz tempo que não trabalho com linux… 😦

Event Notification para kill


i-see-dead-people-you-must-be-new-hereAlgum tempo atras fiz um post sobre Event Notification para monitorar DeadLock o que funciona muito bem,,,

Com um pouco de modificação o mesmo código pode ser utilizado para muitas outras coisas,,, por exemplo: limitar acesso a uma instância bloqueando a conexão do usuário…

Aí você pensa: “posso fazer isso por trigger, por que fazer diferente?”

Eu respondo,,, se você trabalha em ambientes com alto volume de conexões, ou ambientes que as vezes sofrem com falta de recursos como cpu ou memória sabe que a trigger pode ser uma pedra no sapato,,, ao invés de respeitar o filtro e impedir apenas algumas conexões ela bloqueia tudo e todos…

Já com o Event Notification é o contrário,,, no pior cenário ele só para de funcionar…

Os dois pontos que vejo como mais complicados são:

  • Não é possível personalizar uma mensagem para o usuário que sofreu o kill
  • Dependendo do que você fizer, ele pode começar a encher o errorlog com mensagens de kill

com o código abaixo consegui chegar a 20.000 tentativas de conexão simultâneas e nenhuma conseguiu conectar,,,


--apenas para constatar quais tipos de eventos podemos tratar... o que queremos eh o audit_login
select * from sys.event_notification_event_types with (nolock)
where type_name like '%login%'
use master
go

alter database BASE_DE_ADMINISTRACAO_DO_DBA set enable_broker with rollback immediate
go

alter database BASE_DE_ADMINISTRACAO_DO_DBA set TRUSTWORTHY on
go

use BASE_DE_ADMINISTRACAO_DO_DBA
go

CREATE QUEUE [Login_Killer_Queue]
GO

CREATE SERVICE [Login_Killer_Service]
AUTHORIZATION [dbo]
ON QUEUE [dbo].[Login_Killer_QUEUE]
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
CREATE ROUTE Login_Killer_Route
WITH SERVICE_NAME = 'Login_Killer_Service',
ADDRESS = 'LOCAL';
GO
DECLARE @AuditServiceBrokerGuid [uniqueidentifier]
,@SQL [varchar](max);

-- Pega o service broker guid da base de dados
SELECT @AuditServiceBrokerGuid = [service_broker_guid]
FROM [master].[sys].[databases]
WHERE [name] = 'ADM_BDADOS'

-- Cria e executa o SQL dinamico para criar o objeto do evento de notificacao
SET @SQL = 'IF EXISTS (SELECT * FROM sys.server_event_notifications
WHERE name = ''Login_Killer_Notification'')

DROP EVENT NOTIFICATION Login_Killer_EventNotification ON SERVER

CREATE EVENT NOTIFICATION Login_Killer_EventNotification
ON SERVER
WITH fan_in
FOR AUDIT_LOGIN
TO SERVICE ''Login_Killer_Service'', '''
+ CAST(@AuditServiceBrokerGuid AS [varchar](50)) + ''';'
EXEC (@SQL)
GO
SELECT * FROM [sys].[server_event_notifications]

select * from sys.server_event_session_actions with (nolock)
CREATE TABLE [dbo].[DBA_FailedConnectionTracker](
[host_name] [varchar](128) NOT NULL,
[login_name] [varchar](128) NOT NULL,
[spidu] int,
[FailedLoginData] XML
) ;

CREATE PROCEDURE dbo.spc_DBA_FailedConnectionTracker
AS
BEGIN
SET NOCOUNT ON;
-- looping infinito
WHILE (1 = 1)
BEGIN
DECLARE @messageBody VARBINARY(MAX);
DECLARE @messageTypeName NVARCHAR(256);
WAITFOR (
RECEIVE TOP(1)
@messageTypeName = message_type_name,
@messageBody = message_body
FROM [Login_Killer_Queue]
), TIMEOUT 500
-- se nao houver mensagens saia
IF @@ROWCOUNT = 0
BEGIN
BREAK ;
END ;
-- se o tipo da mensagem for um EventNotification para a fila atual
IF (@messageTypeName = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification')
BEGIN
DECLARE @XML XML,
@host_name varchar(128) ,
@login_name varchar(128) ,
@SPID varchar(5);

SELECT @XML=CONVERT(XML,@messageBody)
,@host_name = ''
,@login_name = ''
,@SPID ='';

-- Pega o SPID e as informacoes de login
SELECT @SPID = @XML.value('(/EVENT_INSTANCE/SPID)[1]', 'varchar(5)')
, @host_name = @XML.value('(/EVENT_INSTANCE/HostName)[1]', 'NVARCHAR(128)')
, @login_name = @XML.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(128)')
;

DECLARE @kill varchar(8000) = '';
--Caso o hostname e o login entrem no criterio abaixo
if ((@host_name like 'SPO%') and (@login_name like '%_user'))
SELECT @kill = @kill + 'kill ' + @SPID + ';'
EXEC(@kill);

--/* esta parte pode ser comentada para não gerar "log" do kill
if ((@host_name like 'SPO%') and (@login_name like '%_user'))
INSERT INTO [dbo].[DBA_FailedConnectionTracker]
([host_name], [login_name], FailedLoginData,spidu)
values ( @host_name, @login_name,@XML,@SPID);
--*/
END;
END;
END;
--inicia a matanca

ALTER QUEUE [dbo].[Login_Killer_Queue]
WITH STATUS = on
,ACTIVATION (PROCEDURE_NAME = [spc_DBA_FailedConnectionTracker]
,STATUS = ON
,MAX_QUEUE_READERS = 1
,EXECUTE AS OWNER)
GO

Ativando um Windows trial


As vezes é legal voltar a trabalhar um pouco com outras coisas além de SQL Server,,,

Essa semana fiz uma consultoria em Active Directory,,,, Fui resolver alguns problemas de replicação e configurar um novo domain controler para replicação,,,

Até aí nenhum grande problema,,, o cliente já havia instalado o SO (Windows Server 2012R2),,, atualizei o SO, alteramos o nome, adicionando ao domínio,,, até aí tudo tranquilo,,,

O problema: o cliente havia instalado o SO na versão trial de 180 dias,,, quando tentamos registrar o Windows ele informava que o número de série não era compatível com a versão instalada,,,

Em contato com o time de suporte da MS eles informaram que a única opção era reinstalar o SO na versão correta,,, o que na teoria não estava errado,,, mas sempre tem um jeito,,,,

Caso você tenha instalado um SO trial e queira registra-lo como full faça o seguinte:

  1. Abra o prompt em modo elevado e digite  dism /online /get-targeteditions
  2. Ele vai te mostrar para quais versões você pode elevar a instalação atual,,,
  3. Tenha em mãos com o serial da versão que você quer usar
  4. Agora digite:  DISM /online /Set-Edition:ServerStandard /ProductKey:XXXXX-XXXXX-XXXXX-XXXXX-XXXXX /AcceptEula Onde neste caso a edição é para qual queremos registrar este SO e o xxxx é o número de série,,,
  5. Se você fez certo será apresentada uma mensagem de erro,,,, Error 87
  6. Você não tem como forçar um upgrade de versão usando um serial que nao seja de KMS,,, haha pegadinha do malandro,,,
  7. Entre nesse site https://technet.microsoft.com/en-us/library/jj612867.aspx e copie o serial correto para sua versão,,,
  8. Substitua o xxxxx por esse serial e agora sim vai funcionar,,, só que ele não avisa que vai reiniciar,,, provavelmente ele deve reiniciar umas 2x
  9. Após isso, você deve conseguir ativar seu Windows com onseu serial sem problemas,,,

Caso não funcione sempre tem a opção do backup, reinstalar certo dessa vez e restore,,,

MSDB e os Pacotes de SSIS


Esse código mostra o tamanho ocupado por cada pacote de SSIS carregado dentro do MSDB.

Algumas vezes o MSDB começa a ganhar proporções ocupadas que apenas a limpeza de rotina não dá conta e o pessoal esquece que os pacotes também ocupam espaço…


with ChildFolders
as
(
select PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,
cast('' as sysname) as RootFolder,
cast(PARENT.foldername as varchar(max)) as FullPath,
0 as Lvl
from msdb.dbo.sysssispackagefolders PARENT
where PARENT.parentfolderid is null
UNION ALL
select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,
case ChildFolders.Lvl
when 0 then CHILD.foldername
else ChildFolders.RootFolder
end as RootFolder,
cast(ChildFolders.FullPath + '/' + CHILD.foldername as varchar(max))
as FullPath,
ChildFolders.Lvl + 1 as Lvl
from msdb.dbo.sysssispackagefolders CHILD
inner join ChildFolders on ChildFolders.folderid = CHILD.parentfolderid
)
select F.RootFolder, F.FullPath, P.name as PackageName,
P.description as PackageDescription, P.packageformat, P.packagetype,
P.vermajor, P.verminor, P.verbuild, P.vercomments,
cast(cast(P.packagedata as varbinary(max)) as xml) as PackageData,datalength(P.packagedata)/1024 AS SpaeceusedKB
from ChildFolders F
inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid
order by F.FullPath asc, P.name asc;

Event Notification para monitorar DeadLock


A algumas semanas tivemos a necessidade de substituir a forma que usávamos para monitorar Dead Lock por alguma coisa mais usual…

Antes o Dead Lock era gravado no errorlog e depois encaminhado por email para o time,,, isso não é usual já que fica colocando muita informação desnecessária no errorlog e depois de alguns restarts perde-se algum histórico…

Com isso, cheguei a conclusão,,, vamos usar o event notification….

A vantagem é ser muito menos intrusivo que uma trigger e bem mais fácil de trabalhar que o System_Health…

Qual a ideia:

  1. O Event Notification vai enviar um email para cada vez que o evento de DeadLock acontecer, neste email vai conter informação da base que sofreu o DeadLock, horário e o DeadLock Graphic.
  2. Vai gravar em uma tabela na base de administração do DBA informações como: data e hora, base, XML com o evento.

use master
go

/*você já deve ter uma base de administração, caso não tenha, crie uma e troque o nome*/

alter database BASE_DE_ADMINISTRACAO_DO_DBA set enable_broker with rollback immediate
go

alter database BASE_DE_ADMINISTRACAO_DO_DBA set TRUSTWORTHY on
go

use BASE_DE_ADMINISTRACAO_DO_DBA
go

/**criando a tabela de controle/

create TABLE [dbo].[SQLDeadlockEvents](
[EventRowID] [bigint] primary key IDENTITY(1,1) NOT NULL,
[EventType] [nvarchar](128) NOT NULL,
[AlertTime] [datetime] NULL,
[DeadlockGraph] [xml] NULL,
[DatabaseName] [sysname] null,
[AuditDate] [smalldatetime]
) ON [FG_Data_01]
with (data_compression = PAGE) --neste caso tenho EE se você usar SE comente esta linha
GO

/**Criando a fila do Broker/

CREATE QUEUE [DeadlockGraphQueue]
GO

/*Criando o serviço para a fila do Broker*/

CREATE SERVICE [DeadlockEventsService]
AUTHORIZATION [dbo]
ON QUEUE [dbo].[DeadLockGraphQueue]
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

/*criando rota*/

CREATE ROUTE DeadlockGraphRoute
WITH SERVICE_NAME = 'DeadlockEventsService',
ADDRESS = 'LOCAL';
GO

/* Para ficar mais fácil ele vai criar o event notification com os dados dinamicamente, se não, eu teria que explicar que o guid altera, onde tem que pegar essa informação,,, era mais SQL para uma coisa que pode ser feita de forma dinâmica */

DECLARE @AuditServiceBrokerGuid [uniqueidentifier]
,@SQL [varchar](max);

-- broker guid do CaptureDeadlockGraph
SELECT @AuditServiceBrokerGuid = [service_broker_guid]
FROM [master].[sys].[databases]
WHERE [name] = 'BASE_DE_ADMINISTRACAO_DO_DBA'

SET @SQL = 'IF EXISTS (SELECT * FROM sys.server_event_notifications
WHERE name = ''DeadlockGraphEventNotification'')

DROP EVENT NOTIFICATION DeadlockGraphEventNotification ON SERVER

CREATE EVENT NOTIFICATION DeadlockGraphEventNotification
ON SERVER --aqui é o detalhe legal... servidor e não database
WITH fan_in
FOR DEADLOCK_GRAPH
TO SERVICE ''DeadlockEventsService'', '''
+ CAST(@AuditServiceBrokerGuid AS [varchar](50)) + ''';'
EXEC (@SQL)
GO

/*Para confirmar que foi criado,,, lembra que é nível servidor,,, por isso a consulta no [sys].[server_event_notifications] */

SELECT * FROM [sys].[server_event_notifications]
WHERE [name] = 'DeadlockGraphEventNotification';
GO

/* onde a mágica acontece.... */
create PROC [dbo].[usp_sCaptureDeadlockGraph]
AS BEGIN

SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON

DECLARE @SQLProcedureName [varchar](64)
SET @SQLProcedureName = 'usp_sCaptureDeadlockGraph'

BEGIN TRY

DECLARE @message_body [xml]
, @EventTime [datetime]
, @EventType [varchar](128)
, @message_type_name [nvarchar](256)
, @dialog [uniqueidentifier]

-- Endless loop
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION ;

-- Receive the next available message
WAITFOR (RECEIVE TOP(1)
@message_type_name = [message_type_name],
@message_body = [message_body],
@dialog = [conversation_handle]
FROM [dbo].[DeadlockGraphQueue]), TIMEOUT 2000

-- Rollback and exit if no messages were found
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END;

-- End conversation of end dialog message
IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
PRINT 'End Dialog received for dialog # ' + CAST(@dialog as [nvarchar](40));
END CONVERSATION @dialog;
END;

ELSE
BEGIN

SET @EventTime = CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS [nvarchar](max)) AS [datetime])
SET @EventType = CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS [nvarchar](128))

INSERT INTO [dbo].[SQLDeadlockEvents] ([EventType], [AlertTime], [DeadlockGraph])
VALUES (@EventType, @EventTime, @message_body)
END

COMMIT TRANSACTION

DECLARE @MailSubject [nvarchar](256)
,@MailBody [nvarchar](max)
,@SQLQuery [nvarchar](max)
,@AttachedFileName [nvarchar](256)
,@EventDateTime [datetime]
,@EventID [int]
,@Counter [int]
,@dbname [sysname]

IF EXISTS (SELECT * FROM [tempdb].[dbo].[sysobjects]
WHERE name = '##ListDeadlocks' AND type in (N'U'))
DROP TABLE ##ListDeadlocks

CREATE TABLE ##ListDeadlocks
([EventRowID] [int]
,[AlertTime] [datetime]
,[DeadlockGraph] [xml]
,[DeadlockXml] [xml])

INSERT INTO ##ListDeadlocks
([EventRowID]
,[AlertTime]
,[DeadlockGraph]
,[DeadlockXml])
SELECT [EventRowID]
,[AlertTime]
,[DeadlockGraph].query('./EVENT_INSTANCE/TextData/deadlock-list')
,DeadlockGraph FROM [dbo].[SQLDeadlockEvents]
WHERE [EventRowID] = @@IDENTITY

select @dbname = db_name(DeadlockGraph.value( '(/deadlock-list//@dbid)[1]', 'int' ))
--db_name(DeadlockGraph.value( '(/EVENT_INSTANCE/TextData/deadlock-list//@dbid)[1]', 'int' ) )
FROM ##ListDeadlocks
--where [EventRowID] = SCOPE_IDENTITY()

update [dbo].[SQLDeadlockEvents]
set [DatabaseName] = @dbname
where [EventRowID] = (select [EventRowID] FROM ##ListDeadlocks )

SELECT @Counter = MIN(EventRowID)
FROM ##ListDeadlocks

WHILE @Counter IS NOT NULL
BEGIN

SELECT @EventDateTime = [AlertTime]
,@EventID = [EventRowID]
FROM ##ListDeadlocks
WHERE [EventRowID] = @Counter

SELECT @MailSubject = 'Um deadlock ocorreu em ' + cast(@dbname as [varchar](256)) --+' servidor: '
--+ CONVERT([varchar](128), SERVERPROPERTY('ServerName'))
,@MailBody = 'Deadlock EventRowID: ' + CAST(@EventID AS [varchar](12))
+ char(13) + char(13) + 'O deadlock ocorreu as '
+ CONVERT([varchar](50), @EventDateTime, 120)
+ ' no Servidor: ' + CONVERT([varchar](128), SERVERPROPERTY('ServerName'))+'.'+char(13) + char(13) + 'Verifique o anexo xdl-file para mais detalhes.'
+ char(13) + char(13) +'Para mais informações ou eventos anteriores consulte: SELECT * FROM BASE_DE_ADMINISTRACAO_DO_DBA.dbo.SQLDeadlockEvents WITH (NOLOCK)'
,@SQLQuery = 'SET NOCOUNT ON; SELECT [DeadlockGraph] FROM [##ListDeadlocks] WITH (READUNCOMMITTED) WHERE EventRowID = '
+ CAST(@EventID AS [varchar](10))
,@AttachedFileName = CONVERT([varchar](128), SERVERPROPERTY('ServerName')) + '_' + CAST(@EventID AS [varchar](12)) + '.xdl'

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'email@dominio.com.br' -- troque o email aqui
,@subject = @MailSubject
,@body = @MailBody
,@query = @SQLQuery
,@attach_query_result_as_file = 1 ,@query_attachment_filename = @AttachedFileName -- http://support.microsoft.com/kb/924345
,@query_result_header = 0
,@query_result_width = 32767
,@query_no_truncate = 1

DELETE FROM ##ListDeadlocks
WHERE EventRowID = @Counter

SELECT @Counter = MIN(EventRowID)
FROM ##ListDeadlocks
end
END --End of loop

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER()
,ERROR_SEVERITY()
,ERROR_STATE()
,ISNULL(ERROR_PROCEDURE(), @SQLProcedureName)
,ERROR_LINE()
,ERROR_MESSAGE()

END CATCH

END
GO

/*inicia a fila para começar a capturar os DeadLocks*/
ALTER QUEUE [dbo].[DeadlockGraphQueue]
WITH STATUS = ON
,ACTIVATION (PROCEDURE_NAME = [usp_sCaptureDeadlockGraph]
,STATUS = ON
,MAX_QUEUE_READERS = 1
,EXECUTE AS OWNER)
GO