ezzadin
asked on
Shrink T-log - Mirroring enabled.
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:
I get the following error:
I then tried to run:
It took about 5 minutes but the size is still at 16 gigs.
How can I reduce the tlogs size to 15 meg?
Thanks.
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
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.
I then tried to run:
BACKUP LOG [dbname] TO DISK='NULL'
and
DBCC SHRINKFILE(DBname_log, 15)
It took about 5 minutes but the size is still at 16 gigs.
How can I reduce the tlogs size to 15 meg?
Thanks.
ASKER
Hi,
I'm running MS SQL 2005 64bit.
So the only way is to drop the mirror??
I'm running MS SQL 2005 64bit.
So the only way is to drop the mirror??
you may add an another t-log on another drive and limit the current one size too
ASKER
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?
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
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
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
*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.
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.
ASKER
Thanks.
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