SQL Profiler create question

[jaylou]

Hi All,
I have created a trace using a script to run to a 1MB file, with rollover option turned on.
Every few minutes I poll the folder to see if it was released yet and I copy it to a table for later auditing.

Is there a way to specify the file # at the begining?

The reason I am asking is beacuse:

The trace creates a file called file1.trc
when that grows to the specified size it rolls over to a new file called file1_1.trc.
As we all know Micrsoft for some reason says _9.trc is greater then _10.trc.. the same happens at _99.trc, and my job that looks to the next file blows up because _99.trc is greater then _100.trc which is still in use by the trace, so my jobs fails.
I would like to create the trace starting at _100.trc, If I call it that, the next file is _100_1.trc.

TIA,
Joe

 

[mrdenny]

Nope there isn't any way to specify that. Keep in mind that with SQL Server 2005 you can't end a trace file with _{Number}.

This seams like a lot of overhead just in case you need to audit something on the SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[jaylou]

Yes I agree with you about the overhead, but I need to have an audit trail of all the events of any SA or DB_owner to stay SOX compliant. Unfortunately I couldn't come up with a better solution for this.

the _{Number} is added to the file name by the SQL trace. I did not add it to my file name.

If you have a better solution for SOX please share!
I have been tryin to figure out separation of duty but that would slow my already paper filled life to even a slower crawl. SOX has made my productivity go down 75%. It took me 5 hour to do a one row insert into a production table, (do it on Dev, show it in the table, do it to staging show it in staging, get all my signatures, Get all my test proofs, Blah, blah, finally get it to production)
I do more running around getting signatures then actual work.
Those guys that forced us to work like this should be hung by their you know whats and have darts thrown at them!!!!!!!!!!!!!
Anyway enough complaining,
Thanks for the help,
Joe

 

[mrdenny]

SOX totally sucks. It's a major pain. Some companies seam to be taking it to more extreems than others. The last company I was at was taking it to the same extreme you are dealing with. It's one of the reasons that I left. The company I'm at now it's so extreme, so it's not so bad.

With the number of transactions that we are pushing through on our boxes we would run out of storage if we tried to keep trace logs on all of our databases.

Have you thought about having the traces feed into tables in a database. Then you can BCP out the data on a schedule, zip it up and compress it.

We handle SOX by simple restriction of access (only the DBAs get rights to production, and the app). No one has the passwords for the app accounts. When the app is put into production the DBAs create the password and the sysadmin updates the connection string in the app. No one else ever sees it.

That pretty much limits any one who could do any damage to just the DBAs. We all know that if we do stuff to production with out it going through the process that heads will roll so we all behave our selves. Mainly so that we don't have to setup profiler and monitor everything that runs through the system. If worse came to worse we'd use log explorer to find out who made the change and what change was made. It can pull all that info from the transaction logs.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[jaylou]

Denny,
I am tracing all events of SA and db_owners into 1MB files. I have SQL jobs to import the entire trace file to a temp table, which loads an audit table. This job runs every 5 minutes. The job has fail safes to restart or recreate my traces if need be. My next task is to create a reporting system for the SOX Auditors.
I will be purging my audit every 6 months, and the trace files will be housed on a file server on my network with access only to my account that starts SQL. This password is held by my director, and locked away in a safe for emergencies. Other then that I think I am SOX compliant.

Man What A PITA!!!!!!!!!!
Joe

 

[mrdenny]

Yeah, that is a major PITA. Sounds like you are SOX complaint to the letter.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief