1

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?

cachedrive
  • 53
  • 1
  • 6
  • Your transaction file should be shrinkable. You should allocate enough space in your data file to support growth while limiting auto-grow events. There could be a transaction(s) holding up your log file. Look for uncommitted transactions. Also, I would post this in http://dba.stackexchange.com. – Ross Bush Feb 10 '16 at 22:19
  • Can you confirm you are DBCC SHRINKFILE shrinking your log file and that the data file is not "just that big"? – Ross Bush Feb 10 '16 at 22:28
  • Also, how often do you rebuild the indexes? – Ross Bush Feb 10 '16 at 22:31
  • Can you not shrink the log file because it is fully utilised? What is the % figure when you run `DBCC SQLPERF(LOGSPACE);` ? – Simon Ridd Feb 10 '16 at 23:20
  • 1
    What you shouldn't do: shrink the log files. What happens then is the disk get busy shrinking the files. Then straight after the disk gets busy growing them again. So your disks are busy shrinking and growing when instead they should be servicing the database. As a "SQL Server DBA" you should be working out what activity is making them grow and addressing that. Here are some articles to read: http://dba.stackexchange.com/questions/29829/why-does-the-transaction-log-keep-growing-or-run-out-of-space – Nick.Mc Feb 10 '16 at 23:21

4 Answers4

0

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.

Chris Wijaya
  • 1,276
  • 3
  • 16
  • 34
  • This is really not advisable if the DB is to support long running transactions. – Ross Bush Feb 10 '16 at 22:20
  • 1
    Unless you fully understand the ramifications this is NOT a good approach. There are some extremely limiting aspects of using Simple recovery. If there is a database in production in full recovery just switching it to simple is dangerous and irresponsible at the very least. – Sean Lange Feb 10 '16 at 22:24
  • Good points, although I must say that this is still a valid answer nonetheless. – Chris Wijaya Feb 10 '16 at 23:02
  • The OP says "The database is replicated in full recovery model to a subscriber". I don't do replication but I assume full recovery mode is required for this operation. – Nick.Mc Feb 10 '16 at 23:19
  • My mistake I am confusing log shipping with replication.. though for all I know that's what they might mean by rpelication - they are not very specific – Nick.Mc Feb 11 '16 at 02:43
  • 1
    Chris - that's utter nonsense. The Log Reader Agent job running on the distributor has to read the publication database's log file. Replication doesn't create any other log files. – Paul Randal May 30 '16 at 18:25
  • @PaulRandal What you said is right and I was trying to explain it accordingly but did not come out any good. I said 'transactional replication does dig into the database log fie' but what I was trying to say is the log reader agent instead the replication itself. I also pointed out that the replication/log reader agent generates log files but it is the snapshot agent's role instead. – Chris Wijaya May 31 '16 at 04:55
  • You said 'replication won't dig in into the database log file', which is incorrect. The Log Reader Agent *is* replication. And there aren't any log files generated by any replication Agents. The Snapshot Agent creates snapshot files, not log files. – Paul Randal May 31 '16 at 20:13
  • Use the link from Microsoft in the answer for best explanation of how the transaction replication works. As a highlight, Snapshot agent generates snapshot files for initial replication run and then log reader agent reads database log file looking for data modification traces to implement at the subscriber. Somehow I confused the snapshot files and the database log in the previous comments, hence they were deleted to avoid further misleading. – Chris Wijaya May 31 '16 at 23:51
0

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.

cachedrive
  • 53
  • 1
  • 6
0

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

Jeevan Gharti
  • 451
  • 1
  • 7
  • 15
-2

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

Jeevan Gharti
  • 451
  • 1
  • 7
  • 15