Category: SQL Server Toolbox
-
Recover a database with a DAMAGED and/or LOST log file
A corrupted or missing transaction log file is one of the most stressful situations a DBA can face. The first instinct is to restore from the last full backup — but depending on the circumstances, SQL Server offers faster recovery paths that minimize both downtime and data loss. This article walks through the two main…
-
Monitoring Wait Events of a single Session or Query in SQL Server
When troubleshooting a slow query or a specific application session, the instance-level wait statistics in sys.dm_os_wait_stats are too coarse: they aggregate wait events from all processes since the last restart. Resetting the view with DBCC SQLPERF is also problematic on busy instances where hundreds of other sessions generate noise. SQL Server 2008’s Extended Events framework…
-
Massive SQL Server Database Moving Using Detach – Attach: The Complete Procedure
Moving SQL Server databases to a new volume — whether to rebalance I/O across storage, migrate to faster disks, or reclaim space — requires a methodical approach to avoid downtime surprises. The detach/attach method is the fastest option when you can afford a brief offline window: no backup involved, no network transfer overhead. This metascript…
-
Anayze SQL default trace to investigate instance events
SQL Server runs a lightweight background trace called the default trace that records a wide range of server-level events: database auto-growth events, object creation and deletion, login failures, server configuration changes, and more. It’s often the first place to look when investigating an incident that didn’t make it into the error log. This article shows…
-
SQL Jobs Monitoring: check last run datetime and duration
SQL Server Agent jobs are the backbone of automated database maintenance: backups, index rebuilds, integrity checks, ETL pipelines. Knowing at a glance when each job last ran and how long it took is essential for fast daily monitoring, especially when managing multiple instances. This query reads directly from the Agent system tables in msdb and…
-
How to make your databases smaller and faster: find unused indexes
Indexes are one of the most powerful performance tools in SQL Server — and one of the most overlooked sources of overhead. Every index you create must be maintained on every INSERT, UPDATE, and DELETE operation. Unused indexes cost you disk space, backup time, memory, and write performance, without providing any read benefit. Finding and…
-
Analyze SQL Server database historical growth: MONTLY size changes
When the daily backup history is too granular for a high-level capacity planning overview, a monthly view gives a cleaner picture of long-term growth trends. This query aggregates backup data from msdb..backupset by month, taking the peak size reached in each month to calculate the net monthly change. This complements the daily report: use the…
-
Analyze SQL Server database historical growth: DAILY size changes
Capacity planning starts with understanding how your databases grow over time. The simplest source of historical size data in SQL Server is the backup catalog: msdb..backupset records information about every backup taken on the instance. If you don’t have a dedicated monitoring tool, this is a reliable starting point. Two important caveats before using this…
-
Queries to see rapidly what your SQL Server is doing NOW
When troubleshooting performance issues on a SQL Server instance, the first step is always to understand what is happening right now: which queries are running, which sessions are blocking others, and where time is being spent waiting. These three T-SQL scripts give you an immediate picture of the current workload using Dynamic Management Views (DMVs).…
-
Massive Database Migration between SQL Server instances: the complete procedure v.2.0 *UPDATED*
A complete T-SQL based procedure to migrate SQL Server instances — including logins, databases, and file relocations — with no external tools required. Changelog (05/04/2014) Added compression to reduce bandwidth, space and transfer time Reduced stat value for very large databases Added backup type parameter: FULL, FULL_COPYONLY or DIFFERENTIAL Added Maxtransfersize and Buffercount parameters to…