Are You Using Template Explorer

This is an hidden feature for many DBAs and developers. See what you can do with Template Explorer in my latest blog at http://beyondrelational.com/blogs/dinesh_asanka/archive/2010/04/01/are-you-using-template-explorer.aspx

Light Weight Monitoring using Extended Events

This is my latest article on Extended Events in SQL Server 2008. This is the latest monitoring mechanism in SQL Server 2008.

Re-indexing/ Reorganizing a Table after Enabling CDC

In SQL Server 2008 there is a new feature named CDC where you can capture data changes. Sorry this blog entry is not going to discuss about how to work with CDC instead this is an issue which I faced after enabling CDC.

I enabled CDC for a table called CDC with following syntax.

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name   = N'data',

@role_name     = N'dbo',

@supports_net_changes = 1

GO

Then I performed a re-organize for that table.

USE [COB]

GO

ALTER INDEX [PK_Data] ON [dbo].[Data] REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

Then it returned the following error.

Msg 22983, Level 16, State 1, Procedure sp_cdc_ddl_event_internal, Line 77

The unique index 'PK_Data' on source table '[dbo].[Data]' 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 1

The transaction ended in the trigger. The batch has been aborted.

Same error will appear if I do a re-indexing.

This bug is fixed with Cumulative update package 6 for SQL Server 2008 Service Pack 1

Depreciated Features in SQL Server 2008

With every versions of SQL Server, there are new features added. With new features, there are discounted and depreciated features.

Most of the time we are not sure whether our applications are using discounted or depreciated version. Discounted versions anyway will fail, but depreciated features will work.

To identify depreciated features, now there is a new perfmon counter called, SQLServer:Depreciated Features.

This was my output, simply after adding these counters.

Above image shows SQL Server instance has 4 databases with compatibility level 90 and 2 databases with compatibility level 80. (Scale is x10). In SQL Server 2008, compatibility level 70 is discontinued while 80 and 90 compatibility levels are depreciated.

Following query has two depreciated features.

CREATE Table #

(

ID INT,

NAME TEXT,

LANGUAGE NTEXT

)

CREATE Table ##

(

ID INT,

NAME TEXT,

LANGUAGE NTEXT

)

DROP TABLE #

DROP TABLE ##

This will generate few more counters as you can see below.

This count is an aggregated counter and it will be reset to zero when the service is restated.

Functionality Change in REPLACE Function in SQL Server 2008

What is the output for the following query?

DECLARE @V CHAR(6)

SET @V = 'ABC'

SELECT REPLACE(@V,' ','L')

Guess what you have two answers. In SQL Server 2005, it will be ABC while in SQL Server 2008 it is ABCLLL.

What does this mean? They have fixed the bug in REPLACE function in SQL Server 2008. In SQL Server 2005, for char data type, before replacing it trims the variable which is incorrect. In SQL Server 2008 now it is not trimming your data before the replacing function.

If you are in the process of upgrading to SQL Server 2008 this is an point to consider.

Strange Error after DDL Trigger

I created a DDL trigger on my server (SQL Server 2008 with SP1) to pick the database name which is listed below.

CREATE TRIGGER [ddlsvrtrg_create_database_backup_paths] ON ALL SERVER

FOR CREATE_DATABASE

AS

SET NOCOUNT ON

DECLARE @xmlEventData XML,

@database_name VARCHAR(50),@path varchar(500),@fullPath varchar(500),@diffpath varchar(500)

SET @xmlEventData = eventdata()

SELECT @database_name = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(150)')

.

.

.

After creating this trigger, I tried to create a database from the UI and you will get the following error.

TITLE: Microsoft SQL Server Management Studio

------------------------------

Create failed for Database 'DB_DDL'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1045+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. (Microsoft SQL Server, Error: 1934)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=1934&LinkId=20476

------------------------------

BUTTONS:

OK

However, if I create a database from a script it will work. Also, I generated the script from the create database UI. That is still working.

Also, this issue does not exist in the SQL Server 2005 with SP3.

DDL Trigger on Database Create

I created a DDL trigger on SQL Server 2008 with SP1. Trigger is firing well for Create Database statement. However, it does not fire for the instance where you restore a database to a new database. Since, there is no DDL trigger event for Database Restore there are no other alternative.

To capture the database restore event, there was a nice suggestion by a forum member in SQL Server Central to create a DML trigger on [msdb].[dbo].[restorehistory]

SSMS Customize Shortcuts

Do you know that you can customize short cuts in SQL Server Management Studio. This is a small article on www.beyondrelational.com. Read this article SSMS Customize Shortcuts

Not another Comparison Tools for SQL Server

There are several tools for SQL Server schema comparisons. This is a new tool called dbForge.

See the latest review on this http://www.sql-server-performance.com/software/review/dbforge_review_2010_p1.aspx