Solved

Sharepoint Services 3.0 very large database

Posted on 2011-09-17
23
1,497 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
Microservice architecture adoption brings many advantages, but can add intricacy. Selecting the right orchestration tool is most important for business specific needs.
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

738 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