1

hi i was using this query for shrinking the database log file

USE Tfs_CoE;
GO
ALTER DATABASE Tfs_CoE
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (Tfs_CoE_Log, 1);
GO
ALTER DATABASE Tfs_CoE
SET RECOVERY FULL;
GO

it was working fine until i have added the database to always on availability group and now i get this error

Msg 1468, Level 16, State 2, Line 4 The operation cannot be performed on database "Tfs_CoE" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group. Msg 5069, Level 16, State 1, Line 4 ALTER DATABASE statement failed. The log for database 'Tfs_CoE' cannot be shrunk until all secondaries have moved past the point where the log was added..

(1 row(s) affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Can anyone help me in shrinking the log file of the database which has grown to 650gb which is configured for always on availability group actual size of the database is 39gb only ?

I have checked with taking backup of transaction log even but that dont work for me the log file size remains same 650gb.

  • 1
    Why are you trying to switch to simple to shrink the log? Why are you even trying to shrink the log? – Aaron Bertrand Sep 11 '15 at 15:54
  • 1
    Read how to shrink log file - understand it and then find the cause - why did it bubbled up ? – Kin Shah Apr 29 '16 at 22:29
2

For a database to be in an Availability Group it needs to be in FULL recovery mode. Part of your script tries to set it to Simple mode.

The bigger question of course is, why are you shrinking your log at all?

Edit: OK, having seen your edit - the error indicates (one of) your secondary database(s) is not up to date, I'd look into why that might be... You won't be able to truncate the log until all secondaries are up to date.

1

Make sure you have a full or log backup on your primary DB first, and then do the shrink operation from the primary DB.

It'll probably grow again due huge transaction so you may want to see if throwing in a few log backups may help reduce the growth before your full backups occur or else determine what's causing the logs to grow so much bigger than the data file -- typically it's due to poorly written queries or HUGE transactions or maybe not doing enough backups to free up space since your in FULL recovery mode.

  1. So do a FULL backup on your primary DB
  2. Run the SHRINKFILE on the primary DB of the mirror without ALTERing to SIMPLE RECOVERY mode
1

The solution is quite simple.

The issue is that the secondary node is set to 'Readable'. This locks the log file and any operation to shrink it will always fail with error 'The log for database [databasename] cannot be shrunk until all secondaries have moved past the point where the log was added.

Change the configs for the AG and make the secondary unreadable. then perform a log backup of both the primary and secondary to clear the log file. After this is done, perform a shrink file operation by running this query:

 Use [DatabaseName]
 GO
 DBCC SHRINKFILE (N'DatabaseName_log' , 1024, TRUNCATEONLY)
 GO

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.