Solved

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

Posted on 2014-07-28
12
800 Views
Last Modified: 2014-08-18
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
Comment
  • 5
  • 4
  • 3
12 Comments
 
LVL 29

Assisted Solution

by:QPR
QPR earned 200 total points
ID: 40226070
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
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 300 total points
ID: 40227627
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
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 40228260
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
 
LVL 29

Assisted Solution

by:QPR
QPR earned 200 total points
ID: 40228270
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
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 40228299
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
 
LVL 29

Expert Comment

by:QPR
ID: 40228318
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 40228352
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
 
LVL 29

Expert Comment

by:QPR
ID: 40228374
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
 
LVL 35

Accepted Solution

by:
David Todd earned 300 total points
ID: 40228443
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
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 40230754
The WSS 3.0 version from the Add/Remove program is shown as 12.0.6612.1000...

is that SP1 or SP2 ?
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 300 total points
ID: 40230912
0
 
LVL 7

Author Closing Comment

by:Senior IT System Engineer
ID: 40269502
THanks !!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now