Hi guys,
This is a good plan but has the follwing risk:
If you backup tran log with truncate_only and if your tran log backup
failed before that you will lose data for tran logs.so make sure that your
tran log back (plan c) is not failed before you run truncate_log .
Hira
>From: Luís Manuel Martins Ganhão <luis.[Email Address Removed] "LazyDBA.com Discussion" <[Email Address Removed] RE: trans log is still big after db backup
>Date: Fri, 4 Oct 2002 12:30:32 +0100
>
>First you have to see what kind of recovery do you have:
>
> - Full or Simple
>
>If you have simple, you only need to make a DBCC shrink log.
>
>
>If you have FULL... that's important:
>
> - You can make PITR (Point in Time Recovery)
> - The Log Files will grow and grow... and grow
> - You must Truncate the Log before you shrink.
>
>What I advise you to do (this is my backup policy).
>
>- All User Databases in Full Recovery Mode
>- Maintenance Plan A that makes Backup of Database (only MDF) every day at
>0:00
>- Maintenance Plan B that makes Backup of Log Files (LDF) every 4 hours
>between 0:00 and 23:59 (with this you can make PITR)
>- Job that run at Wednesday's and Sunday's at 23:55(SQLSERVER JOB) that do
>this:
> DBCC SHRINKFILE(pubs_log, 2)
> BACKUP LOG pubs WITH TRUNCATE_ONLY
> DBCC SHRINKFILE(pubs_log,2)
> (of course you should change the names)
>- Because of this Truncate you should have a Maintencance Plan C that
>Backups Log File at 23:50 every WED and SUN.
>
>If you need to make a PITR, you only will loose 5 minutes between 23:50 and
>23:55 of WED and SUN (I can live with that)... and the log will always be
>small.
>
>Of course you can alter the TRUNCATE LOG to run every day (if the log
>increases a lot)... then you also should change Maintenance Plan C.
>
>If you need to make PITR, don't user the GUI... but Query Analyzer, like
>this:
>
>RESTORE DATABASE [FUSION25]
>FROM DISK = N'C:\Program Files\Microsoft SQL
>Server\MSSQL\BACKUP\fusion\fusion_db_200206242100.BAK'
>WITH FILE = 1, NOUNLOAD , STATS = 10,
>NORECOVERY ,
>MOVE N'fusion_Data'
>TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\FUSION25.mdf',
>MOVE N'fusion_Log'
>TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\FUSION25_log.ldf'
>GO
>
>RESTORE LOG [FUSION25]
>FROM DISK = N'C:\Program Files\Microsoft SQL
>Server\MSSQL\BACKUP\fusion\fusion_tlog_200206251300.TRN'
>WITH FILE = 1, NOUNLOAD , STATS = 10, NORECOVERY
>GO
>
>RESTORE LOG [FUSION25]
>FROM DISK = N'C:\Program Files\Microsoft SQL
>Server\MSSQL\BACKUP\fusion\fusion_tlog_200206261300.TRN'
>WITH RECOVERY, STOPAT = '2002-06-25 02:00 PM'
>GO
>
>
>
>Regards,
>
>
>
>
>Luís Manuel Martins Ganhão
>
>Novabase, Desenvolvimento à Medida
>Serviços de Valor Acrescentado
>DBA Oracle Certified Professional 8i
>Av. Engº Duarte Pacheco, Torre 1 - 9º
>1099-078 Lisboa
>Tel: 351213836300
>Tlm +351967610858
>http://novabase.pt
>
>CONFIDENCIALIDADE: Este e-mail é confidencial e apenas pode ser lido,
>copiado ou utilizado pelo destinatário. Se o recebeu por engano, por favor
>contacte o remetente através de e-mail ou pelo telefone + 351 21 383 63 00.
>Agradecemos ainda que apague este e-mail e não divulgue o respectivo
>conteúdo.
>CONFIDENTIALITY: This e-mail is confidential and may be privileged. It may
>be read, copied and used only by the intended recipient. If you have
>received it in error, please contact the sender immediately by return
>e-mail
>or by phoning + 351 21 383 63 00. Then please delete this e-mail and do not
>disclose its contents to any person.
>
>----------
>This message may contain confidential information or privileged material,
>and is intended only for the individual(s) named. If you are not in the
>named addressee you should not disseminate, distribute or copy this e-mail.
>Please notify the sender immediately by e-mail if you have received this
>e-mail by mistake and delete this e-mail from your system.
>E-mail transmission cannot be guaranteed to be secure or error-free as
>information could be intercepted, corrupted, lost, destroyed, arrive late
>or
>incomplete, or contain viruses. The sender therefore does not accept
>liability for any errors or omissions in the contents of this message which
>arise as a result of e-mail transmission. If verification is required
>please
>request a hard-copy version.
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed] with friends online, try MSN Messenger: http://messenger.msn.com
MS Sql Server LazyDBA home page