SOLVED: SQL 2012 Shrink Command is Not Working

After working with Microsoft Partner Support (generally I have good response from this group) on and off for a few months, I called Microsoft support and had a SQL tech walk me through the issue.

There is a handy query you can run to see what your databases are doing…

select name,log_reuse_wait,log_reuse_wait_desc from sys.databases

If you see a “2” in the LOG_REUSE_WAIT column, you will likely see LOG_BACKUP in the Log_Reuse_Wait_Desc column and that means that your Transaction Logs are waiting to be backed up.

Why this is occurring I can not say but the solution was to force a backup of the logs:

1. Right click on the database in question
2. Click TASKS > BACKUP
3. Change the BACKUP TYPE to TRANSACTION LOG
   1. If you do not see this as an option, you likely have your database set to SIMPLE recovery model and you will likely want to change that to FULL recovery model by
      1. Right click on the database in question
      2. Select PROPERTIES
      3. Click OPTIONS
      4. Change the RECOVERY MODEL to FULL
      5. Click OK and have a nice day
4. Confirm that your BACKUP TO is some place you are happy with
5. Click OK

According to the MS support tech, I may need to do this 4 or 5 times, which struck me as VERY odd.  Even after doing this that number of times I found the SELECT NANE,LOG_REUSE_WAIT… command often showed the same results.

Regardless the next step was to manually run a Shrink:

1. Right click on the database in question
2. Click TASKS > SHRINK > FILES
3. Change the FILE TYPE to LOG
4. Click OK

Again, according to the MS support tech, this can/should be run multiple times to get the size down.

In my case this process shrunk my .LDF’s from 35GB to .35GB which is about where they should be.

Now I need to wait and see if the SHRINK in my scheduled Maintenance Plan works.