January 5, 2012 at 1:32 pm
Hello everyone,
I need to implement the CDC system in sql server 2k5.
I have read quite a bit about the operation of CDC in 2k8, but we cannot implement 2k8, so we are stuck in 2k5 land.
I have tried to use a table based auditing system, that uses the triggers to populate the audit table, BUT... there seems to be a problem with the liberal usage of the WITH (NOLOCK) and cascading triggers that are implemented in the database system. With these little goodies
in the system, the auditing using triggers is 1) not updating when operations occur and 2) tremendious overhead on the server... sometimes to the point that a timeout occurs to the web based user.
In my test world, i have created a 2k8 system, copied the tables and data from production that i need to audit, implemented the CDC and performed load tests... and it worked great.... but our IT staff will not upgrade the SQL server.... so i am stuck.
What i am looking to do is this.... create a CDC equiv for 2k5 that performs the audit operations against the transaction log instead of the actual live tables.
My first request is to ask is are there any real good papers/links/pointers about the contents of the transaction log in 2k5?
thank you
tony
January 5, 2012 at 6:40 pm
You might want to look at the undocumented DBCC command
DBCC log ({dbid|dbname}, [, type={-1|0|1|2|3|4}])
where dbid is either the dbid or the name of the database
and type is the type of output, and is one of...:
0 - minimum information (operation, context, transaction id)
1 - more information (plus flags, tags, row length, description)
2 - very detailed information (plus object name, index name, page id, slot id)
3 - full information about each operation
4 - full information about each operation plus hexadecimal dump of
the current transaction log's row.
-1 - full information about each operation plus hexadecimal dump of
the current transaction log's row,
plus Checkpoint Begin, DB Version, Max XDESID
If you do not specify a type it defaults to 0
Above found at:
http://www.simple-talk.com/community/forums/thread/1439.aspx
It might produce the information that you are seeking.
January 6, 2012 at 8:31 am
Very nice - thank you.
Using DBCC does not give enough information for me...
thanks again for the help
take care
tony
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply