sharepoint db grows quickly

Question

Post reply

sharepoint db grows quickly

sqlfriend

SSC Guru

Points: 52769
More actions
June 5, 2011 at 2:59 pm

#251287

We have a sharepoint collabration site database getting bigger very quickly.
It's for a database named WSS_Content_Collaboration.
It's current size is 75 gb. It grows 30 gb a week.
Is it a pretty normal size for sharepoint db, or do I need to do something?
Thanks

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Samantha J SSC Veteran Points: 212 More actions · Answer 1

Heya!!

High transact database are notorious for getting fat real quick.

I would advise running a simple little script every night to make sure that it is not storing empty space in the database file.

USE Database_name;

GO

ALTER DATABASE Database_name

SET RECOVERY SIMPLE;

GO

DBCC SHRINKFILE (2, 1);

GO

ALTER DATABASE Database_name

SET RECOVERY FULL;

GO

All that will do it look over the database and where the data is sitting, if it finds an empty block that is not being used by the data it returns it to the system, so a 100 gig database could really only have 20 gig of data in it and so on. You don't need to take your database offline or anything to run that.

I hope this helps a little, give me a hollar if you want me to come up with better ideas...

Sam!

martusha SSC Veteran Points: 236 More actions · Answer 2

Also check versioning in your sharepoint. Especially in libraries with large files.

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 3

sqlfriends,

I don't think there is a 'typical' size for a Sharepoint database, that would depend on the amount of data being stored in it.

You don't say if your database in in FULL or SIMPLE recovery model. If in FULL and the growth is in the transaction log, check the frequency of your log backups, more frequent backups will keep this under control.

I would not recommend you run the posted script. As posted it will shrink file id 2, which may or may not be a data file. Read this to see why doing that would be a bad idea http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/.

If file id 2 is the transaction log, then the file will simply grow again as it needs more space - using server resources/affecting performance as it does so.

Changing from FULL to SIMPLE recovery will also break the log chain for disaster recovery. Here's another link http://sqlblog.com/blogs/aaron_bertrand/archive/2009/07/27/oh-the-horror-please-stop-telling-people-they-should-shrink-their-log-files.aspx

sqlfriend SSC Guru Points: 52769 More actions · Answer 4

So, if shrinking files are not a good option, then what should I do?

Just watch it getting bigger every day?

Lowell SSC Guru Points: 323507 More actions · Answer 5

sqlfriends (6/6/2011)
So, if shrinking files are not a good option, then what should I do?
Just watch it getting bigger every day?

regularly scheduled transaction log backups throughout the day.

a database in FULL mode adds transactions to the log file, right?

if you never do a backup, the transaction log gets HUGE on a busy system.

regular transaction log backups dump the changes into the backup for recovery, and the space int he log is re-used for new transactions again. This is one of the first things you'll need to learn about FULL recovery mode...if you don't back it up, it grows with every transaction.

there's probably ten thousand posts on this site where someone posts "I need help, my transaction log is full and I'm out of disk space!", precisely because they never do backups at all.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

martusha SSC Veteran Points: 236 More actions · Answer 6

Lowell is right. If you db grow cause transaction logs, configure maitenance plan to backup them every 15 min. This make trn log size under control.

Aslo you can check article: Database Maitenance for Sharepoint

http://technet.microsoft.com/en-us/library/cc262731.aspx

forsqlserver SSCoach Points: 18902 More actions · Answer 7

Shrink Creates Fragmentation, U can use other ways to shrink the database.

check the mdf,Ldf size,

Take a full backup,

Change the recovery model to simple,

again do a full backup,

check the ldf file size now it will be decreased,

change the recovery model to full.

Please correct me..if wrong

Thanks

Lowell SSC Guru Points: 323507 More actions · Answer 8

forsqlserver (6/7/2011)
Shrink Creates Fragmentation, U can use other ways to shrink the database.
check the mdf,Ldf size,
Take a full backup,
Change the recovery model to simple,
again do a full backup,
check the ldf file size now it will be decreased,
change the recovery model to full.
Please correct me..if wrong

While what you state here is true, it has the critical side affect of breaking the log chain, making any previous transaction logs unusable and prevents you from doing a point in time restore from the last time a backup was made to when you switched the recovery model to SIMPLE.

the purpose of the FULL recovery mode is to have the maximum ability to recover to specific points in time. to do that, you need a FULL backup, and then transaction log backups after that point. If your db is going to be in FULL recovery mode, you must

put a backup plan in place. If you have a db that is in FULL, but has never had a backup taken on it, there's a breakdown in the DBA's responsibilities somewhere.

If you don't need to have that granular of a restore point, then the database should just be in SIMPLE mode. Toggling between the two recovery modes is not the right way to try and recover some disk space.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 9

forsqlserver (6/7/2011)
Shrink Creates Fragmentation, U can use other ways to shrink the database.
check the mdf,Ldf size,
Take a full backup,
Change the recovery model to simple,
again do a full backup,
check the ldf file size now it will be decreased,
change the recovery model to full.
Please correct me..if wrong

What you said is wrong. Changing the recovery model will not shrink the files.

Hello SQLfriends,

Pls read gail's article already given.