Tag: Database Maintenance
-
How to Shrink SQL Server Database Files in Chunks
🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Storage & Capacity 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.…
Written by
-
DBA Scripts: Get Suspect Pages and Integrity Checks
🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Backup & Recovery Pages Already Recorded as Bad, and a Backup Check Before DBCC CHECKDB An 823 or 824 just landed in the error log, or DBCC CHECKDB is due and you want to know whether anything is already recorded against this server.…
Written by
-
Kill All User Sessions on a Database in SQL Server
When you need to drop, restore, or change database settings, active user sessions often get in the way. SQL Server will block these operations until all connections are cleared. In busy environments, waiting is rarely practical. If you arrived here from Msg 5030, the database could not be exclusively locked, or from a restore that…
Written by
-
DBA Scripts: Get Missing Indexes
🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Maintenance & Automation › Index Maintenance Every time SQL Server builds an execution plan that could have been improved with a better index, it makes a note of it. Those notes accumulate in memory and are queryable through the sys.dm_db_missing_index_* DMVs, and combined…
Written by
-
DBA Scripts: Get Index Fragmentation Across Databases
🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Maintenance & Automation › Index Maintenance Reports and range scans have slowed over weeks, or you have just inherited a server and need to know whether anyone has been maintaining its indexes. This lists every index at 5% fragmentation or more in every…
Written by
-
Deleting Rows in Batches in SQL Server
A DELETE that removes hundreds of thousands of rows in one go is the one that fills the transaction log, locks the whole table and leaves everything else waiting. Delete in batches instead: small transactions whose log space can be reused, and a lock footprint other sessions can work around. Here is the loop, and…
Written by
-
Applying Data Retention Safely in SQL Server
Data retention in SQL Server usually means deleting old data in a controlled and repeatable way. That might be driven by compliance requirements, table growth that’s starting to hurt performance, SQL Server Express size limits, or simply keeping log and audit tables under control. Whether this is a one-off cleanup or a permanent maintenance task,…
Written by
- SQL Injection in Dynamic SQL: Use sp_executesql
- RESOURCE_SEMAPHORE When SQL Server Is Nowhere Near Its Memory Cap
- Dynamic Data Masking: What a WHERE Clause Still Reveals
- DBA Scripts: Get Log Shipping Status
- SQL Server Row-Level Security: The Filter Does Not Stop Cross-Tenant Writes
- View the Definition of a Stored Procedure in SQL Server
- Backups & Recovery (17)
- DBA Scripts (152)
- High Availability (HA) (13)
- Installation & Configuration (49)
- Maintenance (25)
- Migration & Upgrades (14)
- Monitoring (9)
- Performance Tuning (37)
- Security (Encryption & Permissions) (22)
- Storage & Capacity (16)
- T-SQL Fundamentals (15)
- Troubleshooting (95)
- Wait Types (235)