| Author |
Topic |
|
t1g312
Posting Yak Master
148 Posts |
 Posted - 2005-02-02 : 05:42:47
|
| Hi all,I used nr's script to create an audit trail for all my transaction tables. While it proves very useful, the size of the database is growing exponentially. For instance, if my transaction tables are 500KB, the audit trail table is over 1.5 MB. Is this normal? Is there any way to optimise the use of the audit trail?Thanks in advanceAdi-------------------------/me sux @sql server |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-02 : 12:26:40
|
| Which audit trail did yo8u use - I have a few on my web site.Have a look at the audit trail data to see what is changing. It could be that you are updating data to the same value or that you just have a lot of updates.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-02-02 : 12:38:52
|
| 1.5 mg?Kinda a small ain't it?btw, that's about 3 changes for every row...Hope you don't move inserts to the audit...Brett8-) |
|
|
|
t1g312
Posting Yak Master
148 Posts |
Posted - 2005-02-02 : 14:40:53
|
| I am moving inserts to the audit! You are right. I need to remove them.Adi-------------------------/me sux @sql server |
|
|
|
t1g312
Posting Yak Master
148 Posts |
Posted - 2005-02-02 : 23:47:11
|
quote:
Originally posted by nr
Which audit trail did yo8u use - I have a few on my web site.Have a look at the audit trail data to see what is changing. It could be that you are updating data to the same value or that you just have a lot of updates.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Hi nr,I'm using this one:http://www.mindsdoor.net/SQLTriggers/AuditTrailTrigger.htmlAdi-------------------------/me sux @sql server |
|
|
|
t1g312
Posting Yak Master
148 Posts |
Posted - 2005-02-03 : 00:06:46
|
quote:
Originally posted by X002548
1.5 mg?Kinda a small ain't it?btw, that's about 3 changes for every row...Hope you don't move inserts to the audit...Brett8-)
I misstated the size. The size of the audit trail table is 1096k while that of the transaction tables is 984k. But in terms of rows, the audit trail has 7472 rows while the transaction tables have 295. The program is currently in test phase and once it is deployed, 100s of rows will be added everyday. Also, there are a few transaction tables that I would like to add to the audit. That is why I would like to find out if I am doing it right. Basically, I want to know the "best practices" for using audit trails.Do you create audit trails for masters too?Thanks in advance.Adi-------------------------/me sux @sql server |
|
|
|
t1g312
Posting Yak Master
148 Posts |
Posted - 2005-02-22 : 08:49:58
|
| One more question. I need to store the user's name in the audit trail table but the problem is that all the users connect to SQL Server using a single userid (I have my own layer of authentication using a "user table"). How do I pass the username to the trigger?Thanks in advance,Adi-------------------------/me sux @sql server |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
t1g312
Posting Yak Master
148 Posts |
Posted - 2005-02-22 : 23:18:55
|
| Can I not create a global variable which holds the user name and can be picked up by any SP or trigger? If yes how?ThanksAdi-------------------------/me sux @sql server |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-23 : 00:14:20
|
| We store the user id in a "Last updated by" column in our tables ... that way the user id gets into the audit tables too! Might as well put a "lasted updated on" datetime in there too whilst you are at it - if you don't already have one.Kristen |
|
|
|
t1g312
Posting Yak Master
148 Posts |
Posted - 2005-02-23 : 00:22:44
|
| Hi Kristen,I do that for all my major transaction tables. But for smaller tables, i.e. tables that have only 2 - 3 columns (used for linking 2 or more tables), I don't think it's justified. I've just come up with an idea. While authenticating the user, I could insert the spid in my user table and use that in the trigger to get the user name. Don't know if it will work though.CheersAdi-------------------------/me sux @sql server |
|
|
|
t1g312
Posting Yak Master
148 Posts |
Posted - 2005-02-23 : 00:25:19
|
| And to minimize the size of my audit trail table, I am planning to insert only the pk if it is an insert, otherwise, all the fields that have been updated. Is that a good idea?ThanksAdi-------------------------/me sux @sql server |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-23 : 09:07:06
|
| I never bother to audit INSERTS. The new data is in the table itself. Thus my trigger code is basically:INSERT INTO MyAuditTable SELECT * FROM deletedKristen |
|
|
|
|
Audit Trail