Task Manager, show Disk Performance = diskperf -Y

 Enable Disk Performance Monitor

Ensure the Task Manager is closed. Open the Command Prompt with Elevated permission. run the following command: diskperf -Y

MSDB transaction log was full due to 'CHECKPOINT' despite disk has space. Had to rebuild from scratch

Today I came across something new in my 20 years of DBA life - MSDB transaction log was full due to 'CHECKPOINT'

Due to the secure environment I'm not able to copy-and-paste out anything but you'll get the idea

Issue

• We had a customer disk running out of space, at 0 bytes, it caused everything to stop
• After some cleanup and emergency actions
• Removed pagefile
• Emptied Recycle Bin and disabled saving space for the drives
• We managed to free up 20~80GB+ space, yet the SQL Agent still won't start up, and the error log/event view shows - MSDB transaction log was full due to 'CHECKPOINT'

Things Tried (and did NOT work)

• Obviously restart SQL Server and Agent
• Put MSDB into Emergency mode - nope, not allowed
• Rebuild Log
• Backup Log
• Adding new Log file to MSDB
• Change MSDB recovery model (from SIMPLE to FULL or anything)
• Reboot the physical server
• ^ all above made sense because the LOG is full and cannot do any action

Resolution

• Had to rebuild MSDB using this method
• https://sites.google.com/site/sqlservermsdn/rebuild-msdb-database
• We lost the Backup plan, all SQL Jobs
• had to re-create Backup plan using Maintenance Wizard
• created the jobs elsewhere and paste and re-create

Things to Do AFTER or Prepare beforehand

• Script out your SQL Jobs if possible
• Backup if possible, for restore AFTER the rebuild
• AFTER - had to re-grant permissions to MSDB on SQL Logins

Every Query Plan Operator Listed and Explained

 Every SQL Server DBA needs to bookmark this, myself included :)

Every Query Plan Operator Listed and Explained

https://sqlserverfast.com/epr/operator-list/

SQL & SSMS Tricks

Just posting some tricks I've used or just learned

http://www.e-squillace.com/ssms-tricks-shortcuts/

SSMS Tricks or Options I like

• ALT + SHIFT =multi-line select > cool
• Options > Text Editor > All Languages > Scroll Bars > Use map mode for vertical scroll bar > cool
• Splitting query windows
• Options > Text Editor > All Languages > check Line Numbers
• Dark theme/Font
• https://sqlstudies.com/2018/04/04/ssmss-dark-theme/
• or Dark fonts only https://sqlstudies.com/2015/11/16/ssms-setup/
• Making SSMS Pretty : My Dark Theme - SentryOne Team Blog
• https://github.com/tcartwright/SSMSThemeEditor
• 
  
• Options > keyboard > Keyboard: search command Query.ChangeConnection , press shortcut keys ALT + G, and Assign
• Options > keyboard > Query shortcuts; Set to anything you want. In future, highlight a table name (with or without schema) and CTRL+3. these are my settings
• ALT + F1 = sp_help
• CTRL + F1 = sp_helptext
• CTRL + 1 sp_who
• CTRL + 2 sp_lock
• CTRL + 3 SELECT TOP 100 * FROM 
• CTRL + 4 SELECT COUNT(*) FROM
• CTRL + 5 SELECT * FROM
• CTRL + 6
• CTRL + 7 sp_helpdb
• CTRL + 8 USE <database name>
• CTRL + 9 sp_helptext
• CTRL + 0 sp_whoisactive
• CTRL + SHIFT + R to refresh IntelliSense
• ALT + SHIFT + T to move down 1 line
• CTRL + SHIFT + ENTER to create new empty line below
• Options > Query Execution > Advanced - uncheck SET ARITHABORT
• Options > Query Results - Play the Windows default beep
• Change Environment (Object Explorer font) to go bigger
• Script out permissions in SSMS
• In SSMS (Management Studio) you need to go to Tools-->Options  then look for "SQL Server Object Explorer" and expand it and go to "Scripting".Then look for Object scripting options and change "Script Permissions" to "True".

Starting SSMS with a specific connection and script file - SQLServerCentral

Configuring SSMS for presenting - Paul S. Randal

SSMS Shortcut Keys
Bookmark - CTRL K, K to bookmark, N for Next, P for Previous
ALT+F8 to open Object Explorer
SHIFT+F1 on a keyword/object (or DMV) to open up in browser

SSMS Add-Ons (FREE)

• Red Gate SQL Search
• APEX SQL Complete - free SQL complete
• APEX SQL Refactor - free SQL Formatter
• APEX SQL Search
• Poor SQL http://poorsql.com/  - SQL Formatter

SQL Code tricks

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

GO

--instead of (NOLOCK) on every table

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

GO

-- /* First line. Removing the two dashes activates the block comment

SELECT 

patientname,

Patientid,

Language

FROM whatevertable

Where name = 'Whatever'

-- */ Last line. When the block comment is on, this terminates it


Resources
http://www.sqlservercentral.com/articles/Management+Studio+(SSMS)/160267/ 

DBA Salary

Sharing interesting info as I was reading Brent's blog

- at least I was doing pretty OK in Canada, relatively
- DBA in Canada do earn (much less) than USA

What Should We Change About the Data Professional Salary Survey for 2019?
The 2018 Data Professionals Salary Survey Results - Brent Ozar Unlimited®
Analyzing Salary Data with Power BI and R – Part 1 |

Enable Telnet in Windows 10

Telnet is off by default in Windows 10

How to enable it?
Open Command prompt as Admin
Run this code - dism /online /Enable-Feature /FeatureName:TelnetClient
Voila!

Do it via GUI
https://social.technet.microsoft.com/wiki/contents/articles/38433.windows-10-enabling-telnet-client.aspx

SSMS XEvent Profiler to read Extended Events

I'm learning the new SQL Profiler, found in SSMS 17.3+, called XEvent Profiler which uses Extended Events


Resources
Overview of XEvent Profiler in SQL Server Management Studio

Inside the XEvent Profiler - SQL Hammer | SQL Hammer

I didn't know this - DBCC CLONEDATABASE

To copy a database for debug/troubleshoot - but only copy structure & stats

DBCC CLONEDATABASE(SourceDB, TargetDB);

SQL 2014 and above

https://www.itprotoday.com/data-privacy/why-you-should-be-using-dbcc-clonedatabase-performance-tuning-queries

How to use DBCC CLONEDATABASE to generate a schema and statistics only copy of a user database in SQL Server 2014 SP2 and SQL Server 2016 SP1

List All Groups, DisplayName, ServerName in CMS (Central Management Server)

If you use CMS
If you have a large group of servers and can't memorize all the names/IPs
Use below code to show them all (and copy to Excel to format)

Connect to your CMS directly (not as a group) and run below code (using Recursion CTE)

WITH MyCTE
AS (
--root, anchor
SELECT server_group_id, name, G.parent_id, ParentName = CAST('' AS sysname), 1 AS Grouplevel
FROM msdb.dbo.sysmanagement_shared_server_groups_internal G
WHERE is_system_object <>1 AND parent_id = 1
UNION ALL
--child
SELECT G.server_group_id, G.name, G.parent_id, parent.name, parent.Grouplevel+1
FROM msdb.dbo.sysmanagement_shared_server_groups_internal G
INNER JOIN MyCTE AS parent ON G.parent_id = parent.server_group_id
)
SELECT
G.grouplevel, Parent = CASE G.ParentName WHEN '' THEN G.name ELSE G.ParentName END, Child = CASE G.ParentName WHEN '' THEN '' ELSE G.name END
,G.name, G.ParentName
,svr.name AS 'Display Name',svr.server_name AS 'Server Name'
FROM MyCTE  G
LEFT OUTER JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal svr ON G.server_group_id = svr.server_group_id
WHERE 1 = 1
ORDER BY
GroupLevel ASC, Parent, Child, [Display name]

Testing SQL connection using file.udl trick

Besides the usual
>telnet SQLname 1433

One can create a file and open it (note: show the extention in your File Explorer settings)
TestDatabase.udl 


Source
https://dougrathbone.com/blog/2013/11/18/testing-connectivity-to-microsoft-sql-server-without-any-tools-installed

Set Default template for SSMS New Query button by editing SQLFile.sql

1. Go to SQL install path, for me it's "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql"
2. Edit the SQLFile.sql in Admin mode
3. Type in whatever you want for default
4. Save and try in SSMS [New Query] button

Source
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b56b9ed6-1786-4ad6-87c9-7e4e81068672/how-to-set-the-default-query-for-the-ssms-new-query-button?forum=transactsql

SQL Cluster Registry Checkpoint

Error
[sqsrvres] GetRegKeyAccessMask: Could not get registry access mask for registry key Software\Microsoft\Microsoft SQL Server\MSSQL10.HA5\Replication (status 2)).

Fix - Populate the registry to the other node

https://www.mssqltips.com/sqlservertip/3440/registry-check-pointing-in-a-windows-cluster-to-bring-sql-server-online/

https://sqlserver-help.com/2015/04/17/information-checkpoint-in-sql-server-cluster-resources/

> cluster res /checkpoints

Find your resource name to replace below
Run in cmd (Admin)

cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\Cluster" 

cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\MSSQLServer" 

cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\Replication" 

cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\Providers" 

cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\SQLServerSCP" 

cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\CPE" 

cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\SQLServerAgent"

2008 Cluster install fails on newer Windows 2012/2016

Tried to install SQL 2008 R2 on Windows 2016 = fails, cluster verification fails
SQL 2014 was a-okay

PowerShell (Admin mode)
Install-WindowsFeature -Name RSAT-Clustering-AutomationServer

Error to install SQL Server 2008 on Windows 2012 – Marcelo's Spaces
https://marcelodba.wordpress.com/2014/08/17/error-to-install-sql-server-2008-on-windows-2012/

https://manjusullad.wordpress.com/2015/11/26/unable-to-install-sql-2008-on-windows-server-2012-failover-cluster/

SQL Server 2008 Add Cluster Node workarounds

I haven't installed/added SQL 2008 cluster node for so long, but just today I had to use 2 workarounds just to get it working. Whew

First workaround, Cluster Validation was good but SQL installer still complained
https://support.microsoft.com/en-ca/help/953748/error-message-when-you-install-sql-server-2008-on-a-windows-server-200

Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode


Second Workaround is actuall a MS Bug, where I had to comment out the pre-saved license key in the installer folder and entered it manually instead
https://digitaljive.wordpress.com/2009/06/22/%E2%80%9Cthe-current-sku-is-invalid%E2%80%9D-when-adding-second-node-to-sql-2008-cluster/

https://support.microsoft.com/en-nz/help/957459/fix-error-message-when-you-try-to-add-a-second-node-to-a-sql-server-20

Basically go to \x64 folder and modify the Defaultsetup.ini file to comment out the serial number (and paste it in during install)

;SQLSERVER2008 Configuration File

[SQLSERVER2008]

;PID="XXXXXXXXXXXXX"

Create SQL Server Alias using CliConfg.exe if you don't have SQL Configuration Manager

source:
https://sqlandme.com/2011/05/05/create-sql-server-alias-cliconfg-exe/

Set DEADLOCK_PRIORITY to update Database in Single_User mode

If you need to access a Database in Single_user mode, to either take it offline or change to MULTI_USER, change the DEADLOCK_PRIORITY so your process does not get picked as deadlock victirm.

SET DEADLOCK_PRIORITY HIGH

--KILL 58   -- kill the session using the database

ALTER DATABASE [DB_NAME] SET OFFLINE WITH ROLLBACK IMMEDIATE

--ALTER DATABASE [DB_NAME] SET MULTI_USER WITH ROLLBACK IMMEDIATE

SET DEADLOCK_PRIORITY NORMAL

Unable to manage cluster using failover cluster manager = Start the Server service

I had trouble connecting to my cluster in Failover Cluster Manager today and Google found me this

Easy resolution
Open services console and start the Server service.

Unable to manage cluster using failover cluster manager. Error Received: "Connection to the cluster is not allowed since you are not an administrator on the clu...

Changing the default SQL Server backup folder, using SSMS Facet

I found out that I cannot input a file share location during SQL Server 2014 installation, under Database Engine Configuration -> Data Directories tab -> Backup directory (I could do it in 2008 for sure)
e.g. \\fileserver\sql_backup

Therefore I had to input a local Backup path, and change the setting after install

I always thought I had to hack the registry (something like changing the key value in Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer, value: BackupDirectory)

But instead there's an easier way, by using SSMS -> right-click and choose "Facet"

Source:
The old registry way - Changing the default SQL Server backup folder
The NEW Facet way - Changing the default SQL Server backup folder

The application is unable to start correctly 0xc0000005 after windows update >> Uninstall Microsoft Update

I don't know how this Windows Update got by Microsoft QA but be warned - it has caused issue on 2 of our Windows 2008 R2 Enterprise servers.

I had to uninstall the KB and reboot.
FYI: I didn't do the strikeout steps, and both reboot times were normal and not long

the application is unable to start correctly 0xc0000005 after windows update

1. Disconnect from the internet  

2. Open Control Panel

3. Open Program & Features

4. Click "View installed updates" on the left-hand side

5. Find "Security update for Microsoft Windows (KB2859537)" under the Microsoft Windows section

6. Uninstall that update and restart you PC (may take a very-VERY long time to configure)

7. Go to Windows Updates and hide  (KB2859537)

8. Reconnect to the internet

9. Fix your System Restore feature; You might have to remove a fake C: drive and enable System Restore on the correct Windows drive.

Use [Ctrl+Alt+End] in RDP session to change the password, instead of [Ctrl+Alt+Del]

Use [Ctrl+Alt+End] in RDP session to change the password, instead of [Ctrl+Alt+Del]