Author: Peter Whyte

  • Troubleshoot “Login Failed for User” (Error 18456) in SQL Server

    Msg 18456, Level 14, State 1, Login failed for user 'pete'. is the most common security-related error in SQL Server, and it’s also the most useless on its own. Microsoft deliberately hides the real reason from the client for security (so an attacker can’t tell “wrong password” from “account doesn’t exist”), which means the error…

    Written by

    Peter Whyte

  • PAGEIOLATCH_SH Wait Type in SQL Server

    Part of the SQL Server Wait Types Library. PAGEIOLATCH_SH appears when a query needs a data page that is not in the buffer pool, so SQL Server must read it from disk. The SH stands for shared latch, the read operation. The query waits until the I/O completes and the page is loaded into memory.…

    Written by

    Peter Whyte

  • ASYNC_NETWORK_IO Wait Type in SQL Server

    Part of the SQL Server Wait Types Library. ASYNC_NETWORK_IO appears when SQL Server has finished producing rows and is waiting for the client application to read them from the network buffer. SQL Server filled its output buffer, the client hasn’t acknowledged it yet, and SQL Server is sitting idle waiting to send the next batch.…

    Written by

    Peter Whyte

  • PAGELATCH_EX Wait Type in SQL Server

    Part of the SQL Server Wait Types Library. PAGELATCH_EX (and its sibling PAGELATCH_UP) is a latch wait on a page that is already in memory. That one word, memory, is the whole story. This is not disk I/O. If you see PAGE**IO**LATCH you have a storage problem; if you see PAGELATCH you have a concurrency…

    Written by

    Peter Whyte

  • DBA Scripts: Get Job Schedules and Duration Trends

    Part of the DBA-Tools Project. When Jobs Run, and Whether They’re Taking Longer to Do It SQL Agent Job Failure Summary tells you what broke. These two scripts answer two quieter but just as important questions: Get-JobScheduleSummary shows exactly when every enabled job is scheduled to run next, useful for spotting overlapping windows or confirming…

    Written by

    Peter Whyte

  • How to Shrink SQL Server Database Files in Chunks

    Part of the DBA-Tools Project. A data file that’s grown far past what the database actually needs, after a one-off bulk load, an archive cleanup, a migration that left behind a much bigger footprint than the data now occupies, eventually needs to come back down. The problem is that a single DBCC SHRINKFILE targeting the…

    Written by

    Peter Whyte

  • DBA Scripts: Get Lock Escalation, Contention Analysis, and Blocking Chains with Plan

    Part of the DBA-Tools Project. Three Different Views of the Same Underlying Problem Blocking Chains shows what’s blocked right now. These three scripts go wider and deeper: Get-LockEscalationStats finds which tables have converted row/page locks into full table locks (a common, self-inflicted cause of blocking), Get-ContentionAnalysis rolls up lock waits, latch waits, TempDB allocation contention,…

    Written by

    Peter Whyte

  • AM_INDBUILD_ALLOCATION and AM_SCHEMAMGR_UNSHARED_CACHE Wait Types in SQL Server

    Part of the SQL Server Wait Types Library.Related deep dive: IO_COMPLETION Wait Type. Two Access Methods (AM) waits share this page: AM_INDBUILD_ALLOCATION: a thread waiting for exclusive access to an extent during allocations while building or rebuilding indexes. Parallel index builds allocate space aggressively, and workers occasionally queue at the same extent. AM_SCHEMAMGR_UNSHARED_CACHE: a thread…

    Written by

    Peter Whyte

  • WAITFOR Wait Type in SQL Server

    Part of the SQL Server Wait Types Library.Related deep dive: SOS_SCHEDULER_YIELD Wait Type. WAITFOR is the most literal wait type in the DMV: it records sessions executing a WAITFOR DELAY or WAITFOR TIME statement. The duration is exactly what the T-SQL asked for. Nothing is contended and nothing is slow; somebody’s code chose to sleep.…

    Written by

    Peter Whyte

  • BACKUP Wait Type in SQL Server

    Part of the SQL Server Wait Types Library.Related deep dive: BACKUPIO and BACKUPBUFFER Wait Types. BACKUP is recorded when a thread involved in backup processing waits to synchronise with the other threads of the same backup. Backups run as a multi-threaded pipeline (readers, writers, a coordinator), and the plain BACKUP wait is the general coordination…

    Written by

    Peter Whyte

Key Links

DBA Scripts: The Complete Guide
Query and Performance Tuning
High Availability
Security
Storage and Capacity
Installation and Patching
Backups and Recovery

Blog Categories

Latest Posts

Blog Tags

Access (4) Always On Availability Groups (4) Auditing (7) Backups (6) Blocking (4) Blocking Locking (6) Collectors (7) Database Backups & Recovery (8) Database Backups and Recovery (5) Database Maintenance (7) DBA Tools (24) Disk Space (5) Extended Events (8) Features (6) High Availability (6) Indexes (7) Installation (7) Inventory (8) Jobs (3) Maintenance (7) Migration (15) Monitoring (27) Performance (21) Performance Troubleshooting (9) PowerShell (4) Query Behaviour (5) Security (10) SQL Agent (5) SQL Scripts (17) SQL Server Administration (10) SQL Server Configuration (7) SQL Server Errors (5) SQL Server High Availability (4) SQL Server Internals (3) SQL Server Management Studio (SSMS) (6) SQL Server Monitoring (7) SQL Server Networking (9) SQL Server Security (7) Storage and Capacity (7) T-SQL (10) Temp DB (3) Tracing (7) Transaction Logs (4) Troubleshooting (24) Windows Server (4)