SQL Server & database/log files; Tracing Restore Operations

Este post é uma compilação de vários tópicos referentes a arquivos de dados ou de log que todo DBA deve saber.

 

Do you have Instant File Initialization?

Tibor Karaszi
http://sqlblog.com/blogs/tibor_karaszi/archive/2009/03/09/do-you-have-instant-file-initialization.aspx

Instant File Initialization ("IFI") permite a alocação de espaço de forma muito rápida. Porém, o SQL Server aloca espaço para arquivos ao efetuar as operações de :

  • Create Database
  • Alter Database Add File
  • Restore
  • Crescimento de arquivo (manual ou automático)
  • Backup

No SQL Server 2005 ou posterior, a criação ou alocação de espaço para arquivos de dados (datafiles) pode ser instantânea se a conta de serviço tiver grant no privilégio SE_MANAGE_VOLUME_NAME (administradores têm). Veja o post original para ver comparações (preview: 100 vezes mais rápido).

 

Trace de Restore de Backup

Microsoft Customer Service and Support (CSS) SQL Support
http://blogs.msdn.com/b/psssql/archive/2008/01/23/how-it-works-what-is-restore-backup-doing.aspx

O SQL Server tem uma flag de trace que pode ser utilizada para obter mais informações sobre as operações de backup e restore: 3004.

Para exibir as mensagens no log, deve ser habilitada também a flag 3605:

DBCC TRACEON ( 3004, 3605, –1 )

Outro blog sobre esta informação:

http://blogs.msdn.com/b/sqlserverfaq/archive/2011/01/07/case-study-troubleshooting-a-slow-log-shipping-restore-job.aspx

Snippet from SQL Errorlog
2010-12-29 16:11:19.10 spid64      RestoreLog: Database TESTDB 
2010-12-29 16:11:19.10 spid64      X-locking database: TESTDB 
2010-12-29 16:11:19.10 spid64      Opening backup set 
2010-12-29 16:11:19.10 spid64      Restore: Configuration section loaded 
2010-12-29 16:11:19.10 spid64      Restore: Backup set is open 
2010-12-29 16:11:19.10 spid64      Restore: Planning begins 
2010-12-29 16:11:19.12 spid64      Dismounting FullText catalogs 
2010-12-29 16:11:19.12 spid64      Restore: Planning complete 
2010-12-29 16:11:19.12 spid64      Restore: BeginRestore (offline) on TESTDB 
2010-12-29 16:11:19.12 spid64      Restore: Undoing STANDBY for TESTDB 
2010-12-29 16:11:23.46 spid64      SnipEndOfLog from LSN: (296258:29680:1) 
2010-12-29 16:11:23.46 spid64      Zeroing D:\SQL\SQLLog\TESTDB.ldf from page 2492695 to 2492738 (0x4c122e000 to 0x4c1284000) 
2010-12-29 16:11:23.46 spid64      Zeroing completed on D:\SQL\SQLLog\TESTDB.ldf 
2010-12-29 16:11:23.46 spid64      Restore: Finished undoing STANDBY for TESTDB 
2010-12-29 16:11:23.51 spid64      Restore: PreparingContainers 
2010-12-29 16:11:23.51 spid64      Restore: Containers are ready 
2010-12-29 16:11:23.51 spid64      Restore: Restoring backup set 
2010-12-29 16:11:23.51 spid64      Restore: Transferring data to TESTDB 
2010-12-29 16:11:23.51 spid64      Restore: Waiting for log zero on TESTDB 
2010-12-29 16:11:23.51 spid64      Restore: LogZero complete 
2010-12-29 16:11:24.24 spid64      FileHandleCache: 0 files opened. CacheSize: 10 
2010-12-29 16:11:24.24 spid64      Restore: Data transfer complete on TESTDB 
2010-12-29 16:11:24.24 spid64      Restore: Backup set restored 
2010-12-29 16:11:24.24 spid64      Restore-Redo begins on database TESTDB 
2010-12-29 16:11:25.69 spid64      Rollforward complete on database TESTDB 
2010-12-29 16:11:25.69 spid64      Restore: Done with fixups 
2010-12-29 16:11:25.74 spid64      Transitioning to STANDBY 
2010-12-29 16:11:26.74 spid64      Starting up database 'TESTDB'. 
2010-12-29 16:11:26.76 spid64      The database 'TESTDB' is marked RESTORING and is in a state that does not allow recovery to be run. 
2010-12-29 16:11:27.63 spid64      FixupLogTail() zeroing S:\SQLServer\SQLLog\TESTDB.ldf from 0x4c1769400 to 0x4c176a000. 
2010-12-29 16:11:27.63 spid64      Zeroing D:\SQL\SQLLog\TESTDB.ldf from page 2493365 to 2493425 (0x4c176a000 to 0x4c17e2000) 
2010-12-29 16:11:27.65 spid64      Zeroing completed on D:\SQL\SQLLog\TESTDB.ldf 
2010-12-29 16:24:30.55 spid64      Recovery is writing a checkpoint in database 'TESTDB' (5). This is an informational message only. No user action is required. 
2010-12-29 16:24:35.43 spid64      Starting up database 'TESTDB'. 
2010-12-29 16:24:39.10 spid64      CHECKDB for database 'TESTDB' finished without errors on 2010-12-21 23:31:25.493 (local time). This is an informational message only; no user action is required. 
2010-12-29 16:24:39.10 spid64      Database is in STANDBY 
2010-12-29 16:24:39.10 spid64      Restore: Writing history records 
2010-12-29 16:24:39.10 Backup      Log was restored. Database: TESTDB, creation date(time): 2008/01/26(09:32:02), first LSN: 296258:29680:1, last LSN: 298258:40394:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'S:\SQL\SQLLogShip\TESTDB\TESTDB_20101229011500.trn'}). This is an informational message. No user action is required. 
2010-12-29 16:24:39.12 spid64      Writing backup history records 
2010-12-29 16:24:39.21 spid64      Restore: Done with MSDB maintenance 
2010-12-29 16:24:39.21 spid64      RestoreLog: Finished 
Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s