Archive for August, 2010

Transaction Log File (Full) Issues

August 28, 2010 Leave a comment

While troubleshooting the replication issue I came across the very common error

–Msg 9002, Level 17, State 4, Line 1

–The transaction log for database ” is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

I did the same as suggested by the error, checking the sys.database for the reason why the error log is full.

select log_reuse_wait ,log_reuse_wait_desc  from sys.databases where name =’Databases Name’

As a first step of troubleshooting I checked the logfile configuration to see if log file size is ristricted ,Auto Growth is enabled (is auto growth in percent or MB) and the initial size of the log file.

The Log file initial size is set to 3 MB (Default) and as we can see the log file is enabled for autogrowth with Ristricted file growth of 10 MB.

So, the maximum log file can grow upto 10 MB.

The question come’s in one’s mind is “How can we check the used percentage of the log file?”

To check the Log file used Percentage

DBCC SQLPERF(logspace)

Database Name Log Size (MB) Log Space Used (%) Status
Xzcvxz 9.929688 99.76396 0


As u can see the logfile has been used till the last extend and there is no more space to grow.

Now the question is how to determine SQL Server database transaction log usage?

To determine the Log Usage there is an undocumented command

(DBCC LOGINFO(‘Database name’)

This will give you information about your virtual logs inside your transaction log.  The primary thing to look at here is the Status column.  Since this file is written sequentially and then looped back to the beginning, you want to take a look at where the value of “2” is in the output.  This will tell you what portions of the log are in use (2) and which are not in use Status = 0.  Another thing to keep an eye on is the FSeqNo column. This is the virtual log sequence number and the latest is the last log.  If you keep running this command as you are issuing transactions you will see these numbers keep changing.

FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
2 253952 8192 2275 2 128 0
2 253952 262144 2276 2 128 0
2 253952 516096 2273 2 64 0
2 278528 770048 2274 2 64 0
2 262144 1048576 2277 2 64 1571000000037600000
2 262144 1310720 2278 2 64 1572000000050200000
2 262144 1572864 2279 2 64 1573000000040000000
2 262144 1835008 2280 2 64 1574000000050200000
2 262144 2097152 2281 2 64 1575000000050000000

Now as we know the issue however the confusion is how to resolve the same.

But wait

(1)What if there are transaction still running against the database.

To check the same run

DBCC Opentran() against the database.

Transaction information for database ‘xzcvxz’.

Oldest active transaction:

    SPID (server process ID): 57

    UID (user ID) : -1

    Name          : SELECT INTO

    LSN           : (3395:100:1)

    Start time    : Aug 29 2010  2:35:32:837AM

    SID           : 0x01

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If there are still open transaction againt the database or if the database is setup for replication or CDC and the transaction are still not marked as replicated in the transaction log, the logfile can’t be shrinked.


To resolve the same I go around multiliple ways will explain few workaround here.

1.If database is not setup for Logshipping,Replication,Mirroring and CDC.

1.  set the database into simple recovery mode

2.  Set the database back to full recovery mode.

3.  Run the shrinkfile command.





DBCC SHRINKFILE (2,truncate_only)

(Mirosoft do not recommend to use truncate statement in production)


2.In case the database is setup for Replication (any topology) and transaction log file is growing tremendously and soon will be full. Transaction Log file can’t be shrinked as there are transaction which are not marked as replicated in the transaction log file.


If transaction log is full and transaction written on the log file are not marked as replicated the logreader agent will keep trying to scan the log and mark the transaction as replicated but it will not succeed as there is no space to write transaction in the transaction (Mark the transaction as replicated).As the transaction are not marked as replicated DBA’s cant shrink the Log file.

To resolve the issue we can execute the below steps.

1.  Lets say the trasaction log file is on drive D:\ of size 200 GB.

2.  The transaction log file has grown by size 200 GB.

3.  Create a new log file to some other drive.Let’s say on drive F:\ at the publisher database.

USE [master]


ALTER DATABASE [xzcvxz] ADD LOG FILE ( NAME = N’xyz_log’, FILENAME = N’F:\xyz_01.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)


4.  As new log file is added to the database, SQL Server will start using it.the new transaction will be written to the newly added transaction log files.The Log reader agent will start scaning the old log file and will mark the transaction as distributed once sp_repcmd will replicate the commands or transaction to the distributor database.

5.  Alter the Database in simple recovery mode.


 6.  Alter the Database again in full recovery mode. 


7.  Now Shrink the Log file.

USE [xzcvxz]



 Will write about how to shrink log file in Mirroring and Log shipping in my next article.

Temp Database Issues Resolution (Move Tempdb)

August 25, 2010 Leave a comment


SQL Server Services is not starting because temp database can’t grow further as it reached to its maximum size (All Disk Space is utilized). We can’t delete the temp database file from the disk because of disk issues.

What can be done to resolve the issue?


To resolve the issue DAC should be enabled.

What basically we will do is moving the temp database on to some other drive.

Steps to do

Start SQL Server Service with minimal configuration


Start the command prompt with Administrative Priviliges

D:\Program Files\Microsoft SQL Server\MSSQL10.DELL\MSSQL\Binn>sqlservr.exe -sInstancename -f

In case instancename is default  then u don’t provide the instance name

D:\Program Files\Microsoft SQL Server\MSSQL10.DELL\MSSQL\Binn>sqlservr.exe -f

Start another instance of command prompt with administrative priviliges.


sqlcmd -A -dmaster -E -SNL03-DF271\instance name

In case instancename is default  then u don’t provide the instance name

 sqlcmd -A -dmaster -E -SNL03-DF271\instance name

Move the temp database file to some other location

USE master;
MODIFY FILE (NAME = tempdev, FILENAME = ‘E:\SQLData\tempdb.mdf’);
MODIFY FILE (NAME = templog, FILENAME = ‘F:\SQLLog\templog.ldf’);

Once file get moved;Restart the SQL Server Services

Press Ctrl+C on the command Prompt in the first step

Restart the SQL Server Service.

Setup CDC (Change Data Capture)

August 20, 2010 Leave a comment

Here we will go over how to setup CDC on an exiting or new database in SQL Server 2008

How to setup Change Data Capture (CDC)

Create a Database or User the exiting Database

1.  Enable the Database for CDC 

Use <Database name>
exec sys.sp_cdc_enable_db

  1. Check the database is enabled for CDC
     select name,is_cdc_enabled from sys.databases where name=’cdc’

3.  Enable Table for Change Data Capture

USE <DatabaseName>;


EXECUTE sys.sp_cdc_enable_table

    @source_schema = N’dbo’

  , @source_name = N’Dep_Master’

  , @role_name = N’cdc_Tables’

  , @capture_instance=’cdc_dbo_DepMaster’;

4.  Get Objects list which are enabled for CDC

 exec sys.sp_cdc_help_change_data_capture

  1. Check minimum LSN for the CDC Table

Use <Database Name>

SELECT sys.fn_cdc_get_min_lsn (‘@Capture_instance’)AS min_lsn;

  1. Check Max LSN

7.  Use <Database Name>

SELECT  sys.fn_cdc_get_max_lsn()

8.  Check all LSN’s

SELECT * FROM cdc.lsn_time_mapping

9.  When u enable cdc for a table couple of things happens at the backgroud

1. creates @capture_instance Object

2. Creates two function as below. This will give u all the details



3. Creates two Jobs (Make sure SQL Server Agent Services are running)



10.  Get all the changes happened on the object enabled for CDC
Declare @begin_time datetime,
        @end_time   datetime,
        @from_lsn   binary(10),
        @to_lsn binary(10),

        @max_lsn binary(10);

SET @begin_time=GETDATE()-1;

SET @end_time=GETDATE();

SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn (‘smallest greater than or equal’,@begin_time)

SELECT @to_lsn=sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’,@end_time)

–SELECT @begin_time,@end_time,@from_lsn,@to_lsn

–Get all the changes happened on the object enabled for CDC

SELECT * FROM CDC.fn_cdc_get_all_changes_cdc_dbo_depmaster(@from_lsn,@to_lsn,’all’)

11.  Get all the net changes done on each row of the object enabled for CDC.

Returns one net change row for each source row changed within the specified LSN range. That is, when a source row has multiple changes during the LSN range, a single row that reflects the final content of the row is returned by the function. For example, if a transaction inserts a row in the source table and a subsequent transaction within the LSN range updates one or more columns in that row, the function returns only one row, which includes the updated column values.

Declare @begin_time datetime,
        @end_time   datetime,
        @from_lsn   binary(10),
        @to_lsn binary(10),

        @max_lsn binary(10);

SET @begin_time=GETDATE()-1;

SET @end_time=GETDATE();

SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn (‘smallest greater than or equal’,@begin_time)

SELECT @to_lsn=sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’,@end_time)

–SELECT @begin_time,@end_time,@from_lsn,@to_lsn

–Get all the changes happened on the object enabled for CDC

SELECT * FROM CDC.fn_cdc_get_all_changes_cdc_dbo_depmaster(@from_lsn,@to_lsn,’all’)

  1. To get Date and Time value for transaction sequence number (LSN).

declare @begin_time datetime,@end_time datetime,@from_lsn binary(10),@to_lsn binary(10)

DECLARE @max_lsn binary(10);

SET @begin_time=GETDATE()-1;

SET @end_time=GETDATE();

SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn (‘smallest greater than or equal’,@begin_time)

SELECT @to_lsn=sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’,@end_time)

SELECT @max_lsn = MAX(__$start_lsn)

FROM cdc.fn_cdc_get_all_changes_cdc_dbo_depmaster(@from_lsn, @to_lsn, ‘all’);

SELECT sys.fn_cdc_map_lsn_to_time(@max_lsn);

Will write about how to use SSIS (No thrid party control) to perform ETL from CDCenabled table to Staging or Reporting Database in my next blog.