Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Shrink T-log - Mirroring enabled.

Posted on 2014-04-12
10
Medium Priority
?
972 Views
Last Modified: 2014-05-27
Hi,

I have enabled Mirroring for one of my databases and I cannot shrink the transaction logs on the primary db anymore.

I'm running the following query:

USE DBname
GO
DBCC SHRINKFILE(DBname_log, 0)
BACKUP LOG DBname WITH TRUNCATE_ONLY
DBCC SHRINKFILE(DBname_log, 0)
GO 

Open in new window


I get the following error:

Cannot shrink log file 2 (DBname_log) because all logical log files are in use.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 3048, Level 16, State 1, Line 2
BACKUP LOG WITH TRUNCATE_ONLY cannot operate on database 'DBname' because it is configured for database mirroring.
Msg 3013, Level 16, State 1, Line 2
BACKUP LOG is terminating abnormally.
Cannot shrink log file 2 (DBname_log) because all logical log files are in use.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Open in new window


I then tried to run:

BACKUP LOG [dbname] TO DISK='NULL'
and 
DBCC SHRINKFILE(DBname_log, 15)

Open in new window


It took about 5 minutes but the size is still at 16 gigs.

How can I reduce the tlogs size to 15 meg?

Thanks.
0
Comment
Question by:ezzadin
[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
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 39997791
what is your sql server ver\edition sp?
as the main idea unless you drop mirror
you will have opened transactions preventing tlog from shrink.
it means in this case you can not shrink




Database Mirroring in SQL Server 2005
http://technet.microsoft.com/library/Cc917680

make sure you run tlog backups often to keep size tlog down
0
 

Author Comment

by:ezzadin
ID: 39997861
Hi,

I'm running MS SQL 2005 64bit.

So the only way is to drop the mirror??
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 39997863
you may add an another t-log on another drive and limit the current one size too
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:ezzadin
ID: 39997918
If I drop the mirror, do I have to backup and restore the entire database for the mirror again or can I just re-use the database on the backup server?
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39997954
I believe that you'll have to go through the whole process from scratch.

This time, though, look at what you need for your transaction log. You shouldn't really need to shrink it - ever. If you size it right to begin with then it'll not grow. Remember that as it grows it grabs disk space from the File System, so if you continually shrink your t-log - just like any other file - and let it grow again you'll be getting randomly-located pieces of disk space. This will inevitably slow down your system, as it will forever be exercising the heads as it seeks the "next" piece of log file.

hth

Mike
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39997968
How can I reduce the tlogs size to 15 meg?
15MB ?  Are you serious?  And more importantly why are you shrinking them ?  You do realize that is a very bad idea and should only be done in emergencies,  right?
0
 

Assisted Solution

by:ezzadin
ezzadin earned 0 total points
ID: 39998027
I'm running out of space on the server and need to shrink the tlog.

I came across the following on the net and it worked for me:

use [dbname]
checkpoint
BACKUP LOG [dbname] TO DISK =  'C:\dbname'
dbcc shrinkfile(dbname_log,1)
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 39998032
Agree...

You really do have to manage the transaction log file size.

Suggest a quick read of : http://www.experts-exchange.com/Database/MS-SQL-Server/SQL-Server-2005/A_657-Managing-the-Transaction-Log-for-the-Accidental-DBA.html

Now, your main problem is the TRUNCATE_ONLY

You really have to do a full transaction log backup first. Allow time for the transactions to be reflected on the mirror. Problem with truncate is it will remove trailing space back to last fully flagged transaction. There might be a ton of empty space, but size is still showing quite large.

Suggest you do the following...
1) open T-SQL window using the DB needing attention
2) run the t-sql CHECKPOINT command
3) do a full database backup (just in case)
4) run another CHECKPOINT command (even thought the db backup is a checkpoint event)
5) run a transaction log backup (with out the truncate option)
6) wait a little for any transactions to tidy
7) now run your shrinkfile (best to use SSMS)
 
In an emergency...

1) remove partner ALTER DATABASE database_name SET PARTNER OFF
2) follow steps 1-5 above
3) change recovery to SIMPLE
4) issue command EXEC sp_dboption database_name, 'trunc. log on chkpt.', true
5) run a CHECKPOINT (and / or backup)
6) use SSMS, right click on DB, go into tasks > shrink > files
7) choose the transaction log file and use shrink with reorg option
8) set a realistic size - if it doesn't shrink immediately then repeat 5 and 6.
9) set recovery mode back to FULL
10) re-establish (rebuild) mirror

Good reading resource : http://technet.microsoft.com/en-us/library/ms190941(v=sql.90).aspx use the drop down to select your version

Suggest you try first method to begin with and the second is really more a brute force approach.

Then make very sure you schedule to run transaction log backups regularly throughout the day - at least start of day, morning tea, midday, afternoon tea, end of day

Hope that helps...
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39998038
*laughing*

the "agree" was in response to acperkins. It just took a while to type in my response and didn't see your reply.

which I also happen to agree with :)

but would still follow the extra steps so that the full DB and the Trans Log backups are in synch.

also make sure your "once -off" fix is done in scheduled "down time" otherwise current transactions from active uses can interfere.
0
 

Author Closing Comment

by:ezzadin
ID: 40092483
Thanks.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

660 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