Post reply

How to view transaction log for SQL Server 2005

  • April 7, 2008 at 10:12 pm

    #205239

    I need to view last 7 days transaction on a database in MS SQL Server 2005. Can anyone pls help me how to view the same ?

  • April 8, 2008 at 9:16 am

    #799840

    If you have the log (or log backups), you can download Log Rescue from Red Gate and use that.

  • April 8, 2008 at 7:16 pm

    #800084

    Hi ,

    Thanks for your reply. But Log rescue works only with MS SQL Server 2000. I need to retrieve transaction log from SQL Server 2005. Can you help in this ?

  • April 8, 2008 at 7:42 pm

    #800088

    Apex SQL Log is compatible with SQL Server 2005:

    http://www.apexsql.com/sql_tools_log.asp

    K. Brian Kelley
    @kbriankelley

  • April 8, 2008 at 8:05 pm

    #800096

    Hi Brian,

    Thanks for the reply. I already tried with ApexSQL. Free copy can result in limited number of rows only. Its too costly for us to buy bcoz Its just for one time use only. Can you pls refer me any free software or any SQL commands to do this process. Thanks

  • April 9, 2008 at 7:20 am

    #800367

    There are no free log readers for 2005. It's a lot of investment for someone to decode the log and write the software.

    There's no good way to do this in 2005 without buying the software.

  • April 9, 2008 at 8:52 am

    #800474

    And, while you'd like to think that you'll only need a log reader once, it's pretty cheap insurance. If you need it this time, you'll probably need it again.

    Greg

  • October 3, 2008 at 12:08 am

    #880298

    toad for sql server has a log reader. (I haven't tried that part of it yet).

    my boss is getting me to trial it presently.

    You can download it at http://www.quest.com/toad-for-sql-server/software-downloads.aspx

  • October 3, 2008 at 4:44 am

    #880373

    You can read the transaction log raw if you like. It's not easy to understand though. Also it will only have up to the last log backup (in full recovery) or checkpoint (in simple recovery)

    SELECT * FROM master.dbo.fn_dblog(null, null)

    What's the purpose behind this request?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 12, 2009 at 6:14 am

    #1037927

    Hi Guys,

    Similar problem here, I need to find out if the database logs will indicate that a database has been recovered and I need to show the log as proof to business.

    Will the db log contain that info? And how do I read it?

    Regards,

    William

    ----------------------------------------------------------------------------------------------
    Struggling to find the question?
    http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

  • August 12, 2009 at 6:23 am

    #1037932

    Nevermind, I found it... 😀

    ----------------------------------------------------------------------------------------------
    Struggling to find the question?
    http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

  • August 12, 2009 at 6:31 am

    #1037940

    WilliamB (8/12/2009)

    Nevermind, I found it... 😀

    What did you find? Details please, someone else may have the same question.

  • August 12, 2009 at 6:35 am

    #1037945

    WilliamB (8/12/2009)

    Similar problem here, I need to find out if the database logs will indicate that a database has been recovered and I need to show the log as proof to business.

    If you're looking for info on whether a database has been restored from backup, that info should be in the SQL error log, not the transaction log.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 12, 2009 at 8:39 am

    #1038028

    Okay, for those who would like to get the info here is a step by step:

    1. Open Management Studio and connect to the server you require the info from.

    2. Expand the Management folder.

    3. Expand the SQL Server Logs folder.

    4. Double click on the log to view it (or right click on the log and select "View SQL Server Log")

    5. Step 4 opens the Log File Viewer. At the top of the viewer is an option to Search. I used this option with criteria "RESTORE" and it directed me to the information I was looking for.

    @Gail - I'm guessing these are the Error Logs you are talking about? I'm still a bit of noob so any info to clarify this would be great.

    @Lynn - Sorry, I should have posted this first instead of just saying "I found it!"

    😉

    ----------------------------------------------------------------------------------------------
    Struggling to find the question?
    http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

  • August 12, 2009 at 9:21 am

    #1038066

    WilliamB (8/12/2009)

    @Gail - I'm guessing these are the Error Logs you are talking about? I'm still a bit of noob so any info to clarify this would be great.

    Yup.

    They're visible through Management Studio as you described, but they're just text files (errorlog, errorlog.1, errorlog.2, etc), so you don't have to read them through Management Studio.

    p.s. Please could you in the future post new questions in new threads? Sometimes people don't look at old threads cause they think they're 'solved'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply