Solved

shrink log files to a database in sql server 2008 r2 configured mirror mode

Posted on 2014-03-20
2
913 Views
Last Modified: 2014-03-31
Hello,
I would need to know which is the correct way to do the shrink log files to a database in sql server 2008 r2 configured mirror mode.
thanks
0
Comment
Question by:pozlu0
2 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 39943052
From http://technet.microsoft.com/en-us/library/ms191235(v=sql.105).aspx
a "Backup of the principal database is allowed, but BACKUP LOG WITH NORECOVERY is not allowed" you can shrink the file after a regular T-log backup or issue a explicit CHECKPOINT against that database then use the SSMS UI to "Shrink Files" and select the Log file of the DB you want to shrink.

or by T-SQL:


Simple recovery model
USE dbname
CHECKPOINT -- commit all uncommitted transactions
--First param below is fileno for log file, often 2. Check with sys.database_files
--Second is desired size, in MB.
DBCC SHRINKFILE(2, 500)
DBCC SQLPERF(LOGSPACE) --Optional
DBCC LOGINFO --Optional
Now repeate above commands as many times as needed!


Full or bulk_logged recovery model
USE dbname
BACKUP LOG dbname TO DISK = 'C:\x\dbname.trn'
--First param below is fileno for log file, often 2. Check with sys.database_files
--Second is desired size, in MB.
DBCC SHRINKFILE(2, 500)
DBCC SQLPERF(LOGSPACE) --Optional
DBCC LOGINFO --Optional
Now repeate above commands as many times as needed!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question