How to find sql transactions with large logs

There are quite a few solutions on the internet dealing with runaway transaction log files.During some sql server performance troubleshooting yesterday, I was reminded of how important analysing large transactions and the impact on transaction logs. The sys.dm_tran_database_transactions DMV returns transaction information at the database level. Creating a JOIN with the sys.dm_exec_requests DMV (which returns details of sql requests executing) , creates a resultset with granular information on transaction log usage per request. What’s the point of this information? What sort of problems can be solved using this information? Here are some examples : 1)Identifying very large deletes generating large... Read more →

How to deal with unexpectedly large transaction log files

A common problem faced by SQL server administrators is the unexpectedly large size to which transaction log files often grow. Large transaction log files pose many problems to the server itself such as sluggish performance, threat of data corruption, server downtime and server inaccessibility. It thus becomes a priority to deal with such huge files at the earliest before they can cause hindrances in the normal working of the server. In this article we’ll be focusing on this issue and detailing the causes behind this problem, ways to prevent it from happening (if possible) and tips to resolve it. Read... Read more →

Database has more than 1000 virtual log files which is excessive – informational message 9017

Question: I get the 9017 Informational Message when I start the SQL Server Instance Database Mydatabase has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files. What does it mean and what steps can I take to fix? Answer:The 9017 message appears because a threshold is met on the number of virtual log files for a database. Within a SQL Server Transaction Log file exist virtual log files (VLF) .... Read more →

The transaction log for database is full due to ACTIVE_TRANSACTION

During an ETL process failure occurred. An alert was raised and I started to investigate . This is a chronology of the errors with some explanation notes 04:22:11 - error message” I:\MSSQL\MSSQL$INST1\Data\myDB_Log.LDF: Operating system error 112(There is not enough space on the disk.) encountered.” The transaction log for database 'myDB' is full due to 'ACTIVE_TRANSACTION'. Could not write a checkpoint record in database myDB because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files One or more recovery units belonging to database 'myDB' failed to generate... Read more →

Full Recovery without Log Backups

When a database is in Simple Recovery Mode , log space is automatically reclaimed when a transaction finishes. This means log space management is not required . In the event of database loss , only data from the last back is recoverable. Any data since the last backup is lost, and needs to be recreated. If the recovery Point required is different, change the recovery mode to FULL and take log backups. Regular log backups will ensure a data file loss does not mean a catastrophic failure, but only data loss up to the last log backup. Recovering a database... Read more →