The Risk
Please help me understand something regarding the transaction log Hello
Submitted by Wesker » Thu 03-Dec-2020, 23:13Subject Area: TestingKeywords: SQL, databases, transaction, log | 1 member rating |
 |
I'm quite new in the world of SQL. I do not use the databases myself, rather I only have a pure operations role. Unfortunately I feel like the SQL knowledge in my team is lacking and I'm not getting a good grip on a specific issue.
A couple of times now i've had servers were the transaction log grows very large, fills up the disk and it won't shrink after a log backup.
What is the best course of action in these scenarios? I've googled a bit, but I just get confused because everyone seems to have different opinions
The scenario I had today:
DB around 200 GB, Logfile around 310 GB.
Got an event saying
"Database log file is full. Back up the transaction log for the database to free up some log space - The transaction log for database ''DATABASE'' is full”"
Doing a log backup did nothing running "SELECT name, log_reuse_wait_desc FROM sys.databases;"
Showed LOG_BACKUP as waiting.
"DBCC OPENTRAN"
showed no active open transactions
"DBCC Loginfo"
Showed status 0 on everything except 2 entries which had "2".
I performed two more log backups and now everything had status 0, but the size remained unchanged.
Available free size was reported as 98%
The disk was completely full, so in this case I had no choice but to shrink it but what am I supposed to do in these scenarios?
1 Comment
