SQL Server 2005 - Recovery model and database mirroring

Last Post 28 Sep 2013 01:45 AM by Henry Desouzas. 13 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
Topic is locked
Author Messages
river1
New Member
New Member

--
03 Dec 2010 02:42 AM
Hi Masters,

    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,

    Pedro







gunneyk
New Member
New Member

--
03 Dec 2010 04:43 AM
In order to use Mirroring you need to be in Full recovery mode. Once in FULL mode you have to take regular log backups in order to reuse the log space again. Truncating or backing up the log does not shrink it you need to use DBCC SHRINKFILE to shrink the log file. Since you don't need the data I suggest this. Set a SQL Agent job to backup the log file every 5 minutes and just write it to the same backup file each time but use the WITH INIT option to overwrite it. Once you have take a full backup and then at least one log backup you can shrink the log file down to a reasonable size but leave enough room for the transactions that will occur in between the 5 minute process. You can backup the log more often if the file import process is very intensive.
river1
New Member
New Member

--
06 Dec 2010 06:27 AM
Thank you for the feedback.

I understood.

Now i have other problem.

I want to create a job that makes full backup of my database , if the database is the prin***l (master database).
I Need to have this Job running in the two instances of sql server (The Master and the Mirror instance).

I want that the job is fired, only on the instance that is currently as the master.

This job must do, first a full backup and then a log backup.

Do you have any ideia on how to do a think like this?
gunneyk
New Member
New Member

--
06 Dec 2010 06:41 AM
You can't have any jobs running against the mirrored instance as it will not be usable including backups. However you should create all the jobs and make them disabled so that wehn you do fail over to the mirror you can enable them so they can perform the required tasks.
One thing I want to point out is that if you are in FULL recovery mode you should not have a single job to do both the FULL backup and the LOG backups. The Log backups should run at a much higher frequency than the FULL backups and thus should be their own job altogether.
river1
New Member
New Member

--
06 Dec 2010 06:46 AM
Thank you once again for your feedback Master Gunneyk.

In my case, i was thinking in doing a thing like this:

create a script that creates a job for full backups. This script only executes if the database is online (select state from sys.databases).
This way, the job can be created on both SQL Server instances (the Prin***l and the Mirror) and the both can be enabled, because when the mirror tries to do a backup, it will see that the database is not online and terminates. Is it possible? or i will receive an error in the mirror instance?

Thank you
gunneyk
New Member
New Member

--
06 Dec 2010 11:02 AM
Well that might work for hte FULL backup but what about any other jobs you may have? And you don't want the log backups to happen until the FULL has occured at least once after the fail over. I would read this Knowledge Base article carefully and make sure you understand all the implications of what has to be done to ensure a proper failover.

http://technet.microsoft.com/en-us/...17680.aspx
rm
New Member
New Member

--
06 Dec 2010 11:33 AM
Yes, we have check db online step in all jobs that run against mirrored db. We don't have to disable\enable jobs on different servers in case mirror switched automatically for any reason.
river1
New Member
New Member

--
07 Dec 2010 02:51 AM
First of all, thank you very much Master Gunneyk for the link. It as very good and complete information. 

1) We do not have any other jobs (this two jobs - full backup and log backups) will be the first and second to be created on the servers.


2) didn't understood this part of your answer: 

"And you don't want the log backups to happen until the FULL has occured at least once after the fail over"



This is what we want to do:


1) Each job will have a first step, as Master RM told. This step will verify if the database is online and only execute the next steps, it the status returned is online.

I don't know how to do this, but i will study.

E.G - The "Full Backup" job executes it's first step (verify if database is online). If succeded, then it will execute the second step (make the full backup). If database is not online, then the second step will not be executed (the backup command).



This "Full Backup" job, will have 3 steps. First and second already mentioned, the third is a log backup.


So, the full backup job will be like this:


1 tp) Verify database status

2 tp) If database online, make full backup

3 tp ) if step 2 succeded, execute log backup

















 







river1
New Member
New Member

--
07 Dec 2010 03:08 AM

First of all, thank you very much Master Gunneyk for the link. It as very good and complete information. 

1) We do not have any other jobs (this two jobs - full backup and log backups) will be the first and second to be created on the servers.


2) didn't understood this part of your answer: 

"And you don't want the log backups to happen until the FULL has occured at least once after the fail over"



This is what we want to do:


1) Each job will have a first step, as Master RM told. This step will verify if the database is online and only executes the next step, if the status returned is online.

I don't know how to do this, but i will study....

E.G - The "Full Backup" job executes it's first step (verify if database is online). If succeded, then it will execute the second step (make the full backup).
If database is not online, then the second step will not be executed (the backup command).



This "Full Backup" job, will have 3 steps. First and second already mentioned, the third is a log backup.


So, the full backup job will be like this:


1 tp) Verify database status

2 tp) If database online, make full backup

3 tp ) if step 2 succeded, execute log backup



As to log backups, they will have their one job, as you told me to do.

This job will be executed every 1 hour.

This job will have a first step equals to the other job (verify if database is online) and will not execute at same time as the full backup, because full backup job will be executed once per day at 16:00h.


I think that if the log backup is made from hour to hour, it will be sufficient for the log not to grow.


I think that with this "And you don't want the log backups to happen until the FULL has occured at least once after the fail over" you meant that i need to do a full backup immediatly after failover, because the log files that where created on the prin***l server will not be capable of be restored in the mirror database (that is now the prin***l database) because they are two differente databases. Am i right?  



 










 









river1
New Member
New Member

--
07 Dec 2010 07:03 AM

Other question that i have, after read the link, is this:


"Log records that result from bulk_loggeg operations cannot be sent to the mirror database"


I have an application that imports data using bulk load.

I have created a mirror and imported the data, and the data is on the two databases  (prin***l and mirror)....

So, i don't understand what they mean with this.

could someone explain?


Thank you


P.S - I'm worry with this, because some of the databases use Bulk to import data and they need to use mirror ... 

 

 

 

rm
New Member
New Member

--
07 Dec 2010 12:01 PM
I'll not count this on year 2005's kb article.
river1
New Member
New Member

--
07 Dec 2010 01:53 PM
sorry, didn't understood your answer Master RM
gunneyk
New Member
New Member

--
07 Dec 2010 01:56 PM
It only counts if the bulk load was a minimally logged load. In order for that to happen you need to have several key factors of which the recovery mode needs to be in BULK LOGGED or SIMPLE for that to happen. So if you are in FULL recovery mode you are all set. A BULK load without minimal logging still logs every row and that is what is needed for the mirroring to work.
Henry Desouzas
New Member
New Member

--
28 Sep 2013 01:45 AM
If you wish to know which recovery model you are using, then use this query

SELECT name, recovery_model_desc

FROM sys.databases <>br
WHERE name = 'model' ;

GO

If you wish to change the recovery model then use the following syntax

USE master ;

ALTER DATABASE model SET RECOVERY FULL ;


If you wish to know further you should check this blog for various MS SQL recovery models.
Topic is locked

Acceptable Use Policy