Truncate log in a mirrored database

Hi guys
i have a mirrored db on a sql 2012 server . The server is a preprod environment which has been forgot for a while, and the log grew and fill the whole disk
Since during this months the server has not been used at all, i dont care about saving the log. What i need is just truncate/delete/shrink the log and start over from a clean situation
Can you suggest the best way to proceed since it's a mirrored db ?
If it can help, only the log on the principal grew

any help will be appreciated
MA
Mark AyreSysadminAsked:
Who is Participating?
 
Máté FarkasDatabase Developer and AdministratorCommented:
If mirroring status is Suspended that is a big problem. You cannot truncate and/or shrink transaction log on principal side.
You have 2 choices:
1. Make mirroring work again and bring MIRROR in Synchronized state.
2. Break mirroring, do the truncate and shrink what I showed you in previous reply and rebuild mirroring again (full backup, copy, restore etc.)
0
 
Máté FarkasDatabase Developer and AdministratorCommented:
You cannot truncate the log in SQL Server anymore, that feature is depricated.
You have to make a transaction log backup to truncate it.
But you can do this only on PRINCIPAL side and not on mirror.
If you do this on PRINCIPAL then you will truncate and shrink the log also in MIRROR:
BACKUP LOG [YourDatabaseName] TO  DISK = 'null' WITH STATS = 1;
DBCC SHRINKFILE ('LogicalNameOfYourDatabaseLogFile' , 0, TRUNCATEONLY);

Open in new window

0
 
Mark AyreSysadminAuthor Commented:
I did run these queries on principal. BACKUP LOG completes with no error, while SHRINKFILE cannot complete because it says it's in use
The application server running on this database is shut down
Does it have something to do with the mirrored database, which is still online?

Mirroring Status on the database i tried shrinking was Suspended

Message is: Cannot shrink log file 2 (MyDatabase_log) because the logical log file located at the end of the file is in use.
0
 
Scott PletcherSenior DBACommented:
No point in backing up a log that big, you'll never want or need to apply it.

Break the mirroring, put the db into simple recovery model.  Then CHECKPOINT the db and take a full backup.  That should truncate the log so it can be shrunk.  Fully shrink the log and regrow it to the appropriate size.  For example, if the log normally needs 32GB, shrink it as much as possible:
DBCC SHRINKFILE(2)
Then regrow it in increments of 4 or 8GB, depending on what you prefer and the speed of your disk subsystem:
ALTER DATABASE ... MODIFY FILE ( NAME = <logical_file_name_for_log>, SIZE = 8GB )
ALTER DATABASE ... MODIFY FILE ( NAME = <logical_file_name_for_log>, SIZE = 16GB )
ALTER DATABASE ... MODIFY FILE ( NAME = <logical_file_name_for_log>, SIZE = 24GB )
ALTER DATABASE ... MODIFY FILE ( NAME = <logical_file_name_for_log>, SIZE = 32GB )

FInally you can put the db back into full recovery model and reapply mirroring.
0
 
Mark AyreSysadminAuthor Commented:
I run the BACKUP LOG and disconnected the applications from the database, and some space was made available on the disk.
Hence i managed to resume mirroring for the suspended ones
Once all the dbs were in state Principal / Synchronized i have been able to run the DBCC SHRINKFILE query.

Everything ok now, thank you so much !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.