A client’s SQL Server Cluster Resource sometimes failed to come online after a failover to another node. Here is what caused it and how we fixed it.
How an Open Transaction Uses Free Log Space
An open transaction holds free log space hostage. This post runs one transaction in a SIMPLE recovery database and reads the log after the load and after the commit. It explains why the log grows before COMMIT, what ACTIVE_TRANSACTION means, and why a new FULL recovery database behaves like SIMPLE until its first full backup.
Find Who Dropped a Table by Reading the Transaction Log
To find who dropped a table, read the transaction log for the DROPOBJ transaction and look up the login behind its security ID. This post runs that search on a demo database, shows the default trace as a second source when the log has been truncated, and explains what to do when everyone shares one login.
SQL SERVER – Creating a Login and Database User Without Automatic Sysadmin Grants
To Create a Login and User, I separate the instance identity from database access. Database ownership and sysadmin membership have different scopes.
SQL SERVER – Databases Going to Recovery Pending State Randomly
A client’s databases kept going to recovery pending state randomly. The ERRORLOG showed why. Here is how I found the cause and fixed it.
SQL SERVER – FIX: Msg 22004 – The Log File is Not Using Unicode Format
The SQL Server Agent log would not open in SSMS and showed Msg 22004, the log file is not using Unicode format. Here is the setting that fixed it.
How to Find Last Full Backup Time and Size for Database? – Interview Question of the Week #173
Too many DBAs I meet have no recent backup. Here is a script that shows when each database had its last Full Backup and how big that backup was.