Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 840
  • Last Modified:

SQL server maintenance plan for SHarePoint Server 2007 in SQL Server 2005 ?

Hi People,

Can anyone here please let me know me what is the suggested SQL server maintenance plan for my SharePoint Server 2007 so that the database can runs at the highest performance ?

what are the type of maintenance and when to execute it ?

Background:

Application Server
Windows Server 2003 Std 32 bit
SharePoint 2007
CA DB - SQL Server Express Edition --> I guess that there is nothing else I can do here to gain performance.

Database Server
Windows Server 2003 Std 32 bit
SQL Server 2005 SP4
WSS_Content DataBase --> what sort of SQL maintenance task to create and run it on schedule overnight ?

The SharePoint Server 2007 is running InfoPath form library to submit document based on certain workflow, the document size is ranging from 1 MB up to 20 MB attachments. it is used during the business hours by 350 users.
0
Senior IT System Engineer
Asked:
Senior IT System Engineer
  • 5
  • 4
  • 3
5 Solutions
 
QPRCommented:
This is the good oil when it comes to cutting through all the supposed dos and don'ts when it comes to sql maintenance for sharepoint

http://www.sqlskills.com/blogs/kimberly/database-maintenance-best-practices-part-i-clarifying-ambiguous-recommendations-for-sharepoint/
0
 
David ToddSenior DBACommented:
Hi,

I use Ola Hallegren's free maintenance script to build jobs for this stuff.
http://ola.hallengren.com/

Although having said this, I've added to Ola's backups for one client who has a backup server that everything is copied to.

SharePoint does its own backups (although I backup the db's anyway) because it backs up a site which includes related databases, and there are things that need to be backed up that aren't in the databases.

HTH
   David
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
Thanks David,

So in this case I just let it run after hours once I created the SQL maintenance job under the SQL 2005 agent object ?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
QPRCommented:
You schedule it to run when you want it to. Did you read the article I posted? Ola's scripts are great, I use them extensively, but you won't want to enable all parts by default and quite a bit of it may be confusing depending on your knowledge of SQL server
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
ah yes, so that's the thing, I know nothing about SQL server apart from backing up and restoring the database. hence I ask the question here for the best practice for the SQL maintenance job to be run regularly to optimize the SharePoint server.
0
 
QPRCommented:
Read the article to see what you need to tick in the maintenance plan and what to not tick because SharePoint already does it eg stats
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
ok, I've managed to find the version of my sharepoint server from Central Administration > Operations > Servers in Farm  , it is 12.0.0.6690

what version is that ? I cannot find it in anywhere by Googling around ?

I'm about to apply the scheduled after hours SQL script from http://support.microsoft.com/kb/943345 but not sure if it is applicable since I cannot determine the version of my WSS 3.0

Note:  the scheduling and running of the script are included in SP2
0
 
QPRCommented:
I'm a bit lost, why are you concerned with versions and why are you looking at defragmentation?
Create a maintenance plan in SQL, the wizard will guide you through backup options and locations as well as integrity checks and index rebuilds (can ignore the statistics step). Then you get the option to schedule the MP and you are done
0
 
David ToddSenior DBACommented:
Hi,

Question:
How big are your databases relative to the server, and when were they last defragged? It could be then quite big and time consuming to do everything at once.

What I do when introducing a defrag job for the first time, is to look at the stats first, then manually do one table's clustered index, and get a feel for how long it will take and whether size will blow out or not. (The rebuild can leave up to 1.4 times the largest index/table behind as free space - this takes room you know. That is not a biggie for dbs with lots of similarly sized tables and indexes. It is a biggie if there are a few large transaction tables dating back to when Adam was a boy. You know the kind. Table is 10GB, total database is 12GB.

The I manually run the job that Ola's script creates on the db in question, and set the rebuild pretty high like 80%, and the reindex at 79% - its gotta be less than the rebuild option. And then in an iterative fashion lower this in steps back to the normal setting of 30% and 5% for the lower bound of the reindex,

HTH
  David

PS The rebuild should be cumulative. That is a half rebuilt index should be half as fragmented as previously, so nothing is wasted if you have to cancel. But if you do, do allow it to cancel in its own time.
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
The WSS 3.0 version from the Add/Remove program is shown as 12.0.6612.1000...

is that SP1 or SP2 ?
0
 
David ToddSenior DBACommented:
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
THanks !!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now