7

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

Improve this question
6
  • What exact commands or actions have you tried to achieve this? Please be complete.
    – Peter B
    Commented Dec 30, 2016 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
    Commented Dec 30, 2016 at 16:50
  • @PeterB I have added the command in my question
    – Ramya
    Commented Dec 30, 2016 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
    Commented Dec 30, 2016 at 16:53
  • @Ramya In that case, I'd switch to Simple recovery, shrink, then return to Full recovery.
    – cloudsafe
    Commented Dec 30, 2016 at 16:56

3 Answers 3

Reset to default
13

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.

Improve this answer
2
  • Perfect workaround, make sure this is executed on an exceptional basis. Also this does not apply for high disponibility contexts.
    – Hybris95
    Commented Jun 9, 2017 at 11:49
  • 2
    @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
    Commented Jun 9, 2017 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.

Improve this answer
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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