3

I moved to a new company recently wherein they have 2 databases in a SQL server of size 20-25 GB. I'm unable to shrink the log file of the databases.
Transaction log backup of the databases are set to run every 30 mins from 6 AM-7PM.
They are of size 10 and 2 Gb respectively.
I tried to shrink it around 8-9 PM last night but I was not able to do.

The log_reuse_wait column stated it's waiting on a log_backup.
I tried taking couple of log backups which didn't help.
I ran DBCC OPENTRAN but there are no open transactions.

Also, this morning even after the Tlog backups started at 6:00 AM till now(10:30) it still shows LOG_BACKUP in log_reuse_wait column.
And no OPEN TRAN as well.

When I researched on this I found this article from Paul Randal: http://www.sqlskills.com/blogs/paul/why-is-log_reuse_wait_desc-saying-log_backup-after-doing-a-log-backup/

It states that if the DB is small and all the content of tlog file is in same VLF it couldn't truncate.
I have some 300-400 VLFs in the Databases.
So that doesn't make sense here as well.

One thing I noticed that every morning at 6 AM the tlog is a little bigger than the other ones.
So I'm thinking to check with the lead DBA to run tlog round the clock.
Other than that, I would like to see why is this happening and Am i missing anything.

I tried to shrink with the below command:

Use DB1
go
DBCC SHRINKFILE(2,10) 
GO

Note: It's SQL 2012 instance running 0n 2012 compatibility

migrated from stackoverflow.com Dec 30 '16 at 22:17

This question came from our site for professional and enthusiast programmers.

  • What exact commands or actions have you tried to achieve this? Please be complete. – Peter B Dec 30 '16 at 16:49
  • 2
    Why do you want to shrink the transaction log? It is that size because the SQL Server activity required it and will most likely require it again. Shrinking will only mean it has to autogrow when the server is in use. – cloudsafe Dec 30 '16 at 16:50
  • @PeterB I have added the command in my question – Ramya Dec 30 '16 at 16:51
  • 1
    @cloudsafe I agree with you Cloudsafe. The transaction log files are unrestricted and we even did not have proper Tlog backups set up for the DB. Now that it is set up we would like to shrink and analyse how much of log space we actually need and regulate the file growth setting. It's a one time thing but I wonder why i'm not able to do so – Ramya Dec 30 '16 at 16:53
  • @Ramya In that case, I'd switch to Simple recovery, shrink, then return to Full recovery. – cloudsafe Dec 30 '16 at 16:56
5

The reason your process isn't working is that it's waiting to release the inactive portion of the log using a transaction log backup. Switching to Simple Recovery Model bypasses this issue.

If you really need to shrink down the transaction log, so that you can regrow it (to reduce the number of VLFs, for instance), this is the process:

1) Switch to Simple Recovery Model

2) Run a CHECKPOINT and DBCC DROPCLEANBUFFERS (just in case)

3) Shrink the log file

4) Switch back to Full Recovery Model

5) Grow the log in reasonable increments (I use 4000MB increments)

6) Set autogrowth to a value your I/O subsystem can handle

7) Run a full or differential backup to restart the log backup chain.

  • Perfect workaround, make sure this is executed on an exceptional basis. Also this does not apply for high disponibility contexts. – Hybris95 Jun 9 '17 at 11:49
  • 1
    @Hybris95 Ha, had to look up "disponibility" (it means availability). But yes, do all the above during a planned maintenance window with no user activity, otherwise you could lose user transactions if something went bad and you had to restore. Actually, I'd swap steps 4 and 5 - grow the log manually before switching back to FULL recovery mode. – BradC Jun 9 '17 at 13:38
3

It sounds in your situation that you just need to continue to run transaction log backups overnight. Maybe not every 30 min like during the day, but an 11-hour window with no tran log backups is likely your issue here.

Even if actual user activity is low, any other activity happening at night will grow the transaction log. For example: database maintenance (like reindexing), ETL processes (new data imports, data archiving/deletes, updates from other systems, nightly calculations, etc).

Personally, for a 20-25GB database, I'd be totally comfortable with the transaction log anywhere in the range of ~1GB to ~10GB, depending on the level of activity. Not sure why you think 2GB is that big. That's working space for the SQL engine, not waste.

If you do want to reduce the larger one, here's my recommendation for shrinking a log while in full recovery mode:

  1. Run the transaction log backup job (or allow it to run on schedule).
  2. Do a SHRINKFILE (name='mylog',size=4000) with a specific target size.
  3. Immediately run the transaction log backup job again.
  4. Immediately repeat the SHRINKFILE.

Except for databases with extremely long-running open transactions, that's never failed to allow me to shrink the file.

1

Even after changing recovery model to simple check if any lock was there on the object own by database for which you want to shrink the log file, i have face similar issue not able to shrink the log file even after changing recovery model simple, once i release the lock i was able to shrink the log file.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.