Morning,

I am creating my first maintenance job today and I have some general 
questions about the order of steps regarding the db and t-logs. I am looking 
to create 3 jobs that run nightly on a production database for my company. 

First Job:
Check Database Integrity
Reorganize Index
Rebuild Index
Update Statistics
Check Database Integrity

Second Job:
Backup Database
Cleanup History

Third Job:
Backup Transaction Log
Execute T-SQL statement ( Truncate the Log) ... is this needed???
Maintenance Cleanup Task (Logs)

I am not sure whether I have the correct ordering of these events, and any 
suggestions would be fantastic. 

I am also wondering if it is possible to backup to a network drive via 
"Backup Database Task". It seems as though the only selection options are 
physical disks although I can backup to a network drive using a simple backup 
SQL statement. Am I missing something or can i manipulate or trick the 
program somehow?

Thanks in advance.

Antony

On Thu, 9 Sep 2010 08:41:03 -0700, Noncentz
<Noncentz@discussions.microsoft.com> wrote:

>Morning,
>
>I am creating my first maintenance job today and I have some general 
>questions about the order of steps regarding the db and t-logs. I am looking 
>to create 3 jobs that run nightly on a production database for my company. 
>
>First Job:
>Check Database Integrity
>Reorganize Index
>Rebuild Index
>Update Statistics
>Check Database Integrity
>
>Second Job:
>Backup Database
>Cleanup History
>
>Third Job:
>Backup Transaction Log
>Execute T-SQL statement ( Truncate the Log) ... is this needed???
>Maintenance Cleanup Task (Logs)
>
>I am not sure whether I have the correct ordering of these events, and any 
>suggestions would be fantastic. 
>
>I am also wondering if it is possible to backup to a network drive via 
>"Backup Database Task". It seems as though the only selection options are 
>physical disks although I can backup to a network drive using a simple backup 
>SQL statement. Am I missing something or can i manipulate or trick the 
>program somehow?
>
>Thanks in advance.
>
>Antony


Hi Antony

Truncating the transaction log should not be necessary under normal
operations. The size of the transaction log should be set at a size
that is large enough for normal activity as it take time and resources
to increase the size an you don't want that to happen when the system
is under load.

I assume the database is in FULL recovery if you are backing up the
log. If you try to backup the log on a database with SIMPLE recovery
you will get an error message. This makes using options such as all
user databases not as attactive as they may initially appear.

You should not need to rebuild and re-organize the indexes together
unless they are on exclusive sets of tables. For example tables with
small amounts of data or few updates may be reorganised abnd heavily
changed tables rebuilt. As the task does not allow selectivity choose
indexes to be rebuilt or re-organised it may attempt to rebuild an
index that is not very fragmented. This is one of the reason why some
DBA will use a script solution rather than the maintenance plan.

I assume that you do not rebuild the indexes if the database integrity
check fails. The second database integrity check only display
something that has occurred during the the rebuild so it probably has
limited value.

If you don't have any non-index statistics you won't need to update
the statistics as they will be up-to-date after a rebuild. 

John 
  

Anyone that would give you a straight 123 answer is crazy. It really depends on the your business and what it is that you want to accomplish. 

If your database is a high transaction database (meaning you have alot of writes) you should be backing up the transaction log frequently (every 30 min, every 1hr) 

If your database is a high transaction database you probaly should only truncate the log (don't shrink) and do this after hours. 

If you have any data import processes you should perform your backups afterward. You should truncate the logs afterwards as well. 

Integrity checks should be performed on the back ups as well the current database. Weekly for the current live database is acceptable in my opinion. Integrity checks on the back ups should be performed immediately. 

I recommend you create an auidit of the data intensive processes that run againts that database server and schedule around them if your a 24 7 business. If you do have after hours establish a blackout period where the database shouldn't be touched by back end processes you can run your maintenance cycles without contention.

Submitted via EggHeadCafe - Software Developer Portal of Choice 
ComponentOne Studio for ASP.NET AJAX - Free License Giveaway
http://www.eggheadcafe.com/tutorials/aspnet/ce98ce1f-2b5d-4ec8-b6d5-a1049651514e/componentone-studio-for-aspnet-ajax--free-license-giveaway.aspx

Noncentz (Noncentz@discussions.microsoft.com) writes:
> I am creating my first maintenance job today and I have some general 
> questions about the order of steps regarding the db and t-logs. I am
> looking to create 3 jobs that run nightly on a production database for
> my company. 
> 
> First Job:
> Check Database Integrity

Make sure that if this job fails, that you get an alarm. Well, that 
applies to any step, but this is particularly important, because
integrity errors are usually due to hardware issues, which means 
that if you get one - more may be coming.

It's fairly pointless to run DBCC CHECKDB and never look at the logs.

> Reorganize Index
> Rebuild Index
> Update Statistics

There is little need to do all these three on the same table. REORGANIZE
and REBUILD achieves the same thing, but in different ways. And UPDATE
STATISTICS is implied by a REBUILD.

The best is to rebuild indexes only if fragmentation is above a certain
level (usuaully 30%) and run UPDATE STATIISTCS WITH FULLSCAN, INDEX if
you don't rebuild.

Rebuilding indexes nightly may a bit overkill. Most shops do it weekly,
although for tables nightly be needed - or at least statistics needs 
to be updated.

> Second Job:
> Backup Database
> Cleanup History
> 
> Third Job:
> Backup Transaction Log

I hope you back up the transaction log more often once a night. Every
15 minutes is good.

But the important thing here is not the backups. It is the restore.
Have you planned how to do a restore in case of an emergency. This
is something you should rehearse, particularly if your business cannot
afford any longer downtime.

> Execute T-SQL statement ( Truncate the Log) ... is this needed???

No, and it's a bad idea to. The log is truncate it when you back up,
and any explicit truncate without backup breaks up the log chain, so
that you cannot to a point-in-time restore.

> I am also wondering if it is possible to backup to a network drive via 
> "Backup Database Task". It seems as though the only selection options
> are physical disks although I can backup to a network drive using a
> simple backup SQL statement. Am I missing something or can i manipulate
> or trick the program somehow? 
 
Dunno. I don't do maintenance plans myself.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

On Thu, 09 Sep 2010 20:44:36 +0100, John Bell
<jbellnewsposts@hotmail.com> wrote:

>On Thu, 9 Sep 2010 08:41:03 -0700, Noncentz
><Noncentz@discussions.microsoft.com> wrote:
>
>>Morning,
>>
>>I am creating my first maintenance job today and I have some general 
>>questions about the order of steps regarding the db and t-logs. I am looking 
>>to create 3 jobs that run nightly on a production database for my company. 
>>
>>First Job:
>>Check Database Integrity
>>Reorganize Index
>>Rebuild Index
>>Update Statistics
>>Check Database Integrity
>>
>>Second Job:
>>Backup Database
>>Cleanup History
>>
>>Third Job:
>>Backup Transaction Log
>>Execute T-SQL statement ( Truncate the Log) ... is this needed???
>>Maintenance Cleanup Task (Logs)
>>
>>I am not sure whether I have the correct ordering of these events, and any 
>>suggestions would be fantastic. 
>>
>>I am also wondering if it is possible to backup to a network drive via 
>>"Backup Database Task". It seems as though the only selection options are 
>>physical disks although I can backup to a network drive using a simple backup 
>>SQL statement. Am I missing something or can i manipulate or trick the 
>>program somehow?
>>
>>Thanks in advance.
>>
>>Antony
>
>
>Hi Antony
>
>Truncating the transaction log should not be necessary under normal
>operations. The size of the transaction log should be set at a size
>that is large enough for normal activity as it take time and resources
>to increase the size an you don't want that to happen when the system
>is under load.
>
>I assume the database is in FULL recovery if you are backing up the
>log. If you try to backup the log on a database with SIMPLE recovery
>you will get an error message. This makes using options such as all
>user databases not as attactive as they may initially appear.
>
>You should not need to rebuild and re-organize the indexes together
>unless they are on exclusive sets of tables. For example tables with
>small amounts of data or few updates may be reorganised abnd heavily
>changed tables rebuilt. As the task does not allow selectivity choose
>indexes to be rebuilt or re-organised it may attempt to rebuild an
>index that is not very fragmented. This is one of the reason why some
>DBA will use a script solution rather than the maintenance plan.
>
>I assume that you do not rebuild the indexes if the database integrity
>check fails. The second database integrity check only display
>something that has occurred during the the rebuild so it probably has
>limited value.
>
>If you don't have any non-index statistics you won't need to update
>the statistics as they will be up-to-date after a rebuild. 
>
>John 
>  

For the backup to a network drive have you tried a UNC path?

You may want to look at something like
http://ola.hallengren.com/Documentation.html


John