Monday, March 18, 2013

Spent 2 days for Disaster Recovery

I just spent 2 days working on updating the documentations for this year disaster recovery. It's fun stuff - writing and practicing recovery.

Friday, March 15, 2013

Type of backups in SQL Server

Goals:
Known all available type of backups in SQL Server. This info will be used later for our backup/restore process.

As far as I know, these are the available type of backup in SQL Server:
 > Full
 > Full with Copy-only option
 > Differential
 > Transaction Log
 > Transaction Log with Copy-only option
 > Partial
 > File
 > Filegroup

Note:
There's no incremental backup in SQL Server. Also, incremental and differential backup are not the same.
 
I'll cover each type of backup in later posts.

Credits:
http://msdn.microsoft.com/en-us/library/ms186865(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ms179401(v=sql.105).aspx
https://www.simple-talk.com/sql/backup-and-recovery/partial-backup-and-restore/
http://msdn.microsoft.com/en-us/library/ms191495.aspx

Recovery Models in SQL Server

Goal:
Know all available recovery models in SQL Server. This will build the foundation for our backup/restore skill.


There are 3 recovery models in SQL Server: simple, bulk logged, and full.

We'll cover each recovery model later.

Credits:
http://msdn.microsoft.com/en-us/library/ms189275.aspx

Monday, March 11, 2013

fn_dblog( ) function documentation

Goal:
This post provide some documentation for the fn_dblog() function.

Key Points:
    * view transaction log of a database (if you want to view the transaction log of a log backup, use fn_dump_dblog function instead.)
    * useful for:
          > restore database to a certain point in time
          > find out when an operation (page split, table delete, table truncate...).
   * take 2 parameters, start time and end time.
   * It's not recommended use this function to scan the transaction log of a production database because it can reduce performance due to increase disk I/O.

-- get fn_dblog() definition
sp_helptext 'sys.fn_dblog';

--result
 create function sys.fn_dblog  ( 
  @start   nvarchar (25) = NULL, 
  @end     nvarchar (25) = NULL 
 )    
returns table 
as 
 return select * 
 from OpenRowset (DBLog, @start, @end, NULL, 1, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL )

-- get table result definition.
sp_help 'sys.fn_dblog';
Result.
Column_name                Type           Length  Prec   Scale  Nullable
Current LSN nvarchar 46             no
Operation nvarchar 62             no
Context nvarchar 62             no
Transaction ID nvarchar 28             no
LogBlockGeneration bigint 8 19 0 no
Tag Bits binary 2             no
Log Record Fixed Length smallint 2 5 0 no
Log Record Length smallint 2 5 0 no
Previous LSN nvarchar 46             no
Flag Bits binary 2             no
Log Reserve int 4 10 0 no
AllocUnitId bigint 8 19 0 yes
AllocUnitName nvarchar 774             yes
Page ID nvarchar 28             yes
Slot ID int 4 10 0 yes
Previous Page LSN nvarchar 46             yes
PartitionId bigint 8 19 0 yes
RowFlags smallint 2 5 0 yes
Num Elements smallint 2 5 0 yes
Offset in Row smallint 2 5 0 yes
Modify Size smallint 2 5 0 yes
Checkpoint Begin nvarchar 48             yes
CHKPT Begin DB Version smallint 2 5 0 yes
Max XDESID nvarchar 28             yes
Num Transactions smallint 2 5 0 yes
Checkpoint End nvarchar 48             yes
CHKPT End DB Version smallint 2 5 0 yes
Minimum LSN nvarchar 46             yes
Dirty Pages int 4 10 0 yes
Oldest Replicated Begin LSN nvarchar 46             yes
Next Replicated End LSN nvarchar 46             yes
Last Distributed Backup End LSN nvarchar 46             yes
Last Distributed End LSN nvarchar 46             yes
Server UID int 4 10 0 yes
SPID int 4 10 0 yes
Beginlog Status binary 4             yes
Xact Type int 4 10 0 yes
Begin Time nvarchar 48             yes
Transaction Name nvarchar 66             yes
Transaction SID varbinary 85             yes
Xact ID bigint 8 19 0 yes
Xact Node ID int 4 10 0 yes
Xact Node Local ID int 4 10 0 yes
End Time nvarchar 48             yes
Transaction Begin nvarchar 46             yes
Replicated Records int 4 10 0 yes
Oldest Active LSN nvarchar 46             yes
Server Name nvarchar 258             yes
Database Name nvarchar 258             yes
Mark Name nvarchar 66             yes
Master XDESID nvarchar 28             yes
Master DBID int 4 10 0 yes
Preplog Begin LSN nvarchar 46             yes
Prepare Time nvarchar 48             yes
Virtual Clock bigint 8 19 0 yes
Previous Savepoint nvarchar 46             yes
Savepoint Name nvarchar 66             yes
Rowbits First Bit smallint 2 5 0 yes
Rowbits Bit Count smallint 2 5 0 yes
Rowbits Bit Value binary 1             yes
Number of Locks smallint 2 5 0 yes
Lock Information nvarchar 512             yes
LSN before writes nvarchar 46             yes
Pages Written smallint 2 5 0 yes
Data Pages Delta int 4 10 0 yes
Reserved Pages Delta int 4 10 0 yes
Used Pages Delta int 4 10 0 yes
Data Rows Delta bigint 8 19 0 yes
Command Type int 4 10 0 yes
Publication ID int 4 10 0 yes
Article ID int 4 10 0 yes
Partial Status int 4 10 0 yes
Command nvarchar 52             yes
Byte Offset smallint 2 5 0 yes
New Value binary 1             yes
Old Value binary 1             yes
New Split Page nvarchar 28             yes
Rows Deleted smallint 2 5 0 yes
Bytes Freed smallint 2 5 0 yes
CI Table Id int 4 10 0 yes
CI Index Id smallint 2 5 0 yes
NewAllocUnitId bigint 8 19 0 yes
FileGroup ID smallint 2 5 0 yes
Meta Status binary 4             yes
File Status binary 4             yes
File ID smallint 2 5 0 yes
Physical Name nvarchar 522             yes
Logical Name nvarchar 258             yes
Format LSN nvarchar 46             yes
RowsetId bigint 8 19 0 yes
TextPtr binary 16             yes
Column Offset int 4 10 0 yes
Flags int 4 10 0 yes
Text Size bigint 8 19 0 yes
Offset bigint 8 19 0 yes
Old Size bigint 8 19 0 yes
New Size bigint 8 19 0 yes
Description nvarchar 512             no
Bulk allocated extent count int 4 10 0 yes
Bulk RowsetId bigint 8 19 0 yes
Bulk AllocUnitId bigint 8 19 0 yes
Bulk allocation first IAM Page ID nvarchar 28             yes
Bulk allocated extent ids nvarchar 1922             yes
RowLog Contents 0 varbinary 8000             yes
RowLog Contents 1 varbinary 8000             yes
RowLog Contents 2 varbinary 8000             yes
RowLog Contents 3 varbinary 8000             yes
RowLog Contents 4 varbinary 8000             yes
Compression Log Type smallint 2 5 0 yes
Compression Info varbinary 8000             yes
PageFormat PageType smallint 2 5 0 yes
PageFormat PageFlags smallint 2 5 0 yes
PageFormat PageLevel smallint 2 5 0 yes
PageFormat PageStat smallint 2 5 0 yes
PageFormat FormatOption smallint 2 5 0 yes
Log Record varbinary 8000             no

As you can see there're  many columns the  fn_dblog() returns.
We won't likely need all of them.

--An example how to use sys.fn_dblog function
SELECT
  [Current LSN], [Previous LSN], Operation, Context,
  [Transaction ID], [Transaction Name], [Transaction SID],
  [Transaction Begin], [Begin Time], [End Time],
  [Log Record], [Minimum LSN], 
  [Checkpoint Begin], [Checkpoint End], Description,
  [Page ID], [Dirty Pages] AS dirty_page_number,
  [New Split Page], [Pages Written], [Used Pages Delta],
  [Lock Information], [Number of Locks]
FROM sys.fn_dblog(NULL, NULL);

Paul Randal has a blog post about fn_dblog() and provide some detailed example. You can read it here. 

Credits:
http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/#comment-7872
http://sqlblog.com/blogs/kalen_delaney/archive/2011/03/08/what-gets-logged-for-index-rebuilds.aspx
http://stevestedman.com/tag/fn_dblog

Friday, March 8, 2013

My coworker is leaving us today.

Today, I'm very sad because my wonderful coworker is leaving our company. We've been working together for almost two years on the ehr project. I don't know what I will do without her.

I will miss her a lot and wish the best of luck to her new journey.

Thursday, March 7, 2013

SQL Server Transaction Log

Goal: understand SQL Server Transaction Log architecture.

This topic is complex, and I'm no expert; that's why I've attached several links from book online and other experts.
Before going to details about backup and restore. We must understand the basic of the SQL Server Transaction Log architecture. For more details, please see Paul Randal's post here

Key Points:
* Every database has at least one transaction log (yes, including tempdb)
* Every record in a transaction log is identified by a LSN (Log Sequence Number). LSN is unique 10 bytes in size (4 bytes for VLF number + 4 bytes for block number + 2 bytes for log records number) , it's like a time stamp. Ex: 0000002e:0000027a:0001
* Realistically, it's not possible to use all LSN. Kimberly Tripp and Paul Randal provide an excellent example here. Based on the example, it would take 240 million years to generate 4 billion petabytes transaction log.
* Log records are the changes made to a database.
To view the log records, try these (they're not document on  BOL)
DBCC LOG('AdventureWorks',3);
USE AdventureWorks;
go
SELECT * FROM sys.fn_dblog(null,null); --see Paul Randal blog here
* Log records are kept in sequence order in the transaction log; thus there's no performance gain if creating multiple transaction log files per database.
* Each physical log file is divided into multiple uneven virtual log files (VLFs). Think this as a hard drive has multiple partitions.
* VLF states (Brad McGehee sums it up nicely here)
    > Unused: no log record is written to the VLF, and VLF is zeroed out
    > Active: when 1 or more active log record exists in the VLF.
    >  Recoverable: No active log record exists but it's not available for SQL Server to use.VLF is not truncated.
    > Reusable: No active log record exists in the VLF and the VLF is truncated.
* Transaction log can include these: only one logical log, one or more truncated VLF(s), and one or more unused VLF(s).
* Transaction logs are circular. Think this as a race track. This means when logical log reaches end of the physical log file, the new log records are written to the beginning of the physical file. (read more here)
* Transaction logs are truncated after a log backup. Log truncation is not the same as log shrink. After a log truncation the size of the physical log file doesn't get any smaller.
* To reduce the physical log file size, shrink the log by use DBCC SHRINKFILE(). Ex: DBCC SHRINKFILE (AdventureWorks_Tlog, 10); -- Shrink a db's transaction log file to 10 Mb in size.
* Transaction Log truncation:
    Under full and bulk-logged recovery model, VLF is truncated if a checkpoint occurred since the previous backup and after a transaction log backup. Under simple recovery model, log truncation occurs after a checkpoint. (read more here)
* missing any?

Insights:
  1. SQL Server transaction log and database recovery model are linked together.
  2. Good practice to perform regular log backup or the transaction log file will grow very large.
  3. Log backup does not shrink the transaction log file. It's only deallocate the VLFs for later user.
  4. last but not least, understand SQL Server transaction log is one of the key to be an exception DBA. This topic is complex and fun. 

Friday, March 1, 2013

Backup & Restore Basics series

I'm not sure that you heard about the 10,000 hours rule. Basically, it would normally take 10,000 hours of practice to master a skill.

For database backup and restore, I don't know many hours are needed. Well it doesn't hurt to go back to the basics. : )

Thus, my next series of blogs is about "back and restore."