Written by:
5/28/2010 

Among the DNN sites that I actively manage is the Orlando DotNetNuke® Users Group (ODUG) website.  There are other user groups using our DotNetNuke® instance too though.  We have been having issues with the size of our database for nearly a year now.  Since this site is on a shared instance of SQL Server, we are limited in how large our database can get.  In order to stay below that limit, I have on many occasions cleared out the tables commonly know to cause this problem, such as the EventLog and SiteLog.  However, we were still living life on the edge of going over our limit.  Since this site doesn’t pay very much, I hadn’t looked much further into it until today.  :)

First of all, I needed to find out what objects are taking up the most space on our database.  Luckily, there is a nice little T-SQL script that I often use for this purpose.  I cannot take the full credit for it though.  It’s a modified version of the popular script that Mitchel Sellers has once blogged about.  Here is my version of Mitch’s excellent script:

   1: -- For storing values in the cursor

   2: DECLARE @TableName VARCHAR(100);

   3:  

   4: -- Cursor to get the name of all user tables from the sysobjects listing

   5: DECLARE [tableCursor] CURSOR FOR 

   6: SELECT [name]

   7: FROM [dbo].[sysobjects] 

   8: WHERE OBJECTPROPERTY([id], N'IsUserTable') = 1

   9: FOR READ ONLY;

  10:  

  11: -- A procedure level temp table to store the results

  12: CREATE TABLE #TempTable (

  13:     [tableName] VARCHAR(100),

  14:     [numberofRows] VARCHAR(100),

  15:     [reservedSize] VARCHAR(50),

  16:     [dataSize] VARCHAR(50),

  17:     [indexSize] VARCHAR(50),

  18:     [unusedSize] VARCHAR(50)

  19: );

  20:  

  21: -- Open the cursor

  22: OPEN [tableCursor];

  23:  

  24: -- Get the first table name from the cursor

  25: FETCH NEXT FROM [tableCursor] INTO @TableName;

  26:  

  27: -- Loop until the cursor was not able to fetch

  28: WHILE (@@Fetch_Status >= 0)

  29: BEGIN

  30:     -- Dump the results of the sp_spaceused query to the temp table

  31:     INSERT  #TempTable

  32:         EXEC sp_spaceused @TableName;

  33:  

  34:     -- Get the next table name

  35:     FETCH NEXT FROM [tableCursor] INTO @TableName;

  36: END

  37:  

  38: -- Get rid of the cursor

  39: CLOSE [tableCursor];

  40: DEALLOCATE [tableCursor];

  41:  

  42: -- remove the text so we can convert the columns

  43: UPDATE #TempTable 

  44: SET [reservedSize] = REPLACE([reservedSize], N' KB', N''), 

  45:     [dataSize] = REPLACE([dataSize], N' KB', N''), 

  46:     [indexSize] = REPLACE([indexSize], N' KB', N''), 

  47:     [unusedSize] = REPLACE([unusedSize], N' KB', N''); 

  48:  

  49: -- convert the columns to INT so they will sort properly

  50: ALTER TABLE #TempTable ALTER COLUMN [reservedSize] INT NULL;

  51: ALTER TABLE #TempTable ALTER COLUMN [dataSize] INT NULL;

  52: ALTER TABLE #TempTable ALTER COLUMN [indexSize] INT NULL;

  53: ALTER TABLE #TempTable ALTER COLUMN [unusedSize] INT NULL;

  54:  

  55: -- Select all records so we can use the reults

  56: SELECT * 

  57: FROM #TempTable 

  58: ORDER BY [dataSize] DESC, [reservedSize] DESC, [indexSize] DESC, [unusedSize] DESC;

  59:  

  60: -- Final cleanup!

  61: DROP TABLE #TempTable;

This script runs and then returns a sorted selection of the tables in the current database, ordered from the largest table, to the smallest.  The integer values are represented as kilobytes (KB).  In the image below, the Packages table is the largest in this particular database, and each table below it is smaller, and smaller.

Well, the ODUG site couldn’t be upgraded today because it would fail during the installation of updated extensions due to the disk space limit on the SQL Server being reached.  (Actually, it’s the disk space limit on our database itself, but the error is the same.)  I had to find a way to clear more space than usual. 

In looking at the results of the query above, I saw that our SeachItemWord table had well over 300,000 records in it.  Whoa!  That’s a lot of records.  Looking into the related search tables, I saw that they all appeared to be larger than necessary.  I am not an expert on the search provider in DNN, but it was pretty easy to figure this out when looking at the number of search records, and comparing that to the size and number of sites on this instance of DNN.  I just needed to get some background to find out what I could and couldn’t get away with when lowering the footprint of these tables.

(Please Note:  The query results above does not reflect the database that I am talking to you about right now.)

Before I began doing some research using my default search engine, I posted a status to twitter asking if anyone knew more about this.  Luckily, an answer came back in no time at all, via ODUG regular and member, Brad Bamford.  He suggested a query that he uses regularly on his production DNN instances to do the very thing I intended to do.  I did modify it slightly though to make it look prettier.  :)

   1: TRUNCATE TABLE [dbo].[SearchItemWordPosition];

   2: DELETE FROM [dbo].[SearchItemWord];

   3: DELETE FROM [dbo].[SearchWord];

   4: DELETE FROM [dbo].[SearchItem];

This query does a couple of simple things.  First, it truncates the SearchItemWordPosition table, which is to day that its records were all deleted, but the identity column gets reset to the original seed value (usually 0 or 1).  Then, this query deletes all of the records in the SearchItemWord, SearchWord, and SearchItem tables.  Easy stuff!  Luckily, there’s only minimal impact to your DNN site – but more on that later…

What impact did this have for my database size?  It literally shrunk my database disk space footprint from 1500 MB to 37 MB!  That’s incredible!  It probably goes without saying, that this site has been in use for a long time, and the search tables have never been managed until now.  Following this bit of DBA work, I was able to successfully upgrade the ODUG site without any other issues. 

Administration Clean-Up

Now that we have completely cleared out the data that DNN would use for a search, we need to fill it back in.  If we don’t, the search won’t properly function.  So, you will need to login using a Host account, and go to the Search Admin page.  You will find that page in the Host menu.  Once there, click on the Re-Index Content link.

Performance

One thing to note is that this process has no affect on the performance of DNN page loads, unlike when we clear the EventLog and SiteLog tables.  This is because the search tables are not hit on hardly any page loads, but rather only when a search is performed.  So, it’s safe to assume that your searches will be much faster, depending on the original table sizes.

Trackback Print

Tags:

Categories: DotNetNuke

Location: Blogs The Mighty Blog

Your name:
Your email:
	(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Cancel