Solved

Sharepoint Services 3.0 very large database

Posted on 2011-09-17
23
1,493 Views
Last Modified: 2013-12-01
Hello,

I have an SBS 2008 on which I am running WSS 3.0 for our intranet.

The ShareWebDb.mdf database has grown to oaver 120Gb!
Although our intranet is heaviuliy used, I don't understand how the database can be that large.
Disk space is not a problem at the moment but I am curious as to why the ShareWebDb.mdf is so large.
I have downloaded a tool (SharePoint Monitor by Tim Dobrinski) which reports that the Site is only 50.6Mb.
Is there any way of checking for sure why the discrepancy?
Any suggestions appreciated.
0
Comment
Question by:caratech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 11
23 Comments
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 36556994
By any chance do you have all auditing options turned on?  Auditing is great for adding metrics but it adds an unreasonable amount of extra data to the database.

Do you have auditing turned on?
0
 

Author Comment

by:caratech
ID: 36557013
This is WSS 3.0, as far as I know it auditing is not included?
I dont know how to check if it is on or off.

Thanks

0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 36557022
You can turn it on using the API and some people do: http://blog.sallarp.com/enable-auditing-in-wss-3/

Plus I wouldn't completely trust a 3rd party tool.  You might actually be using that much space.

Do you have multiple site collections?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:caratech
ID: 36557040
Only Company web.
7 sub sites, but only is used daily, if the tool I sued is to be believed, it only uses about 50Mb.
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 36557051
Well, if you have a copy of SQL Management Studio running you could trying shrinking the MDF file.  Sadly SharePoint doesn't provide any maintenance routines by default to keep the SQL instance running well.
0
 

Author Comment

by:caratech
ID: 36557061
Already tried that, it only shows 928Mb available free space.
I trioed to run it anyway and eventually it timed out.
Attach
sql.jpg
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 36557078
Have you run any maintenance to detect index fragmentation?

http://support.microsoft.com/kb/943345

SharePoint makes heavy use of GUID's for primary keys which causes excessive fragmentation which in turn can waste space.
0
 

Author Comment

by:caratech
ID: 36557102
Tried to run the scrip as indicated in the KB article.
I get this error when it executes from SQL Studio Express:

Msg 33003, Level 16, State 1, Procedure proc_DefragmentIndices, Line 84
DDL statement is not allowed

Any ideas?
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 36557110
Ah, the account you are running doesn't have sufficient privileges to run the script.  In order to have DDL privileges you have to be a DB sysadmin.
0
 

Author Comment

by:caratech
ID: 36557132
I am logged as domain administrator, this is the username I used to install the system
System is an SBS 2008.
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 36557146
When SharePoint installs it sets custom access for the database that can block the domain admin who may not automatically inherit the right permissions.
0
 

Author Comment

by:caratech
ID: 36557150
How do I find out which user will have DB sysadmin then?
Can I use Management Studio to add the current user as a DB sysadmin?
If so, do I rightlcik on ShareWebDb, properties, Permissions, add?

Thanks
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 36557161
Yes you should be able to use Management Studio to do it and yes your steps are correct.
0
 

Author Comment

by:caratech
ID: 36557192
Oh well, tried that, made no difference.
I'm not too confident in making changes with SQL just in case I end up screwing things up.
It seems I will have to leave it as it is, althouh the damn thing is growing on a daily basis :)

0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 36557206
That script is safe and wil help performance.  I ran it consistently on a large farm used 24/7 globally.

I must admit there were aspects of how SharePoint stored data that were a little frustrating.

If you use more site collections you can split the load out into multiple databases.  With WSS 3/MOSS 2007 100 GB is the recommended limit per site collection.

This administration toolkit can be used to move site collections to new content databases (and it might do a better job of reporting the true DB size)

http://www.microsoft.com/download/en/details.aspx?id=1219
0
 

Author Comment

by:caratech
ID: 36557249
Thanks

I dont get it why the script doesnt run in my environment, have you tried it on a SBS 2008 WSS 3.0 installation?
I think that WSS 3.0 runnning the Windows internal database is a bit quirkier than standard SQL...

I have dowloaded the tool and will install it tomorrow, I'll let you know if I get anywhere with it.

Thanks for all your help so far.
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 36557257
I've used all these scripts on WSS 3 or MOSS 2007, not the SBS integrated version.  As far as I know there is no functional differences.
0
 

Author Comment

by:caratech
ID: 37052894
Sorry for the delay.. been rather busy on other things.

I have discovered why thge database is so large.
I have a list that we use as part of our support call logging system and this list had versioning enabled (without limit)!
This has caused a table (AllDocsVersions) to grow to over 225Gb !!!
I have now diabled versioning on the list but I dont know whjat to do about removing all the previous versions for every entry on ther list (there are over 2000 entries).
I have tried to manually deleting all versions (by viewing the inten in datasheet mode and viewing previous versions) but this will take me forever and I am not sure the table will be emptied anyway.

Any ideas of a script that will automatically go thrugh each item in the list and delelete ALL previous versions?

Thanks
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 37086844
Well, you could try saving the list as a template including content, then deleting the current one, then recreating it using the template.  Try it on a second list with a different name first to be sure it works.
0
 

Author Comment

by:caratech
ID: 37088241
Hi,

Tried that but it fails with a nondescriptive error.
I have now engaged MS support.
They have been working on it for over 4 days now and as yet no resolution.
I will let you know if they can sorted and how..
0
 
LVL 51

Accepted Solution

by:
Ted Bouskill earned 500 total points
ID: 37088478
Sadly, PowerShell support for this version is limited, otherwise we could script it.

For example, instead of orphaning the previous versions we could have left versioning on, then for each document collapse all the previous versions, then disable versioning.

This guys has some great custom extensions for SharePoint and this article talks about copying a list and might be able to do what you need by copying the list without bringing the version information, then you can delete the old list:
http://blog.falchionconsulting.com/index.php/2007/09/importexportcopy-lists/
0
 

Author Comment

by:caratech
ID: 37364997
Sorry for the delay...

In the end I got MS involved.
They tried all sorts of ways to try resolve the issue, exporting the DB to a new one etc.
None were satisfactory so I have left it as is it. I will be starting afresh with SP 2010 later in the year so it will be a fresh database.
The moral of the story is: make sure that versioning is not enabled on lists/libraries on which items are being accesssed a large number of times.!
I will award the points as you try your best.
Thanks
0
 

Author Closing Comment

by:caratech
ID: 37365001
Thanks for the suggestion, as I explain in my previous post,  no one could help me on this one.
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

These days socially coordinated efforts have turned into a critical requirement for enterprises.
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

735 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