Database mirroring with truncate log on checkpoint

Hi All,
Understand that database mirroring need to use FULL recovery model, meaning
that all transaction detail will be save into log file. Hence the log file
will increase tremendously.
Can I enable truncate log on checkpoint on principle database and mirror
database? so the log file size will under control.
Thanks.
--
Regards,
VenedictHello There!
You have to set FULL recovery model for database mirroring.
To learn more about this, you can refer to the following document:
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
Also, when you try to dump \ truncate your log file which is used by
Database Mirroring, you'll get an error and operation will not be completed
successfully.
To keep log file's size under control, you may try to setup a job to take
it's backup regularly for example every 10-20 mins and disable it's
autogrowth and set some size manually. However, be wise while configuring
it.
Ekrem Ã?nsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Venedict" <Venedict@.discussions.microsoft.com> wrote in message
news:36A23B54-820D-4B0E-9DA9-8A37A850D7F8@.microsoft.com...
> Hi All,
> Understand that database mirroring need to use FULL recovery model,
> meaning
> that all transaction detail will be save into log file. Hence the log file
> will increase tremendously.
> Can I enable truncate log on checkpoint on principle database and mirror
> database? so the log file size will under control.
> Thanks.
> --
> Regards,
> Venedict

database 'master4IDR' in load msg in log

I am seeing the message 'bypassing recovery of database master4IDR because it is marked IN LOAD' in my sql log. But I have not restored any of the sys dbs. The msg is appearing every day, but everything else seems to be fine.

What is causing it? Should I worry about it ? (I do) and what can I do about it?

I just searched a bit and found this discussion:

http://www.sqlteam.com/Forums/post.asp?method=ReplyQuote&REPLY_ID=162149&TOPIC_ID=48723&FORUM_ID=6

From this post:

"I found that the model4IDR thing is the database created by Veritas BackExec which has the Interlligent Disaster Recovery option. The Veritas backup process will use these databases during backup. "

Thanks,
Sam Lester (MSFT)

database 'master4IDR' in load msg in log

I am seeing the message 'bypassing recovery of database master4IDR because it is marked IN LOAD' in my sql log. But I have not restored any of the sys dbs. The msg is appearing every day, but everything else seems to be fine.

What is causing it? Should I worry about it ? (I do) and what can I do about it?

I just searched a bit and found this discussion:

http://www.sqlteam.com/Forums/post.asp?method=ReplyQuote&REPLY_ID=162149&TOPIC_ID=48723&FORUM_ID=6

From this post:

"I found that the model4IDR thing is the database created by Veritas BackExec which has the Interlligent Disaster Recovery option. The Veritas backup process will use these databases during backup. "

Thanks,
Sam Lester (MSFT)

Database Market SUSPECT

How to recovery ?
starting up database 'DATA'
opening file d:\dados\data\DATA_data.mdf.
opening file d:\dados\data\msdblog.ldf
opening file d:\dados\data\northwnd.ldf
opening file d:\dados\data\pubs_log.ldf
opening file d:\dados\data\DATA_log.ldf
bypassing recovery for database 'DATA' because it is market SUSPECT
I don't have backup.
TanksBelow is a good start:
http://www.karaszi.com/sqlserver/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"jcarlos0402" <anonymous@.discussions.microsoft.com> wrote in message
news:4D6E77FE-4189-44C0-9EC2-B6E2C2BA0552@.microsoft.com...
> How to recovery ?
> starting up database 'DATA'
> opening file d:\dados\data\DATA_data.mdf.
> opening file d:\dados\data\msdblog.ldf
> opening file d:\dados\data\northwnd.ldf
> opening file d:\dados\data\pubs_log.ldf
> opening file d:\dados\data\DATA_log.ldf
> bypassing recovery for database 'DATA' because it is market SUSPECT
> I don't have backup.
> Tanks

Database Market SUSPECT

How to recovery ?
starting up database 'DATA'
opening file d:\dados\data\DATA_data.mdf.
opening file d:\dados\data\msdblog.ldf
opening file d:\dados\data\northwnd.ldf
opening file d:\dados\data\pubs_log.ldf
opening file d:\dados\data\DATA_log.ldf
bypassing recovery for database 'DATA' because it is market SUSPECT
I don't have backup.
Tanks
Below is a good start:
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"jcarlos0402" <anonymous@.discussions.microsoft.com> wrote in message
news:4D6E77FE-4189-44C0-9EC2-B6E2C2BA0552@.microsoft.com...
> How to recovery ?
> starting up database 'DATA'
> opening file d:\dados\data\DATA_data.mdf.
> opening file d:\dados\data\msdblog.ldf
> opening file d:\dados\data\northwnd.ldf
> opening file d:\dados\data\pubs_log.ldf
> opening file d:\dados\data\DATA_log.ldf
> bypassing recovery for database 'DATA' because it is market SUSPECT
> I don't have backup.
> Tanks

Database Market SUSPECT

How to recovery
starting up database 'DATA
opening file d:\dados\data\DATA_data.mdf
opening file d:\dados\data\msdblog.ld
opening file d:\dados\data\northwnd.ld
opening file d:\dados\data\pubs_log.ld
opening file d:\dados\data\DATA_log.ld
bypassing recovery for database 'DATA' because it is market SUSPEC
I don't have backup
TanksBelow is a good start:
http://www.karaszi.com/sqlserver/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"jcarlos0402" <anonymous@.discussions.microsoft.com> wrote in message
news:4D6E77FE-4189-44C0-9EC2-B6E2C2BA0552@.microsoft.com...
> How to recovery ?
> starting up database 'DATA'
> opening file d:\dados\data\DATA_data.mdf.
> opening file d:\dados\data\msdblog.ldf
> opening file d:\dados\data\northwnd.ldf
> opening file d:\dados\data\pubs_log.ldf
> opening file d:\dados\data\DATA_log.ldf
> bypassing recovery for database 'DATA' because it is market SUSPECT
> I don't have backup.
> Tanks

Database Marked Suspect

Hi,
What does a database marked suspect for recovery means and
how could I recover from this?
Thanks in advance
TengHi,
There are many possibilities for suspect status,
1. File being used by another processes during SQL server service start up
(mostly backup process)
2. Transaction Log file corruption
3. Data integrity issue
Solutions:
1. First one can be identified by SQL server logs, "it say file being used
by another process". In this case you case use
sp_resetstatus <dbname> procedure to reset the status and restart sql
server. Now the database wil be online
2. Second case, you can start the sql server in Emergency mode (Update the
sysdatabase table .. Status column to 32768 for the affected database)
3. 3rd case try to execute DBCC CHeckDB with repair_rebuild option. If not
rectified contact Microsoft PSS (Support)
Thanks
Hari
MCDBA
"teng" <anonymous@.discussions.microsoft.com> wrote in message
news:388801c3fdb8$68782560$a301280a@.phx.gbl...
> Hi,
> What does a database marked suspect for recovery means and
> how could I recover from this?
> Thanks in advance
> Teng|||Hi Hari,
Thanks for the reply.
Does these also apply to sql 7.0? I'm trying to do the
second option but can't find the sysdatabase table. Where
should I look for this table.
After starting the server in emergency mode, will it
correct the problem now? Will I be able to start it
normally the next time around?
Teng
>--Original Message--
>Hi,
>There are many possibilities for suspect status,
>1. File being used by another processes during SQL server
service start up
>(mostly backup process)
>2. Transaction Log file corruption
>3. Data integrity issue
>Solutions:
>1. First one can be identified by SQL server logs, "it
say file being used
>by another process". In this case you case use
>sp_resetstatus <dbname> procedure to reset the status and
restart sql
>server. Now the database wil be online
>2. Second case, you can start the sql server in Emergency
mode (Update the
>sysdatabase table .. Status column to 32768 for the
affected database)
>3. 3rd case try to execute DBCC CHeckDB with
repair_rebuild option. If not
>rectified contact Microsoft PSS (Support)
>Thanks
>Hari
>MCDBA
>
>
>"teng" <anonymous@.discussions.microsoft.com> wrote in
message
>news:388801c3fdb8$68782560$a301280a@.phx.gbl...
and
>
>.
>|||Hi,
1. Does these also apply to sql 7.0?
Yes.
2. Where should I look for this table.
Sysdatabases table is in Master database.
Before updating sysdatabases table, Check whether your filed is full. That
also will create a suspect status.
a. If the data file (MDF) is fiull then, you can execute procedure
"sp_add_data_file_recover_suspect_db" (Please refer Books online)
b. If the Log file (LDF)ull then, you can execute procedure
"sp_add_log_file_recover_suspect_db" (Please refer Books online)
Incase above steps fails then do,
Will I be able to start it normally the next time around?
No, You may need pull all the objects and Data into a new database using
DTS.
Thanks
Hari
MCDBA
"teng" <anonymous@.discussions.microsoft.com> wrote in message
news:381401c3fdd6$193331c0$a401280a@.phx.gbl...
> Hi Hari,
> Thanks for the reply.
> Does these also apply to sql 7.0? I'm trying to do the
> second option but can't find the sysdatabase table. Where
> should I look for this table.
> After starting the server in emergency mode, will it
> correct the problem now? Will I be able to start it
> normally the next time around?
> Teng
> service start up
> say file being used
> restart sql
> mode (Update the
> affected database)
> repair_rebuild option. If not
> message
> and|||Here are the general recommendations for handling a suspect or corrupt
database:
0. Ensure you have a backup strategy that you can use to recover from
hardware failures (including corruption). I recommend performing both
database and log backup in most situations.
1. If you can run DBCC CHECKDB against the database: Search Books Online and
KB for the error numbers that CHECKDB gives you. There might be specific
info for that type of error.
2. Find out why this happened. Check eventlog, do HW diagnostics etc.;
search Books Online and KB for those errors. You don't want this to happen
again! If the database is suspect, the file might have been in use by for
instance an anti-virus program and restarting SQL Server might be all that
is needed - but you still want to read logs etc to find out what happened.
3. If there is a hardware problem, ensure the faulty hardware is replaced.
4. Backup the log. This assumes that log backup schedule is in place, of
course. If the database is suspect, then the NO_TRUNCATE option for the
RESTORE command must be used. Also, you might want to do a file backup of
the mdf and ldf files, for extra safety.
5. Restore is the best thing to do now. If you managed to backup log as per
step 4, then you will most probably have zero dataloss. You should restore
the latest clean database backup and the subsequent log backups including
the one taken in above step.
If the database isn't suspect, then DBCC with a REPAIR option might be a
secondary option but this will often result in loss of data. Additional
solutions, depending on the errors, may be to manually rebuild non-clustered
indexes, manually drop and reload a table if the data is static, and so on.
If the database is suspect, a secondary option can be to try to "un-suspect"
the database using sp_resetstatus. Read about it (books online, KB, google
etc). It might help but if the database is too damaged, it might just pop
back to suspect again. There's also something called "emergency mode" which
is a "panic" status you can set in order to try to get data out of a damaged
database. I think the name of that option speaks for itself. Again search
the net for info.
If you feel uncertain with above steps, I recommend letting MS hand-hold you
through the steps appropriate for your particular situation.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"teng" <anonymous@.discussions.microsoft.com> wrote in message
news:388801c3fdb8$68782560$a301280a@.phx.gbl...
> Hi,
> What does a database marked suspect for recovery means and
> how could I recover from this?
> Thanks in advance
> Teng

Database Marked Suspect

Hi,
What does a database marked suspect for recovery means and
how could I recover from this?
Thanks in advance
TengHi,
There are many possibilities for suspect status,
1. File being used by another processes during SQL server service start up
(mostly backup process)
2. Transaction Log file corruption
3. Data integrity issue
Solutions:
1. First one can be identified by SQL server logs, "it say file being used
by another process". In this case you case use
sp_resetstatus <dbname> procedure to reset the status and restart sql
server. Now the database wil be online
2. Second case, you can start the sql server in Emergency mode (Update the
sysdatabase table .. Status column to 32768 for the affected database)
3. 3rd case try to execute DBCC CHeckDB with repair_rebuild option. If not
rectified contact Microsoft PSS (Support)
Thanks
Hari
MCDBA
"teng" <anonymous@.discussions.microsoft.com> wrote in message
news:388801c3fdb8$68782560$a301280a@.phx.gbl...
> Hi,
> What does a database marked suspect for recovery means and
> how could I recover from this?
> Thanks in advance
> Teng|||Hi Hari,
Thanks for the reply.
Does these also apply to sql 7.0? I'm trying to do the
second option but can't find the sysdatabase table. Where
should I look for this table.
After starting the server in emergency mode, will it
correct the problem now? Will I be able to start it
normally the next time around?
Teng
>--Original Message--
>Hi,
>There are many possibilities for suspect status,
>1. File being used by another processes during SQL server
service start up
>(mostly backup process)
>2. Transaction Log file corruption
>3. Data integrity issue
>Solutions:
>1. First one can be identified by SQL server logs, "it
say file being used
>by another process". In this case you case use
>sp_resetstatus <dbname> procedure to reset the status and
restart sql
>server. Now the database wil be online
>2. Second case, you can start the sql server in Emergency
mode (Update the
>sysdatabase table .. Status column to 32768 for the
affected database)
>3. 3rd case try to execute DBCC CHeckDB with
repair_rebuild option. If not
>rectified contact Microsoft PSS (Support)
>Thanks
>Hari
>MCDBA
>
>
>"teng" <anonymous@.discussions.microsoft.com> wrote in
message
>news:388801c3fdb8$68782560$a301280a@.phx.gbl...
>> Hi,
>> What does a database marked suspect for recovery means
and
>> how could I recover from this?
>> Thanks in advance
>> Teng
>
>.
>|||Hi,
1. Does these also apply to sql 7.0?
Yes.
2. Where should I look for this table.
Sysdatabases table is in Master database.
Before updating sysdatabases table, Check whether your filed is full. That
also will create a suspect status.
a. If the data file (MDF) is fiull then, you can execute procedure
"sp_add_data_file_recover_suspect_db" (Please refer Books online)
b. If the Log file (LDF)ull then, you can execute procedure
"sp_add_log_file_recover_suspect_db" (Please refer Books online)
Incase above steps fails then do,
Will I be able to start it normally the next time around?
No, You may need pull all the objects and Data into a new database using
DTS.
Thanks
Hari
MCDBA
"teng" <anonymous@.discussions.microsoft.com> wrote in message
news:381401c3fdd6$193331c0$a401280a@.phx.gbl...
> Hi Hari,
> Thanks for the reply.
> Does these also apply to sql 7.0? I'm trying to do the
> second option but can't find the sysdatabase table. Where
> should I look for this table.
> After starting the server in emergency mode, will it
> correct the problem now? Will I be able to start it
> normally the next time around?
> Teng
> >--Original Message--
> >Hi,
> >
> >There are many possibilities for suspect status,
> >
> >1. File being used by another processes during SQL server
> service start up
> >(mostly backup process)
> >
> >2. Transaction Log file corruption
> >
> >3. Data integrity issue
> >
> >Solutions:
> >
> >1. First one can be identified by SQL server logs, "it
> say file being used
> >by another process". In this case you case use
> >sp_resetstatus <dbname> procedure to reset the status and
> restart sql
> >server. Now the database wil be online
> >
> >2. Second case, you can start the sql server in Emergency
> mode (Update the
> >sysdatabase table .. Status column to 32768 for the
> affected database)
> >
> >3. 3rd case try to execute DBCC CHeckDB with
> repair_rebuild option. If not
> >rectified contact Microsoft PSS (Support)
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >
> >
> >
> >
> >"teng" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:388801c3fdb8$68782560$a301280a@.phx.gbl...
> >> Hi,
> >>
> >> What does a database marked suspect for recovery means
> and
> >> how could I recover from this?
> >>
> >> Thanks in advance
> >> Teng
> >
> >
> >.
> >|||Here are the general recommendations for handling a suspect or corrupt
database:
0. Ensure you have a backup strategy that you can use to recover from
hardware failures (including corruption). I recommend performing both
database and log backup in most situations.
1. If you can run DBCC CHECKDB against the database: Search Books Online and
KB for the error numbers that CHECKDB gives you. There might be specific
info for that type of error.
2. Find out why this happened. Check eventlog, do HW diagnostics etc.;
search Books Online and KB for those errors. You don't want this to happen
again! If the database is suspect, the file might have been in use by for
instance an anti-virus program and restarting SQL Server might be all that
is needed - but you still want to read logs etc to find out what happened.
3. If there is a hardware problem, ensure the faulty hardware is replaced.
4. Backup the log. This assumes that log backup schedule is in place, of
course. If the database is suspect, then the NO_TRUNCATE option for the
RESTORE command must be used. Also, you might want to do a file backup of
the mdf and ldf files, for extra safety.
5. Restore is the best thing to do now. If you managed to backup log as per
step 4, then you will most probably have zero dataloss. You should restore
the latest clean database backup and the subsequent log backups including
the one taken in above step.
If the database isn't suspect, then DBCC with a REPAIR option might be a
secondary option but this will often result in loss of data. Additional
solutions, depending on the errors, may be to manually rebuild non-clustered
indexes, manually drop and reload a table if the data is static, and so on.
If the database is suspect, a secondary option can be to try to "un-suspect"
the database using sp_resetstatus. Read about it (books online, KB, google
etc). It might help but if the database is too damaged, it might just pop
back to suspect again. There's also something called "emergency mode" which
is a "panic" status you can set in order to try to get data out of a damaged
database. I think the name of that option speaks for itself. Again search
the net for info.
If you feel uncertain with above steps, I recommend letting MS hand-hold you
through the steps appropriate for your particular situation.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"teng" <anonymous@.discussions.microsoft.com> wrote in message
news:388801c3fdb8$68782560$a301280a@.phx.gbl...
> Hi,
> What does a database marked suspect for recovery means and
> how could I recover from this?
> Thanks in advance
> Teng|||HI Dear
It means there is no space avaialable for the database to
recover the database.
Sol.-- create some space on the same drive where ur
database is, alter the database and add a new file on it.
First of all detach ur database and restart the system,
then attach it again the add the file.
>--Original Message--
>Hi,
>What does a database marked suspect for recovery means
and
>how could I recover from this?
>Thanks in advance
>Teng
>.
>

Database Maintenance Plan Question

In Microsoft Knowledge Base Article 303292, one of the workarounds
states to create 2 maintenance plans, one for simple, and one for
full/bulk recovery models. Assuming I currently have 10 user
databases, 3 simple and 7 full and I put them into the appropriate
plan, I understand how they would be backed up. How would databases
created after the plans are setup be added?
Thanks much, Lane LesperanceYou would have to change the main plan each time a db is added.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Lane Lesperance" <llesperance@.cooperpower.com> wrote in message
news:66feb5c.0403190933.4f0296a5@.posting.google.com...
> In Microsoft Knowledge Base Article 303292, one of the workarounds
> states to create 2 maintenance plans, one for simple, and one for
> full/bulk recovery models. Assuming I currently have 10 user
> databases, 3 simple and 7 full and I put them into the appropriate
> plan, I understand how they would be backed up. How would databases
> created after the plans are setup be added?
> Thanks much, Lane Lesperance

database maintenance plan - differential backup

I have implemented a backup strategy using the Full Recovery mode. Using the database maintenance plan I created 2 jobs:
1. Full database backup once each night
2. Transaction Log backups every 15 minutes
However, I would also like to implement a differential backup perhaps 4 times per day. This would save me from having to restore dozens of transaction Log backups in the event of a failure towards the end of a day
I know that the database maintenance plan does not support differential backups. Is there any plans to add this in the future?
Also, if I create a separate job for differential backups, how do avoid the T-Log backup and differential backup executing at the same time without increasing the T-Log interval?
What happens if both the T-Log and differential backup occur at the same time?
Scott,
I basically asked the same question on 07/09 with subject "backup sets to
named device". I solved my own problem like this:
Job A runs once daily at zero hours.
Step 1 creates a backup device pointing to local file with sp_addumpdevice.
Step 2 performs full backup with BACKUP DATABASE.
Step 3 removes old backup devices with sp_dropdevice.
Job B runs daily every 4 hours.
Step 1 performs diff backup with BACKUP DATABASE.
Job C runs daily every 10 minutes.
Step 1 performs log backup with BACKUP DATABASE.
Each job uses datetime and string functions to create the device and file
names, such as NorthWind_YYYYMMDD and NorthWind_db_YYYYMMDD.bak. For step 3
of Job A, I query sysdevices to get the names of old devices. You can
determine how many devices and associated backups remain based on the query.
I end up with complete backups for each day encapsulated in a single file
which gets written to tape and stored offsite.
It's working like a charm. I'll post the detailed t-sql if you need it.
Anthony
|||Scott,
Sorry about not really answering your questions in the previous post. I
have had backup jobs executing at the same time and didn't see any problems.
But for my previous suggestion, I have full at zero hours, logs every 10
minutes starting at 00:05 to 23:59, and diffs every 4 hours starting at
04:00 to 23:59. Now none of the jobs intersect.
I haven't looked at Yukon. But since it's .Net based, you will probably
be able to code you're own maint plans.
Anthony
|||Thanks Anthony, I would love to see the T-SQL. It sounds very creative.
My only concern is if both the transaction log and the differential backups execute at the same time, will it cause conflicts somehow.
"Anthony" wrote:

> Scott,
> Sorry about not really answering your questions in the previous post. I
> have had backup jobs executing at the same time and didn't see any problems.
> But for my previous suggestion, I have full at zero hours, logs every 10
> minutes starting at 00:05 to 23:59, and diffs every 4 hours starting at
> 04:00 to 23:59. Now none of the jobs intersect.
> I haven't looked at Yukon. But since it's .Net based, you will probably
> be able to code you're own maint plans.
> Anthony
>
>

database maintenance plan - differential backup

I have implemented a backup strategy using the Full Recovery mode. Using th
e database maintenance plan I created 2 jobs:
1. Full database backup once each night
2. Transaction Log backups every 15 minutes
However, I would also like to implement a differential backup perhaps 4 time
s per day. This would save me from having to restore dozens of transaction
Log backups in the event of a failure towards the end of a day
I know that the database maintenance plan does not support differential back
ups. Is there any plans to add this in the future?
Also, if I create a separate job for differential backups, how do avoid the
T-Log backup and differential backup executing at the same time without incr
easing the T-Log interval?
What happens if both the T-Log and differential backup occur at the same ti
me?Scott,
I basically asked the same question on 07/09 with subject "backup sets to
named device". I solved my own problem like this:
Job A runs once daily at zero hours.
Step 1 creates a backup device pointing to local file with sp_addumpdevice.
Step 2 performs full backup with BACKUP DATABASE.
Step 3 removes old backup devices with sp_dropdevice.
Job B runs daily every 4 hours.
Step 1 performs diff backup with BACKUP DATABASE.
Job C runs daily every 10 minutes.
Step 1 performs log backup with BACKUP DATABASE.
Each job uses datetime and string functions to create the device and file
names, such as NorthWind_YYYYMMDD and NorthWind_db_YYYYMMDD.bak. For step 3
of Job A, I query sysdevices to get the names of old devices. You can
determine how many devices and associated backups remain based on the query.
I end up with complete backups for each day encapsulated in a single file
which gets written to tape and stored offsite.
It's working like a charm. I'll post the detailed t-sql if you need it.
Anthony|||Scott,
Sorry about not really answering your questions in the previous post. I
have had backup jobs executing at the same time and didn't see any problems.
But for my previous suggestion, I have full at zero hours, logs every 10
minutes starting at 00:05 to 23:59, and diffs every 4 hours starting at
04:00 to 23:59. Now none of the jobs intersect.
I haven't looked at Yukon. But since it's .Net based, you will probably
be able to code you're own maint plans.
Anthony|||Thanks Anthony, I would love to see the T-SQL. It sounds very creative.
My only concern is if both the transaction log and the differential backups
execute at the same time, will it cause conflicts somehow.
"Anthony" wrote:

> Scott,
> Sorry about not really answering your questions in the previous post. I
> have had backup jobs executing at the same time and didn't see any problem
s.
> But for my previous suggestion, I have full at zero hours, logs every 10
> minutes starting at 00:05 to 23:59, and diffs every 4 hours starting at
> 04:00 to 23:59. Now none of the jobs intersect.
> I haven't looked at Yukon. But since it's .Net based, you will probably
> be able to code you're own maint plans.
> Anthony
>
>

Database Maintenance Plan

Overview of my database: size - 45 GB, Full Recovery Mode, Log shipping ever 15 minutes; Full Backup nightly; Optimization and integrity checks on Sunday night.

The Database takes a transactional log every 15 minutes and ships it to our failover database. Usually these are small logs and are executed in matter of seconds.
On sunday night there is a problem. The optimization job (part of DB maintenance plan) runs for 3 hours and during this time the transactional log backup continues to runs and takes close to 4 hours of complete. When the tran log completes its close to a 30 GB file and this is causing problems with space and shipping. This also causes the failover server to be out of sync and at times we have to re setup log shipping.

Does anyone know if there is a way to get around this problem. Appreciate all help and comments.Its common to bulge Tlog when db maintenance plan perform DB optimization process and its better to pause Log shipping during this operation and take full backup once Maint.plan finishes the task and apply to standby server, then re-enable LS process.|||Thanks.

Is there a way to automate the pause of the TLog backup? I hate to be up at 3 in the morning to disable/enable the jobs manually.

Database log truncated: Database: MyDatabase.

Hi,

My Database recovery model is set to Bulk Logged. I recently got this error in the Application Event Log.

EVENT LOG Application
EVENT TYPE Error
SOURCE MSSQLSERVER
CATEGORY Backup
EVENT ID 17055
MESSAGE 18278 :
Database log truncated: Database: MyDatabase.

I've checked the MS Knowledgebase, however article 818202 relates to Full Recovery model not Bulk Logged. I would like to know why this error has occurred and what it really means.

Cheers

DamianThis is not an error, it's just information only message. It's no different than when you issue a Truncate_only log backup.

Database log truncated: Database: MyDatabase.

Hi,
My Database recovery model is set to Bulk Logged. I
recently got this error in the Application Event Log.
EVENT LOG Application
EVENT TYPE Error
SOURCE MSSQLSERVER
CATEGORY Backup
EVENT ID 17055
MESSAGE 18278 :
Database log truncated: Database: MyDatabase.
I've checked the MS Knowledgebase, however article 818202
relates to Full Recovery model not Bulk Logged. I would
like to know why this error has occurred and what it really
means.
Cheers
DamianIt means you (or someone) did a BACKUP LOG WITH TRUNCATE_ONLY or =similar statement. And the log files can not be used for recovery until =the next full db backup is taken.
Mike John
"Damian Jolly" <damian.jolly@.sageautomation.com> wrote in message =news:38fe01c3c8dc$ba792e10$7d02280a@.phx.gbl...
> Hi,
> > My Database recovery model is set to Bulk Logged. I
> recently got this error in the Application Event Log.
> > EVENT LOG Application > EVENT TYPE Error > SOURCE MSSQLSERVER > CATEGORY Backup > EVENT ID 17055 > MESSAGE 18278 :
> Database log truncated: Database: MyDatabase.
> > I've checked the MS Knowledgebase, however article 818202
> relates to Full Recovery model not Bulk Logged. I would
> like to know why this error has occurred and what it really
> means.
> > Cheers
> > Damian|||To add to Mike's response, I ran the repro script in the MSKB article but
with the recovery model set to BULK_LOGGED. The error also appeared in the
log with BULK_LOGGED recovery.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Damian Jolly" <damian.jolly@.sageautomation.com> wrote in message
news:38fe01c3c8dc$ba792e10$7d02280a@.phx.gbl...
> Hi,
> My Database recovery model is set to Bulk Logged. I
> recently got this error in the Application Event Log.
> EVENT LOG Application
> EVENT TYPE Error
> SOURCE MSSQLSERVER
> CATEGORY Backup
> EVENT ID 17055
> MESSAGE 18278 :
> Database log truncated: Database: MyDatabase.
> I've checked the MS Knowledgebase, however article 818202
> relates to Full Recovery model not Bulk Logged. I would
> like to know why this error has occurred and what it really
> means.
> Cheers
> Damian