Track down DDL Database changes

If you want to know who alters/drops/creates tables/views/stored procedures... I would like to share with you the following script. As you know DDL Triggers (introduced in SQL Server 2005) work very like the DML triggers but details of the event that fired the a trigger are available only in XML format.

Fist of all I create a table that will hold events.

CREATE TABLE [dbo].[DDL_ChangeEvents](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Session_ID] [int] NOT NULL CONSTRAINT [DF_ddl_change_Session_ID] DEFAULT (@@spid),
[Session_IPAddress] [nvarchar](50) NULL,
[Insert_Date] [datetime] NOT NULL CONSTRAINT [DF_ddl_change_Insert_Date] DEFAULT (GETDATE()),
[Username] [nvarchar](100) NOT NULL CONSTRAINT [DF_DDL_change_Username] DEFAULT (CONVERT([nvarchar](100),ORIGINAL_LOGIN(),(0))),
[EventType] [nvarchar](200) NULL,
[objectName] [nvarchar](200) NULL,
[objectType] [nvarchar](200) NULL,
[sql] [nvarchar](max) NULL
) ON [PRIMARY]

It wont help if I get only SPID of the session as in many cases users get logged with only one defined login or even with 'sa'.So I need IP address of those workstations thus I added Session_IPAddress column.
Now, let's create a database trigger to capture the info.

CREATE TRIGGER [trgDataDDLChangeEvent] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @eventdata XML
SET @eventdata = EVENTDATA()
IF @eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(200)')
<> 'CREATE_STATISTICS'
INSERT INTO DDL_ChangeEvents
(
EventType,
ObjectName,
ObjectType,
[sql] ,
Session_IPAddress
)
SELECT @eventdata.value('(/EVENT_INSTANCE/EventType)[1]',
'nvarchar(200)'),
@eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]',
'nvarchar(200)'),
@eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]',
'nvarchar(200)'),
@eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]',
'nvarchar(max)'), client_net_address
FROM sys.dm_exec_connections WHERE session_id=@@SPID
;

Well I won't bother to record CREATE STATISTIC events hence there is an IF block to skip this event. I get the IP Address from sys.dm_exec_connections DMV which has client_net_address column.
Now create/drop/alter table (also via SSMS) for example and query the DDL_ChangeEvents table to see what happened.

There is a clever way of rebuild indexes

Hi everybody.
It seems like I am seeing more and more inquires from our clients asking for help solving performance related issues with rebuilding indexes. All of them (or almost all of them) have been using Maintanace Plan Rebuild/Reorganize Index Task. We have lots of clients who have pretty big databases(>200GB) and have not hired yeat a DBA:-).They used to use this task and specify all tables as well as all databases, moreover, one client used to run such tasks in the middle of work day. It leads to locks on tables and performance decreasing. I would also notice you to not cancelling the task as SQL Server will rolback the whole transactions and you are about to wait a lot of time. Just let the task to complete. I suggested instead of running the task, first, identify fragmented indexes on tables that have more than 1000 pages.

DECLARE @RebuildStatement nvarchar(4000)
DECLARE RebuildStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT 'ALTER INDEX '+i.name+ ' ON '+
OBJECT_NAME(i.object_id)+' REORGANIZE;'
FROM
sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'DETAILED') phystat inner JOIN sys.indexes i
ON i.object_id = phystat.object_id
AND i.index_id = phystat.index_id WHERE phystat.avg_fragmentation_in_percent > 40
and page_count>=1000

OPEN RebuildStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM RebuildStatements INTO @RebuildStatement
IF @@FETCH_STATUS <> 0 BREAK
EXEC(@RebuildStatement)
END
CLOSE RebuildStatements
DEALLOCATE RebuildStatements

The above SELECT generates a simple script to REORGANIZE (change to REBUILD) indexes and EXECUTES the dynamic sql. As you probaly know this script has to be run on SQL Server 2005/2008 and do not forget about really great feature such rebuilding indexes ONLINE. For more details please see BOL.

To speak kindly about RedGate

All of us are aware that restore database with different collation may cause a headache. That what happened to one of our databases where one developer created a database without to pay attention about what kind of data he is going to deal with. Ok,we can set COLLATION even per column , but what if you have lots of tables to be altered with new COLLATION. Well, we can use either import/export or SSIS package or perhaps write some T-SQL script to do the job , however I'd like to tell you how easy to get a new database with desired COLLATION by using RedGate tool.
1) CREATE DATABASE dbname COLLATE 'your desired collation'
2) Open RedGate (SQL Compare)tool to move the structure of source db to the destination db. That's all.

I takes a few minutes even we had 25GB database. I'd strongly recommend to have a look at this great tool.(www.red-gate.com)

Computed column is PERSISTED?

Hi everybody.
I'd like to share with you how important to define a computed column to be PESRISTED.
As you know from the BOL
/*
For columns specified as PERSISTED, the SQL Server 2005 Database Engine physically stores the computed values in the table and updates the values when any other columns on which the computed column depends are updated. By marking a computed column as PERSISTED, you can create indexes on computed columns defined on expressions that are deterministic, but not precise.
*/
I visited our client two days ago who has been experienced with performance issue for one of their very important query.One big table containes a computed column which SELECT statement is using for to return to the client. We have seen very high number of logical reads and TWO computer scalar iterators. For an obvious reason we define the computed column as PESRISTED and performance was increased dramatically.Moreover, create an index on computed column and see how perfromance will be increased more..


See demo script to see how it is affected.

CREATE TABLE t(c INT NOT NULL identity(1,1) PRIMARY KEY,
c1 AS '00'+cast(c AS VARCHAR(100)))

SET NOCOUNT ON
INSERT INTO t DEFAULT VALUES
GO 100000


SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT c1 FROM t
--Table 't'. Scan count 1, logical reads 1250, physical reads 0, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--select c1 from t
|--Compute Scalar(DEFINE:([master].[dbo].[t].[c1]=[master].[dbo].[t].[c1]))
|--Compute Scalar(DEFINE:([master].[dbo].[t].[c1]='00'+CONVERT(varchar(100),[master].[dbo].[t].[c],0)))
|--Clustered Index Scan(OBJECT:([master].[dbo].[t].[PK__t__1446FBA6]))



SET STATISTICS IO ON
SET STATISTICS PROFILE ON

SELECT c1 FROM t
--Table 't'. Scan count 0, logical reads 50, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--select c1 from t
|--Compute Scalar(DEFINE:([master].[dbo].[t].[c1]=[master].[dbo].[t].[c1]))
|--Clustered Index Scan(OBJECT:([master].[dbo].[t].[PK__t__1446FBA6]))

DROP TABLE t

String or binary data would be truncated

Hi folks.
I named this article with this pretty famous error message. I'm sure that everybody has seen this error at least once.This week I visited our client who asked me very intresting question. They have a table with many columns that most of them defined as VARCHAR(n) datatype. One of the query has been failing with below error.

"Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated."

Well ,we knew for sure that the user supplied a string which does not match with column datatype but real question is WHICH of so many columns? As I said, they have more than 50 columns and it was pretty difficult to identify on which column is failed.
My point is that it would be nice to have more information from this error message about which column is failing and I hope that MS will do something for the matter.

Cannot delete a job which is related to MP?

Hi folks
If you are using SQL Server 2005 (SP) and used to build Maintanace Plans especially with adding subplans so you are probable seen the folowing error message
/*
Drop failed for Job ‘jobname’. (Microsoft.SqlServer.Smo)

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’.
The statement has been terminated. (Microsoft SQL Server, Error: 547)
*/

So if you create a MP,SQL Server will create a job and SSIS which is refernced to the subplan as well as inserts the data into system tables in msdb database. (sysmaintplan_subplans,sysjobs_view,sysjobschedules).

Intresting is that if you execute a job it makes more insert into log table called sysmaintplan_log. All of these tables are linked through FK and PK relationships. The problem is when you try to delete a job it gives a Foreign Key errors until you manually remove those entries by the SQL tables.

Please see the link written by Jonas Kempas http://gudenas.com/2007/04/20/sql-server-2005-delete-maintenance-plan-error/ explains step by step how to delete not associated jobs.

I tried it and it worked just fine.

How do we open a large table?

This question raised when we worked at the client's side by one of developers. They use SQL Server 2005 (SP2) and remembered that in SQL Server 2000 we can open the table via EM and then specify (All rows , Top rows...) do you remember?

I rarely use SSMS to open/edit tables data, and it seems that MS just removed this option. In SQL Server 2005 we have TABLESAMPLE clause that used to open a table with lots of rows. So we only needed to see what kind of data this psecific table has without open entire table or using TOP clause.

This example returns an approximate percentage of rows and generates a random value for each physical 8-KB page in the table.

SELECT * FROM table
TABLESAMPLE system(5 PERCENT)

I'd really advise you to read BOL about this great feature
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8868e8fd-6c42-4171-9eab-a0e38cb1bfd3.htm