| Author |
Topic |
|
SqlZ
Yak Posting Veteran
69 Posts |
 Posted - 2004-08-16 : 13:58:03
|
| 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
38200 Posts |
Posted - 2004-08-16 : 14:09:59
|
| 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 |
 |
|
|
SqlZ
Yak Posting Veteran
69 Posts |
Posted - 2004-08-16 : 14:27:23
|
| 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. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-16 : 15:13:42
|
| 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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
SqlZ
Yak Posting Veteran
69 Posts |
Posted - 2004-08-17 : 15:12:47
|
| 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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-17 : 15:14:57
|
| 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 |
|
|
|
SqlZ
Yak Posting Veteran
69 Posts |
Posted - 2004-08-17 : 15:27:31
|
| Very true, I never thought about that.Thanks Tara! |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-17 : 22:41:19
|
| 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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
SqlZ
Yak Posting Veteran
69 Posts |
Posted - 2004-08-18 : 11:06:59
|
| 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. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-18 : 12:11:52
|
| 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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
trc file vs. transaction log file size