Archives gerados por hora/dia – Oracle

Neste artigo mostrarei um script que mostra a quantidade dearchives gerados por hora pelo RMAN.

Este script foi criado pelo Jeffrey M. Hunter. Este script ‘analisa’ a view v$log_history.

Este script pode servir como um troubleshooting simples, por exemplo: às 13hrs sempre são gerados, em média, cerca de 5 de archives, porém alguns dias depois você percebe que foram gerados 10 archives no mesmo horário, ou seja, pode ser indício que algum processo pesado foi executado. É importante lembrar que isto são apenas indícios e que uma análise mais detalhada deverá ser realizada.

SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY   off

COLUMN H00   FORMAT 999     HEADING '00'
COLUMN H01   FORMAT 999     HEADING '01'
COLUMN H02   FORMAT 999     HEADING '02'
COLUMN H03   FORMAT 999     HEADING '03'
COLUMN H04   FORMAT 999     HEADING '04'
COLUMN H05   FORMAT 999     HEADING '05'
COLUMN H06   FORMAT 999     HEADING '06'
COLUMN H07   FORMAT 999     HEADING '07'
COLUMN H08   FORMAT 999     HEADING '08'
COLUMN H09   FORMAT 999     HEADING '09'
COLUMN H10   FORMAT 999     HEADING '10'
COLUMN H11   FORMAT 999     HEADING '11'
COLUMN H12   FORMAT 999     HEADING '12'
COLUMN H13   FORMAT 999     HEADING '13'
COLUMN H14   FORMAT 999     HEADING '14'
COLUMN H15   FORMAT 999     HEADING '15'
COLUMN H16   FORMAT 999     HEADING '16'
COLUMN H17   FORMAT 999     HEADING '17'
COLUMN H18   FORMAT 999     HEADING '18'
COLUMN H19   FORMAT 999     HEADING '19'
COLUMN H20   FORMAT 999     HEADING '20'
COLUMN H21   FORMAT 999     HEADING '21'
COLUMN H22   FORMAT 999     HEADING '22'
COLUMN H23   FORMAT 999     HEADING '23'
COLUMN TOTAL FORMAT 999,999 HEADING 'Total'


SELECT
    SUBSTR(TO_CHAR(first_time, 'DD/MM/RRRR HH:MI:SS'),1,10)                       DAY
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  v$log_history  a
GROUP BY 
	SUBSTR(TO_CHAR(first_time, 'DD/MM/RRRR HH:MI:SS'),1,10)
ORDER BY 1
Anúncios

Movendo arquivos dos databases de sistema – SQL Server

Neste artigo irei mostrar um método para mover os arquivos de DATA e LOG dos databases system (master, msdb, tempdb e model).

Caso você queira saber como mover os arquivos de DATA e LOG dos databases users (databases criado por usuário, a grosso modo), veja este post: Movendo Arquivos dos Databases – SQL Server

Antes de mover os arquivos de DATA e LOG dos databases system no SQL Server, é preciso entender melhor a importância de cada um:

• master: este database armazena todas as configurações à nível de sistema. Qualquer coisa que é definida no nível de instância do SQL Server normalmente é armazenada neste mesmo database. Se o database master estiver danificado ou corrompido, a instância do SQL Server não será iniciado (é importante sempre ser feito um backup deste database!).
• msdb: este database contém informações do SQL Server Agent, assim como Jobs, Operadores e alertas. Este database também contém informações sobre backups e restores feitos, assim como o histórico dos planos de execuções de manutenções, etc. Até a versão do SQL Server 2008 R2 os pacotes do SQL Server Integration Services (SSIS) também eram armazenados neste database.
• model: este database possuí um template no qual todos os usuários dos databases são baseados. Qualquer novo database que é criado usa o model como modelo. Todos os objetos criados no database model estarão presentes em todos os databases que forem criados na instância. Pode parecer que este database não é muito importante, porém caso este database esteja danificado ou corrompido, a instância do SQL Server não será iniciado.
• tempdb: este database armazena os dados temporários da instância. Toda vez que o SQL Server for iniciado, este database é truncado, portanto não há necessidade de realizar um backup deste banco de dados, tanto que não há opção para ser executado um backup deste database.

Vamos começar pelo database master. Você verá que o procedimento para alterar o caminho dos arquivos do database master é diferente dos demais, por que? Quando você inicia o serviço da instância, ele executa um executável e passa os parâmetros dos arquivos de DATA e LOG da master, pois lá que as informações pertinentes à instância se encontram. Claro que a ordem de quais databases você irá mudar não influência, porém há de se tomar MUITO cuidado com os passos a serem feitos, pois um problema que ocorrer, não será possível voltar atrás, claro, dependendo do problema.

Para mudar o caminho dos arquivos de DATA e LOG do database master é necessário realizar os seguintes passos principais:
1. Alterar os parâmetros de inicialização do serviço do SQL Server;
2. Parar o serviço (instância);
3. Mover os arquivos de LOG (mastlog.ldf) e DATA (master.mdf);
4. Iniciar o serviço (instância).

Para começar, vamos verificar onde estão os arquivos de DATA e LOG do database master através da query:

Use master 
GO
EXEC sp_helpfile 

Anote o caminho que está na coluna filename, pois iremos copiar os arquivos que estão neste caminho para um outro caminho, no caso o caminho “C:\CaminhosSQLServer\LOG” para o arquivo de LOG, e o caminho: “C:\CaminhosSQLServer\DATA” para o arquivo de DATA.

master_01

Logo em seguida abra o SQL Server Configuration Manager através do comando mmc.exe

master_02

No nó SQL Server Services, dê um duplo clique na instância do SQL Server, logo em seguida clique em Propriedades, e depois vá na aba Avançado:

master_03

Edite o Parâmetros de Inicialização alterando os parâmetros para onde você irá copiar os arquivos de DATA e LOG. Por exemplo, no meu caso está configurado da seguinte maneira:

-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.ALAN\MSSQL\DATA\master.mdf;-ec:\Program Files\Microsoft SQL Server\MSSQL10_50.ALAN\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.ALAN\MSSQL\DATA\mastlog.ldf

Onde há o parâmetro -d significa o caminho e o arquivo de DATA e o -l para LOG.
Como neste caso eu vou alterar os arquivos para os caminhos “C:\CaminhosSQLServer\LOG” e “C:\CaminhosSQLServer\DATA”, como dito anteriormente, ele deve estar configurado da seguinte maneira:

-dC:\CaminhosSQLServer\DATA\master.mdf;-ec:\Program Files\Microsoft SQL Server\MSSQL10_50.ALAN\MSSQL\Log\ERRORLOG;-lC:\CaminhosSQLServer\LOG\mastlog.ldf

* Não é permitido espaços depois dos parâmetros -d e -l e seus respectivos caminhos.

Em seguida pare o serviço da instância. Com o serviço parado (ele TÊM que estar parado), mova os arquivos de DATA e LOG atuais do database master

Neste exemplo os arquivos estão caminho “C:\Program Files\Microsoft SQL Server\MSSQL10_50.ALAN\MSSQL\DATA\”. Mova os arquivos de LOG e DATA para os respectivos caminhos configurados.

E em seguida inicie o serviço da instância. Se o serviço da instância subir, significa que a mudança do caminho dos arquivos foram feitos com sucesso!

Verifique o novo caminho dos arquivos de LOG e DATA utilizando a mesma query no início do passo-a-passo:

Use master 
GO
EXEC sp_helpfile 

Resultado:

master_04

Feito a mudança dos arquivos de LOG e DATA do database master, vamos executar o passo-a-passo dos demais databases system, no caso as databases: msdb, model e a tempdb.

Neste caso, os passos a serem realizados são mais simples, porém o cuidado deve ser o mesmo! Neste caso farei a mudança de apenas um database, no caso a msdb. Os passos a serem realizados no database msdb são os mesmos para os demais. Você pode fazer a mudança para os três de uma vez só, caso você queira. Não há problema nisso, desde que, claro, seja feito com muita cuidado e atenção.

Neste caso serão realizados 4 passos:
1. Para cada database que terão os arquivos movidos, executar o comando “ALTER DATABASE…MODIFY FILE”;
2. Parar o serviço da instância do SQL Server;
3. Mover os arquivos para os novos caminhos;
4. Iniciar a instância do SQL Server.

Vamos ao exemplo:

Assim como no primeiro passo do database master, obtenha o caminho atual dos arquivos de LOG e DATA do database msdb.

Use msdb
GO
EXEC sp_helpfile 

Guarde o caminho e o nome dos arquivos de LOG e DATA.

master_05

Agora execute o comando a seguir para mover os arquivos de LOG e DATA do database msdb.

USE master
GO
ALTER DATABASE msdb 
	MODIFY FILE 
		(NAME = MSDBData, FILENAME = 'C:\CaminhosSQLServer\DATA\MSDBData.mdf');
GO
ALTER DATABASE msdb 
	MODIFY FILE 
		(NAME = MSDBLog, FILENAME = 'C:\CaminhosSQLServer\LOG\MSDBLog.ldf');
GO

Após executar esta query, você deverá receber esta mensagem:

The file “MSDBData” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “MSDBLog” has been modified in the system catalog. The new path will be used the next time the database is started.

Mova os arquivos de LOG e DATA para os respectivos caminhos conforme o comando feito acima. Agora dê um STOP-START no serviço da instância do SQL Server. Se o serviço da instância subir, significa que a mudança do caminho dos arquivos foram feitos com sucesso!

Verifique novamente o caminho dos arquivos de LOG e DATA utilizando a mesma query no início do passo-a-passo:

Use msdb 
GO
EXEC sp_helpfile 

Resultado:

master_06

É algo simples de ser feito, porém é preciso ser feito com calma e ter cuidado. Todos os passos são importantes. Caso aconteça algo de errado, você não poderá conseguir novamente a instância do SQL Server.

Em caso de erros ao realizar este passo-a-passo, deixe um comentário que eu poderei ajudá-lo da melhor maneira. Em casos de duvidas, também pode deixar um comentário que tentarei respondê-lo o quanto antes.

É isso aí!

Backup Differential não pode ser restaurado porque não há arquivos prontos para reversão – SQL Server

Esta mensagem ocorre quando você tenta restaurar um backup diferencial utilizando o SQL Server 2005 – 2012.

“System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.SqlServer.Smo)”.

Este erro está dizendo que não há nenhuma base de dados no modo no-operational (não-operacional), e, portanto, não foi feita de tal forma que as transações não comitadas (confirmadas) não fossem revertidas. Quando você restaura um backup no modo RESTORE WITH RECOVERY (padrão), indica que o roll forward deve ser realizado somente enquanto o backup FULL for restaurado, ou seja, quando o restore for finalizado nenhum arquivo de LOG ou Differential poderá ser restaurado, pois o arquivo de LOG estará truncado. Ao realizar o restore no modo RESTORE WITH NORECOVERY, após o final do restore do backup FULL o roll forward não será finalizado, assim deixando os arquivos de LOG “abertos” para que mais arquivos de LOG sejam restaurados.

A maneira mais fácil de reproduzir este erro é realizando um backup FULL da sua base de dados, e em seguida restaura-lá com a opção RESTORE WITH RECOVERY. Logo em seguida faça um backup Differential da mesma base de dados e tente restaurar o backup no modo RESTORE WITH RECOVERY. Ao realizar o backup ele irá retornar esta mensagem de erro:

msg_error

Para realizar o restore do Backup Differential da maneira correta, antes você precisa restaurar o backup FULL com a opção WITH NORECOVERY (este é o passo mais importante!):

step02

Após o restore verifique se o database está com o status “RESTORING”:

Use Master
GO
SELECT
	Name, State_Desc
FROM
	sys.databases
WHERE
	Name = N'DBTeste1'

step04

Se ele estiver com o status RESTORING, faça o restore do backup Differential com a opção WITH RECOVERY:

step01

E assim o Backup Differential será restaurado.

É importante e primordial lembrar-se que antes de restaurar um backup Differential ou de Log é preciso restaurar o backup Full da maneira correta para evitar esta mensagem de erro e refazer todo o processo do restore.

É isso aí!

Monitoramento de Serviços do SQL Server – VBScript

Neste artigo mostrarei um script (vbscript) que gera um arquivo no Excel com algumas informações sobre os serviços do SQL Server de um ou mais servidores.

O script irá retornar os seguintes campos:

• System Name: hostname do servidor;
• Display Name: nome do serviço que é mostrado no services.msc;
• Name: nome do serviço;
• Started: se o serviço está ou não iniciado;
• Start Mode: se o serviço está desabilitado, se é iniciado manualmente ou automaticamente;
• Logon Name: qual conta sobe o serviço;
• State: o status do serviço: running (rodando) ou stopped (parado).

Caso o serviço esteja parado, a linha estará com a fonte com cor vermelha.

Este script efetua um SELECT em uma classe (ou tabelas) do Windows – para mais informações destas classes, veja este post. Nesta classe ele coleta as informações referentes aos serviços que possuem o “SQL” no nome. Lembrando que ele não olha nada no banco de dados, ou efetua alguma conexão com o banco de dados, etc. Ele olha diretamente estas informações no SO!

Importante: Este script necessita de um driver, no caso o “Microsoft Excel Driver”. Verifique se você possuí o driver instalado: para isso, vá em Control Panel->Administrative Tools->Data Sources (ODBC), dê um duplo clique. Ele irá mostrar uma série de abas, na aba User DSN (a primeria aba), clique em Add. Veja se está listado o “Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)” ou “Microsoft Excel Driver (*.xls)”:

driverexcel

Caso você não possua o driver, você pode baixar neste link do MSDN.

Obs.: O usuário que executa o script precisa ter permissão para ler estas classes nos servidores onde serão feitas as verificações.

Para executar o script, é preciso criar um arquivo.txt com a lista dos servidores a serem verificados. No script o arquivo é este : C:\Maquinas.txt. Você pode mudar o caminho e o nome do arquivo no seguinte trecho código:

Set InputFile = fso.OpenTextFile(“C:\Maquinas.txt“)

Exemplo: C:\Maquinas.txt

localhost

O script do arquivo verifica_servicos.vbs é este:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
intRow = 2

objExcel.Cells(1, 1).Value = "System Name"
objExcel.Cells(1, 2).Value = "Display Name"
objExcel.Cells(1, 3).Value = "Name"
objExcel.Cells(1, 4).Value = "Started"
objExcel.Cells(1, 5).Value = "Start Mode"
objExcel.Cells(1, 6).Value = "Logon Name"
objExcel.Cells(1, 7).Value = "State"

Set Fso = CreateObject("Scripting.FileSystemObject")
Set InputFile = fso.OpenTextFile("C:\Maquinas.txt")
Do While Not (InputFile.atEndOfStream)
	strComputer = InputFile.ReadLine

	Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
	Set colItems = objWMIService.ExecQuery("Select * From Win32_Service Where Name Like '%SQL%'")

	For Each objItem in colItems
		objExcel.Cells(intRow, 1).Value = objItem.SystemName
		objExcel.Cells(intRow, 2).Value = objItem.DisplayName
		objExcel.Cells(intRow, 3).Value = objItem.Name
		objExcel.Cells(intRow, 4).Value = objItem.Started
		objExcel.Cells(intRow, 5).Value = objItem.StartMode
		objExcel.Cells(intRow, 6).Value = objItem.StartName
		objExcel.Cells(intRow, 7).Value = objItem.State

		If objExcel.Cells(intRow, 7).Value = "Stopped" Then
			objExcel.Cells(intRow, 7).EntireRow.Font.ColorIndex = 3
		End If

		intRow = intRow + 1
	Next
Loop

objExcel.Range("A1:G1").Select
objExcel.Selection.Interior.ColorIndex = 19
objExcel.Selection.Font.ColorIndex = 11
objExcel.Selection.Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
Set objRange = objExcel.Range("B2")
objRange.Sort objRange,1,,,,,,1

Execute o verifica_servicos.vbs, o resultado será:

result

Caso você queria saber mais sobre como utilizar o objeto Excel.Application, veja este link.

É isso aí!

Lendo Informações do Backup – SQL Server

Neste artigo irei mostrar alguns comandos T-SQL, que estão disponíveis do SQL Server 2005, que retornam informações úteis dos backups realizado, tais como: arquivos de data e log que estão no backup, horário de inicio e término do backup, integridade do backup realizado, etc.

Os comandos T-SQL são:

RESTORE FILELISTONLY
RESTORE LABELONLY
RESTORE HEADERONLY
RESTORE VERIFYONLY

Obs.: estes comandos apenas extraem as informações que estão no backup, isso sem a necessidade de restaura-lós.

Para saber mais detalhes de cada informação retornada nestes comandos, clique no título do comando.

RESTORE FILELISTONLY

Este comando retorna informações sobre os arquivos de dados (mdf e ndf) e log (ldf) armazenados em um dispositivo.

-- FROM DISK = Caminho\NomeDoArquivo.BAK
RESTORE FILELISTONLY FROM DISK = 'C:\Backups\ALAN_TESTE\ALAN_TESTE_FULL.bak'

Por exemplo, neste comando ele retorna algumas informações importantes como:
– LogicalName: nome lógico do arquivo no SQL Server;
– PhysicalName: caminho e nome do arquivo;
– Type: o tipo do arquivo, ex: se o Type for “D”, trata-se de um arquivo de dados.

bkp01

RESTORE LABELONLY

Este comando retorna um conjunto de resultados que contém informações sobre as mídias de backup identificadas pelo dispositivo de backup designado. É um modo mais rápido de descobrir que a mídia de backup contém. A instrução lê somente o cabeçalho da mídia, essa instrução terminará rapidamente mesmo quando estiverem sendo usados a mídia como a fita.

-- FROM DISK = Caminho\NomeDoArquivo.BAK
RESTORE LABELONLY FROM DISK = 'C:\Backups\ALAN_TESTE\ALAN_TESTE_FULL.bak'

RESTORE HEADERONLY

Este comando retorna informações sobre os backups (Backup Set) armazenados em um dispositivo. É um dos comandos mais utilizados, pois retorna todos os backups armazenados no dispositivo, seus tipos e quais databases eles pertecem, o usuário que realizou o backup, a data de inicío e fim dos backups, collation, modo de recovery, etc.

-- FROM DISK = Caminho\NomeDoArquivo.BAK
RESTORE HEADERONLY FROM DISK = 'C:\Backups\ALAN_TESTE\ALAN_TESTE_FULL.bak'

bkp02

RESTORE VERIFYONLY

Este comando verifica se o conjunto de backup está completo e se todo o backup pode ser lido. Porém, RESTORE VERIFYONLY não tenta verificar a estrutura dos dados contida nos volumes de backup. Se o backup for válido, o retorno será de sucesso.

-- FROM DISK = Caminho\NomeDoArquivo.BAK
RESTORE VERIFYONLY FROM DISK = 'C:\Backups\ALAN_TESTE\ALAN_TESTE_FULL.bak'

Caso o backup seja válido, o retorno deverá ser:

The backup set on file 1 is valid.

Essas informações são importantes para verificar o status do backup para evitar erros ao restaurar o backup, entre outras informações.
É isso aí!

Movendo Arquivos dos Databases – SQL Server

Neste artigo irei mostrar um método para alterar o caminho dos arquivos de log e data dos databases.

Este método pode ser utilizado do SQL Server 2000 até a mais atual, o SQL Server 2012.

Este método contém os seguintes passos:
• Obter o diretório atual dos arquivos;
• Alterar o database para SINGLE_USER, para fechar todas as conexões no banco;
• Alterar o status do database para OFFLINE para que ele não leia mais os arquivos;
• Realizar o comando T-SQL para fazer o “detach” no database, ou seja, durante este processo o db engine ignora a existência dessa base;
• Mover os arquivos para o novo diretório;
• Realizar o comando T-SQL para fazer o “attach” no database;
• Alterar o status do database para ONLINE para subir o database e para ler os arquivos;
• Alterar o database para MULTI_USER para que outras conexões possam ser abertas;
• Verificar se o diretório foi modificado.

Antes de mostrar um exemplo prático, é necessário estar atento à alguns detalhes:
– Você precisa copiar os arquivos para o caminho novo antes de realizar o comando para efetuar a troca, pois isso dará erro, e mesmo com o erro se você tentar subir o database, o mesmo não achará os arquivos, ou seja, o database não irá subir;
– Apesar de ser bem simples o passo-a-passo, tenha muito cuidado ao escrever o nome da pasta e dos arquivos nos comandos;
– O caminho novo (diretório) precisa que o usuário, da qual é executado o Serviço do SQL Server, tenha permissão para escrever, ler e apagar quaisquer arquivos.

Vamos ao exemplo prático:

Neste exemplo criei um database chamado ALAN_TESTE. Os arquivos de data e log do database (ALAN_TESTE) estão no Dir1. Vou mudar o caminho do arquivo de data (mdf) para outro diretório: \Dir2\Data, e também vou mudar o caminho do arquivo de log (ldf) para outro diretório: \Dir2\Log.

1. Obter o diretório atual dos arquivos

-- ROWS = Arquivos de Data
-- LOG  = Arquivos de Log
SELECT
	Type_Desc, Name, Physical_Name, State_Desc
FROM
	sys.master_files 
WHERE
	database_id = DB_ID(N'ALAN_TESTE')

O resultado:

movedb01

2. Alterar o database para SINGLE_USER

Use master 
GO
ALTER DATABASE ALAN_TESTE
	SET SINGLE_USER
GO

3. Alterar o status do database para OFFLINE

Use master 
GO
ALTER DATABASE ALAN_TESTE
	SET OFFLINE 
GO

4. Realizar o comando T-SQL para fazer o “detach” no database

Use master 
GO
sp_detach_db 'ALAN_TESTE'
GO

5. Mover os arquivos para o novo diretório

Mova o arquivo C:\Dir1\ALAN_TESTE.mdf para o diretório C:\Dir2\Data. E também mova o arquivo C:\Dir1\ALAN_TESTE_log.ldf para o diretório C:\Dir2\Log.

6. Realizar o comando T-SQL para fazer o “attach” no database

Use master 
GO
sp_attach_db 'ALAN_TESTE'
	,'C:\Dir2\Data\ALAN_TESTE.mdf'
	,'C:\Dir2\Log\ALAN_TESTE_log.ldf'
GO

7. Alterar o status do database para ONLINE

Use master 
GO
ALTER DATABASE ALAN_TESTE
	SET ONLINE
GO

8. Alterar o database para MULTI_USER

Use master 
GO
ALTER DATABASE ALAN_TESTE
	SET MULTI_USER
GO

9. Verificar se o diretório foi modificado

-- ROWS = Arquivos de Data
-- LOG  = Arquivos de Log
SELECT
	Type_Desc, Name, Physical_Name, State_Desc
FROM
	sys.master_files 
WHERE
	database_id = DB_ID(N'ALAN_TESTE')

Veja que os diretórios foram alterados com sucesso:

movedb02

Não tem muito segredo, mas é importante fazer com cuidado, seguindo passo-a-passo com calma.
É isso!

Ú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.