Solved

Sharepoint Services 3.0 very large database

Posted on 2011-09-17
23
1,483 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
  • 12
  • 11
23 Comments
 
LVL 51

Expert Comment

by:tedbilly
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:tedbilly
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
 

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:tedbilly
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:tedbilly
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:tedbilly
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:tedbilly
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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:tedbilly
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:tedbilly
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:tedbilly
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:tedbilly
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:
tedbilly 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

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

How to update Firmware and Bios in Dell Equalogic PS6000 Arrays and Hard Disks firmware update.
Create your own, high-performance VM backup appliance by installing NAKIVO Backup & Replication directly onto a Synology NAS!
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

744 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

11 Experts available now in Live!

Get 1:1 Help Now