Mashed Articles

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.

ESB’s use adapters to connect to all sorts of systems: back-end applications, databases, queueing systems, (S)FTP(S) servers, Web Services, HTTP(S) servers or B2B counterparts. The ESB usually uses a technical user account to connect to these systems. Unless the real identity of a human user is carried along to the back end systems (identity propagation).

Larger organizations enforce password change policies. But changing the password with which such technical user connects to one of these other systems is a tough task. The password change in the target system and the ESB need to happen at the same time. And to avoid any problems or disturbing the business, this usually means late at night or in the middle of weekend (when the system goes down for scheduled maintenance).

It would be nice that adapters would provide support for such password changes. One option would be to pre-configure a new password and the datetime from which it should be applied. Another alternative is the configuration of 2 or 3 passwords. If the ‘current’ password doesn’t work, try the other (newer) ones.

PS: similar problem is the changeover of encryption keys

Last week I visited our client who has pretty big databases and performs BACKUP LOG ..operation on almost all user databases. Now, one of the most critical databases got corrupted and the DBA was pretty confident that he won’t loose any data (as he had backup of log file) and brings the database from the backup within 10-12 minutes.

They also have very well written stored procedure that does RESTORE DATABASE based on name of the database and number of log files to be restored. They run the stored procedure and it has been running for almost 5 hours till DBA canceled the process. What happened? Why it has taken so much time? I thought about it and asked him a question,:-”Have you ever cleared backup history?”, he replied that he hasn’t. Then we checked backupset system database that contained more than one million rows!!!!

I remember SQL Server MVP Geoff N.Hiten wrote the blog about the issue and I even posted a comment on.Please check the following article

http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx

It tooks only 3 minutes to run a script that create indexes and about 15 munutes to run
use msdb

go

declare @OldestDate datetime

set @OldestDate = getdate() -100

exec sp_delete_backuphistory @OldestDate

Now that it is finished , our RESTORE command took only 12 minutes to complete.
I’d like to point out how important is to clear backup history (Fortunately, in SQL Server 2005 we have builtin taks to do the job) as on time ‘X’ you will succefully restore a needed database.