Supporting SQL (what is in my Monday morning notepad)

In my role supporting hundreds of SQL clients, each with multiple servers, Monday morning can involve triaging over thirty 3rd line tickets.

If a backup fails over the weekend, it must be rectified on Monday so that Monday night's backup doesn't fail again. This involves some rapid diagnosis and resolution at a high speed. One way to assist is using the notepad with a few key commands to get the initial thirty tickets down to zero by 17:30.

The secret is the Monday Morning Notepad file with some tools to close off the quick wins. An example is the server backup failure due to a reboot caused by a Windows update. You should identify this in minutes.

So here is my Monday morning notepad. (It's a notepad; it's unstructured and not a fix-all; it's a quick win to get tickets down from thirty to zero on a Monday morning.)

1. EXEC sys.xp_readerrorlog 0, 1, N'The tempdb database has'  (  )

2. Log drive full > change database to simple recovery and back to Full recovery and shrink log file

3. Large tempDB > restart SQL service > set tempDB all to the same size

4. Long-running write process > when killed, will take the same amount of time to roll back

5. Low disk space > Check free space in the database, remove old backups, expand the drive

6. SQL performance issues > run DMV checks CPU, RAM, Disk I/O

7. SQL server will not start> Drives space, Drive not disconnected, Wrong Password

8. Locking and blocking > locate lead blocker, sentryone ,DMVs, blocker spid , read or write blocker, how long blocker process has been running, kill lead blocker

Quick check SQL error log

SP_READERRORLOG 0, 1

SP_READERRORLOG 1, 1

Quick check sql agent log

SP_READERRORLOG 0, 2

SP_READERRORLOG 1, 2

Get-ClusterLog -Destination c:\diag

Cluster Config

Get-Cluster|Format-List-Property*

Get-ClusterNode

Get-ClusterResource

--who restarted sql server (no results if no errors in system log after running in -scope process mode)

Get-EventLog System -Newest 10000 | `

        Where EventId -in 41,1074,1076,6005,6006,6008,6009,6013 | `

        Format-Table TimeGenerated,EventId,UserName,Message -AutoSize -wrap

--extended events search

Get-EventLog System -Newest 10000 | `

        Where EventId -in 41,1074,1076,6005,6006,6008,6009,6013,24053,24054,24056,24057,24082,4725,4726,4740 | `

        Format-Table TimeGenerated,EventId,UserName,Message -AutoSize -wrap

--if power shell is restriced use below to get a workarround.

Get-ExecutionPolicy -List

Get-ExecutionPolicy -Scope Process

Get-ExecutionPolicy -Scope CurrentUser

Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope Process

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope Process

Set-ExecutionPolicy -ExecutionPolicy AllSigned -Scope Process

Set-ExecutionPolicy -ExecutionPolicy Restricted -Scope Process

Windows update list

wmic qfe list

wmic qfe list full

wmic qfe list brief

wmic qfe list full /format: table 

Check if Windows Update service is running 

get-service wuauserv

Most Common Wait Types in SQL Server and What They Mean

ASYNC_NETWORK_IO—The async_network_io wait types point to network-related issues. Engage the network team if there’s a long distance between servers, or the application team to check on application/server resources.

CXPACKET—This wait type is involved in parallel query execution and indicates the SPID is waiting on a parallel process to complete or start. Excessive CXPacket waits may indicate a problem with the WHERE clause in the query. Look at researching and changing Maximum Degree of Parallelism (MAXDOP).

DTC—This wait type is not on the local system. When using Microsoft Distributed Transaction Coordinator (MS-DTC), a single transaction is opened on multiple systems at the same time, and the transaction cannot be concluded until it’s been completed on all of the systems.

LCK_M*— Review the day and time the locking occurred and which SQL statements were being executed. Tuning these statements will reduce the session holds on the locks.

NETWORKIO—wait types can point to network-related issues, but most often are caused by a client application not processing results from the SQL Server quickly enough.

OLEDB—SPID is waiting for remote procedure calls, linked server queries, BULK INSERT commands, or full-search queries.

PAGEIOLATCH_*— SQL Server is waiting to read a data file page or workload from storage. These pages and workloads were not cached in memory and need to be retrieved from the disk. Additional memory will help prevent pages from getting pushed out.

SOS_SCHEDULER_YIELD—query needs more CPU time. Check the Max Degree of Parallelism (MAXDOP) to ensure proper core usage. Ensure high CPU performance from both within Windows and the system BIOS.

WRITELOG—Disabling unused indexes will help, but the disk is the bottleneck here, and the transition log should be moved to more appropriate storage.

#####################################################

As an added tip, Brent Ozar's First responder kit can give you an overview of SQL server health if installed in advance.