Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 trc file vs. transaction log file size
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SqlZ
Yak Posting Veteran

USA
69 Posts

Posted - 08/16/2004 :  13:58:03  Show Profile  Reply with Quote
If I ran a c2 trc on a production sql server and made transaction log backups every hour of the day for 24 hours would the size of the C2 trc files be equal to the size of the transaction log files? I am trying to estimate the space required for c2 trc files.

Thanks in advance,
Anthony

"DBAs are overpaid and their jobs are easy...heck, I could do it."

-Quote from a former boss who calls often for help and doesn't get any :)

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 08/16/2004 :  14:09:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
What is c2 trc?


[EDIT]

Oh nevermind I see that has to do with the audit thing. I doubt that the tlog being backed up every hour will give you the c2 trc file size.

[/EDIT]

Tara

Edited by - tkizer on 08/16/2004 14:15:51
Go to Top of Page

SqlZ
Yak Posting Veteran

USA
69 Posts

Posted - 08/16/2004 :  14:27:23  Show Profile  Reply with Quote
Sorry for not being specific in my previous post. I meant that if I turn on the configuration option for C2 Auditing which results in 200 MB trace files in the default (.mdf) Data Directory.

Basically I was wondering if the total size of all the transaction log files gathered for one day on a particular SQL Server (if I backed them up every hour) would equal the same size of the C2 Audit trc files (200 MB a piece) for one day assuming the same server and same activity. I know that reads are not captured in tlogs but I was under the impression that trc files are very small compared to log files.

We are trying to audit activity on the SQL Server and am pretty sure I should customize my own traces in Profiler to do it but was curious about the sizes of logs compared to trace files.


Thanks again.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 08/16/2004 :  15:13:42  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
Our logs are several gigs each day. I run a few traces against production non-stop and they aren't even close to that. I would set up the traces. You can't do performance tuning if you don't know what's going on. We can help you get them going.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

SqlZ
Yak Posting Veteran

USA
69 Posts

Posted - 08/17/2004 :  15:12:47  Show Profile  Reply with Quote
I am trying to convince a fellow dba here that his assumption that reads:writes being a 5:1 ratio might be a safe one but the trace files will no way be five times as large as the transaction logs.

I run traces on production servers sometimes all day, then import them into a db table and the tables are small.




Edited by - SqlZ on 08/17/2004 15:13:19
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 08/17/2004 :  15:14:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
The size of the trace files doesn't correlate to the size of the transaction log. When a DML statement occurs that affects an index, this isn't shown in the trace file but the tlog is affected by it.

Tara
Go to Top of Page

SqlZ
Yak Posting Veteran

USA
69 Posts

Posted - 08/17/2004 :  15:27:31  Show Profile  Reply with Quote
Very true, I never thought about that.

Thanks Tara!
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 08/17/2004 :  22:41:19  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
You also need to look at what you are capturing. Normally, you only want to capture "some" events such as SP:Completed and RPC:Completed. This is a small amount of what actually happens and is recorded in the transaction log which is EVERY transaction.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

SqlZ
Yak Posting Veteran

USA
69 Posts

Posted - 08/18/2004 :  11:06:59  Show Profile  Reply with Quote
Thanks Derrick.

Those two events along with Security Audit are the events I am capturing. I ran a trace for 24 hours on our busiest production server with the events mentioned here and the trace file was about 4.5 GB...WOW! I still need to monitor all DDL statements and other anonymous SQL run on the server.

Thanks again for all your help. I guess c2 traces really would fill up disk space rather quickly.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 08/18/2004 :  12:11:52  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
lol Well, you probably want to limit it. We limit ours to any reads over 1000. Then another one with any writes over 1000. We started it out at 5000 and have tuned the environment to get it to where it is now.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000