locked
Extracting SQL server event log RRS feed

  • Question

  • I would like to extract SQL server (event) log information into text file or DB tables so that we can read it with a script everyday.

    Could you please advise how that can be done?

    Thanks in advance!

    Pingx


    pingx

    Tuesday, September 15, 2015 7:19 PM

Answers

All replies

  • Hi Pingx,

    You can use PowerShell to read the SQL Server Error Log or Windows Event log and write the output into a text/CSV file or store the output in a SQL Server table. Use the Get-SqlErrorLog function to read SQL Server Error Log and use Get-EventLog function to read Windows Event log. For more details, please review this blog: The PoSh DBA - Reading and Filtering Errors.

    In addition, you can click ‘Export’ button in Log File Viewer to save current contents of SQL Server Log and Windows Log to a *.log, *.csv or *.txt file, then read detailed information from the saved file. For more details, please review this similar blog.

    Thanks,
    Lydia Zhang

    If you have any feedback on our support, please click here.


    Lydia Zhang
    TechNet Community Support




    Wednesday, September 16, 2015 6:12 AM
  • Hello,

    You can use the unspupported (!!) XP xp_readerrorlog to read the error log with T-SQL, see

    https://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 16, 2015 6:55 AM
  • Here's what I'm using to analyze the errorlog. Perhaps you can pick things from my code that is useful for you...?

    http://www.karaszi.com/SQLServer/util_analyze_sql_server_logs.asp


    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, September 16, 2015 8:26 AM
  • I tried what you have suggested.

    I ran the following command on the server, it was successful.

    Get-SqlErrorLog  -sqlserver bcmdbwe24 | where-object { $_.logdate -ge ((Get-Date).adddays(-1)) }

    But when I put this same command inside the sql server agent job step. I got:

    Executed as user: BCMDBWE24\SYSTEM. A job step received an error at line 3 in a PowerShell script. The corresponding line is 'Get-SqlErrorLog  -sqlserver bcmdbwe24 | where-object { $_.logdate -ge ((Get-Date).adddays(-1)) }'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'Get-SqlErrorLog' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.  '.  Process Exit Code -1.  The step failed.


    pingx

    Thursday, September 17, 2015 1:03 AM
  • Thank you!

    I will give it a try.

    Ping x


    pingx

    Thursday, September 17, 2015 1:04 AM
  • Thank you, I will look at it tomorrow.

    Pingx


    pingx

    Thursday, September 17, 2015 1:04 AM
  • I tried what you have suggested.

    I ran the following command on the server, it was successful.

    Get-SqlErrorLog  -sqlserver bcmdbwe24 | where-object { $_.logdate -ge ((Get-Date).adddays(-1)) }

    But when I put this same command inside the sql server agent job step. I got:

    Executed as user: BCMDBWE24\SYSTEM. A job step received an error at line 3 in a PowerShell script. The corresponding line is 'Get-SqlErrorLog  -sqlserver bcmdbwe24 | where-object { $_.logdate -ge ((Get-Date).adddays(-1)) }'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'Get-SqlErrorLog' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.  '.  Process Exit Code -1.  The step failed.


    pingx


    Hi pingx,

    Please make sure that you have the sqlpsx module. Before run 'Get-SqlErrorLog ‘, please import the module first by running ‘Import-Module sqlpsx’. You can include all the codes in a .ps file and then run the .ps file in SQL Agent job as described in this similar blog.

     

    Thanks,
    Lydia Zhang

    If you have any feedback on our support, please click here.


    Lydia Zhang
    TechNet Community Support



    Thursday, September 17, 2015 9:18 AM
  • Hi Lydia,

    Thank you very much for your reply.

    I did import sqlpsx module at PowerShell prompt command line. 

    I also tried to import it at Sql Server job step before executing Get-SqlErrorLog, but sql server agent does not regconize the import statement.

    How do I import it with sql server job?

    Pingx


    pingx

    Thursday, September 17, 2015 2:18 PM
  • This is the content of my job step

    These two line runs fine at Powershell window but gives error when I put the as a sql server agent job.

    Import-Module sqlpsx
    Get-SqlErrorLog  -sqlserver bcmdbwe24 | where-object { $_.logdate -ge ((Get-Date).adddays(-1)) }

    This is the job Message
    Executed as user: BCMDBWE24\SYSTEM. A job step received an error at line 2 in a PowerShell script. The corresponding line is 'Import-Module sqlpsx'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'Import-Module' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.  '.  Process Exit Code -1.  The step failed.

     


    pingx

    Thursday, September 17, 2015 6:12 PM
  • Hi Olaf,

    This works except that when I call xp_readerrorlog remotely, the date filters does not work.

    Thank you!

    Pingx


    pingx

    Monday, September 21, 2015 5:47 PM
  • This is the content of my job step

    These two line runs fine at Powershell window but gives error when I put the as a sql server agent job.

    Import-Module sqlpsx
    Get-SqlErrorLog  -sqlserver bcmdbwe24 | where-object { $_.logdate -ge ((Get-Date).adddays(-1)) }

    This is the job Message
    Executed as user: BCMDBWE24\SYSTEM. A job step received an error at line 2 in a PowerShell script. The corresponding line is 'Import-Module sqlpsx'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'Import-Module' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.  '.  Process Exit Code -1.  The step failed.

     


    pingx


    Hi pingx,

    Please review the following blog to get more information about how to execute PowerShell scripts in SQL Server Agent job.
    https://www.simple-talk.com/sql/database-administration/the-posh-dba---sqlpsx-sql-server-powershell-extensions/  

    Thanks,
    Lydia Zhang

    If you have any feedback on our support, please click here.


    Lydia Zhang
    TechNet Community Support



    Tuesday, September 22, 2015 7:05 AM
  • The link to karaszi.com returns a 404 Not Found error.
    Monday, February 24, 2020 8:52 PM
  • I've changed the link structure since... https://karaszi.com/analyze-sql-server-and-agent-errorlogs

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, February 24, 2020 9:26 PM