Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-03-20
2
Medium Priority
?
1,062 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

636 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