Care and Feeding Of Community Server

Tuesday, February 7, 2006

Over the weekend, OdeToCode bumped up against it’s SQL Server disk space quota. Some operations, like adding comments, were throwing exceptions because the primary filegroup was full. Instead of paying for more disk space, I looked to see if there was some extra baggage I could get rid of.

Step 1 of the adventure was to verify the database size. The sp_helpdb stored procedure verified that the database was using almost all of the 100MB allocated by the ISP.

Step 2 was to see investigate the space reserved by each table in the database. The sp_spaceused stored procedure can list the amount of disk space used and reserved by a table, but only for one table at a time. The trick is to execute sp_spaceused with sp_MSforeachtable. sp_MSforeachtable is an undocumented tidbit inside SQL Server, but you can find information around the web (see Raymond Lewallen’s post, as a good example). The following script will display the space reserved and used for each table in the current database.

CREATE TABLE #TableSizes
(
  table_name SYSNAME,
  row_count int,
  reserved_size varchar(10),
  data_size varchar(10),
  index_size varchar(10),
  unused_size varchar(10)
)

INSERT #TableSizes
   EXEC sp_MSforeachtable 'sp_spaceused ''?'''

SELECT * FROM #TableSizes ORDER BY table_name 

The good news from the script was that content (blog posts, articles, and comments) only needed 10MB of storage. I still have plenty of headroom for incoherent ramblings.

Three tables stood out for using almost 70MB of the site’s 100MB quota: cs_Urls, cs_Referrals, and cs_SearchBarrel. I didn’t want to take away any of the search capabilities, as I actually use the search feature myself. The other two tables, however, track who has been coming to the site and from what location. I tend not to make use of this information, so the next step was to free up 60MB of space:

BEGIN TRANSACTION

DELETE FROM cs_Referrals WHERE LastDate < '1/1/2006'

DELETE FROM cs_Urls
WHERE UrlID NOT IN 
  (SELECT UrlID FROM cs_Referrals)

I chased the above with a COMMIT TRANSACTION once I was sure I had typed in the right table names.

At this point, the database was still as big as it ever was. I’ll admit I’m a bit hazy as to how and when SQL Server gives back reserved free space. I can only tell you what worked for me:

DBCC DBREINDEX (cs_Urls)
DBCC DBREINDEX (cs_Referrals)
DBCC SHRINKDATABASE(<database_name>)

DBCC DBREINDEX rebuilds all indexes. Since both cs_Urls and cs_Referrals have a clustered index, DBREINDEX effectively rebuilt the tables themselves, and the amount of reserved space for both dropped. DBCC SHRINKDATABASE then shrank the data file to under 55MB, and peace returned to the site. The irony is, I needed free space for these operations to free up space. As dbo I could bend the rules temporarily to make this happen.

All My Pluralsight Courses

OdeToCode by K. Scott Allen