I was following a video from Pinal Dave called "Who Dropped Your Table?" and was getting a message that, "Currently, this report does not have any data to show, because default trace does not contain relevant information."

I googled for the reason why and guess what? The first hit (and answer) was on sqlauthority.com:

One of my clients of Comprehensive Database Performance Health Check recently asked me if there is a way to fix the error which they get when they try to open Schema Changes History. The error was related to Not Have Any Data to Show in the report. Let us see how we can fix that.

โ€ฆ

The solution of the same is very simple and I have previously blogged about it here: SQL SERVER โ€“ SSMS: Configuration Changes History.

Here is the solution:

1
2
3
4
5
6
7
8
EXEC sp_configure 'Show Advanced Options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1
GO
RECONFIGURE WITH override
GO

When you run the above command, SQL Server will first enable the advanced options. Right following that, it will enable the default trace.

And now I too can tell who dropped tables on my database.

(I do worry a little that enabling this might be a significant performance hit, but I just set it on my local dev machine for now. The answer to who messed things up will, here at least, always be me. Hopefully!)

Labels: ,