Solved

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

Posted on 2014-03-20
2
879 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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

744 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now