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
Wednesday, March 31, 2010
Tuesday, March 30, 2010
Monday, March 29, 2010
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.
@source_schema = N'dbo',
@source_name = N'data',
@role_name = N'dbo',
@supports_net_changes = 1
Then I performed a re-organize for that table.
ALTER INDEX [PK_Data] ON [dbo].[Data] REORGANIZE WITH ( LOB_COMPACTION = ON )
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.
Sunday, March 28, 2010
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 #
CREATE Table ##
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.
Thursday, March 25, 2010
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.
Tuesday, March 23, 2010
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
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)', '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
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
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.
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]
Monday, March 8, 2010
Saturday, March 6, 2010
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