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 |