Dependência de Objetos no SQL Server 2008

Finalmente voltei... depois de um longo período sem publicar nada no blog devido a problemas particulares, estou voltando com força total!

Vou escrever hoje sobre uma novidade na dependência de objetos no SQL Server 2008. Até o SQL Server 2005 toda a dependência de objetos era armazenada utilizando os IDs dos objetos, gerando informações incompletas ao se consultar o metadata com sp_depends ou sql_dependencies. Para entender porque veja o script abaixo:

use tempdb
go
create table Ref1 (col1 int)
create table Ref2 (col1 int)
go
create proc spu
as
select * from dbo.Ref1
select * from dbo.Ref2
select * from dbo.Ref3
go

A Stored Procedure SPU acessa três tabelas, porém uma delas (Ref3) não existe no banco de dados, “Deferred Name Resolution” (Resolução de Nomes Tardia). Tanto sp_depends quanto sql_dependencies retornam apenas Ref1 e Ref2 como objetos referenciados pela Stored Procedure SPU, observe no script abaixo:

exec sp_depends 'dbo.spu'

select OBJECT_NAME([object_id]) ObjAnalisado,
OBJECT_NAME(referenced_major_id) ObjReferenciado
from sys.sql_dependencies
where [object_id] = object_id('dbo.spu')

O SQL Server 2008 trouxe novidades, uma View (sql_expression_dependencies) e duas funções (dm_sql_referenced_entities e dm_sql_referencing_entities), que retornam a dependência dos objetos através do nome, resolvendo o problema descrito acima. Veja no script abaixo:

select OBJECT_NAME(referencing_id) ObjAnalisado, referenced_entity_name, referenced_id
from sys.sql_expression_dependencies
where referencing_id = object_id('dbo.spu')

select * from sys.dm_sql_referenced_entities('dbo.spu','OBJECT')

select * from sys.dm_sql_referencing_entities('dbo.spu','OBJECT')

Até o próximo post,
Landry.

Diferença na ocupação do Transaction Log após término de Transação

Olá... Resolvi escrever outro post sobre Transaction Log devido a grande quantidade de dúvidas que recebo dos alunos, principalmente quanto ao crescimento deste arquivo. Vale a penas recordar os 3 artigos sobre a estrutura do Transaction Log no link http://sqlserver-brasil.blogspot.com/search/label/Transation%20Log.

Uma dúvida freqüente dos meus alunos e clientes é a diferença entre o tamanho alocado no Transaction Log durante uma transação e ao seu final. Vamos visualizar esta diferença utilizando o script abaixo:

Primeiro crie um banco de dados para teste chamado Fluminense com o Recovery Model FULL.

-- Criar pasta c:\TesteLog
USE master
go

CREATE DATABASE Fluminense ON
(NAME = 'Fluminense',
FILENAME = 'C:\TesteLog\Fluminense.mdf',
SIZE = 512MB,FILEGROWTH = 100MB)
LOG ON
(NAME = 'Fluminense_log',
FILENAME = 'C:\TesteLog\Fluminense_log.ldf',
SIZE = 1MB,FILEGROWTH = 1MB)
go

ALTER DATABASE Fluminense SET RECOVERY FULL

Agora vamos verificar a ocupação do Transaction Log utilizando o comando abaixo:

DBCC SQLPERF (LOGSPACE)
-- Resultado:
-- Log Size (MB): 0.9921875
-- Log Space Used (%): 34.69488

Veja que o Transaction Log está com 34% de ocupação, o que corresponde a 0,34 MB. Agora vamos gerar atividade neste banco para observarmos a escrita no Transaction Log, rode o script abaixo:

USE Fluminense
go
CREATE TABLE Teste (c1 INT IDENTITY,
C2 CHAR (8000) DEFAULT (REPLICATE ('F', 8000)));
go

BEGIN TRAN
DECLARE @i int
set @i = 0
WHILE (@i < 50500)
BEGIN
INSERT INTO Teste DEFAULT VALUES
SELECT @i = @i + 1
END

Execute o DBCC SQLPERF (LOGSPACE) para verificar o crescimento:

DBCC SQLPERF (LOGSPACE)
-- Resultado:
-- Log Size (MB): 555,9922
-- Log Space Used (%):99,89989

O Transaction Log passou de 0,34 MB para 555,43 MB. Agora vamos executar um ROLLBACK e verificar a ocupação do Transaction Log novamente:

ROLLBACK TRAN;
GO

DBCC SQLPERF (LOGSPACE)
-- Resultado:
-- Log Size (MB): 555,9922
-- Log Space Used (%):99,89989

O Transaction Log passou a ocupar 473,42 MB, liberando 82,01 MB de espaço interno! Que comportamento estranho... Porque o ROLLBACK liberou espaço?

Bem, a resposta é simples... Enquanto uma transação está em curso o SQL Server já aloca espaço no Transaction Log para realizar a operação de RollBack, sendo liberado este espaço ao final da transação. Se não fosse reservado este espaço no Transaction Log, ao esgotar o espaço em disco durante uma transação, o banco de dados entraria em estado SUSPECT, devido a inviabilidade de realizar o RollBack com conseqüente perda de integridade.

Até o próximo post,
Landry.

Recuperando Banco de Dados no SQL Server 2005 e 2008

Desculpem o silêncio neste final de 2008.... após as férias de Dezembro, fiquei atolado de trabalho nestas duas semanas iniciais de Janeiro! Vamos iniciar 2009 com um artigo sobre recuperação de banco de dados...

Os scripts deste artigo funcionam tanto no SQL Server 2005 quanto no SQL Server 2008!

Primeiro vamos criar um banco de dados para teste, o script abaixo cria o banco TesteDB contendo uma tabela de Clientes:
USE master
go
CREATE DATABASE TesteDB
go

USE TesteDB
go
CREATE TABLE Clientes (
ClientesID int not null,
Nome varchar(40) not null,
Telefone varchar(20) null)
go

INSERT Clientes VALUES (1,'Jose','(21) 2521-2252')
INSERT Clientes VALUES (2,'Maria','(21) 2521-1151')
go

Agora para corromper o banco TesteDB, basta simular uma queda de energia:

1) Na mesma conexão utilizada no script anterior, execute o código abaixo e mantenha a conexão aberta:
-- Abre a transacao e atualiza tabela
BEGIN TRAN
UPDATE Clientes SET Telefone = '(21) 2521-1151'
WHERE ClientesID = 2
go
CHECKPOINT
go

2) Abra uma nova conexão e execute o comando “SHUTDOWN WITH NOWAIT”.

O SQL Server para o serviço com o arquivo de dados atualizado pela transação que ficou em aberto, mantendo no arquivo de log o registro desta transação incompleta. Em uma situação normal, O SQL Server resolveria o problema ao iniciar, executando um ROLLBACK da transação que ficou em aberto, utilizando as informações do arquivo de log.

Para corromper o arquivo de log basta abri-lo no NOTEPAD, apagar alguns caracteres no seu início e salvar. Após corromper o arquivo de log inicie o SQL Server e tente entrar no banco TesteDB, teremos a mensagem de erro abaixo:

USE TesteDB

Resultado:
Msg 945, Level 14, State 2, Line 1
Database 'TesteDB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Se você pesquisar no log do serviço (Management Studio – Object Explorer – Management – SQl Server Logs) veremos as mensagens abaixo:

- File activation failure. The physical file name "F:\sql2005\TesteDB_log.LDF" may be incorrect.
- The header for file 'F:\sql2005\TesteDB_log.LDF' is not a valid database file header. The PageAudit property is incorrect.

Para verificar o status do banco TesteDB:
SELECT databasepropertyex ('TesteDB', 'STATUS');
Resultado: SUSPECT

Vamos tentar resolver o problema com Detach e depois Attach:

EXEC sp_detach_db 'TesteDB'

Resultado:
Msg 947, Level 16, State 1, Line 1 Error while closing database 'TesteDB'. Check for previous additional errors and retry the operation.

O Detach ocorreu, apesar do erro alertando o estado inconsistente do banco de dados. Verifique com o comando abaixo:
SELECT * FROM sys.databases WHERE NAME = 'TesteDB'

Se você tentar realizar um Attach utilizando sp_attach_db a operação irá falhar, pois o arquivo de log foi corrompido.

EXEC sp_attach_db @dbname = 'TesteDB',
@filename1 = 'F:\sql2005\TesteDB.mdf',
@filename2 = 'F:\sql2005\TesteDB_log.LDF'

Resultado:
Msg 5172, Level 16, State 15, Line 1 The header for file 'F:\sql2005\TesteDB_log.LDF' is not a valid database file header. The FILE SIZE property is incorrect.

Outra opção, que também não irá funcionar, seria tentar criar um novo arquivo de log utilizando a opção ATTACH_REBUILD_LOG do comando CREATE DATABASE.

CREATE DATABASE TesteDB ON
(NAME = TesteDB, FILENAME = 'F:\sql2005\TesteDB.mdf')
FOR ATTACH_REBUILD_LOG

Resultado:
File activation failure. The physical file name "F:\sql2005\TesteDB_log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.

Msg 1813, Level 16, State 2, Line 1 Could not open new database 'TesteDB'. CREATE DATABASE is aborted.

Para resolver o problema, basta seguir os passos abaixo:

1) Mover (copiar para outra pasta e apagar) os arquivos de dados e log do banco TesteDB para outro local.

2) Criar um novo banco com o mesmo nome (TesteDB) e parar o serviço do SQL Server.
CREATE DATABASE TesteDB
SHUTDOWN

3) Apagar o arquivo de log e copiar o arquivo de dados que você salvou no item 1, por cima do arquivo novo criado no item 2 acima.

4) Executar o script abaixo.
ALTER DATABASE TesteDB SET EMERGENCY
ALTER DATABASE TesteDB SET SINGLE_USER
go
DBCC CHECKDB (TesteDB, REPAIR_ALLOW_DATA_LOSS)
WITH NO_INFOMSGS, ALL_ERRORMSGS
go
ALTER DATABASE TesteDB SET read_write
ALTER DATABASE TesteDB SET multi_user
go

Repare que foi gerado um novo arquivo de log e o banco de dados foi recuperado, verifique rodando o script abaixo:
USE TesteDB
go
select * from TesteDB.dbo.Clientes

Até o próximo post e feliz 2009,
Landry.