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.