Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Friday, January 4, 2019 7:19 AM
Hi, I want to know which information does this columns holds when we execute below query
SELECT
[Log Record]
FROM fn_dblog(NULL,NULL)
Is there any way we can get information from this stored data, if possible, what is the way to retrieve it.
Sandeep J. Sharma (eZee Technosys)
Friday, January 4, 2019 7:36 AM
fn_dblog is an undocumented and so un-supported function.
Olaf Helper
Friday, January 4, 2019 7:41 AM
What do you want to retrieve ? Did you got opportunity to see below blog
How to read and interpret SQL Server errorlog
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
Friday, January 4, 2019 8:03 AM
I want to retrieve all the queries ran from the beginning when the sql server is installed.
Sandeep J. Sharma (eZee Technosys)
Friday, January 4, 2019 8:47 AM
I want to retrieve all the queries ran from the beginning when the sql server is installed.
Then you should have set up a trace or extended-event session first thing when you installed SQL Server.
The transaction log holds information about how pages and rows were modified. These are the result of INSERT, UPDATE and DELETE statements as well as DDL statements, but you will not find the actual statements in the log. And you will find no trace of SELECT operations.
Furthermore, the transaction log is truncated every time it is backed up. Or at CHECKPOINT if the database is in simple recovery.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Friday, January 4, 2019 8:51 AM
I want to retrieve all the queries ran from the beginning when the sql server is installed.
Sandeep J. Sharma (eZee Technosys)
That is not possible, no matter what trace you setup you just cannot capture *all* queries and it really does not makes sense. Yes trace and EE sessions can be set to capture specific set of queries and that would make sense
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
Monday, January 7, 2019 7:29 AM
Hi Sandeep J. Sharma,
Fn_dblog() enables you to read from you transaction log which contains very valuable information about stuff that is happening in your database. So if you would like to retrieve all the queries ran from the beginning when the SQL Server is installed, I think it might be impossible.
I agree with Erland's advice, and transaction log can find most information instead of SELECT operations and some missing log due to log truncation. Of course, if you have full log backups, you can find the missing log back .
By the way , there is an article about SQL Server fn_dblog() Function Details and Example, if you are interesting in it , please refer to it: https://logicalread.com/sql-server-dbcc-log-command-tl01/#.XDL63lwzaUk
Hope it can help you.
Best Regards,
Rachel
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.