I recently spoke to another DBA who had a serious problem. Application software had stopped functioning because an application database log file had grown until it completely filled the disk drive on which it resided. He asked how he could solve the problem without losing transactions in the log file. The best immediate solution (which I won’t relate here) was going to destroy important data in the log file that might be needed later for data recovery operations, and – most importantly – this situation should have never happened. Running out of space on a disk drive, whether it be direct attached storage or on a SAN, should never happen. It’s a junior DBA mistake to allow it to happen and indicates a reactive server management style, instead of a pro-active, interceptive style of server management.
The data file is where the actual database data is kept. The log file is where the instructions for database data file modifications are kept. Either one can and will grow for a variety of reasons. Growth should be anticipated but how to do it in a pro-active manner? I have developed proprietary SQL Agent jobs that track the histories of database file utilization and growth. Using the histories of database file growth and the “high water marks” of file utilizations, it is relatively easy to predict the future of database file growth and utilization, and to allow a margin of safety for unexpected surges in file growth.
It sometimes happens for a variety of reasons. There are ways to automatically, in real-time, monitor current database data and log file sizes and disk space utilizations and to send alerts to a DBA and/or other administrators that certain thresholds have been crossed and that an unexpected growth event is about to occur, before disk free space is exhausted, to allow for corrective administrative actions. These functionalities can be easily implemented and appropriately tailored to a company’s production environment. I have implemented proprietary SQL Server Agent jobs to do this.