0

My LDF file is too big (almost 500 GB) because we had some problems.
I know that is not good to shrink file, but we need to do it this time.
My database is FULL RECOVERY MODE and it is in a Alwayson High Availability.

I runned DBCC SHRINKFILE (N'My DB_Log' , 250) and it not worked

I've checked DBCC SQLPERF(logspace) and it returned:

Database .....| LogSize (MB) | Log Space Used (%)
MyDatabase | 450994.6 .........| 0.4529642

I runned DBCC LOGINFO too and returned 2293 lines and all with Status = 2

Any Idea???

Alexandre_Almeida
  • 131
  • 1
  • 3
  • 10
  • What return `log_reuse_wait_desc` column within following query `SELECT d.name, d.log_reuse_wait_desc, d.recovery_model_desc FROM sys.databases d WHERE d.database_id = DB_ID()` ? – Bogdan Sahlean May 30 '16 at 19:52
  • returned **log_reuse_wait_desc = NOTHING** – Alexandre_Almeida May 30 '16 at 20:06
  • Check http://dba.stackexchange.com/questions/114787/shrink-database-in-always-on-availability and http://sharepoint.stackexchange.com/questions/117545/cant-shrink-a-log-file-in-an-always-on-configuration . These questions seem to answer the same problem. – Pio May 30 '16 at 20:53
  • It did not. AlwaysOn need to be **FULL RECOVERY MODE** – Alexandre_Almeida May 30 '16 at 21:16
  • 2
    It may not be current but I have had problems with shrinking unless there had been a full backup – Peter Smith May 30 '16 at 22:32
  • Do a Full log backup like Peter says so that you can get the active log sections out of the end of the file. Then shrink. You may have to repeat this. It is highly possible that a full backup is required as well. – Sql Surfer May 31 '16 at 02:38
  • Thanks all. I did a full backup and it worked =) – Alexandre_Almeida May 31 '16 at 15:02

0 Answers0