.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Latest Best Practices in Transaction Auditing in ASP.NET and SQL Server?

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :ASP.Net


What is the most recent best practice on how transaction auditing should be done for transactional systems using ASP.NET 3.5 and SQL Server 2008?

My requirement is to track changes done by a user and allow them to view the audit trail history in the front-end.

Here’s the different ways I found so far:

  1. Create triggers and separate table(s). There are 2 options on this approach:
    1. Create an audit table for each table. This will be exact copy of the table, but will have additional field such as transactionID and datetime fields, etc.
    2. Create one audit table to keep track of all tables. This will be a more generic table to keep track of changes to the table's data.
  2. I read about the new auditing feature on SQL Server 2008 (http://msdn.microsoft.com/en-us/library/dd392015%28v=sql.100%29.aspx). Can this be leverage for in ASP.NET? Can I access the file directly and allow users to view it? Or is this really more of a DBA Admin usage?

Thanks for your help in advance!

View Complete Post

More Related Resource Links

SQL Server Database Transaction

A transaction is a group of SQL commands executed together as a single component of work to be accomplished. If all of these commands succeed, then a transaction is committed - the changes to the data are made final. If any of the commands within a transaction fail, then the entire transaction is cancelled, or in SQL Server terms, the transaction will be rolled back.

Transactions are one of the things that keep your data safe, but they're not necessarily easy to understand. Here's some help:

Best Practices for Content Types in SharePoint Server 2007 and Windows SharePoint Services 3.0

Learn best practices to develop, deploy, and manage custom content types, as well as scenarios for making updates to existing content types in lists.

Where to find the latest list of critical SQL Server fixes?

Hi, Where can I find the latest list of critical SQL Server fixes?

Transaction Handling in SQL Server 2008

Hi there,        I am using .NET (C#) and sql server 2008 for my application, I am fetching records from a table and using gridview to show them.        Now saving selected records (using checkbox to select records) into another table row by row. before saving the records if records with same ID is present need to delete those records. could anyone tell me how to use transaction in SQL server 2008. any blog or website............... anything   Thanks, Sadaf

Doesn't restart sql server rollback uncommitted transaction?

hi, i executed a long-running sql in the management studio. The sql updates many rows and all updates are within a transaction. After runing 30 mins, the hard disk is full. That's because of the uncommitted updates. Then, i restarted the sql server because I thought after restarting, the uncommitted transaction is rollback and the occupied hard disk space is also released. But i found that the transaction is rollbacked but the hard disk space is not released. Why the hard disk space is not released? how can i release the disk space now? Thank you for your help.

SQL Server + .Net | Transaction | Can Transaction level be esclated

Hi We are facing a unique problem which ends up causing deadlocks. We have found that We have a very heavy load OLTP Sql server 2008. In .net code we set the Transaction isolation level to Read commited while executing a SP. We find deadlocks and in the dead lock details it is explictly called out that the SP in question was executing in transaction level serializable. Lets call this SP1 The only other thing we can say is that there are some other unrelated stored procedures which access the same tables as SP1 and are executing in isolation level serializable. Is it possible for one SP to escalate isolation level like locks are escalated? We found the following links which hint this, by saying that isolation level is just a hint. http://msdn.microsoft.com/en-us/library/ms229978(v=VS.90).aspx http://technet.microsoft.com/en-us/magazine/2008.04.blocking.aspx?pr=blog http://msdn.microsoft.com/en-us/library/ms189857.aspx http://aartemiou.blogspot.com/2009/01/table-level-locking-hints-in-sql-server.html Any help in this regard will be great.

Best practices on moving SharePoint databases physically, while keeping them on the same SQL server?

I'd need to move the SharePoint 2010 databases (preferably all of them) with as little hassle as possibly from one partition to another. I tried once, and ended up with a broken SharePoint and had to recover from backups. What's the best practices for moving the databases without breaking SharePoint completely in the process? I'm guessing I need to shut down the SharePoint server for this... That's not impossible if needed.

what is the latest version for SQL Server 2008 R2

We are using Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2).    LISA86

Very high transaction log file growth in SQL server

Hi,   I am using with below version of SQL server. Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb  9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2).   One of my database transaction log file is getting full very frequently on PROD database,which i have truncated ones and shrunk ones after that i have taken full backup immediately.Again the Db tran log file grown to very big size. We are taking the transaction log backup for evry one hour. and The column shows log_reuse_wait=2 & log_reuse_wait_desc=LOG_BACKUP from sys.databases. Can you please suggest me free the transaction log space without truncating the log file.   Thanks, Gangadhar  

Serve to server Transaction Replication error


I have windows 2003 sp2 servers each hosts SQL Server 2008 SP1 standard edition. I am trying to replicate both servers, using the tutorial available in the online documentation <Tutorial: Replicating Data Between Continuously Connected Servers> which can be found at: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10rp_1devconc/html/9c55aa3c-4664-41fc-943f-e817c31aad5e.htm

I have created the users (repl_logreader, repl_distibution, repl_snapshot, and repl_merge and setting their security. Then I have created the distibutor, setting database security as mentioned in the document, and last creating the publication with a snapshot to be run immediately, distributor and publisher are on the same server and the publication will be pushed to the subscriber. Once the publication is created succesfully, I am checking the agents to see the errors. I am always stuck with the following two error messages:

1- snapshot agent error:

Error messages:

Message: Failed to create AppDomain "mssqlsystemresource.dbo[runtime].1090".
The domain manager specified by the host could not be instantiated.
Failed to create AppDomain "mssqlsystemresource.dbo[runtime].1091".
The domain manager sp

SQL Server 2008 - Best Practices Analyzer

Is there a version of SQL Server 2008 Best Practices Analyzer available for download?   If not, can I use BPA for SQL Server 2005 to run a DB assessment on a SQL Server 2008 database?  Please let me know what your recommendation is?


The server failed to resume the transaction. Desc:4800000002


Hi friends !

I have a problem with SQL Server 2008. When I insert / update into DB then exception is thrown:

   The server failed to resume the transaction. Desc:4800000002

Pls help me solve this problem.

Thank u


SQL Server 2008 r2 developer RTM edition not installing on windows 7 64 bit machine with latest serv


This machine had sql server 2008 installed.  I removed it since the upgrade would not work.   The machine currently doesn't have any sql installs.    The error fails during the Setup Support Files step.    Any help on this would be greatly appreciated.  I've included last part of file since you can only include 60k chars here.

2010-09-24 10:14:14 Slp: C:\sql2008r2\x64\FixSqlRegistryKey_x64.exe exit code: 0
2010-09-24 10:14:14 Slp: Launching external tool: C:\sql2008r2\x64\FixSqlRegistryKey_x86.exe
2010-09-24 10:14:14 Slp: ------------------------------------------------------------
2010-09-24 10:14:14 Slp: HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer - OK.
2010-09-24 10:14:14 Slp: HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client - OK.
2010-09-24 10:14:14 Slp: HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\SNI9.0 - OK.
2010-09-24 10:14:14 Slp: HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\SNI9.0\GeneralFlags - OK.
2010-09-24 10:14:14 Slp: HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\SNI9.0\GeneralFlags\Flag1 - OK.
2010-09-24 10:14:14 Slp: HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\SNI9.0\GeneralFlags\Flag2 - OK.
2010-09-24 10:14:14 Slp: HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\SNI9.0\LastConnect -

Distributed transaction using linked server not working in SQL Server 2008 64 bit


Hi. I have had an issue trying to get distributed transactions to work in SQL Server 2008 using a linked server. The error message I get is

OLE DB provider "SQLNCLI10" for linked server "pod1" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "pod1" was unable to begin a distributed transaction.

My Environment:

Windows Server 2008 Enterprise 64 bit, SQL Server 2008 Enterprise 64 bit

Problem occurs with multiple different builds of SQL Server 2008 - I have been able to reproduce the problem with 10.0.1600.0, 10.0.1779.0 (CU 2), as well as 10.0.2531 (SP1)


I am aware that other people have had this issue and have reviewed all existing posts. I have verified that the MSDTC is configured correctly on both machines. I have also used DTCTester to verify that DTC is working correctly on all machines in question. None of the mentioned resolutions has solved this problem for me.

I am not seeing this problem occur on my 32 bit test machines - it is only occurring if at least one of the two machines is 64 bit.


advantages of transaction log backup in sql server 2008?


i want to know what are the advantages of transaction log backup in sql server 2008?

Auditing in SQL Server 2008 (or 2008 R2)


Hi, I believe that audit specifications cannot be used in the standard version of SQL 2008 and 2008 R2 ? If this is the case, how can I create an audit trail on a table or view which records info such as Active Directory account name (of query executor), date and time of query, query details (ie. select statement), etc etc for each select query executed on the table or view.

Auditing in SQL Server 2008 (or 2008 R2)


Hi, I believe that audit specifications cannot be used in the standard version of SQL 2008 and 2008 R2 ? If this is the case, how can I create an audit trail on a table or view which records info such as Active Directory account name (of query executor), date and time of query, query details (ie. select statement), etc etc for each select query executed on the table or view.

Any help much appreciated, my client cannot upgrade to enterprise (and therefore use audit specifications) to to cost limitations. 

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend