Maintenance Plan - General beginner questions

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
0
Utf
9/9/2010 3:41:03 PM
sqlserver.server 1327 articles. 0 followers. Follow

4 Replies
1509 Views

Similar Articles

[PageSpeed] 2

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 
  

0
John
9/9/2010 7:44:36 PM
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
0
Emmanuel
9/9/2010 8:22:52 PM
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

0
Erland
9/9/2010 9:41:30 PM
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
0
John
9/10/2010 7:54:42 AM
Reply:

Similar Artilces:

How do you get the breadth of a question?
example the area is 36cm2 and the length 9cm what's the breadth if your result is shown as it is that is 36cm2 instead of the number 36 cell A1 = 36cm2 B1 = 9cm on cell C1 type this =--SUBSTITUTE(A1,"cm2","")/--SUBSTITUTE(B1,"cm","") Now this formula will only work if the cells have cm2 and cm respectively if they are different like sqft etc.. then you will need a different formula However if its plain 36 and 9 then its simply =a1/b1 However if you "Pual" <Pual @discussions.microsoft.com> wrote in message news:F54B8C3D-13C3-4B36...

quickbooks intergration question
when RMS exports to quick books it post to the main account only not the sub-accounts. eg in quickbooks i have account: 4000 computer sales The sub accounts are 4010 laptop sales, 4020 desktop sales, 4030 monitor sales In RMS the department is computers. and the catagories are laptop, desktop, and monitor Help please -- Thanks Ian ...

Natural Balance in General Ledger
I know I should know the answer to this. How can I turn off the feature that automatically places the cursor on the debit or credit when I make JE's (depending on the balance type)? I want it to automatically land on the debit everytime when I tab over. Thank you! In the account card, you select whether the account has a typical balance of debit or credit. Frank Hamelly, MCP-GP NOVA Solutions LLC Melbourne, FL Frank, thank you for the post. I don't think I explained my question. When I key in a Journal entry I first key in the account string. After that, if you hit the TAB ...

excel question borderline programing
here is what i am trying to do. lets say i want cell a1 to reflect upon cell b1. b1 can represnt 1 of 2 things what ever it may be. if b1 contains lets say x or (2pm-6pm) to make cell a1 represent an (*). but if b1 then has y or somthing like (req off) make a1 either be 0 or better yet be blank. is this possible? email me dkuhl75@yahoo.com. thank you duane Hi Duane, Put =IF(B1="x","*","") in cell A1 if you want it to return blank or =IF(B1="x","*",0) if you want it to return 0 HTH Martin ...

Newbie MFC question: child dialog becomes unclickable!?!
Hi, I'm programmin a visual application using MDI and MFC. The standard document is a real time capture from the camera. I want to create a dialog box staying inside of the mainframe window so i think it must be a child (not popup). but if I set the style to child the dialog becomes unclickable. I'm a newbie to MFC and VC++. Can anybody helps me please? thanks violink Are you using the DoModal method to create and show the dialog? If you're not then I'm not sure what the problem is and you might want to post the code that you're using to show the dialog. If you are usi...

looking for a resource planning template
Need a resource planning template for college course I am undertaking ...

Recovery Group Question
Hi all, My boss asked me to figure out an interesting question and I didn't know the answer. Say we have an Exchange 2000 server and something happens and we need to move it to an Exchange 2003 box. Assume that there are no backups, but we have the Exchange database files, and logs, are we able to use the recovery groups in Exchange 2003 to mount an Exchange 2000 database? Before I get flamed here, no this hasn't happened and yes we backup ALL of our servers...so this is more of a functional question, will it work? Thanks! Michael Fairly certain this will work with Exch...

A question for all of you Microsoft Office Specialists
I have been considering taking the steps to become an Office Specialist. I was wondering if some of you could answer a few questions. I understand that getting the certification can only be good. However, I am wondering how marketable I would become once I completed it. Is it feasible that I would be able to get a job somewhere based solely on the certification, and if so, would pay any better than the McDonald’s down the street? I don't have a college degree but have been considering an Associates Degree to go along with the certification. If I should pursue a 2-year degree, sh...

IF funtion question
Hi all, Kindly assist on the following: eg: 10.00 per 1st 5 items, 5.00 per every additional 5 items Thus, if the total is 20 items, then the answer would be 20.00 Pls adv on generating the formula for the above. Thanks in advance. wm.. Is your maths right? =A1-5+10 George Gee "wm" <wm@discussions.microsoft.com> wrote in message news:1D100FC4-7880-436E-B3CD-8302040D2A2A@microsoft.com... > Hi all, > > Kindly assist on the following: > > eg: 10.00 per 1st 5 items, 5.00 per every additional 5 items > Thus, if the total is 20 items, then the answer wou...

couple of questions from a stumped person
Hi, couple of questions, mainly to do with macro's i think. I'm new t macro code & VBA (actually until last week i didnt know that macro' used VBA) firstly some background: i have a workbook which has 12 worksheets in it, one for each month each worksheet has information for each day that month in rows. i hav another worksheet (HWAWD) in the same workbook which collaborates thi information in a list for the end of each week. this sheet i protected. what i would like help with: i want another (template) sheet in the same workbook which retrieve specific data from HWAWD sh...

a condition question
I have two columns of data. I want to know the average GPA of only those students at Carter Middle School. Help? GPA(COL A) School (COL H) 3.3 Carter Middle School 2.3 Carter Middle School 2.8 Centennial 4.0 Carter Middle School =SUMPRODUCT((B1:B4="Carter Middle School")*A1:A4)/SUMPRODUCT(--(B1:B4="Carter Middle School")) "Penny" <Penny@discussions.microsoft.com> wrote in message news:A4FB97B8-8E72-4431-BA97-3F9259E85200@microsoft.com... >I hav...

Diagnostic logging question
When the diagnostic logging in Exchange 2000 is enabled, where are the logs stored? The Application Event Log. -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "news.microsoft.com" <Xeon@donotemailme.com> wrote in message news:eGHrvbr3FHA.3296@TK2MSFTNGP09.phx.gbl... > When the diagnostic logging in Exchange 2000 is enabled, where are the > logs > stored? > > ...

Question about Excel 2003
When I open a file by Excel 2003, many cells has the tiny triangle(like the sign of a comment) insered in the upper left corner. I have no idea why they appeared because they don't when opened by Excel 2000. Please advise. Thanks in advance. Jason Jason, that is excel "helpful" error checking, have a look at tools options error checking to see what it is checking for, and to turn it off :) -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Ex...

log parsing question
Is there any tool or script that allows one to parse the SMTP logs for servers connecting to bad addresses then given X number of bad addresses, add them to a block list? I have seen an uptick in servers connecting with automated attempts at email addresses which do not exist and never have, and I know it is an obvious spam attempt. I'd like to auto block them after a few junk attempts that don't match addresses in the AD. Any ideas? Is it worth the effort? thanks "tester" <tester@testthis.net> wrote in news:11cntk5jgnedpe6@corp.supernews.com: > Is there...

pst file repair question
Hey guys, I was in the process of backing up a PST before moving the client to exchange. During this process Mcafee decides to kick on, and locks the computer in question, and the pst file is now showing limited emails, but the whole subfolder structure. The pst is 605 meg, and no matter what i do, i can "recover" about 110 megs of mail. I;ve run scanpst.exe, a few free tools that claim to be able to read pst files. Regardless if i use one of these pst readers, or outlook, it only shows me the 110 meg of mail. So my question is, A> can there really be ~500 of dead spa...

An awkward timesheet question
This is a bit difficult to explain. I am trying to set up a timesheet which covers a 13 week span. Because we have employees working a jobshare basis (most do 2 day weeks followed by 3 day weeks), full-timers and part-timers, I have set up a sheet where variables can be input in a header row as follows: Initial balance, Week 1 total hours, number of days worked week 1, week 2 total hours, number of days worked week 2. No problem so far. However, this is being done as a replacement for an old system, and some people willbegin the sheet in deficit, say -(minus)1 hour. I can't seem to get ...

Application Deployment Question
I have developed an application using Access 2k running on Windows XP that I would like to make available to users by downloading from a web site. After reading about “Inno” in an earlier post, I am intending to use it to install the application. 1)Would my application work with all the versions of Access that users may have on their machines? 1a) If it would not work with versions earlier than 2k, what could I do for these users? 2)Would my application work with Access2007 runtime for users that do not have a version of Access already installed? 2a) If yes, wo...

Back up question
I am trying to back on back office and Looks like I am not doing right way. My pc where data sit name is REGISTERPC When I go to back office and try to back up and It asking me USENAME; REGIDTERPC/GUEST AND PASSWORD. I tried all the password didnt work. I tried SA password and My adminstrator password. the otherthing when I turn on guest account on register pc then I got massege Your local setting not allowing. My window furewall is off But antivirus is on . WHat I am doing wrong ? Gill, You can't use the RMS built-in backup routine to backup a database to a workstation'...

A couple of questions...
I have a couple questions regarding a program I'm working on that exports information from an Excel schedule to Outlook Calendar and Tasks: 1) I have several schedules in different Excel files that I need to export. I need to run the exporter from each file everyday to keep my Calendar up to date. How can I have the macro erase all items from the Outlook Calendar with a given address before it exports my dates? In other words: I run the Macro; it scans the outlook calendar and erases all entries with an address of "123 Anywhere St"; then it scans my list of dates and copies the...

Exchange Send as question
Good Day, I have a problem that perhaps somebody out there can help me with. We are running an Exchange Server 2003 with SP1 on a Windows 2003 with SP1 server. I have a user named Anne who is the Admin for two people Aaron and Liz. A few months back Anne wanted rights to send email as Aaron so she can just send email as him with out the On Behalf showing up in the email. I went to class after that and I asked the question on how can I do it. They said that I should just give her full rights to Aaron mailbox. So she already had full rights so I added the from option to her Outlook ...

various problems with automatic account maintenance
I've had really quite a few problems with investment account updates over the years, with different brokers. I can't say that any of them really work well. Recently there's the problem with MMF prices being reported incorrectly. I also have accounts with many spurious "Add shares" transactions, followed a few days later by "Remove shares" transactions, all entered automatically. Dividend reinvestments are routinely added as purchases, followed by dividend received. Same for capital gains. I have account adjustments added from I don't know where, whic...

Font and Line width Question
Greetings, When I use the Process Engineering templates, I add several pieces of equipment. Then connect them with the connector tool. Most times I add a name to the connector. When I do this the default font size is 6. Is there a way to set this to default to 9 in this drawing and all templates? Also the connector weight is the second from the top. Is there a way to set this to default to the third one down in this drawing and all templates? Thanks for your help! There is no simple way to set a default for all drawings. Within a single drawing, go to File > Shapes > Show Do...

Systems Integration question
Does anyone know of any [authoritative] resources (books, papers etc) which compare and contrast different Systems Integration technologies (e.g. J2EE and .NET)? In particular, I'm looking for a comprehensive comparison of several of these techonologies. ...

Pretty simple question
Hello I use Excel 97 in work. I have a frustrating problem. If I have two Excel workbooks open then, in order to switch between them, I have to go to the menu bar and select <Window> and then the appropriate workbook. I am used to Excel 2000 where I can just click the workboook 'icons / tabs' along the bottom of my screen. This is far better. How can I change Excel 97 to allow the open workbooks to display as icons/tabs along the bottom of the screen (as is standard these days). Please help lower my blood pressure... Regards Alex Alex, I don't think that was an ...

Questions about MFC Dialog Box
Right now I am coding a programming that will scan a computer for mp3s and display where they are located in a log or text file. I have built the GUI using VS C++ MFC and things are looking good, but I wanted to add a couple of features that I feel I am a little shaky on, so I figured I would try my luck with you guys! Basically I want to add 2 things: When the user clicks the scan button I would like a popup messagebox stating the the program is infact scanning. The other function would be a pop up that would come about after the scan has ceased. My source code for the button is fairly basic...