Home / Guides / Read SQL Server error logs from many instances

Read SQL Server error logs from multiple instances at once

A login keeps failing, a backup went wrong overnight, or an instance restarted and you want to know why, and the answer is in an error log on one of twenty instances. SSMS opens them one at a time. This guide covers where the error log lives and how many are kept, sp_readerrorlog with search strings and archived logs, the permissions it needs, three ways to search many instances in one go (SSMS, PowerShell and dbatools), the errors you will meet, and where those methods stop.

Where the error log is, and how many are kept

SQL Server writes its error log to a text file called ERRORLOG in the instance's LOG folder. Every time the service starts, the current file is renamed ERRORLOG.1, the old ERRORLOG.1 becomes ERRORLOG.2, and so on. By default SQL Server keeps the six previous logs plus the current one, and drops the oldest when a new one starts. SQL Server Agent does the same with its own log (SQLAgent.1, SQLAgent.2 ...). To see where the current file is:

USE [master];
SELECT SERVERPROPERTY('ServerName')       AS server_name,
       SERVERPROPERTY('ProductVersion')   AS version,
       SERVERPROPERTY('ErrorLogFileName') AS error_log_file;

Both the number of files and their maximum size can be changed in SSMS: Management, right-click SQL Server Logs, Configure; a size of 0 means no limit. The SqlServer PowerShell module has Set-SqlErrorLog -MaxLogCount for the same setting.

Cycling the log

An instance that has not restarted for months has one very large error log, which is slow to open and slow to search. Microsoft suggests starting a new log periodically with sp_cycle_errorlog, which closes the current file and renames the others exactly like a restart, without restarting anything. sp_cycle_agent_errorlog in msdb does the same for the Agent log. Both need sysadmin, and both push the oldest file out, so raise the number of kept logs before you cycle every night.

-- Start a new SQL Server error log (sysadmin only; changes files, not run for this guide)
USE [master];
EXECUTE sp_cycle_errorlog;

-- Start a new SQL Server Agent log
USE [msdb];
EXECUTE dbo.sp_cycle_agent_errorlog;

We did not run these for this guide: they change the server's files. The syntax and the permission come from Microsoft's reference pages, linked below.

Search one instance with sp_readerrorlog

sp_readerrorlog is the documented way to read the error log from T-SQL. It takes up to four arguments, all optional:

• Log number (@p1): 0 is the current log, 1 is ERRORLOG.1, 2 is ERRORLOG.2, and so on.
• Product (@p2): 1 for SQL Server, 2 for SQL Server Agent.
• Search string (@p3): only lines that contain it are returned.
• Second search string (@p4): narrows the first, so a line must contain both.

USE [master];
-- current SQL Server error log, lines that contain "Login failed"
EXECUTE sp_readerrorlog 0, 1, N'Login failed';

-- the log before the last restart, lines that contain both "backup" and "failed"
EXECUTE sp_readerrorlog 1, 1, N'backup', N'failed';

-- the previous SQL Server Agent log, all lines
EXECUTE sp_readerrorlog 1, 2;

One trap: give the product number whenever you search. On SQL Server 2025 we read the procedure's own definition, and when the product argument is NULL it calls xp_readerrorlog with the log number only, so the search strings are ignored and you get the whole log. EXECUTE sp_readerrorlog 0, NULL, N'timeout'; returns every line. Write 0, 1, N'timeout' instead.

xp_readerrorlog

Many scripts call xp_readerrorlog directly. It is the extended procedure that sp_readerrorlog calls after its permission check (we read that in the definition), and Microsoft's own examples sometimes use it with the same first three arguments. It has no reference page on Microsoft Learn, though. Community scripts pass more arguments to it, such as a time window and a sort order, but none of that is documented and it can change between versions. In the master database we looked at, only sp_readerrorlog was granted to public, so a login that is not sysadmin normally goes through sp_readerrorlog anyway. This guide sticks to the documented procedure.

The permissions it needs

From Microsoft's reference page for sp_readerrorlog:

• SQL Server 2019 and earlier: membership in securityadmin, or VIEW SERVER STATE on the server.
• SQL Server 2022 and later: securityadmin, or either VIEW ANY ERROR LOG or VIEW SERVER STATE.

Prefer the narrow permission. Microsoft says to treat securityadmin as equivalent to sysadmin, because its members can grant themselves most rights. VIEW SERVER STATE opens all the server-wide dynamic management views as well, so on SQL Server 2022 and later VIEW ANY ERROR LOG is the one to grant. Check what the current login has on an instance:

USE [master];
SELECT IS_SRVROLEMEMBER('securityadmin')                     AS is_securityadmin,
       HAS_PERMS_BY_NAME(NULL, NULL, 'VIEW SERVER STATE')    AS has_view_server_state,
       HAS_PERMS_BY_NAME(NULL, NULL, 'VIEW ANY ERROR LOG')   AS has_view_any_error_log;

A 1 means yes and a 0 means no. The last column is NULL on SQL Server 2019 and earlier, because that permission does not exist there (HAS_PERMS_BY_NAME returns NULL for a permission name it does not know). To grant the read right to a monitoring login (the login must exist; replace the name with yours):

USE [master];
-- SQL Server 2022 and later: the narrow right
GRANT VIEW ANY ERROR LOG TO [CONTOSO\svc-logsearch];

-- SQL Server 2019 and earlier: the documented alternative
GRANT VIEW SERVER STATE TO [CONTOSO\svc-logsearch];

-- Rollback (only if the login did not hold the right for something else):
-- REVOKE VIEW ANY ERROR LOG FROM [CONTOSO\svc-logsearch];
-- REVOKE VIEW SERVER STATE FROM [CONTOSO\svc-logsearch];

Search many instances at once

SSMS: a multi-server query

SSMS can run one query against a group of servers. In Registered Servers (View, Registered Servers, or Ctrl+Alt+G), create a server group under Local Server Groups or a Central Management Server, register your instances in it, right-click the group and choose New Query. Run the sp_readerrorlog call there. By default the results from all servers are merged into one grid with a Server Name column added.

Two things to know: Microsoft's documentation says that when the connection to a server fails, no error message is displayed, so check the connection count in the status bar (for example Connected. (4/4)) before you trust an empty result. And each instance runs the query with your own permissions there, so an instance where you lack VIEW SERVER STATE returns an error, not rows.

PowerShell: loop with Invoke-Sqlcmd

For a list of instances, a CSV file, or a scheduled run, loop in PowerShell. This script uses Invoke-Sqlcmd from Microsoft's SqlServer module (Install-Module SqlServer once). The search runs on each server, inside sp_readerrorlog, so only the matching lines travel back. Every line gets its instance and log number, everything is sorted newest first and written to a CSV, and an instance that fails is reported at the end instead of stopping the run.

param(
    [string[]]$ServerInstance = @('SQL01', 'SQL02', 'SQL03\SALES'),
    [string]$Search           = 'Login failed',     # empty = every line
    [int[]]$LogNumber         = @(0, 1),            # 0 = current log, 1 = the one before, ...
    [ValidateSet(1, 2)][int]$Product = 1,           # 1 = SQL Server, 2 = SQL Server Agent
    [string]$CsvPath          = "$env:USERPROFILE\Desktop\SqlErrorLogSearch.csv"
)
Import-Module SqlServer -ErrorAction Stop

# The search string goes into the T-SQL as a literal, so double any single quote in it.
$p3     = if ($Search) { "N'" + $Search.Replace("'", "''") + "'" } else { 'NULL' }
$found  = [System.Collections.Generic.List[object]]::new()
$failed = [System.Collections.Generic.List[object]]::new()

foreach ($instance in $ServerInstance) {
    foreach ($n in $LogNumber) {
        # Always pass the product number: with NULL there, sp_readerrorlog ignores the search string.
        $query = "EXECUTE sys.sp_readerrorlog $n, $Product, $p3;"
        try {
            $rows = Invoke-Sqlcmd -ServerInstance $instance -Database master -Query $query `
                                  -QueryTimeout 300 -ErrorAction Stop
        }
        catch {
            $failed.Add([pscustomobject]@{ Instance = $instance; Log = $n; Error = $_.Exception.Message })
            continue
        }
        foreach ($r in $rows) {
            $found.Add([pscustomobject]@{
                Time     = $r.LogDate
                Instance = $instance
                Log      = $n
                Process  = $r.ProcessInfo
                Text     = $r.Text
            })
        }
    }
}

$sorted = $found | Sort-Object Time -Descending
$sorted | Format-Table Time, Instance, Process, Text -AutoSize -Wrap
$sorted | Export-Csv -Path $CsvPath -NoTypeInformation -Encoding UTF8
"{0} lines from {1} instances written to {2}" -f $found.Count, $ServerInstance.Count, $CsvPath

foreach ($f in $failed) { Write-Warning "$($f.Instance), log $($f.Log): $($f.Error)" }

Save it as C:\Scripts\Search-SqlErrorLogs.ps1 and run it with your own instance names:

C:\Scripts\Search-SqlErrorLogs.ps1 -ServerInstance SQL01, SQL02, 'SQL03\SALES' -Search 'Login failed'

# The instance list from a file (one per line), the last four logs, the Agent log:
C:\Scripts\Search-SqlErrorLogs.ps1 -ServerInstance (Get-Content C:\Scripts\instances.txt) -LogNumber 0, 1, 2, 3 -Product 2 -Search 'failed'

• -ServerInstance: the instances, written as you would in SSMS (SERVER for a default instance, SERVER\INSTANCE for a named one).
• -Search: the text to look for. Leave it empty to get every line of those logs.
• -LogNumber: which logs to read. The default is the current log and the one before it; add 2 to 6 to go further back.
• -Product: 1 for the SQL Server error log (default), 2 for the SQL Server Agent log.
• -CsvPath: where the results go.

PowerShell: Get-SqlErrorLog

The same SqlServer module has a cmdlet made for this. -ServerInstance takes a list, -Since takes Midnight, Yesterday, LastWeek or LastMonth (or use -After and -Before), and every entry carries a ServerInstance property. It has no text filter, so the search is a Where-Object in your session, and its own sort is per instance, so sort the combined list yourself:

Import-Module SqlServer

Get-SqlErrorLog -ServerInstance SQL01, SQL02, 'SQL03\SALES' -Since Yesterday |
    Where-Object { $_.Text -like '*Login failed*' } |
    Sort-Object Date -Descending |
    Select-Object Date, ServerInstance, Source, Text

dbatools: Get-DbaErrorLog

The free, community-run dbatools module has Get-DbaErrorLog, which many DBAs already use. It takes several instances, a -Text filter, -After and -Before, -Source (such as Logon or Backup) and -LogNumber, and every entry has SqlInstance, LogDate, Source and Text:

# Install-Module dbatools -Scope CurrentUser    (once)
Get-DbaErrorLog -SqlInstance SQL01, SQL02, 'SQL03\SALES' -LogNumber 0, 1 -Text 'login failed' -After (Get-Date).AddDays(-1) |
    Sort-Object LogDate -Descending |
    Select-Object LogDate, SqlInstance, Source, Text |
    Export-Csv "$env:USERPROFILE\Desktop\SqlErrorLogSearch.csv" -NoTypeInformation -Encoding UTF8

Give it -LogNumber. Without it, dbatools reads every log number from 99 down to 0, and its filters run in PowerShell after the entries arrive (we read that in its source code), so a big log is sent over the network in full before -Text is applied.

Tested on SQL Server 2025 LocalDB (17.0.4025.3) on Windows 11: the location and permission queries ran as shown, and we read the definitions of sp_readerrorlog and sp_enumerrorlogs and the master grants there. LocalDB cannot run sp_readerrorlog itself (it fails with "Failed to open loopback connection"), so its arguments, permissions and archive numbering come from Microsoft's documentation. The GRANT and cycling statements were only parsed (SET PARSEONLY ON), not run. The PowerShell scripts were syntax-checked and their cmdlets and parameters compared with the SqlServer module and dbatools documentation; they were not run against servers.

Common errors

• "Caller does not have permissions to execute the stored procedure." This is message 27219, raised by sp_readerrorlog's own permission check. Grant VIEW ANY ERROR LOG (2022 and later) or VIEW SERVER STATE to the login, as shown above.
• "xp_readerrorlog() returned error 2, 'The system cannot find the file specified.'" There is no file for the log you asked for: the log number is higher than the number of archives kept, or you asked for the Agent log (product 2) on an instance without one. We got it on LocalDB, which has no SQL Server Agent. In the PowerShell script it shows up as a warning for that instance and log.
• "Failed to open loopback connection." We got this on LocalDB for sp_readerrorlog, xp_readerrorlog and sp_enumerrorlogs. LocalDB keeps its log in a file called error.log in the instance folder (the location query shows it), so open that file instead.
• A certificate error when connecting from PowerShell. Newer versions of the SqlServer module and of SSMS encrypt the connection and check the server's certificate by default. Microsoft's documentation for the module says version 22 defaults to Encrypt Optional and version 23 and later to Mandatory. The fix is a trusted certificate on the server; -TrustServerCertificate skips the check and is only reasonable on a network you trust.
• The search finds nothing, but the text is there. Check the product argument (see the trap above), the log number (the event may be in ERRORLOG.1 after a restart) and the spelling. In the PowerShell script, a single quote in the search text is doubled for you; in your own T-SQL, double it yourself.
• Very slow on one instance. That instance has a huge current log. Read only the logs you need, and cycle the log on a schedule as described above.

Where these methods stop

• Only the logs still on disk. With the default six archives, a few restarts in one bad night (patching, a failover, a crash loop) can push last week's log out before you look. Nothing in these methods keeps a copy.
• A search, not a history. Every run reads the servers again. Nothing tells you when a new error appears, and to compare this week with last week you keep the CSV files yourself.
• The error log is not everything. SQL Server Agent job history lives in msdb, not in the error log, and the same goes for backup history and Database Mail. The default trace, the system_health session and the Windows Event Logs hold more again. Each is a separate query, with its own permission.
• Permissions on every instance. The account needs its grant on each one, and an SSMS multi-server query hides the instances it could not reach.

Questions

Related

• Search Windows Event Logs on multiple servers at once
• Find failed SQL Server logins (error 18456) on all your servers
• LogTrawler: all your server logs in one searchable place
• Remote SQL Server DBA services
• All guides

Sources (Microsoft Learn): sp_readerrorlog, sp_cycle_errorlog, sp_cycle_agent_errorlog, Configure SQL Server error logs, View the SQL Server error log, Manage the SQL Server error log, SERVERPROPERTY, HAS_PERMS_BY_NAME, Server-level roles, Permissions (VIEW ANY ERROR LOG), GRANT server permissions, Execute statements against multiple servers simultaneously, Invoke-Sqlcmd, Get-SqlErrorLog, Set-SqlErrorLog, Azure Database Migration Service FAQ (xp_readerrorlog example). dbatools: Get-DbaErrorLog.