Capture CREATE, DROP, ALTER from the default SQL trace file

Question

Post reply

Capture CREATE, DROP, ALTER from the default SQL trace file

MyDoggieJessie

SSC-Forever

Points: 44486
More actions
February 1, 2015 at 11:22 pm

#295050

Comments posted to this topic are about the item Capture CREATE, DROP, ALTER from the default SQL trace file
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

jonathan.hickford Grasshopper Points: 15 More actions · Answer 1

Thanks for posting!

I've seen a few different change tracking systems, some using the default trace, and others using DDL triggers/ What are your (and others) thoughts about using DDL triggers to capture this change information vs using the default trace?

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Thanks for posting wonderful script for schema change monitoring,

How will altered your whole script for monitoring stand alone instance only.. I don't want capture other instance thru dblink features...

Phil Factor SSC-Insane Points: 20244 More actions · Answer 3

@Jonathan.hickford

The Default Trace method is better where you are not allowed any intrusion of code into the system for monitoring purposes, or if you need to catch DDL changes retrospectively. For a normal system where you want audit, I use service broker because the audit process is then done on a different thread in background, or even delegated to another server via the queue. DDL Triggers are the easiest to implement.

See Feodor Georgiev's great series on the Default Trace auditing.:

The default trace in SQL Server - the power of performance and security auditing[/url]

Collecting the Information in the Default Trace[/url]

Collect Your SQL Server Auditing and Troubleshooting Information Automatically[/url]

Best wishes,
Phil Factor

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 4

jonathan.hickford (2/2/2015)
Thanks for posting!
I've seen a few different change tracking systems, some using the default trace, and others using DDL triggers/ What are your (and others) thoughts about using DDL triggers to capture this change information vs using the default trace?

Personally, I'm not a fan of triggers unless they are absolutely necessary...hence why I went this route!

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

JaimePC SSC Eights! Points: 864 More actions · Answer 5

Very interesting post, i enjoy it :w00t:

Btw, is there any kind of method like this to track DML without using CDC or Triggers??

Thanks, Regards

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 6

@SQL Galaxy - you should be able to change the script to easily monitor a single server

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

azhuravel3343 SSC Enthusiast Points: 179 More actions · Answer 7

Thank you for the article. I would like to know how to capture 'TRUNCATE TABLE' command. The reason I am asking this is DROP CREATE ALTER event can also be captured using DDL triggers on database. However 'TRUNCATE TABLE' is not captured. I know one can use fn_dblog function. Example "SELECT * FROM fn_dblog(NULL, NULL) WHERE [Transaction Name] = ‘TRUNCATE’" . However after full db backup and deletion of old t-logs one cannot track truncate table changes.

SQL Severe DBA Hall of Fame Points: 3579 More actions · Answer 8

It can be captured. SQL:batchcompleted / SQLBatchstarting should be checked . :-). No need of any trigger for that .

Manie Verster SSCertifiable Points: 7152 More actions · Answer 9

Hi MyDoggieJessie,

What a very interesting and absolutely efficient way of tracking changes on your server/s. Two questions though.

1. Can a person not capture the actual changes that was changed? For example, I changed a column's datatype from example char(10) to varchar(10) but all this gives me is that the table was altered. Any reason for that?

2. Why did you use the char data type in stead of varchar? You know that if you use for instance char(256) and you only use 10 characters that the rest of the 256 will be added with spaces. I changed the char data types to varchar and it works fine and no spaces. Just wondering and you might have a perfectly good explanation. Let me know please.:-P:-P:-P:-P:-P:-P

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 10

No, using this method there is no way to track the actual change made. You would need to either use a 3rd party monitoring tool or enable CDC. This is more of a "who did it" process, not "what did they do" :hehe:

I typically use varchar everywhere I can get away with it and have no reason as to why I chose char except "I forgot" 😀

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

mister.magoo SSC-Forever Points: 47068 More actions · Answer 11

Thanks for sharing this.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 12

Thanks for sharing .... I seem to have found a bug though... we are missing some changes and the point of failure that I am looking at.. is when the default trace rolls to a new trace file you miss everything between the last time the capture job was run and when the trace file rolled. Since it is using a rolling 5 files, I am thinking about tracking the names each time it has run... detecting the change and looking at both files in this case. It takes us about an hour to fill up a file and I am capturing every 30 min.

I like this method, over trigger... but need to track down why I am not getting all changes made.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 13

Interesting! I have no experienced this issue.

It could be that you're filling up your files faster than the job is able to pull them out...so it's getting overwritten before the job picks back up again.

You could try extending the number of files out, or shortening the timeframe the collection job is running

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 14

MyDoggieJessie (3/26/2015)
Interesting! I have no experienced this issue.
It could be that you're filling up your files faster than the job is able to pull them out...so it's getting overwritten before the job picks back up again.
You could try extending the number of files out, or shortening the timeframe the collection job is running

I have 5 rolling files, but the code does only uses the current file. So I am going to have to write the file each time to a table, thus if they do not match I can read the old file first, and then the current file in use. That should fix the missing DDL statements.