SQL Server Log File Is Huge

Question

Currently my db logs for my production SQL Server 2008 R2 server is growing out of control:

• DATA file: D:\Data...\MyDB.mdf = 278859 MB on disk
• LOG file: L:\Logs...\MyDB_1.ldf = 394542 MB on disk

The server mentioned above has daily backups scheduled @1am & translog backups every 15 min.

The database is replicated in full recovery model to a subscriber. Replciation is pushed from the node above (publisher). That same db log file on the subscriber is ~< 100 GB on disk.

What I did to try and fix:

1. Run a full backup of the db (takes 1h:47m)
2. Run the translog backup job which runs every 15 min. (takes 1m:20s)
3. Run another full backup of the db

Nothing above has worked so I then attempt to shrink the log files which doesn't work either using DBCC SHRINKFILE. The size doesn't ever change.

Can anyone please tell me what is wrong or what I need to do as a SQL Server DBA to resolve the above issue?

Answer 1

Possible things that may stop you from shrinking the translog file:

1. Long running transaction is occurring on your database
2. Your replication distribution agent runs quite frequent

Looking at the size of your translog file size, most likely it was caused by the 2nd possibility.

Your replication distribution agent runs quite frequent

SQL Server log reader agent marks the translog file as being used and prevent them from being shrunk, which is what SQL Server does after the translog file is backed up. If this process happens frequent and long enough, this could prevent your translog file from being shrunk on translog scheduled back up.

Look at this MSDN transactional explaination and how to modify log reader agent.

And a thread in MSDN forum that describe similar problem, there is DBCC query here that helps you identify running transaction that may be blocking the translog file (DBCC OPENTRAN).

Long running transaction is occurring on your database

You can check wheter any long running transaction is happening by using DBCC OPENTRAN and what process is running then decide what to do with it. As soon as the long running transaction is finished you should be able to shrink the log file.

Answer 2

After running sp_who2, I noticed a long running transaction on the log that was growing uncontrollably. I used kill on that SPID and not I'm proceeding to shrink the log file.

Answer 3

You should make blank database with same table and migrate your old database data to blank database from migration script. for eg: INSERT INTO customers(cust_id, Name, Address) SELECT cust_id, Name, Address FROM olddb.customers

--this script should run in new blank database

Answer 4

You can manually shrink you log file 1.right click your database > task > shrink > file > file type=log than ok