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

 

Sessão WTS – VB.NET

Neste post irei mostrar um código em VB.NET de uma classe chamada clsWTS. Esta classe retorna algumas informações básicas de uma conexão remota conectada em uma estação.

Esta classe utiliza duas API’s (User32.dll e WtsApi32.dll) do Windows para obter as informações a seguir:

• IsRemoteSession: verifica se é uma sessão remota;
• ClientLoginName: login do client conectado;
• ClientComputerName: nome da máquina na rede do client conectado;
• ClientDomainName: domínio do client conectado;
• ClientIPAddress: endereço IP do client que está realizando a sessão remota;
• SessionName: nome da sessão do client conectado.

Código da classe:

Imports System.Runtime.InteropServices

Public Class clsCWTS

#Region "APIs"

    Private Declare Function WTSQuerySessionInformationA Lib "wtsapi32.dll" (ByVal hServer As Integer, ByVal SessionID As Integer, ByVal WTSInfoClass As WTS_INFO_CLASS, ByRef ppBuffer As Integer, ByRef pBytesReturned As Integer) As Integer
    Private Declare Sub WTSFreeMemory Lib "wtsapi32.dll" (ByVal pMemory As Integer)
    Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Integer) As Integer
    Private Declare Function lstrlenA Lib "kernel32.dll" (ByVal lpString As Integer) As Integer
    Private Declare Function lstrcpyA Lib "kernel32.dll" (ByVal lpString1 As String, ByVal lpString2 As Integer) As Integer
    Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (ByVal pDst As Byte, ByVal pSrc As Integer, ByVal ByteLen As Integer)
    Private Declare Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal lpLibFileName As String) As Integer
    Private Declare Function GetProcAddress Lib "kernel32" (ByVal hModule As Integer, ByVal lpProcName As String) As Integer
    Private Declare Function FreeLibrary Lib "kernel32" (ByVal hLibModule As Integer) As Integer
    Private Declare Function WTSQuerySessionInformation2 Lib "WtsApi32.dll" Alias "WTSQuerySessionInformationW" (ByVal hServer As Int32, ByVal SessionId As Int32, ByVal WTSInfoClass As Int32, ByRef ppBuffer As IntPtr, ByRef pCount As Int32) As Boolean

    <DllImport("wtsapi32.dll", CharSet:=CharSet.Auto, SetLastError:=True)> _
    Private Shared Function WTSOpenServer(ByVal pServerName As String) As IntPtr
    End Function

#End Region

#Region "Constantes"
    Private Const SM_REMOTESESSION As Integer = &H1000
    Private Const WTS_CURRENT_SERVER_HANDLE As Integer = 0&
    Private Const WTS_CURRENT_SERVER = 0&
    Private Const WTS_CURRENT_SESSION As Integer = -1

    Private Const AF_INET = 2
    Private Const AF_IPX = 6
    Private Const AF_NETBIOS = 17
    Private Const AF_UNSPEC = 0
#End Region

#Region "Enums e Structures"
    Private Enum WTS_INFO_CLASS
        WTSInitialProgram
        WTSApplicationName
        WTSWorkingDirectory
        WTSOEMId
        WTSSessionId
        WTSUserName
        WTSWinStationName
        WTSDomainName
        WTSconnectState
        WTSClientBuilderNumber
        WTSClientName
        WTSClientDirectory
        WTSClientProductId
        WTSClientHardwareId
        WTSClientAddress
        WTSClientDisplay
        WTSClientProtocolType
    End Enum

    <StructLayout(LayoutKind.Sequential)> _
    Public Structure _WTS_CLIENT_ADDRESS
        Public AddressFamily As Integer
        <MarshalAs(UnmanagedType.ByValArray, SizeConst:=20)> _
        Public Address() As Byte
    End Structure
#End Region

#Region "Variáveis"
    Private m_bolIsRemoteSession As Boolean
    Private m_strWTSClientName As String
    Private m_strWTSUserName As String
    Private m_strWTSDomainName As String
    Private m_strWTSWinStationName As String
    Private m_strClientIPAddress As String
#End Region

#Region "Inicialização da classe"
    Public Sub New()
        QueryWTSSession()
    End Sub
#End Region

#Region "Properties (ReadOnly)"
    Public ReadOnly Property IsRemoteSession() As Boolean
        Get
            IsRemoteSession = m_bolIsRemoteSession
        End Get
    End Property

    Public ReadOnly Property ClientLoginName() As String
        Get
            ClientLoginName = m_strWTSUserName
        End Get
    End Property

    Public ReadOnly Property ClientComputerName() As String
        Get
            ClientComputerName = m_strWTSClientName
        End Get
    End Property

    Public ReadOnly Property ClientDomainName() As String
        Get
            ClientDomainName = m_strWTSDomainName
        End Get
    End Property

    Public ReadOnly Property ClientIPAddress() As String
        Get
            ClientIPAddress = m_strClientIPAddress
        End Get
    End Property

    Public ReadOnly Property SessionName() As String
        Get
            SessionName = m_strWTSWinStationName
        End Get
    End Property
#End Region

#Region "Funções relacionadas à classe clsCWTS"
    Private Sub QueryWTSSession()

        Dim btLinhaErro As Single = 0

        Try
            Dim lpBuffer As Integer = 0
            Dim lngBytesReturned As Integer = 0

            btLinhaErro = 1
            m_bolIsRemoteSession = (GetSystemMetrics(SM_REMOTESESSION) <> 0)
            If Not m_bolIsRemoteSession Then
                Exit Sub
            End If

            btLinhaErro = 2
            If Not IsProcedureAvailable("wtsapi32.dll", "WTSQuerySessionInformationA") Then
                Exit Sub
            End If

            btLinhaErro = 3
            If WTSQuerySessionInformationA(WTS_CURRENT_SERVER_HANDLE, WTS_CURRENT_SESSION, WTS_INFO_CLASS.WTSClientName, lpBuffer, lngBytesReturned) Then
                m_strWTSClientName = LPSTRtoBSTR(lpBuffer)
                WTSFreeMemory(lpBuffer)
            End If

            btLinhaErro = 4
            If WTSQuerySessionInformationA(WTS_CURRENT_SERVER_HANDLE, WTS_CURRENT_SESSION, WTS_INFO_CLASS.WTSUserName, lpBuffer, lngBytesReturned) Then
                m_strWTSUserName = LPSTRtoBSTR(lpBuffer)
                WTSFreeMemory(lpBuffer)
            End If

            btLinhaErro = 5
            If WTSQuerySessionInformationA(WTS_CURRENT_SERVER_HANDLE, WTS_CURRENT_SESSION, WTS_INFO_CLASS.WTSDomainName, lpBuffer, lngBytesReturned) Then
                m_strWTSDomainName = LPSTRtoBSTR(lpBuffer)
                WTSFreeMemory(lpBuffer)
            End If

            btLinhaErro = 6
            If WTSQuerySessionInformationA(WTS_CURRENT_SERVER_HANDLE, WTS_CURRENT_SESSION, WTS_INFO_CLASS.WTSWinStationName, lpBuffer, lngBytesReturned) Then
                m_strWTSWinStationName = LPSTRtoBSTR(lpBuffer)
                WTSFreeMemory(lpBuffer)
            End If

            btLinhaErro = 7
            Dim iptAddress As IntPtr = IntPtr.Zero
            Dim ptrOpenedServer As IntPtr = WTSOpenServer(String.Empty)
            Dim intReturned As Integer = 0

            If WTSQuerySessionInformation2(ptrOpenedServer, 0, WTS_INFO_CLASS.WTSClientAddress, iptAddress, intReturned) = True Then
                Dim wtsAddress As New _WTS_CLIENT_ADDRESS()
                wtsAddress = CType(Marshal.PtrToStructure(iptAddress, wtsAddress.GetType()), _WTS_CLIENT_ADDRESS)
                m_strClientIPAddress = String.Concat(wtsAddress.Address(2) & "." & wtsAddress.Address(3) & "." & _
                                                      wtsAddress.Address(4) & "." & wtsAddress.Address(5))
            End If

        Catch ex As Exception
            Dim strMsg As String = ex.Message
            Throw New Exception("Error in QueryWTSession. Row: " & btLinhaErro & " Msg: " & strMsg)
        End Try

    End Sub

    Private Function LPSTRtoBSTR(ByVal lpBuffer As Integer) As String
        Dim lngChars As Integer = 0
        Dim strChars As String = String.Empty

        lngChars = lstrlenA(lpBuffer)

        If lngChars > 0 Then
            strChars = New String(Chr(0), lngChars)
            lstrcpyA(strChars, lpBuffer)
        End If

        LPSTRtoBSTR = strChars
    End Function

    Private Function IsProcedureAvailable(ByVal ModuleName As String, ByVal ProcName As String) As Boolean
        Dim hMod As Integer
        Dim pAddr As Integer

        hMod = LoadLibrary(ModuleName)

        If hMod Then
            pAddr = GetProcAddress(hMod, ProcName)
            FreeLibrary(hMod)
        End If

        IsProcedureAvailable = (pAddr <> 0)
    End Function
#End Region

End Class

Um exemplo simples de como obter as informações desta classe:

        Try
            Dim WTS As New clsCWTS
            'Só exibe as informações caso a conexão seja remota (Windows Terminal Services)
            If WTS.IsRemoteSession Then
                MsgBox("Client Computer Name: " & WTS.ClientComputerName & vbCrLf & _
                       "Client Domain Name:" & WTS.ClientDomainName & vbCrLf & _
                       "Client IP Address: " & WTS.ClientIPAddress & vbCrLf & _
                       "Client Login Name: " & WTS.ClientLoginName & vbCrLf & _
                       "Session Name: " & WTS.SessionName)
            Else
                MsgBox("A sessão não é uma sessão remota!")
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Neste link você pode baixar o código-fonte exemplo que criei. O código-fonte foi compilado (x86) com o Visual Studio 2008.

Prefixo N em T-SQL – SQL Server

Por que o prefixo N é utilizado nos parâmetros de funções nativas do SQL Server (sp_helptext N’sp_minhaproc’) e por que ela é utilizada no T-SQL?

A resposta para esta pergunta é bem simples. O motivo pela qual você deve utilizar sempre o prefixo N é porque os parâmetros em questão são do tipo nvarchar, nchar ou ntext. O nvarchar, nchar e o ntext são tipos de dados que utilizam um conjunto de caracteres UNICODE UCS-2. O conjuntos de caracteres UNICODE UCS-2 são utilizados quando há alguns idiomas que possuem caracteres especiais no alfabeto, como por exemplo: polônes, japonês, entre outros. Quando você utiliza o prefixo N’xyz’, significa que você está convertendo o parâmetro para UNICODE.

Veja o exemplo a seguir:

DECLARE @ncTeste1 nchar(1)
DECLARE @ncTeste2 nchar(1)

Set @ncTeste1 = 'ą'
Set @ncTeste2 = N'ą'-- Com a conversão para unicode

SELECT @ncTeste1 AS [Sem N], @ncTeste2 AS [Com N]

Veja o resultado do exemplo:

duvidas01

É altamente recomendável utilizar o N quando for utilizar alguma função nativa do SQL Server e também quando o campo em questão for do tipo nvarchar, nchar ou ntext, por mais que você não esteja utilizando algum caractere especial.

Sequência de Collation – SQL Server

As sequências de collation controlam o modo como o SQL Server trata dados alfanuméricos para as opções de armazenamento, recuperação, classificação e comparação. O SQL Server permite especificar uma sequência de collation para suportar qualquer idioma atualmente usado no mundo.

As sequências de collation podem ser especificadas nos níveis de instância, banco de dados, tabela e coluna. A única sequência de collation que é obrigatória é definida em nível de instância, que tem como padrão todos os outros níveis, a não ser que sejam especificamente cancelados.

Uma sequência de collation define o conjunto alfanumérico suportado, incluindo diferenciação de letras maiúsculas e minúsculas, acentos e kana. Por exemplo, se você utilizar a sequência de collation SQL_Latin1_General_CP1_CI_AI, obterá suporte para um conjunto alfanumérico da Europa Ocidental que não diferencia letras maiúsculas e minúsculas e acentos. SQL_Latin1_General_CP1_CI_AI trata e, E, è, é, ê, e ë como se fossem o mesmo caractere para operações de classificação e comparação, enquanto uma sequência de collation francesa, que diferencia letras maiúsculas e minúsculas (CS) e acentos (AS), trata cada uma delas como um caractere diferente.

Para listar quais são as Collations disponíveis no SQL Server, execute o script a seguir:

SELECT * FROM ::fn_helpcollations()

Você terá este resultado:

collation_01

Notem que no final de cada collation existem algumas siglas (_AS, _WS, etc). Essas siglas possuem algumas diferenças que influenciam diretamente no resultado de uma consulta (ou qualquer outra query) e algumas funcionalidades no banco de dados. São 6 siglas:

AI: Accents Insensitive
AS: Accents Sensitive
CI: Case Insensitive
CS: Case Sensitive
KS: Kanatype Sensitive (1)
WS: Width Sensitive (2)

(1) Kana-sensitive: Especifica que o SQL Server deve distinguir entre os dois tipos de caracteres kana japoneses: hiragana e katakana. Se não for selecionada o SQL Server considera caracteres hiragana e katakana iguais.

(2) Width-sensitive: Especifica que o SQL Server deve distinguir entre um caractere de byte único (meia largura) e o mesmo caractere quando representado como um caractere de byte duplo (largura total). Se não for selecionada o SQL Server considera o byte único e byte duplo igualmente.

Ambas as notas ditas acimas são praticamente não utilizadas.

– Para saber a collation configurada a nível de instância (configurada na instalação), execute o script a seguir:

SELECT ServerProperty('Collation') As Collation_Name, ServerProperty('CollationID') As Collation_ID

– Para saber a collation configurada no seu banco de dados (database), execute o script a seguir:

SELECT DatabasePropertyEx('Database','Collation') As Collation_Name_DB

– Para saber a collation configurada em uma tabela, execute o script a seguir:

SELECT 
	Name, Collation 
FROM
	syscolumns 
WHERE 
	[Id] = Object_ID('Tabela')

Caso o retorno em alguma coluna seja NULL, significa que a collation assumida pela mesma é a mesma collation do database.

Links uteis
Como alterar a collation a nível de instância;
Conflitos de Collation em operações de comparação;
Eliminando caracteres especiais de strings no SQL Server.

Script para listar objetos dependentes – SQL Server

Neste artigo irei mostrar um script que mostra todos os objetos e suas respectivas dependências em um database.

Este script é útil para algumas situações, por exemplo: você irá realizar uma manutenção em uma procedure, e para isto você precisa saber quais são os objetos ‘dependetes’ desta mesma procedure a fim de verificar se estes objetos dependentes poderão ser impactados. Outro exemplo: você irá adicionar um novo campo em um tabela X, com este script você pode verificar a quais procedures, views ou functions utilizam esta tabela, entre outros.

O scritpt retorna os seguintes campos:

– DBName: nome do database;
– Object_Schema: o schema do objeto pai;
– Object_Name: o nome do objeto pai;
– Child_Schema: o schema do objeto dependente (filho);
– Child_Name: o nome do objeto dependente (filho);
– Child_Type: o tipo do objeto, por exemplo: USER_TABLE, SQL_STORED_PROCEDURE, etc.

O script analisa as views sys.objects e sysdepends.

Script

Use <Database>
SELECT
	 DB_Name() As DBName 
	,Referencing.DBObject_Schema As Object_Schema
	,Referencing.DBObject As [Object_Name]
	,Referenced.Child_DBBObject_Schema As [Child_Schema]
	,Referenced.Child_DBObject As [Child_Name]
	,Referenced.Child_DBObject_Type As [Child_Type]
FROM
(
	SELECT
		 [Object_ID] As DBObject_Id
		,Schema_Name([Schema_ID]) As DBObject_Schema
		,Name As DBObject
		,Type_Desc As DBObject_Type
	FROM
		sys.objects
	WHERE
		Type Not In('D','IT','PK','SQ','UQ','U','S','TR')
) Referencing
LEFT OUTER JOIN
(
	SELECT
		 Id As Parent_DBObject_Id
		,DepId As Child_DBObject_Id
		,Schema_Name([Schema_ID]) As Child_DBBObject_Schema
		,Name As Child_DBObject
		,Type_Desc As Child_DBObject_Type
	FROM
		sysdepends 
	JOIN	
		sys.objects
	ON
		[Object_ID] = DepId
	GROUP BY
		Id, DepId, [Schema_Id], Name, Type_Desc
) Referenced
ON
	Referencing.DBObject_Id = Referenced.Parent_DBObject_Id

Se você quiser obter as dependências de um objeto especifico, a cláusula WHERE deve ficar desta maneira:

(...)
ON
	Referencing.DBObject_Id = Referenced.Parent_DBObject_Id
WHERE 
	Referencing.DBObject = 'ObjectName'

Ou caso você queria obter os objetos pais de um objeto especifico (são referenciados), a cláusula WHERE deve ficar desta maneira:

(...)
ON
	Referencing.DBObject_Id = Referenced.Parent_DBObject_Id
WHERE 
	Referenced.Child_DBObject = 'ObjectName'

Verifica coleta de estatísticas das Tabelas e Índices – Oracle

Neste post irei mostrar um script que verifica a data da última coleta de estatísticas dos índices e tabelas no Oracle.

Antes de mostrar o script, irei deixar alguns links de conteúdo caso você ainda tenha alguma duvida, ou também não compreenda muito bem como funciona a coleta de estatísticas no Oracle, ou como fazer uma coleta de estatísticas, etc. São eles:

Gerenciando Estatísticas do Otimizador;
Coletando estatísticas para o otimizador de queries do Oracle;
Entendendo o Otimizador de Coleta de Estatísticas (conteúdo excelente!);
ALL_IND_STATISTICS.

Ambos scripts retornam os seguintes campos:
Schema: a qual schema (dono) o objeto faz parte;
Objeto: o nome do objeto;
Tipo do Objeto: qual é o tipo do objeto, neste caso: tabela ou índice;
Qtd. de Linhas: retorna o número de registros que o objeto possui;
Ultima Coleta: mostra a data e hora que fora realizada a última analise (coleta) no objeto.

Verifica a coleta de estatísticas das tabelas

SELECT
   S.Owner As "Schema"
  ,S.Table_Name As "Objeto"
  ,S.Object_Type As "Tipo do Objeto"
  ,S.Num_Rows As "Qtd. de Linhas"
  ,To_Char(S.Last_Analyzed, 'yyyy-MM-dd HH24:mm:ss') As "Ultima Coleta"
FROM
  sys.dba_tab_statistics S
WHERE
  S.Owner Not In('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
ORDER BY
  S.Last_Analyzed;

Verifica a coleta de estatísticas dos índices

SELECT
   S.Owner As "Schema"
  ,S.Table_Name As "Objeto"
  ,S.Object_Type As "Tipo do Objeto"
  ,S.Num_Rows As "Qtd. de Linhas"
  ,To_Char(S.Last_Analyzed, 'yyyy-MM-dd HH24:mm:ss') As "Ultima Coleta"
FROM
  sys.dba_ind_statistics S
WHERE
  S.Owner Not In('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
ORDER BY
  S.Last_Analyzed;

Tamanho do Banco de Dados em MB – Oracle

Neste artigo irei mostrar um script que mostra o tamanho da base de dados em uma instância do Oracle.

O script retorna os seguintes campos: Data, Undo, Redo, Temp, Livre e Total. Para entendimento de alguns campos retornados no script, segue alguns links para melhor entendimento do significado e a importância de cada um:

Arquivos de Data (Datafiles, tablespaces);
Undo;
Redo;
Temp.

Script:

SET FEED OFF

prompt
prompt TAMANHO DO BANCO DE DADOS EM MB
prompt ===============================

col dados for a10
col undo  for a12
col redo  for a12
col temp  for a12
col livre for a12
col total for a12

select to_char(sum(dados) / 1048576, 'fm99g999g990') dados,
       to_char(sum(undo) / 1048576, 'fm99g999g990') undo,
       to_char(sum(redo) / 1048576, 'fm99g999g990') redo,
       to_char(sum(temp) / 1048576, 'fm99g999g990') temp,
       to_char(sum(free) / 1048576, 'fm99g999g990') livre,
       to_char(sum(dados + undo + redo + temp) / 1048576, 'fm99g999g990') total
from (
  select sum(decode(substr(t.contents, 1, 1), 'P', bytes, 0)) dados,
         sum(decode(substr(t.contents, 1, 1), 'U', bytes, 0)) undo,
         0 redo,
         0 temp,
         0 free
  from dba_data_files f, dba_tablespaces t
  where f.tablespace_name = t.tablespace_name
  union all
  select 0 dados,
         0 undo,
         0 redo,
         sum(bytes) temp,
         0 free
  from dba_temp_files f, dba_tablespaces t
  where f.tablespace_name = t.tablespace_name(+)
  union all
  select 0 dados,
         0 undo,
         sum(bytes * members) redo,
         0 temp,
         0 free
  from v$log
  union all
  select 0 dados,
         0 undo,
         0 redo,
         0 temp,
         sum(bytes) free
  from dba_free_space f, dba_tablespaces t
  where f.tablespace_name = t.tablespace_name and
        substr(t.contents, 1, 1) = 'P'
);

prompt

SET FEED ON