I have a database in production which constantly get log file filled, its a data warehouse and have many jobs/queries running for obvious. Below is the error which I am getting
Msg 9002, Level 17, State 4, Line 7 The transaction log for database is full due to 'ACTIVE_TRANSACTION'.
Now this make sense and I understand that SQL can not perform action cause its log file is filled up. I have two log files
- D drive with unrestricted growth and autogrowth enabled [D drive size 180 GB]
- E drive with static size [E drive 120GB, log file size: 20 GB]
I did some research about this issue and found of possible solution to it: Source
- Backing up the log.
- Freeing disk space so that the log can automatically grow.
- Moving the log file to a disk drive with sufficient space.
- Increasing the size of a log file.
- Adding a log file on a different disk.
- Completing or killing a long-running transaction.
Now, assuming that I have limited space (i.e. is 180 GB + 20 GB) which I believe is good enough for a database in SIMPLE RECOVERY MODE. How possibly can I identify this issue and do a rectification before it occurs?
I have tried to replicate this scenario via creating new sample database with below setting
and below query to get million rows and insert them into table
SET NOCOUNT ON; DECLARE @SET_SIZE INT = 500000000; CREATE TABLE dbo.Test500Million (N INT PRIMARY KEY CLUSTERED NOT NULL); ;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N)) ,NUMS(N) AS (SELECT TOP(@SET_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1, T T2, T T3, T T4, T T5, T T6, T T7, T T8, T T9) INSERT INTO dbo.Test500Million(N) SELECT N FROM NUMS; DROP TABLE dbo.Test500Million
Now, while validating sys.databases and looking on error message I am finding both
CHECKPOINTS & ACTIVE_TRANSACTION.
- On my observation, I found that this is happening only with insert statement not with select. So, I presume that log entries will only be for INSERT/UPDATE & not for SELECT statement in any case. Am I correct?
- How can I minimize logging while inserting updating records? I already have SIMPLE RECOVERY MODE.
- I have found that average throughput during this period went from 25 MB approx. to 1.2 MB. What does it mean?
- Is there any other way to troubleshoot this issue, other than increasing disk space?
- If only option is to shrink a file, then when should I do it? Can I do it when any active transaction is ON? [Production environment]
Kindly let me know if you need more stats which I can collect.