Database is "too" large

This forum is only for questions or discussions about working with the mojoPortal source code in Visual Studio, obtaining the source code from the repository, developing custom features, etc. If your question is not along these lines this is not the right forum. Please try to post your question in the appropriate forum.

Please do not post questions about design, CSS, or skinning here. Use the Help With Skins Forum for those questions.

This forum is for discussing mojoPortal development

This forum is only for questions or discussions about working with the mojoPortal source code in Visual Studio, obtaining the source code from the repository, developing custom features, etc. If your question is not along these lines this is not the right forum. Please try to post your question in the appropriate forum.

You can monitor commits to the repository from this page. We also recommend developers to subscribe to email notifications in the developer forum as occasionally important things are announced.

Before posting questions here you might want to review the developer documentation.

Do not post questions about design, CSS, or skinning here. Use the Help With Skins Forum for those questions.
This thread is closed to new posts. You must sign in to post in the forums.
3/4/2014 2:06:22 PM
Jerry McCreary
Gravatar
Total Posts 63
View Posts
Jerry McCreary
SureFire Studios, Inc.

Database is "too" large

I have a mojoportal site where the MS SQL database size seems excessively large.

Are there steps I can take to reduce the database file size?

Stats from SQL Server manager:

  • Total Space Usage: 3,956.00 MB
  • Data Files Space Usage: 218.00 MB
  • Transaction Log Space Usage: 3,738.81 MB

Disk Usage by Top Tables

  • dbo.mp_IndexingQueue:   # Records: 10,064;   Data 103,504 [KB]
  • dbo.mp_ContentHistory:    # Records 7,013;    Data 86,568 [KB]
  • dbo.mp_ModuleSetting:     #Records 25,401;  Data 5,872 [KB]
  • dbo.mp_HtmlContent:        #Records 599;      Data 4,160 [KB]
  • dbo.mp_PageModules:     #Records 1,270;   Data 224 [KB]
  • dbo.mp_Pages:                  #Records  324;      Data 240 [KB]

We are now using Google Search instead of the internal Lucene.
Can dbo.mp_IndexingQueue be reduced?

Re Content History:
The content history can obviously be reduced … at least manually.
Can content history be limited to n-number of iterations?

Thanks for any advice.

3/4/2014 2:21:13 PM
Joe Audette
Gravatar
Total Posts 18439
View Posts

Re: Database is "too" large

Hi Jerry,

You can remove un-needed or wanted data using delete statements or truncate table statements, but that won't reclaim any disk space. To do that see the dbcc shrinkdatabase thread on stackoverflow. So basically delete any data you don't want, shrink the database then defrag the database are the steps to reduce the size after a cleanup. We don't have anything built in to limit the content history but you can run queries to delete some or all of it and you can disable it.

You can disable the internal search by putting this in user.config

<add key="DisableSearchIndex" value="true"/>

which should prevent the indexing table from growing again.

Hope that helps,

Joe

3/4/2014 2:33:40 PM
Jerry McCreary
Gravatar
Total Posts 63
View Posts
Jerry McCreary
SureFire Studios, Inc.

Re: Database is "too" large

Joe,

mp_IndexingQueue
If we are using Google Search, does than mean it is safe to delete the data from mp_IndexingQueue table?

mp_ContentHistory
If I delete all the data from mp_ContentHistory using a delete query, are there any related tables I need to be aware of?

Thanks for the link on shrinkdatabase; I'll check that out.

-- Jerry

3/4/2014 2:39:15 PM
Joe Audette
Gravatar
Total Posts 18439
View Posts

Re: Database is "too" large

yes you can delete or truncate table (which is much more efficient) mp_IndexingQueue

TRUNCATE TABLE mp_IndexingQueue

should do it efficiently

no there are no related tables to worry about for mp_ContentHistory

Best,

Joe

3/4/2014 2:42:00 PM
Jerry McCreary
Gravatar
Total Posts 63
View Posts
Jerry McCreary
SureFire Studios, Inc.

Re: Database is "too" large

Thanks for the help and quick reply.

3/4/2014 2:42:46 PM
Joe Davis
Gravatar
Total Posts 2239
View Posts

Re: Database is "too" large

Hi Jerry,

Removing the data from the mp_IndexingQueue isn't going to help you much.

Notice most of the space is being used by your log which suggests the database hasn't been properly backed up for quite a while. See how the total for all of the tables you listed doesn't add up to anything close to 3,956 MB total usage you listed? Your regular database size is 218MB which isn't too outrageous if the site is rather large.

If regular backups are occurring, the problem is probably just "leftover" from when it wasn't being backed up properly. Basically, in the SQL Server world shrinking databases is seen as taboo because, among other things, it reduces the performance of a database if it must grow on disk to accommodate the data it needs to store. Usually I would be in line with the argument but when a database has unnecessarily grown to a size it would not naturally be due to a misconfiguration of the server, database, application, it is absolutely necessary. 

Here are some good articles on this subject:

HTH,
Joe D.

3/4/2014 3:19:09 PM
Jerry McCreary
Gravatar
Total Posts 63
View Posts
Jerry McCreary
SureFire Studios, Inc.

Re: Database is "too" large

Joe D.

This info will help when I speak with the system administrator.
I'm not sure what backup steps are being implemented (if any).

Note of interest … I compared an earlier installation of the mojoportal
on that same server, and the transaction log is 1/10th the size (~300 MB).

From reading the links you posted, it appears that not doing a backup (or doing one improperly) can cause the transaction log to become so large. Is that correct. Do you know if there are other common reasons for it to grow so large? 

-- Jerry

UPDATE:

I just checked SQL Server management studio … the database is being backed-up, but not the database log.

You must sign in to post in the forums. This thread is closed to new posts.