Thursday, August 25, 2011

Error trying to REORGANIZE/ REBUILD clustered index on CDC Enabled table

Recently I was trying to REORGANIZE a Change Data Capture enabled table when I received the following error:


Msg 22983, Level 16, State 1, Procedure sp_cdc_ddl_event_internal, Line 77
The unique index 'MyTable_PK' on source table 'MyTable' is used by Change Data Capture. To alter or drop the index, you must first disable Change Data Capture on the table.
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.

This table is very huge - more than 500 million rows. So the reorganize took a very long time - almost 19 hours. I was constantly monitoring the progress of the operation. And just when it was about 98% complete, I got this error. The frustrating part was that I got this error after 19 hours of running this command.


This was on SQL Server 2008 SP1 RTM. The error itself is a bug that was fixed in CU6 for SP1. So we either had to update to CU6 or look for a workaround. The workaround was to either use DBCC REINDEX or to use ALTER INDEX ALL on MyTable REORGANIZE

Obviously updating a Production SQL machine had to be planned for and was not going to happen immediately. And DBCC DBREINDEX would take the table offline - so that was also a no go. So the only option left was ALTER INDEX ALL. This was such a waste of resources as I had recently reorganized all the other indexes on the table. But since it was the only feasible option, I had to use it.

NB: The error does not appear when reorganizing a non-clustered index. Or may it DOES appear when reorganizing a non-clustered index on the unique key that is defined in CDC.

Bug Reference: http://support.microsoft.com/kb/977076/

Update: After the reorganize errored out, I checked the fragmentation on the index and found that REORGANIZE did do its magic. Thats logical since REORGANIZE does not roll back the work it has already done. And as I have mentioned the error happened at the end of reorganize. So most of the reorganizing was already complete by then.


Tuesday, August 16, 2011

Faster backups, restores & data file growths in SQL Server

I recently attended SQLSkills.com Performance Tuning SQL Server. One of the things we learnt about was configuration options that can affect performance. One such option is "Instant File Initialization". Instant File Initialization was introduced in SQL Server 2005. This feature allows file allocation requests to skip zero initialization. By default when new files are created for a certain size (say 1GB), SQL server will write zeros to the entire 1GB file and thereby "remove" any data still persistent on that 1GB space. This can take a huge amount of time when the file sizes are big. By enabling "Instant File Initialization", SQL Server skips the process of writing zeros in this space.

Check if this is enabled: Just one of the ways is to enable trace flag 3004 & 3605 and then create a new database. Once done, look at the SQL server log files:


Run following in SQL Mgmt Studio:
DBCC TRACEON (3004 );
DBCC TRACEON (3605 );


CREATE DATABASE testDB_IFI
ON
( NAME = testDB_IFI_dat,
    FILENAME = 'F:\data\testDB_IFI.mdf',
    SIZE = 10GB,
    MAXSIZE = 50GB,
    FILEGROWTH = 5GB )
LOG ON
( NAME = testDB_IFI_log,
    FILENAME = 'F:\data\testDB_IFI.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB )

DBCC TRACEOFF (3004 );
DBCC TRACEOFF (3605 );


Now look at the SQL Server log file. If you see foll. entries it would mean Instant File Initialization is NOT enabled:
Zeroing F:\data\testDB_IFI.mdf from page 0 to 1310720 (0x0 to 0x280000000)
Zeroing completed on F:\data\testDB_IFI.mdf

Conversely, if you DONT see these entries it means Instant File Initialization is enabled.

NB: Log files CANNOT be instant initialized. So you WILL see foll. entries irrespective of whether you have this enabled or disabled:
Zeroing F:\data\testDB_IFI.ldf from page 0 to 640 (0x0 to 0x500000)
Zeroing completed on F:\data\testDB_IFI.ldf

Enabling Instant File Initialization:
This is essentially a Windows feature and you can enable this feature in SQL Server by giving the user account under which SQL Server is running permission to access this Windows feature. The privilege to give is "Perform Volume Maintenance Tasks".

Resources:
Paul Randall's blog: How to tell if you have instant file initialization enabled and Follow-up post.
Kimberly Tripp's blog: "Instant Initialization: What, Why and How?"
Paul's Misconceptions around file initialization post