Verifica Restore em Tempo Real – SQL Server

Neste artigo irei mostrar uma query que mostra informações de um RESTORE em tempo real.

A query irá retornar os seguintes campos:

  • HostName: nome do servidor (hostname) na qual a sessão está sendo executada.
  • LoginName: nome do logon do SQL Server no qual a sessão está sendo executada.
  • SessionID: id da sessão a que esta solicitação está relacionada.
  • Percent: porcentagem de trabalho concluída até o momento.
  • MinutesRunning: tempo de execução em minutos.
  • StartTime: data/hora do inicio do RESTORE.
  • EstimatedCompletion: data/hora previsto para o fim do RESTORE.
  • DatabaseName: database no qual a solicitação está em execução.
  • ProgramName: nome do programa cliente (ou JOB) que iniciou o RESTORE.
  • Command: identifica qual tipo de comando está sendo realizado no momento.
  • Text: comando SQL utilizado no RESTORE.

Links úteis:
sys.dm_exec_requests
– sys.dm_exec_sessions
– sys.dm_exec_sql_text
dbo.sysjobs

* esta query é compatível com o SQL Server 2008 R2 ou versões superiores.

USE master
GO
SELECT
S.Host_Name AS [HostName]
,S.Login_Name AS [LoginName]
,R.Session_ID AS [SessionID]
,Cast(R.Percent_Complete AS decimal(10,3)) AS [Percent]
,IsNull(DateDiff(minute, S.Last_Request_Start_Time, GetDate()), 0) [MinutesRunning]
,Start_Time AS [StartTime]
,DateAdd(second, Estimated_Completion_Time / 1000, GetDate()) AS [EstimatedCompletion]
,DB_Name(R.Database_ID) AS [DatabaseName]
,(CASE
WHEN S.Program_Name Like 'SQLAgent - TSQL JobStep (Job %' THEN J.Name
ELSE S.Program_Name END) AS [ProgramName]
,R.Command
,B.Text
FROM
sys.dm_exec_requests R WITH (NOLOCK)
JOIN
sys.dm_exec_sessions S WITH (NOLOCK)
ON
R.Session_ID = S.Session_ID
OUTER APPLY
sys.dm_exec_sql_text(R.SQL_Handle) B
LEFT OUTER JOIN
msdb.dbo.sysjobs J WITH (NOLOCK)
ON
(SubString(Left(J.Job_ID, 8), 7, 2) +
SubString(Left(J.Job_ID, 8), 5, 2) +
SubString(Left(J.Job_ID, 8), 3, 2) +
SubString(Left(J.Job_ID, 8), 1, 2)) = SubString(S.Program_Name, 32, 8)
WHERE
R.Session_ID > 50
AND R.Session_ID <> @@SPID
AND S.[Host_Name] Is Not Null
AND R.Command = 'RESTORE DATABASE'
ORDER BY
S.[Host_Name], S.Login_Name

Resultado:

resultado_query

 

Anúncios

Últimas Operações das Tabelas – SQL Server

Nesse artigo eu mostrarei um script que retorna a última data de uma operação UPDATE e SELECT nas tabelas de um database.

O script retorna os seguintes campos:
Name: nome da tabela
User_Seeks: número de pesquisas feitas com o FORCESEEK
User_Scans: número de pesquisas feitas com o SCAN
User_Lookups: número de pesquisas de indicador através do LOOKUP
User_Updates: número de Updates realizados (operações como INSERT e UPDATE)
Last_User_Seek: data da última pesquisa utilizando o FORCESEEK
Last_User_Scan: data da última pesquisa utilizando o SCAN
Last_User_Lookup: data da última pesquisa utilizando o LOOKUP
Last_User_Update: data do último UPDATE

Antes é preciso adrentar-se a algumas observações:

Essa query utiliza uma DM chamada sys.dm_db_index_usage_stats. Essa DM retorna as operações realizadas em índices em cada tipo de operação.
É importante estar ciente que essa query não é 100% confiável quando se trata de operações como DELETE. Como ele retorna operações em índices, ele retorna apenas SELECT, INSERT e UPDATE.

Importante: Uma vez que a instância é reiniciada, a tabela qual a DM coleta as informações é zerada.

É preciso analisar com cuidado os resultados: quando uma tabela é consultada com o FORCESEEK e o SCAN, ele adiciona duas linhas para a mesma tabela. Uma linha contém o Last_User_Scan preenchido enquanto o Last_User_Seek está nulo, isso quando a consulta foi realizada com o SCAN. Na outra linha, o Last_User_Seek está preenchido enquanto o Last_User_Scan está nulo, isso quando a consulta foi realizada com FORCESEEK.

No script estou ordenando as últimas consultas realizadas pelo SCAN (Last_User_Scan), você pode alterar a última data conforme sua necessidade.

Obs.: estou apenas mostrando as operações feitas por usuários (NT e SQL). Existem outros campos que mostram as alterações feitas pelo SQL (System). Nesse link você pode verificar a documentação oficial no MSDN.

SELECT DISTINCT 
	 T.Name			
	,User_Seeks		
	,User_Scans		
	,User_Lookups		
	,User_Updates		
	,Last_User_Seek		
	,Last_User_Scan		
	,Last_User_Lookup	
	,Last_User_Update	
FROM
	sys.dm_db_index_usage_stats I 
JOIN
	sys.tables T 
ON 
	T.Object_ID = I.Object_ID
WHERE
	Database_ID = DB_ID()
ORDER BY
	Last_User_Scan DESC

Exemplo

Execute o seguinte script.

USE ALAN_TESTE
GO
CREATE TABLE tbTeste(Data datetime)
GO
INSERT INTO tbTeste VALUES(GETDATE())
INSERT INTO tbTeste VALUES(GETDATE())
INSERT INTO tbTeste VALUES(GETDATE())
GO
SELECT * FROM tbTeste 
GO
SELECT DISTINCT 
	 T.Name
	,User_Seeks
	,User_Scans
	,User_Lookups
	,User_Updates
	,Last_User_Seek
	,Last_User_Scan
	,Last_User_Lookup
	,Last_User_Update
FROM
	sys.dm_db_index_usage_stats I 
JOIN
	sys.tables T 
ON 
	T.Object_ID = I.Object_ID
WHERE
	Database_ID = DB_ID()
ORDER BY
	Last_User_Scan DESC

O resultado será esse:

index_usage

Veja que o campo User_Scans está com o valor = 1, ou seja, foi realizado uma consulta após o INSERT. O campo User_Updates está com o valor = 3, ou seja, foram realizados 3 inserções na tabela. E o Last_User_Scan está com a data em que foi realizado o SELECT e o Last_User_Update está com a data em que foi realizado o INSERT.

Query últimos Backups – SQL Server Pt. 2

Como dito no último post, irei mostrar uma simples query que retornará onde os Backups foram salvos e o tamanho dos mesmos.

A query irá retornar os seguintes campos:

Database_Name: o nome do database.
Physical_Device_Name: o local e nome do arquivo do backup realizado. Caso o Backup tenha sido salvo em uma fita, o resultado será parecido como esse: {7878439E-5007-4EB3-96AC-447AEE19EBE6}1
Size: o tamanho do backup em MB.
Backup_Start_Date: horário de início do backup.
Backup_Finish_Date: horário de término do backup.
Seconds_Duration: o tempo, em segundos, da realização do backup.
Backup_Type: tipo do Backup.
Server_Name: nome do servidor onde foi realizado o backup.

Nessa query estou utilizando um parâmetro de dias x backup_start_date para listar os backups.

Links úteis:
BackupSet
BackupMediaFamily

Use msdb 
GO
SET NOCOUNT ON
GO

DECLARE @Dias Int

Set @Dias = 0

SELECT 
	 S.Database_Name
	,M.Physical_Device_Name
	,Convert(Decimal(12,2), S.Backup_Size / 1024 / 1024) As Size
	,S.Backup_Start_Date
	,S.Backup_Finish_Date
	,Cast(DateDiff(Second, S.Backup_Start_Date , S.Backup_Finish_Date) As Varchar(4)) As Seconds_Duration
	,Case S.Type
		When 'D' Then 'Full'
		When 'I' Then 'Differential'
		When 'L' Then 'Transaction Log'
	End As BackupType
	,S.Server_Name
FROM 
	msdb.dbo.BackupSet S
JOIN 
	msdb.dbo.BackupMediaFamily M
ON 
	S.Media_Set_ID = M.Media_Set_ID
WHERE 
	S.Database_Name In (SELECT Name FROM Sys.Databases)
AND	S.Backup_Start_Date > Convert(Char(10), (DateAdd(Day, - @Dias, GetDate())), 121)
-- Para listar todos os databases sem o parametro de dias,
-- comente a linha as duas linhas acima (S.Database... e S.Back...) e troque pelas linhas abaixo.
/*
	S.Database_Name = 'MyDataBase'
*/
ORDER BY 
	S.Backup_Start_Date DESC, S.Backup_Finish_Date

Query últimos Backups – SQL Server Pt. 1

Neste post irei mostrar uma query que mostra o último Backup realizado em todos os databases, exceto o tempdb, pubs, northwind e o model.

* Em um próximo post irei mostrar o tamanho dos backups realizados e onde os backups foram salvos.

A query retorna os seguintes campos:

Servidor – Instancia: retorna o servidor (hostname) e a instância dos databases.
Database_Name: nome do database
Full_Dias: diferença de dias do último backup FULL realizado.
Full_Termino: data e hora do término do backup FULL.
Full_Tamanho_MB: tamanho do backup FULL em MB.
Diff_Dias: diferença de dias do último backup DIFFERENTIAL realizado.
Diff_Termino: data e hora do término do backup DIFFERENTIAL.
Diff_Full_Diff: diferença dos dias entre o backup FULL e o DIFFERENTIAL.
Diff_Tamanho_MB: tamanho do backup DIFFERENTIAL em MB.
Tran_Minutos: diferença em minutos do último backup LOG realizado.
Tran_Termino: data e hora do término do backup LOG.
Tran_Tamanho_MB: tamanho do backup LOG em MB.

A query é essa:

USE master 
GO
SET NOCOUNT ON
GO

DECLARE @Server varchar(40)

Set @Server = Convert(varchar(35), ServerProperty('machinename')) + '\' + @@ServiceName

BEGIN TRY
	SELECT 
		 @Server As 'Servidor - Instancia'
		,FR.Database_Name
		,DateDiff(Day, FR.Backup_Finish_Date, GetDate()) As 'Full_Dias'
		,FR.Backup_Finish_Date  As 'Full_Termino'
		,Convert(Char,Convert(Numeric(12,2),(FR.Backup_Size / 1024 / 1024))) As Full_Tamanho_MB
		,DateDiff(Day, DR.Backup_Finish_Date, GetDate()) As 'Diff_Dias'
		,DR.Backup_Finish_Date  As 'Diff_Termino'
		,Case 
			When DR.Backup_Finish_Date Is Null Then Null
			Else DateDiff(Day, FR.Backup_Finish_Date, DR.Backup_Finish_Date)
		End As 'Dias_Full_Diff'
		,Convert(Char,Convert(Numeric(12,2),(DR.Backup_Size / 1024 / 1024))) As Diff_Tamanho_MB
		,DateDiff(Minute, TR.Backup_Finish_Date, GetDate()) As 'Tran_Minutos'
		,TR.Backup_Finish_Date As 'Tran_Termino'
		,Convert(Char,Convert(Numeric(12,2),(TR.Backup_Size / 1024 / 1024))) As Tran_Tamanho_MB
	FROM 
		msdb.dbo.backupset As FR
	LEFT OUTER JOIN
		msdb.dbo.backupset As TR
	ON
		TR.Database_Name = FR.Database_Name
	AND TR.Type = 'L'
	AND TR.Backup_Finish_Date =
		(
			(SELECT	Max(Backup_Finish_Date) 
			FROM	msdb.dbo.backupset B2 
			WHERE	B2.Database_Name = FR.Database_Name 
			And B2.Type = 'L')
		)
	LEFT OUTER JOIN
		msdb.dbo.backupset As DR
	ON
		DR.Database_Name = FR.Database_Name
	AND DR.Type = 'I'
	AND DR.Backup_Finish_Date =
		(
			(SELECT Max(Backup_Finish_Date) 
			FROM	msdb.dbo.backupset B2 
			WHERE B2.Database_Name = FR.Database_Name 
			  And B2.Type = 'I')
		)
	WHERE
		FR.Type = 'D' -- full backups only
	AND FR.Backup_Finish_Date = 
		(
			SELECT Max(Backup_Finish_Date) 
			FROM msdb.dbo.backupset B2 
			WHERE B2.Database_Name = FR.Database_Name 
			And   B2.Type = 'D'
		)
	And	FR.Database_Name In (SELECT name FROM master.dbo.sysdatabases) 
	And	FR.Database_Name Not In ('tempdb','pubs','northwind','model')

UNION ALL

	SELECT
		 @Server
		,Name
		,NULL
		,NULL
		,NULL 
		,NULL
		,NULL 
		,NULL
		,NULL
		,NULL
		,NULL
		,NULL
	FROM 
		master.dbo.sysdatabases As Record
	WHERE
		Name Not In(SELECT DISTINCT Database_Name FROM msdb.dbo.backupset)
	And Name Not In('tempdb','pubs','northwind','model')
	ORDER BY
		1, 2
END TRY
BEGIN CATCH
	SELECT message_id, text FROM sys.messages WHERE message_id = @@ERROR AND language_id = 1033
END CATCH

Sessões em Espera – Oracle

Neste artigo irei mostrar um simples script responsável por listar todas as sessões que estão com o ‘status’ de espera, ou seja, ainda estão na fila para poder conectar-se no banco.

O Oracle fornece várias views para facilitar a monitoração, neste post iremos utilizar duas views importantes chamado: v$session_wait e v$system_event. Estas views nos fornecem informações detalhadas sobre o estado de espera das sessões. É sempre importante verificar se há muitas sessões em espera, pois isso pode causar algum gargalo no sistema ou afetar alguns objetos individualmente.

Este script retorna os seguintes campos:

UserName: nome do usuário no Oracle;
SID: o id da sessão;
Serial#: o serial number da sessão, na qual é utilizado apenas para identificar a sessão dos objetos utilizados;
Event: o evento para a qual sessão está em espera;
Wait_Class: nome da classe para o evento em espera;
Wait_Time: se a sessão está atualmente aguardando, então o valor é 0. Se a sessão não está em uma espera e, em seguida, o valor é o seguinte: > 0-valor é a duração da última aguardar em centésimos de segundo -1 – duração da última espera foi menos de um centésimo de um segundo–2 – TIMED_STATISTICS parâmetro foi definido como false;
Seconds_In_Wait: mostra em segundos o tempo que a sessão está como espera;
State: o status da sessão.

Script:

SET LINESIZE 200
SET PAGESIZE 1000

COLUMN UserName FORMAT A20
COLUMN Event FORMAT A30
COLUMN Wait_Class FORMAT A15

SELECT 
	  NVL(s.UserName, '(oracle)') As UserName
	, s.SID
	, s.Serial#
	, sw.Event
	, sw.Wait_class
	, sw.Wait_Time
	, sw.Seconds_In_Wait
	, sw.State
FROM   
	v$session_wait sw, v$session s
WHERE  
	s.SID = sw.SID
ORDER BY 
	sw.Seconds_In_Wait DESC;

SQL Server – Verificar a query rodada por um SPID

Algumas vezes é possivel utilizar a query abaixo para verificar a query que foi rodada (esta sendo) por um SPID, a vantagem de utilizar a mesma é a possibilidade de verificar a query, mesmo depois da mesma ja ter terminado, claro que só por um certo periodo de tempo.

Ja utilizei bastante essa query nas seguinte situação: um SPID esta bloqueando alguns processos importantes, o mesmo precisou ser morto, logo após, me pediram para analisar qual era a query que estava bloqueando todo mundo, foi necessario a utilização da mesma para poder verificar essa informação, sendo que o status retornado estava como “RollBack”, ou seja, não mais ativo.

DECLARE @handle VARBINARY(64)

SELECT @handle = sql_handle from sys.sysprocesses where spid = SpidAPesquisar

SELECT text FROM sys.dm_exec_sql_text(@handle)

By: Fabrizzio Caputo

Active Directory – SQL Server

Basicamente toda infra-estrutura de uma empresa de TI é composta de um AD (Active Directory), para uma breve explicação: Onde ficam os usuários, suas informações e permissões, claro que é mais que isso, mas não tem importância neste momento.

Um DBA é responsavel pela bases de dados da empresa, e uma tarefa que de vez em quando é necessaria, é pesquisar diretamente no AD, principalmente se sua empresa for passar por um processo de auditoria.

Antes da query para realizar a mesma, é importante saber que, é possivel exportar essas informações do AD para uma tabela, porem, suas informações podem não estar atualizadar, portanto, o mais interessante é criar uma View, onde ele pesquisa diretamente, não havendo informações inconsistentes.

OBS: O metodo a seguir é limitado a 1000 linhas de retorno, mas é um metodo simples e que se aplica na maioria das empresas.

CREATE VIEW [dbo].[AD] 
AS
SELECT
	*
FROM
	OpenQuery(ADSI,
			'SELECT 
					Name, Manager, SamAccountName, Department, DistinguishedName, UserAccountControl
			FROM 
				''LDAP://SeuDominio''
			WHERE 
				ObjectCategory = ''Person'' 
			AND ObjectClass = ''user''
		Order By
			Mail'
	)

A unica customização que é necessaria é alterar o campo SeuDominio por seu dominio LDAP.

By: Fabrizzio Caputo