Sharepoint Services 3.0 very large database

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.
caratechAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ted BouskillConnect With a Mentor Senior Software DeveloperCommented:
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
 
Ted BouskillSenior Software DeveloperCommented:
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
 
caratechAuthor Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Ted BouskillSenior Software DeveloperCommented:
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
 
caratechAuthor Commented:
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
 
Ted BouskillSenior Software DeveloperCommented:
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
 
caratechAuthor Commented:
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
 
Ted BouskillSenior Software DeveloperCommented:
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
 
caratechAuthor Commented:
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
 
Ted BouskillSenior Software DeveloperCommented:
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
 
caratechAuthor Commented:
I am logged as domain administrator, this is the username I used to install the system
System is an SBS 2008.
0
 
Ted BouskillSenior Software DeveloperCommented:
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
 
caratechAuthor Commented:
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
 
Ted BouskillSenior Software DeveloperCommented:
Yes you should be able to use Management Studio to do it and yes your steps are correct.
0
 
caratechAuthor Commented:
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
 
Ted BouskillSenior Software DeveloperCommented:
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
 
caratechAuthor Commented:
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
 
Ted BouskillSenior Software DeveloperCommented:
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
 
caratechAuthor Commented:
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
 
Ted BouskillSenior Software DeveloperCommented:
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
 
caratechAuthor Commented:
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
 
caratechAuthor Commented:
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
 
caratechAuthor Commented:
Thanks for the suggestion, as I explain in my previous post,  no one could help me on this one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.