Wednesday, June 14, 2023

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

Tuesday, August 9, 2022

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

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

Sunday, February 28, 2021

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/



Monday, November 5, 2018

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
  • 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)


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/ 

Thursday, October 18, 2018

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 |