Posted by Sudarshan Narasimhan on June 5, 2012
I had posted on this topic before in my previous blog. SQL Server 2008 is also affected by slow purge procedure core.sp_purge_data as well as deadlocks in the purge procedure. This issue was fixed in SQL Server 2008 R2 SP1. For SQL Server 2008, the fix to the purge procedure has been done in Service Pack 3. Those of you using MDW to collect performance data, should apply SP3 as soon as possible. This will greatly reduce the purge procedure run time and your jobs will run faster.
The KB 2584903 has been updated to include SQL 2008 as an affected product. I’ve posted this on the Connect Bug as well here –> https://connect.microsoft.com/SQLServer/feedback/details/657148/management-data-warehouse-mdw-purge-job-proc-performance
Just applying SP1 for R2 or SP3 for SQL 2008 will not be enough to get the MDW purge procedures updated. Once the service pack has been successfully installed, you will have to run through the Configure Management Data Warehouse Wizard from SSMS to get the purge procedures updated with the new T-SQL code.
Posted in Data Collector | Tagged: 2584903, deadlock, ExecMasterPackage, fix, performance, slow purge, SQL Server | Leave a Comment »
Posted by Sudarshan Narasimhan on June 1, 2012
Having worked with multiple customers and different application where at times the data in the table has gotten changed and we need to find out when/where/who made the changes, I used the below T-SQL code to setup a trigger on the table and track the changes into an auditing table. There are obviously other ways to do this like SQL Profiler, XEvents etc., but I find using T-SQL more simpler and lesser overhead. So, sharing the script for others to use.
-- Note: This trigger tracks Updates and Deletes happening on a table.
-- Please delete this trigger once the source has been identified and corrective actions have been taken.
-- 1. Creating the audit table to store information on Update/Delete
CREATE TABLE AuditTable
AuditID [int] IDENTITY(1,1) NOT NULL,
Timestamp datetime not null CONSTRAINT AuditTable_Timestamp DEFAULT (getdate()),
OperationDate datetime DEFAULT (GetDate()),
--2. Creating the audit trigger
-- Replace PrimaryKeyValue with the PK Column Name
-- Replace NewColValue with the column name in the IF BLOCK
-- Replace OldColValue with the column name in the final SELECT statement
-- Replace TBLNAME with the name of your table which you want to track the changes for.
Create trigger TBLNAME_Audit on TBLNAME for update, delete
declare @OperationType char(1),
select @UserName = system_user
select @OperationDate = CURRENT_TIMESTAMP
select @ClientName = HOST_NAME()
select @AppName = APP_NAME()
if exists (select * from deleted)
if exists (select * from inserted)
select @OperationType = 'U'
select @NewColValue = select NewColValue from inserted
select @OperationType = 'D'
select @NewColValue = null
Insert AuditTable (OperationType, OperationDate, PrimaryKeyValue, OldColValue, NewColValue, UserName, AppName, ClientName)
select @OperationType, @OperationDate, PrimaryKeyValue, OldColValue, @NewColValue, @UserName, @AppName, @ClientName
--3. Query the audit table once the values in the base table has changed
select * from AuditTable
Posted in Auditing, Security, T-SQL | Tagged: Auditing, data change, deletes, mysterious, SQL Server, Tracking changes, triggers | 2 Comments »