Tag Archives: SQL Error Log

How to break a SQL 2019 container on my laptop

Posted on by

Just a very quick post today. At the weekend I blogged about creating SQL 2019 containers with named volumes enabling you to persist your data and yesterday about creating a random workload using PowerShell and a big T-SQL script.

The interesting thing about creating workload is that you can break things 🙂

When I created a SQL 2019 container with the data files mapped to a directory on my laptops C Drive with a docker-compose like this 

version: '3.7'

services:
    2019-CTP23:
        image: mcr.microsoft.com/mssql/server:2019-CTP2.3-ubuntu
        ports:  
          - "15591:1433"
          - "5022:5022"
        environment:
          SA_PASSWORD: "Password0!"
          ACCEPT_EULA: "Y"
        volumes: 
          - C:\MSSQL\BACKUP\KEEP:/var/opt/mssql/backups
          - C:\MSSQL\DockerFiles\datafiles:/var/opt/sqlserver
          - C:\MSSQL\DockerFiles\system:/var/opt/mssql

restore the AdventureWorks database to use the /var/opt/sqlserver directory and run a workload after a while the container stops and when you examine the logs you find

I had a whole load of these errors

2019-04-02 20:48:24.73 spid58      Error: 17053, Severity: 16, State: 1.
2019-04-02 20:48:24.73 spid58      FCB::MakePreviousWritesDurable: Operating system error (null) encountered.
2019-04-02 20:48:24.74 spid58      Error: 9001, Severity: 21, State: 1.
2019-04-02 20:48:24.74 spid58      The log for database 'AdventureWorks2014' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
2019-04-02 20:48:25.05 spid58      Error: 9001, Severity: 21, State: 16.
2019-04-02 20:48:25.05 spid58      The log for database 'AdventureWorks2014' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
2019-04-02 20:48:25.06 spid52      Error: 9001, Severity: 21, State: 16.
2019-04-02 20:48:25.06 spid52      The log for database 'AdventureWorks2014' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.

Then some of these

019-04-02 20:55:16.26 spid53      Error: 17053, Severity: 16, State: 1.
2019-04-02 20:55:16.26 spid53      /var/opt/sqlserver/AdventureWorks2014_Data.mdf: Operating system error 31(A device attached to the system is not functioning.) encountered.

Then it went really bad 

2019-04-02 20:55:16.35 spid53      Error: 3314, Severity: 21, State: 3.
2019-04-02 20:55:16.35 spid53      During undoing of a logged operation in database 'AdventureWorks2014' (page (0:0) if any), an error occurred at log record ID (65:6696:25). Typically, the specific failure is logged previously as an error in the operating system error log. Restore the database or file from
a backup, or repair the database.
2019-04-02 20:55:16.37 spid53      Database AdventureWorks2014 was shutdown due to error 3314 in routine 'XdesRMReadWrite::RollbackToLsn'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.
Restart packet created for dbid 5.
2019-04-02 20:55:16.41 spid53      Error during rollback. shutting down database (location: 1).
after that it tried to restart the database
2019-04-02 20:55:16.44 spid53      Error: 3314, Severity: 21, State: 3.
2019-04-02 20:55:16.44 spid53      During undoing of a logged operation in database 'AdventureWorks2014' (page (0:0) if any), an error occurred at log record ID (65:6696:25). Typically, the specific failure is logged previously as an error in the operating system error log. Restore the database or file from
a backup, or repair the database.
2019-04-02 20:55:16.49 spid53      Error: 3314, Severity: 21, State: 5.
2019-04-02 20:55:16.49 spid53      During undoing of a logged operation in database 'AdventureWorks2014' (page (0:0) if any), an error occurred at log record ID (65:6696:1). Typically, the specific failure
is logged previously as an error in the operating system error log. Restore the database or file from a backup, or repair the database.
Restart packet processing for dbid 5.
2019-04-02 20:55:17.04 spid52      [5]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 0.
2019-04-02 20:55:17.06 spid52      Starting up database 'AdventureWorks2014'.

But that caused

2019-04-02 20:55:17.90 spid76      Error: 9001, Severity: 21, State: 16.
2019-04-02 20:55:17.90 spid76      The log for database 'master' is not available. Check the operating
system error log for related error messages. Resolve any errors and restart the database.

Master eh? Now what will you do?

2019-04-02 20:55:25.55 spid52      29 transactions rolled forward in database 'AdventureWorks2014' (5:0). This is an informational message only. No user action is required.
2019-04-02 20:55:25.90 spid52      1 transactions rolled back in database 'AdventureWorks2014' (5:0). This is an informational message only. No user action is required.
2019-04-02 20:55:25.90 spid52      Recovery is writing a checkpoint in database 'AdventureWorks2014' (5). This is an informational message only. No user action is required.
2019-04-02 20:55:26.16 spid52      Recovery completed for database AdventureWorks2014 (database ID 5) in 7 second(s) (analysis 424 ms, redo 5305 ms, undo 284 ms.) This is an informational message only. No user action is required.
2019-04-02 20:55:26.21 spid52      Parallel redo is shutdown for database 'AdventureWorks2014' with worker pool size [1].
2019-04-02 20:55:26.27 spid52      CHECKDB for database 'AdventureWorks2014' finished without errors on 2018-03-24 00:38:39.313 (local time). This is an informational message only; no user action is required.

Interesting, then back to this.

2019-04-02 21:00:00.57 spid51      Error: 17053, Severity: 16, State: 1.
2019-04-02 21:00:00.57 spid51      FCB::MakePreviousWritesDurable: Operating system error (null) encountered.
2019-04-02 21:00:00.62 spid51      Error: 9001, Severity: 21, State: 1.
2019-04-02 21:00:00.62 spid51      The log for database 'AdventureWorks2014' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
2019-04-02 21:00:00.64 spid51      Error: 9001, Severity: 21, State: 16.

It did all that again before

This program has encountered a fatal error and cannot continue running at Tue Apr  2 21:04:08 2019
The following diagnostic information is available:

       Reason: 0x00000004
      Message: RETAIL ASSERT: Expression=(false) File=Thread.cpp Line=4643 Description=Timed out waiting for thread terminate/suspend/resume.
   Stacktrace: 000000006af30187 000000006af2836a 000000006ae4a4d1
               000000006ae48c55 000000006af6ab5e 000000006af6ac04
               00000002809528df
      Process: 7 - sqlservr
       Thread: 129 (application thread 0x1e8)
  Instance Id: 215cfcc9-8f69-4869-9a52-5aa44a415a83
     Crash Id: 53e98400-33f1-4786-98fd-484f0c8d9a7e
  Build stamp: 0e53295d0e1704ae5b221538dd6e2322cd46134e0cc32be49c887ca84cdb8c10
 Distribution: Ubuntu 16.04.6 LTS
   Processors: 2
 Total Memory: 4906205184 bytes
    Timestamp: Tue Apr  2 21:04:08 2019

Ubuntu 16.04.6 LTS
Capturing core dump and information to /var/opt/mssql/log...
/usr/bin/find: '/proc/7/task/516': No such file or directory
dmesg: read kernel buffer failed: Operation not permitted
No journal files were found.
No journal files were found.
Attempting to capture a dump with paldumper
WARNING: Capture attempt failure detected
Attempting to capture a filtered dump with paldumper
WARNING: Attempt to capture dump failed.  Reference /var/opt/mssql/log/core.sqlservr.7.temp/log/paldumper-debug.log for details
Attempting to capture a dump with gdb
WARNING: Unable to capture crash dump with GDB. You may need to
allow ptrace debugging, enable the CAP_SYS_PTRACE capability, or
run as root.

failing to capture it’s dump!! Oops 🙂

I had to recreate the containers without using the named volumes and then I could run my workload 🙂

Nothing particularly useful about this blog post other than an interesting look at the error log when things go wrong 🙂

Searching the SQL Error Log with PowerShell

Posted on by

Another post in the PowerShell Box of Tricks series. Here is another script which I use to save me time and effort during my daily workload enabling me to spend more time on more important (to me) things!

Yesterday we looked at Reading Todays SQL Error Log Today we are going to search all* of the SQL Error Logs. This is usually used by DBAs to troubleshoot issues

The SQL Server Error Logs (by default) are located in the folder Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and are named as ERRORLOG.n files. The most recent has no extension the rest 1 to 6.

Using PowerShell you can easily find the location of the SQL Error Log using the ErrorLogPath Property

image

You can also read it with PowerShell using the ReadErrorLog Method. This has the following properties LogDate, Processinfo and Text. You can easily filter by any of those with a bit of PowerShell 🙂

I have created a function which takes two parameters $SearchTerm and $SQLServer adds *’s to the Search Term to allow wildcards and searches each of the SQL Error Logs

image

Simply call it like this and use the results as needed

image

Of course, as the results are an object you can then carry on and do other things with them

image

The code can be found here

#############################################################################################
#
# NAME: Search-SQLErrorLog.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com
# DATE:22/07/2013
#
# COMMENTS: Load function for Searching SQL Error Log and exporting and displaying to CSV
# ————————————————————————

Function Search-SQLErrorLog ([string] $SearchTerm , [string] $SQLServer) {
 
    $FileName = 'c:\TEMP\SQLLogSearch.csv'
    $Search = '*' + $SearchTerm + '*'
    $server = new-object "Microsoft.SqlServer.Management.Smo.Server" $SQLServer
    $server.ReadErrorLog(5)| Where-Object {$_.Text -like $Search} | Select LogDate, ProcessInfo, Text |Export-Csv $FileName
    $server.ReadErrorLog(4)| Where-Object {$_.Text -like $Search} | Select LogDate, ProcessInfo, Text |ConvertTo-Csv |Out-File $FileName -append
    $server.ReadErrorLog(3)| Where-Object {$_.Text -like $Search} | Select LogDate, ProcessInfo, Text |ConvertTo-Csv |Out-File $FileName -append
    $server.ReadErrorLog(2)| Where-Object {$_.Text -like $Search} | Select LogDate, ProcessInfo, Text |ConvertTo-Csv |Out-File $FileName -append
    $server.ReadErrorLog(1)| Where-Object {$_.Text -like $Search} | Select LogDate, ProcessInfo, Text |ConvertTo-Csv |Out-File $FileName -append
    $server.ReadErrorLog(0)| Where-Object {$_.Text -like $Search} | Select LogDate, ProcessInfo, Text |ConvertTo-Csv |Out-File $FileName -append
    Invoke-Item $filename
}

* Technically we are only searching the default number of 7 but if your environment is different you can easily add the lines to the function

Reading Todays SQL Error Log With PowerShell

Posted on by

Todays post from my PowerShell Box of Tricks series is about the SQL Error Log.

DBAs need to read the error log for many reasons and there are different ways to do it. sp_readerrorlog, xp_readerrorlog, using SSMS opening the file in notepad. I’m sure every DBA has their own favourite. This one is mine.Of course, it uses PowerShell

It is very simple as there is a method on the server property called ReadErrorLog.

In this function I read the latest Error Log and filter it for the last 24 hours using the Get-Date cmdlet and the AddDays Method

image

Here is the output

image

You can also save the output to a text file and open it by piping the function to Out-File

Show-LatestSQLErrorLog fade2black|Out-File -FilePath c:\temp\log.txt
c:\temp\log.txt

or send it by email

image

or as an attachment

image

PowerShell is cool.

The code can be found here Show-Last24HoursSQLErrorLog

#############################################################################################
#
# NAME: Show-Last24HoursSQLErrorLog.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com
# DATE:22/07/2013
#
# COMMENTS: Load function for reading last days current SQL Error Log for Server
# ————————————————————————
Function Show-Last24HoursSQLErrorLog ([string]$Server) {                      
    $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server 
    $logDate = (get-date).AddDays(-1)
    $Results = $srv.ReadErrorLog(0) |Where-Object {$_.LogDate -gt $logDate}| format-table -Wrap -AutoSize 
    $Results         
}

Checking SQL Error Logs, Event Logs and Stopping Services with Powershell

Posted on by

It was patching time this week at MyWork so I thought I would share some Powershell scripts I use to speed up the process.

I keep these in their own folder and cd to it. Then I can just type the first few letters and tab and Powershell completes it. Nice and easy and time saving

The first thing I do is to stop the SQL services with the StopSQLServices.ps1

Get the server name with Read-Host then I like to see the before and after using

get-service -ComputerName $server|Where-Object { $_.Name -like '*SQL*' }

This uses the Get-service CMDlet to find the services with SQL in the name and display them. Then we pass the running services to an array and use the stop method with a while to check if the services are stopped before displaying the services again. Note this will stop all services with SQL in the name so if for example you are using Redgates SQL Monitor it will stop those services too. If that could be an issue then you may need to alter the where clause. As always test test test before implementing in any live environment.

Once the services are stopped I RDP using the RDP script which again uses Read-host to get a server and then opens up a RDP with a simple Invoke-Command. This means I can stay in Powershell.

Then I patch the server and reboot using the ping script to set up a continuous ping.

If you want to install Windows Updates via Powershell you can use the details here. I like to jump on the box to keep an eye on it.

To check the event log The EventLog.ps1 script is very simple

Get-EventLog  -computername $server -log $log -newest $latest | Out-GridView

Enter the server name and then application or system and it will display the results using out-gridview which will allow you to filter the results as required. I have another version of this script with a message search as well.

You can simply add where {$_.entryType -match “Error”} if you only want the errors or Warning for the warnings. I like to look at it all.

Check the SQL error log with this script which uses the SMO method

$Server = Read-Host "Please Enter the Server" 
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server  
$Results = $srv.ReadErrorLog(0) | format-table -Wrap -AutoSize  
$Results

I love these four lines they make it so easy for me to look at the SQL error log whenever I need to. If you want you can pipe to Out-GridView or even to notepad. If I want to check one of the previous error logs I change ReadErrorLog(0) to ReadErrorLog(1) or 2 or 3 etc. I have a daily script which emails me any SQL error log errors and DBCC errors every day so I am aware of any issues before

Then the AutoServices.ps1 to show the state of the auto start services. Strangely you cannot get the Start Type from Get-Service so I use Get-WMIObject. If any have failed to start then I use Get-Service to get the service  and pipe to Start-Service

This is what works for me I hope it is of use to you

Please don’t ever trust anything you read on the internet and certainly don’t implement it on production servers without first both understanding what it will do and testing it thoroughly. This solution worked for me in my environment I hope it is of use to you in yours but I know nothing about your environment and you know little about mine

<# 
.NOTES 
    Name: StopSQLServices.ps1 
    Author: Rob Sewell http://sqldbawithabeard.com
    Requires: 
    Version History: 
                    Added New Header 23 August 2014
    
.SYNOPSIS 
    
.DESCRIPTION 
    
.PARAMETER 
    
.PARAMETER 

.PARAMETER 

.EXAMPLE 
#> 
#############################################################################################
#
# NAME: StopSQLServices.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com @fade2blackuk
# DATE:15/05/2013
#
# COMMENTS: This script will stop all SQL Services on a server
# ------------------------------------------------------------------------

$Server = Read-Host "Please Enter the Server - This WILL stop all SQL services"

Write-Host "###########  Services on $Server BEFORE  ##############" -ForegroundColor Green -BackgroundColor DarkYellow
get-service -ComputerName $server|Where-Object { $_.Name -like '*SQL*' }Write-Host "###########  Services on $Server BEFORE  ##############" -ForegroundColor Green -BackgroundColor DarkYellow
## $Services = Get-WmiObject Win32_Service -ComputerName $server|  Where-Object { $_.Name -like '*SQL*'-and $_.State-eq 'Running' }
$Services = Get-Service -ComputerName $server|Where-Object { $_.Name -like '*SQL*' -and $_.Status -eq 'Running' }

foreach ($Service in $Services) {

    $ServiceName = $Service.displayname
    (get-service -ComputerName $Server  -Name $ServiceName).Stop()
    while ((Get-Service -ComputerName $server -Name $ServiceName).status -ne 'Stopped')
    {<#do nothing#>}
}
Write-Host "###########  Services on $Server After  ##############" -ForegroundColor Green -BackgroundColor DarkYellow
Get-Service -ComputerName $server|Where-Object { $_.Name -like '*SQL*' }
Write-Host "###########  Services on $Server After  ##############" -ForegroundColor Green -BackgroundColor DarkYellow
 

#############################################################################################
#
# NAME: RDP.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com @fade2blackuk
# DATE:15/05/2013
#
# COMMENTS: This script to open a RDP
# ------------------------------------------------------------------------

$server = Read-Host "Server Name?"
Invoke-Expression "mstsc /v:$server"
#############################################################################################
#
# NAME: Ping.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com @fade2blackuk
# DATE:15/05/2013
#
# COMMENTS: This script to set up a continous ping 
# Use CTRL + C to stop it
# ------------------------------------------------------------------------

$server = Read-Host "Server Name?"
Invoke-Expression "ping -t $server"
#############################################################################################
#
# NAME: SQLErrorLog.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com @fade2blackuk
# DATE:15/05/2013
#
# COMMENTS: This script will display the SQL Error Log for a remote server
# ------------------------------------------------------------------------
$Server = Read-Host "Please Enter the Server" 
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server  
$srv.ReadErrorLog(0) | Out-GridView 

#############################################################################################
#
# NAME: Autoservices.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com @fade2blackuk
# DATE:15/05/2013
#
# COMMENTS: # Script to show the services running that are set to Automatic startup - 
# good for checking after reboot
# ------------------------------------------------------------------------

$Server = Read-Host "Which Server?"

Get-WmiObject Win32_Service -ComputerName $Server  |  
Where-Object { $_.StartMode -like 'Auto' }| 
Select-Object __SERVER, Name, StartMode, State | Format-Table -auto
Write-Host "SQL Services"
Get-WmiObject Win32_Service -ComputerName $Server  |  
Where-Object { $_.DisplayName -like '*SQL*' }| 
Select-Object __SERVER, Name, StartMode, State | Format-Table -auto