SQL Server 2005 - Database Mirroring (Change of Recovery Model)

Question

SQL Server 2005 - Database Mirroring (Change of Recovery Model)

river1

SSC-Insane

Points: 24302
More actions
December 3, 2010 at 6:07 am

#146393

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

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please read through this - Managing Transaction Logs[/url]

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, MVP, M.Sc (Comp Sci)
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

river1 SSC-Insane Points: 24302 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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, MVP, M.Sc (Comp Sci)
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

river1 SSC-Insane Points: 24302 More actions · Answer 4

No, google is to broken 🙂

Thank you very much for this second archicle,

PR

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 5

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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, MVP, M.Sc (Comp Sci)
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

river1 SSC-Insane Points: 24302 More actions · Answer 7

"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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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, MVP, M.Sc (Comp Sci)
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

river1 SSC-Insane Points: 24302 More actions · Answer 9

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

<sigh> Please go and do some reading on mirroring before you try implementing it.

IF the mirroring session becomes suspended (and please read up on what 'suspended' means for a mirroring session) then the log records cannot be discarded until the mirroring session is resumed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

river1 SSC-Insane Points: 24302 More actions · Answer 11

i'm sorry. My english is not very good...

Now, with your last post i understood what you meant.

Thank you very much

river1 SSC-Insane Points: 24302 More actions · Answer 12

Yes, i know that if the mirror is in pause, then the log will grow because when i resume the mirror session all the changes that where made in master database need to be commited to the mirror database.