Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server 2005 - Database Mirroring (Change of Recovery Model) Expand / Collapse
Author
Message
Posted Friday, December 03, 2010 6:07 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:25 AM
Points: 715, Visits: 1,018
Hi,

I have a problem...


I have a database that is in Simple recovery model.

Why is it in simple recovery? because it can be loaded from files.

I can loose a day of data with no problem... I load the data again from the files.

This is a database that imports XML data at the end of each day and during the next day pleople take reports from an app that is connected to this database.

The problem is that this database is important and my Boss wants to put it in morriring, because if a server fails , the app can still connect to other server and people can take reports.

It's for high availability...



I have created the mirror and i need to change the recovery model, from Simple to FULL.

This is a problem, because now the log is growing to much and we do not have so much space in disk , if it continuous to grow like this.



I was thinking in truncate the log after each import of data, but i tried and the log did not shrink much (after i truncate, i have made a shrink).


Since this database can loose a day of data with no problem, and knowing that at the end of the day a full backup will be made, what can i do to shrink the size of the log when a database is in mirroring?



Please remember that this database needs to be sent through internet at the end of the day (very poor internet connection in Africa) so, it is important to shrink the log at the most possible so that the backup that is sent over the internet can be smaller...


Thank you very much
Post #1029804
Posted Friday, December 03, 2010 6:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
Please read through this - Managing Transaction Logs

To keep the log small you need frequent log backups. Don't shrink, it's just going to grow again. The size of the log file has no impact on your backups. What has impact on your backups is the size of the active portion of the log around the time of backup, but that's the same in full and simple recovery.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1029840
Posted Monday, December 06, 2010 7:06 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:25 AM
Points: 715, Visits: 1,018
Gail, first of all, thank you very much, what a great archicle.


I have some questions after the archicle read.


1) I will put some apps that i have, in mirror, but this apps use Bulk operations to import data at the end of the day. In mirror, can i use minimal log for bulk operations (put the recovery model as Bulk-logged)?


2) I know what are checkpoint, but in your archicle you talk about lazy writes. What are lazy writes?´


3) You told in your archicle that you would not talk about how mirroring afects the log. Do you have any archicle with that information?


Thank you,

Pedro


Post #1030585
Posted Monday, December 06, 2010 7:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
river1 (12/6/2010)
1) I will put some apps that i have, in mirror, but this apps use Bulk operations to import data at the end of the day. In mirror, can i use minimal log for bulk operations (put the recovery model as Bulk-logged)?


No. Mirroring requires full recovery model.

2) I know what are checkpoint, but in your archicle you talk about lazy writes. What are lazy writes?´


Is google broken?
http://sqlinthewild.co.za/index.php/2009/06/26/the-lazy-writer-and-the-checkpoint/

3) You told in your archicle that you would not talk about how mirroring afects the log. Do you have any archicle with that information?

No, I don't do enough with mirroring to be able to.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1030602
Posted Monday, December 06, 2010 7:33 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:25 AM
Points: 715, Visits: 1,018
No, google is to broken :)

Thank you very much for this second archicle,

PR

Post #1030606
Posted Monday, December 06, 2010 7:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:24 AM
Points: 32,781, Visits: 14,942
Mirroring doesn't affect the log, with the exception that the log is needed to send the data to the mirror database.

If you don't need full recovery mode, why not just script the restore of your daily backup on a second server? Or run a job after the imports that backs up your server, copies the file to a remote server and restores it? That way you would have a second copy of the data and clients could connect there in the event of an issue.

If you do need mirroring and more automatic failover, then you need to run in full recovery mode and incorporate log backups, as Gail mentioned.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1030609
Posted Monday, December 06, 2010 9:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
Steve Jones - SSC Editor (12/6/2010)
Mirroring doesn't affect the log, with the exception that the log is needed to send the data to the mirror database.


unless mirroring gets suspended, then log grows because log records can't be discarded.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1030690
Posted Monday, December 06, 2010 9:28 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:25 AM
Points: 715, Visits: 1,018
"unless mirroring gets suspended, then log grows because log records can't be discarded."


Hum.....

Can not be discarted?

Suppose this:

I have an instance of SQL Server with a database named test1, this database is in other instance as mirror.

Daily backups to log are happening have 10 min. When the backup from the log is made, the information is not discarded as it happens with normal full reconvery model?
Post #1030696
Posted Monday, December 06, 2010 9:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
You missed a key clause.

I said
"unless mirroring gets suspended, then log grows because log records can't be discarded."



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1030702
Posted Monday, December 06, 2010 9:41 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:25 AM
Points: 715, Visits: 1,018
i'm sorry, i didn't understood your answer...

Do you mean that if i do not suspect the mirror, then log backups will not discart transaction from the log file?
Post #1030706
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse