Happens all the time. Twice this week. Vendor Database, something happens, runs out of space for the logs, got a problem. SInce iis a vendor database, and since our best practice for IT support is to get on the phone, why sound we worry? One reason is that if the database is in full recovery mode and backups break (we use MS DPM), the log will grow and we have to do something about it.
If the transaction log is too big, it is not getting purged. that will happen if the db is in full and a backup occurs. Assuming that the log doesn’t grow so fast it messes stuff up. It can happen if the db is simple and we outrun the save to disk function.
In another way of looking at it, we will have a problem if we run out of space. One bad log can mess up the entire volume. Or the log file might not be set to autogrow, and in that case we might have a problem if it is not correctly sized. Also, indexing and bulk-imports can add to the log faster than we anticipate and what was once good is now bad.
Just to make sure it is straight, we usually expect that the backup and the size of the database will grow as new records are added, but the right-sized log should be truncated often enough that we don’t see IT grow. After the backup, it is empty\emptier although the file size is the same (because all the space will be needed, like a high-water mark) and the percent used will increase throughout the process until the log is truncated again. If the log is shrunk and we DO need to grow it, it can be a slow process and the db will be unresponsive during the growth.
Shawn McGehee in SimpleTalk
will report on the size of the log(s) and the percent used like this:
DB Name log (MB) log % used Status
master 1.992188 41.7402 0
tempdb 379.4297 23.68777 0
model 0.7421875 54.93421 0
msdb 417.3672 3.173259 0
MedicatDB 5809.742 0.3472498 0
BB_admin 19.99219 24.47489 0
BBLEARN 135.4297 2.817277 0
BB_stats 1872.992 12.3829 0
BB_cms 19.99219 20.68191 0
We can try to backup the log file with t-sql or run a manual DPM job. We can try to create a second log file on another drive temporarily to get the database to continue to respond. It that works, we can continue our usual maintenance.
In this example, a new database was create, an insert without commit performed and another tranaction started. Since the first log entry (which was the MinLSN or oldest record stil needed) was at the start of the log, the attempt to backup the log, which should have worked, did not result in clearing unnecessary Log entries, since
if a log file is filled and needs to grow (and autogrowth is turned on), it can be enlarged by increasing the size and additing more/new virtual log files (VLFs). The new parts of the log will need to be initialized. . If the settings for autogrowth have small increases, and autogrowth is common, we will have lots of little VLFs. and the log is fragmented. reading these VLFs will make a restart of the server slow due to the need to read each of them
We can count the VLFs with a DBCC command Loginfo (undocumented and unsupported)
Autogrowth is the issue, we can create and manually resize the log file easily with a reasonable number of VLFs – getting there with a number of autogrowths creates unnecessary virtual log files.
another approach is to change the recovery model from full into simple and shrink the log.
DBCC SHRINKFILE (file_name, target_size).
Following this, change back to full and immediately do a log backup to restart the LSN chain. If the log is too large, usually there are too many VLFs as well.