SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Shrink log file of mirrored database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ikariux
Starting Member

16 Posts

Posted - 04/26/2010 :  10:12:42  Show Profile  Reply with Quote
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

USA
35940 Posts

Posted - 04/26/2010 :  12:18:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

Ikariux
Starting Member

16 Posts

Posted - 04/28/2010 :  04:39:07  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 04/28/2010 :  13:06:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

Ikariux
Starting Member

16 Posts

Posted - 04/29/2010 :  13:31:20  Show Profile  Reply with Quote
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?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 04/29/2010 :  13:39:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

Ikariux
Starting Member

16 Posts

Posted - 04/29/2010 :  14:30:56  Show Profile  Reply with Quote
Which recovery mode should i use for database when mirroring is in place, Simple or Full?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 04/29/2010 :  14:39:14  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

Ikariux
Starting Member

16 Posts

Posted - 04/30/2010 :  02:28:06  Show Profile  Reply with Quote
Thank You Tara for help very much ! :)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 04/30/2010 :  12:31:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

smitajain83
Starting Member

India
1 Posts

Posted - 05/24/2010 :  06:33:53  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 05/24/2010 :  13:03:47  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000