Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Posts Tagged ‘parameters’

Tips and Tricks: Useful Parameters of Get-ClusterLog

Posted by blakhani on May 20, 2016


Working with Root Cause Analysis (RCA) is also part of my work at Microsoft. In case of cluster failover RCA, it is very important to get cluster log. Sometimes there are situation where we want to generate cluster log for last few minutes for quicker analysis of live issues. This blog explains some common parameters which I used in my day-to-day troubleshooting.

I have 4 nodes cluster in my lab named SRV1, SRV2, SRV3, SRV4.

  • Default command – generates Cluster.log file on ALL nodes in C:\Windows\Cluster\Reports folder. File name would be Cluster.log

Get-ClusterLog 

  • if we want the cluster log to be generated for specific node(s) then we can use –Node parameter. We can put comma separated node names as shown below.

Get-ClusterLog -Node SRV1, SRV3

  • You might know that the time shown in cluster log is UTC be default. Sometimes its difficult to translate UTC time to local time, specially for time-zones which has daylight saving. Luckily, cluster log can be generated in local time using parameter UseLocalTime . Here is the sample code.

Get-ClusterLog –UseLocalTime

  • Another useful parameter is to copy the files to specific location. This command would generate logs and also dump on specified location. in below example, I am dumping logs from all nodes to C:\Temp folder.

Get-ClusterLog –Destination “C:\Temp”

  • TimeSpan is another parameter which can generate cluster log for last number of minutes specified. By default it would generate Cluster.log for complete time. I find it useful when I repro’ed a problem and I want to look at cluster log for last 2 to 3 minutes. Here is the command to generate log for last 3 minutes.

Get-ClusterLog –TimeSpan 3

So, this is my favorite command after reproducing cluster issue on local node.

Get-ClusterLog -Node SRV1 -TimeSpan 2 -UseLocalTime -Destination C:\

Hopefully it would be useful.

Cheers,
Balmukund

Posted in Cluster, Tips and Tricks, Troubleshooting | Tagged: , , | 2 Comments »

SQL Internals : Useful Parameters for xp_readerrorlog

Posted by blakhani on December 10, 2014


I know that most of you have been using sp_readerrorlog for long time. While working with SSMS, I noticed that we have options to put filters on various parameter. I became curious to know what is done in the background.

If we look at code of sp_readerrorlog using sp_helptext system procedure, this is the output.

create proc sys.sp_readerrorlog(
    @p1        int = 0,
    @p2        int = NULL,
    @p3        nvarchar(4000) = NULL,
    @p4        nvarchar(4000) = NULL)
as
begin

    if (not is_srvrolemember(N'securityadmin') = 1)
    begin
       raiserror(15003,-1,-1, N'securityadmin')
       return (1)
    end
    
    if (@p2 is NULL)
        exec sys.xp_readerrorlog @p1
    else
        exec sys.xp_readerrorlog @p1,@p2,@p3,@p4
end


Which means that under the cover, xp_readerrorlog is being used by sp_readerrorlog. Coming back to SSMS, here is the snip of information taken from profiler while launching Log File Viewer. I have done some formatting to make it look proper.

-- Below is the way SSMS Reads the ErrorLog. Can be found via profiler

Declare @InstanceName nvarchar(4000),
        @ArchiveID int,
        @Filter1Text nvarchar(4000),
        @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,
        @ArchiveID=0,
        @Filter1Text='recovery',
        @Filter2Text='mydb',
        @FirstEntry='2014-11-22 07:14:46.930',
        @LastEntry='2014-11-22 07:14:50.020'

EXEC master.dbo.xp_readerrorlog @ArchiveID, 1, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'asc', @InstanceName 

 

That gives us a lot of hint about the parameters of xp_readerrorlog. Here is the explanation.

Parameter Name Usage
@ArchiveID Extension of the file which we would like to read.

0 = ERRORLOG/SQLAgent.out

1 = ERRORLOG.1/SQLAgent.1  and so on

@LogType 1 for SQL Server ERRORLOG (ERRORLOG.*)

2 for SQL Agent Logs (SQLAgent.*)
@FilterText1 First Text filter on data
@FilterText2 Another Text filter on data. Output would be after applying both filters, if specified
@FirstEntry Start Date Filter on Date time in the log
@LastEntry End Date Filter on Date time in the log
@SortOrder ‘asc’ or ‘desc’ for sorting the data based on time in log.

 

Sample with explanation

I have customized the script so that you can copy paste in your environment and put the values.

Declare @InstanceName nvarchar(4000),
        @LogType int,
        @ArchiveID int,
        @Filter1Text nvarchar(4000),
        @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,          -- Don't know 🙂
        @LogType = 2,                -- File Type (1 = ERRORLOG OR 2 = SQLAgent)
        @ArchiveID=3,                -- File Extension (0 = Current i.e. ERRORLOG or SQLAgent.out, 1 = ERRORLOG.1 or SQLAgent.1 and so on)
        @Filter1Text='Waiting',      -- First Text Filter
        @Filter2Text=NULL,           -- Second Text Filter
        @FirstEntry=NULL,            -- Start Date
        @LastEntry=NULL              -- End Date

EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'asc', @InstanceName 

Here are few samples

Example 1

Read ERRORLOG and apply filter for ‘login failed’ and ‘sa’

Declare @InstanceName nvarchar(4000),
        @LogType int,
        @ArchiveID int,
        @Filter1Text nvarchar(4000),
        @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,          -- Don't know 🙂
        @LogType = 1,                -- 1 = ERRORLOG
        @ArchiveID=0,                -- File Extension 0 
        @Filter1Text='Login failed', -- First Text Filter
        @Filter2Text='sa',           -- Second Text Filter
        @FirstEntry=NULL,            -- Start Date
        @LastEntry=NULL              -- End Date

EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'asc', @InstanceName 

Here is the output

 

Example 2

In above example, let’s apply date time filer as well. We will filter records only for 05-Dec-2014.

Declare @InstanceName nvarchar(4000),
        @LogType int,
        @ArchiveID int,
        @Filter1Text nvarchar(4000),
        @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,                  -- Don't know 🙂
        @LogType = 1,                        -- 1 = ERRORLOG
        @ArchiveID=0,                        -- File Extension 0 
        @Filter1Text='Login failed',         -- First Text Filter
        @Filter2Text='sa',                   -- Second Text Filter
        @FirstEntry='2014-12-05 00:00:00',   -- Start Date
        @LastEntry='2014-12-05 23:59:59'     -- End Date

EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'asc', @InstanceName 

As expected, the output is similar to Example 1 but with date filter applied.

Example 3

In this example, we are trying to read Read SQLAgent.3 (i.e. LogType = 2 and ArchiveID = 3) and output should be descending date time.

Declare @InstanceName nvarchar(4000),
        @LogType int,
        @ArchiveID int,
        @Filter1Text nvarchar(4000),
        @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,    -- Don't know 🙂
        @LogType = 2,          -- 2 = SQLAgent
        @ArchiveID=3,          -- File Extension 3 
        @Filter1Text=NULL,     -- First Text Filter
        @Filter2Text=NULL,     -- Second Text Filter
        @FirstEntry=NULL,      -- Start Date
        @LastEntry=NULL        -- End Date

EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'DESC', @InstanceName

Here is the output.

Hope this gives you an understanding of neat way of reading SQL Server ERRORLOG and SQLAgent logs.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Posted in Internals, SQL Server | Tagged: , , , | 3 Comments »