How could view transaction log (insert, update, delete) on SQL Server 2008
Question
Saturday, December 1, 2012 2:15 PM
Hi,
I want to know is a way to view transaction log (insert, update, delete) on SQL Server 2008
Thank a lot
All replies (13)
Saturday, December 1, 2012 3:36 PM ✅Answered
How about Update? like this?
SELECT*
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation IN ('LOP_DELETE_ROWS', 'LOP_UPDATE_ROWS', 'LOP_INSERT_ROWS')
Sunday, December 2, 2012 5:09 AM ✅Answered
SELECT *
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation IN ('LOP_DELETE_ROWS', 'LOP_INSERT_ROWS','LOP_MODIFY_ROW')
Sunday, December 2, 2012 8:52 AM ✅Answered
SELECT*
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation IN
('LOP_INSERT_ROWS','LOP_MODIFY_ROW',
'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')
Not LOP_UPDATE_ROWS, please use LOP_MODIFY_ROW
Regards
Satheesh
Saturday, December 1, 2012 3:11 PM
one option is to use the function sys.fn_dblog
SELECT*
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation IN ('LOP_DELETE_ROWS', 'LOP_INSERT_ROWS')
Regards
Satheesh
Sunday, December 2, 2012 8:58 AM
Please explain what you are trying to achieve? Take a look at this third party tool.
http://www.lumigent.com/ exploer log for sql
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
Monday, December 3, 2012 12:18 AM
It is good to know about the function usage. But how this information is going to help you. You can not figure out what data is added/removed/updated. To save you some time You can not read the transaction log just like querying the database, as that of i know.
One way of reading is keep a copy of the current database then restore the log file. Then compare the two databases to see the difference. A schema change/data changes etc. There are number of free tools available to compare the databases.
I would love to know if there is an alternative method.
Tuesday, December 4, 2012 9:15 AM
SELECT *
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation IN ('LOP_DELETE_ROWS', 'LOP_INSERT_ROWS','LOP_MODIFY_ROW')
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'fn_dblog', database 'mssqlsystemresource', schema 'sys'.
Help Please
Tuesday, December 4, 2012 9:24 AM
This is a problem with the user privileges. Please check if the user has sysadmin role
here is few links with similar issues and fixes
Regards
satheesh
Tuesday, December 4, 2012 5:04 PM
you can use the Undocumented command fn_dblog(there is an 3rd part tool as well) from SQL prospective-
see
http://www.sqlservercentral.com/blogs/livingforsqlserver/2012/11/19/time-pass-with-transaction-log-part-3-fn-dblog/
http://www.sqlskills.com/blogs/paul/post/Using-fn_dblog-fn_dump_dblog-and-restoring-with-STOPBEFOREMARK-to-an-LSN.aspx
Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.
Wednesday, December 5, 2012 3:33 AM
Undocumented command fn_dblog?
How?
Wednesday, December 5, 2012 4:50 AM
It is the same function that i mentioned earlier :-)
Regards
Satheesh
Wednesday, December 5, 2012 9:16 AM
>I want to know is a way to view transaction log (insert, update, delete) on SQL Server 2008
You have to use 3rd party tools.
You can see the inserted & deleted rows in triggers and the OUTPUT clause:
http://www.sqlusa.com/bestpractices2005/outputidentitycapture/
Kalman Toth SQL 2008 GRAND SLAM
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
Monday, June 24, 2013 10:07 AM
As others already mentioned there is no default way for viewing the transaction log in SQL Server. I suggest using third party tools like ApexSQL Log or SQL Log Rescue for this.
Thanks