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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.