Shrink log file of mirrored database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Shrink log file of mirrored database

Author

Topic

Ikariux
Starting Member

16 Posts

Posted - 2010-04-26 : 10:12:42

Hello

Is it possible to shrink log file of mirrored database without removing mirroring?
Log file of principle database is 8 times bigger than database itself and it is eating space very quickly...

Thank you for help in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 12:18:52

Yes you can via DBCC SHRINKFILE. It'll also mirror the changes to the mirror server.

But you need to figure out why this is happening. Do you have frequent tlog backups, such as every 15 minutes?

What does this show?:
select log_reuse_wait_desc from sys.databases where name = 'dbNameGoesHere'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Ikariux
Starting Member

16 Posts

Posted - 2010-04-28 : 04:39:07

Hello, tkizer

Thank You for reply.
select log_reuse_wait_desc from sys.databases where name = 'dbNameGoesHere' shows result:
log_reuse_wait_desc
1 LOG_BACKUP

I have tried DBCC SHRINKFILE right after full backup completes but it fails because database is in FULL Recovery model. And as i can see i cannot change recovery model to SIMPLE then mirroring is enabled so i fail to trunctate my log file.
What i am doing at the moment manually is:
1. creating full backup of database
2. removing mirroring
3. changing Recovery mode to SIMPLE
4. trunctate log file
5. change Recovery mode to FULL
6. reenabling mirroring

Is there a way to make it more simple and automate process?

Thank You for help in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-28 : 13:06:49

Your process is completely wrong. You should not be truncating the tlog except in an emergency situation. The query I had you run indicates it is waiting for you to backup the tlog. Do you not have tlog backups happening frequently such as every 15 minutes? If you don't, that's a major issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Ikariux
Starting Member

16 Posts

Posted - 2010-04-29 : 13:31:20

Ok, i have created maintenance task to backup transaction log every 20 minutes and one full backup in midnight. But my log is still huge size. Transaction log backups didnt shrink it.
How can i keep it small?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-29 : 13:39:31

Well you do have to shrink it down to a normal size now that you have tlog backups in place. This will be a one-time shrink. You can use DBCC SHRINKFILE for it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Ikariux
Starting Member

16 Posts

Posted - 2010-04-29 : 14:30:56

Which recovery mode should i use for database when mirroring is in place, Simple or Full?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-29 : 14:39:14

You can't use SIMPLE model when using mirroring.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Ikariux
Starting Member

16 Posts

Posted - 2010-04-30 : 02:28:06

Thank You Tara for help very much ! :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-30 : 12:31:06

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

smitajain83
Starting Member

1 Post

Posted - 2010-05-24 : 06:33:53

Hello Tara,

I am also facing same problem of huge size of transaction logs. My principal DB size was
mdf = 9.1GB & ldf = 39.1GB and 24GB free

Mirror DB size
mdf = 13.6GB & ldf = 26.4GB and 9.94GB free

One day suddenly principal status changed to Principal suspended. And to mirroring stopped working, though appplication was able to connect to principal DB.

For restarting mirroring I did following;
1. stopped mirroring(set partner off)
2. truncated & shrunk transaction logs
3. dropped mirror DB
4. took latest DB backup from principal
5. took latest transaction log backup from principal
6. restored DB backup on mirror with NO RECOVER MODE
7. restored transaction log backup on mirror
8. started mirroring

After truncation transaction log size got reduced to 4.6 GB

Please let me know what caused this log size to increase. Also can you guide me for how I can control this increase in log file size.

Thanks & regards,
Smita

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-24 : 13:03:47

The increase in log size was most likely because of mirroring breaking as the transactions wouldn't be able to be cleared from the tlog until they were applied to the mirror. Instead of rebuilding mirroring, you probably could have just clicked the resume button in the GUI and then shrank the file down to a reasonable size.

Also, you did not need to truncate the log. You almost never need to do that. You could have instead taken a regular tlog backup to clear it. Truncating the log affects your recovery points, so it rarely be done.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources