SQL Server Database Maintenance
Learn about database file management, log file growth, and when/how to request database shrinking.
Understanding Database Files
| File Type | Extension | Purpose |
|---|---|---|
| Data File | .mdf | Stores actual data (tables, indexes) |
| Log File | .ldf | Transaction log for recovery |
| Secondary Data | .ndf | Additional data files (less common) |
Why Does the Log File Grow?
The transaction log (.ldf) grows because:
- All database changes are logged first
- In Full recovery model, logs are retained until backed up
- Large operations (imports, updates) generate significant logs
- Long-running transactions hold log space
Checking Database Size in Plesk
- Go to Databases
- View the size column next to each database
- Click on database name for details
When to Request Database Shrinking
Request a shrink operation when:
- Log file is significantly larger than data file
- You've deleted large amounts of data
- Database is approaching quota limit
- You see disk space errors related to the database
How to Request Database Shrinking
Database shrink operations require server administrator access. Submit a support ticket:
Example Ticket:
Subject: Database Shrink Request - [database_name] Please shrink the following database: Database Name: mydb_production Current Size: ~5GB (mostly log file) Expected Size: ~500MB data Actions requested: 1. Shrink the log file (.ldf) 2. If possible, change recovery model to Simple I understand this may cause brief performance impact.
Recovery Model Considerations
| Model | Log Behavior | Best For |
|---|---|---|
| Full | Logs retained until backup | Production with point-in-time recovery needs |
| Simple | Logs auto-truncate | Development, apps that can rebuild data |
| Bulk-Logged | Minimal logging for bulk ops | Data warehouse, large imports |
Recommendation: For most web applications on shared hosting, Simple recovery model is sufficient and prevents log file bloat.
Preventing Large Log Files
1. Use Simple Recovery Model
Request this when setting up your database (or via support ticket).
2. Avoid Large Transactions
// BAD - One huge transaction
DELETE FROM LargeLogs WHERE Date < @OldDate; // Millions of rows
// GOOD - Batch the deletes
WHILE (1=1)
BEGIN
DELETE TOP (10000) FROM LargeLogs WHERE Date < @OldDate;
IF @@ROWCOUNT = 0 BREAK;
END
3. Regular Maintenance
- Delete old data periodically
- Archive historical data to separate tables
- Rebuild indexes during low-traffic periods
Self-Service Database Export
If you need to reduce database size yourself:
- Export database dump from Plesk
- Download the .sql file
- Delete large/old data via SSMS or application
- Request shrink from support
Database Size Limits
Check your hosting plan for database size limits. If consistently at limit:
- Clean up old data
- Archive to external storage
- Consider upgrading to a plan with more database storage
