I was asked a very interesting question by their DBA about log files. The question was – Is there ever need of multiple log files in SQL Server?
KILL WITH STATUSONLY: How Long Will This Rollback Take?
Read KILL WITH STATUSONLY rollback estimates, confirm the target session, avoid repeated kills, and account for accelerated recovery.
Error 9002: Transaction Log Full Due to REPLICATION
A transaction log full due to REPLICATION put error 9002 in a client’s ERRORLOG. The database showed no replication at all. The cause was a leftover publication with no subscriber, and here is how we cleared it and shrank the log.
Active and Inactive VLFs: List Them for Every Database
To list active and inactive VLFs for every database, count the rows of sys.dm_db_log_info by vlf_active and read the log reuse wait next to them. This post builds a demo log with 24 VLFs, shows how an open transaction and small autogrowth steps add more, and fixes the count with one shrink and one regrow.
Autogrowth Settings: Check Every Database File in SQL Server
To check autogrowth settings for every database file, read sys.master_files and turn its raw page counts into sizes you can judge. This post builds a demo database with three differently set files, reports the current size, the next growth step and the room left, and fixes the weak settings. It also explains why the same query can return no rows.
SQL SERVER – Regular Table or Temp Table – TempDB Logging Explained
After the blog posts about temp and regular table, I have received questions about it hence I am writing this blog post about TempDB Logging.
Database Mail Health Check: Unsent Items and the Event Log
The alert job succeeded, yet nobody got the email. Here are three read-only checks that show whether Database Mail is really working.