0Disk space is something that really critical for SQL Server, and if we don’t keep a close eye on available disk resources, it could cause database failures or hamper depended on the major business application.
Always increasing the space may not be the solution if you are managing the database server as a DBA, then you should focus on below basic things to avoid a big problem.
Let's understand what the different problem that may create a space crunch on the database server is?
1. Long-running transaction.
2. Incorrect Database recovery model and transaction log backup strategy.
3. No Cleanup of an old backup.
4. Non-Archival of history related to maintenance of SQL Agent from MSDB.
5. Missing Maintenance task on the Database.
What is a long-running transaction which may cause space issue?
DML operations which are not getting committed will cause the log file to grow very quickly. Example bulk Insert, Delete executed in a single batch which is not getting committed may lead to grow the log file very quickly and run out of space and even can cause database failure.
Incorrect Database recovery model and transaction log backup strategy?
Database recovery model plays a critical role during a disaster. If you have a critical database, it is must that you should configure your database recovery model to FULL and plan the backup strateby with incremental and Transaction log backup frequently to have a point in time recovery. Apart from this if you have a database with huge transaction running frequently, then you should configure the transaction log backup every 30 minutes consider business strategy and the extent to which business can bare data loss during a disaster. This tlog backup will even help to move the committed transaction from log file to data file and thus will help to keep the log file intact.
No Cleanup of old backup from database drive.
Many times it is found the DBA configure the backup on a local drive or same server which is not really good practice. If you see with the recent case, we have good hardware and drive with huge space. However, ransomware virus attack has really caused big problems to the database server. If you have backups of the database on the same drive, then this backup may even get infected with this virus and of no use for recovery. Always keep the copy of the backup on other media server.
Coming to point DBA normally create a maintenance backup plan, however, miss to create the cleanup task which creates a space issue on the drive. There lot of article which guides on clean up task.
Non-archival of history related to maintenance of SQL Agent from MSDB.
MSDB stores all sorts of data, such as backup and restore history, SQL Agent job history, log shipping monitor history, SSIS packages, Database Engine Tuning Advisor data, and Service Broker queue data. Just like user databases, msdb needs regular maintenance, including index optimisations and, more importantly, regular purging.
All above activity can be achieved via Maintenance Tasks for SQL Server.
Happy Learning
Ajay
