Find the Cause of Transaction Log Growth Using Sys.Databases

There could be several reasons which leads to excessive growth of the transaction log,
Some of them may be as follows:

•   operating a database in FULL recovery model, without taking log backups
•   performing index maintenance like index rebuild and index reorganize
•   long-running or uncommitted transactions that prevent space in the transaction log from being reused.

We can investigate the cause of same by looking at the value of column log_reuse_wait_desc from sys.databases.

 We can simply run the below code:

SELECT name , recovery_model_desc , log_reuse_wait_desc FROM sys.databases WHERE name = 'Database_name'

Database_name= Please enter the name of your database.

The value of the log_reuse_wait_desc column will show the current reason why log space cannot be reused.

It may be possible that more than one thing is preventing log reuse. The sys.databases view will only show one of the reasons. It is therefore possible to resolve one problem, query sys.databases again and see the value of log_reuse_wait_desc for different reason.

The reasons could be as follows:

NOTHING :It means that currently there is no problem with transaction Log.
CHECKPOINT :his value means that a checkpoint hasn't occurred since the last time log truncation occurred.  
LOG_BACKUP 
ACTIVE_BACKUP_OR_RESTORE 
ACTIVE_TRANSACTION 
DATABASE_MIRRORING 
REPLICATION 
DATABASE_SNAPSHOT_CREATION 
LOG_SCAN 
AVAILABILITY_REPLICA 

Please refer MSDN site for detailed information for above reasons:

http://msdn.microsoft.com/en-in/library/ms178534.aspx

NOLOCK and READPAST Table Hint in SQL Server

Table Hint:

We use Table Hints to override the default behaviour of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method.

Table hints are specified in the FROM clause of the DML statement and affect only that particular table.

Syntax: Select column_name1,column_name2..,column_namen from Table_name with(Table_Hint)

In this blog post will talk only about NOLOCK and READPAST. 

NOLOCK:

This table hint NOLOCK is also known as READUNCOMMITTED, is applicable to SELECT statements only. NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.

This increases concurrency and performance of the transaction because the database engine does not have to maintain the shared locks involved. The problem with NOLOCK hint is DIRTY READ, because the statement does not issue any locks against the tables being read, some "dirty," uncommitted data could potentially be read.

A "dirty" read is one in which the data being read is involved in a transaction from another connection. If that transaction rolls back its work, the data read from the connection using NOLOCK will have read uncommitted data. This type of read makes processing inconsistent and can lead to problems. 

READPAST

READPAST is less popular than NOLOCK. After specifying this hint into the query Database Engine do not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped. That is, the Database Engine skips past the rows instead of blocking the current transaction until the locks are released.

The advantage of READPAST is that dirty reads will not happen because the hint will not return locked records. The problem with this is that you will not get all your records, because locked rows will be skipped.

Let see the same with a sample database that is “DEMO”.

USE master

GO

CREATE DATABASE DEMO

GO

USE DEMO

GO

CREATE TABLE NUMBER(ID INT, NAME CHAR(3))

GO

INSERT INTO NUMBER VALUES(1,'XXX'),

(2,'YYY'),

(3,'ZZZ'),

(4,'AAA'),

(5,'BBB')

 GO

SELECT * FROM NUMBER;

The following transaction will show how NOLOCK and READPAST behaves.  In the script below, I begin a transaction and updated a record in the NUBER table as below:

BEGIN TRAN

UPDATE NUMBER SET NAME='TIM' WHERE ID =1

Now open another session and execute the below script and see the OUTPUT for same:

SELECT * FROM NUMBER WITH(NOLOCK);

From the output you can see that the name for ID is TIM. The data for ID 1 is inconsistent. This is DIRTY READ. What if the update transaction is roll backed, well in that case the select statement read the data which never existed in database.

Now lets see that how READPAST will behave in this situation:

SELECT * FROM NUMBER WITH(READPAST);

From the output you can see that the record  for ID 1 is missing. As this row is locked by some other transaction. So READPAST table hint skipped that row.

Rollback the above update transaction. Now, Clean the database "DEMO" as:

Use master

Go

Drop Database Demo

Go

Thanks For Reading this Post!!!

How to Recover a database that is in the "restoring" state!!!

Suppose you are restoring a database using multiple backup files, you would use the WITH NORECOVERY option for each backup file except the last one. If you will go through the above steps your database will be recovered and will be usable.

But suppose in last backup file you have used WITH NORECOVERY option instead of WITH RECOVERY. In this situation your database will go into restoring state as below:

Now let see how to recover it without restoring additional backups. To do the same you can execute below command to bring the database online.

RESTORE DATABASE <Database_Name> WITH RECOVERY 

As per screen shot the database name is "Recover" so you will have to execute command as below:

RESTORE DATABASE Recover WITH RECOVERY 

After this your database will be available for use.

Thanks For Reading this Post!!!