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.


Comments
Haacked Tuesday, February 7, 2006
I got the following error message when I tried to run that first script.

EXECUTE cannot be used as a source when inserting into a table variable.

I ended up just doing this:

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

DROP TABLE TableSizes
scott Tuesday, February 7, 2006
Good catch, Phil. The script I gave only works on SQL Server 2005. :/

Thanks for catching that.
Keyvan Nayyeri Tuesday, February 7, 2006
Thanks for the tips Scott ;)
I have same problem with my database. I don't have a large database like you because use CS for personal purposes but it's not good to have larg database when we don't want much of its data!
Having all referrals in database for long time is bad idea because I think that no one wants them after a months.
scott Wednesday, February 8, 2006
Yes, it would be nice if CS provided an option to purge referrals, or turn off storing them.
Comments are now closed.
by K. Scott Allen K.Scott Allen
My Pluralsight Courses
The Podcast!