Sorry to hijack the thread, but I'm seeing our Kentico db grow rapidly, and I thought our details would be helpful to others.
Our Situation:In a week, our db went from 30Mb to >2GB! This had me worried, since at this rate we would soon run out of space on the partition, perhaps have windows 'large file' problems and the performance was degrading by the hour.
After reading Jurajo's suggestions:- Keeping files in the Db will grow the db
- Content staging will grow the db too - depending on how much activity you have, and how big the sources that are being tracked are.
- don't know about web farming
To help diagnose exactly
what was taking up all the space, I used my 'Space diagnosis script' (see below). Here's what by DB looked like:
table_name rows reserved (KB) data (KB) index size (KB) unused (KB)
---------------------- -------- ------------- --------- --------------- -----------
Staging_Task 188575 1621752 1575288 44464 2000
CMS_AttachmentHistory 2593 121320 119984 760 576
CMS_User 56051 110016 47664 61744 608
CMS_VersionHistory 6438 65520 62960 1456 1104
Staging_Synchronization 488792 26712 14920 11496 296
CMS_UserSettings 56051 20128 5728 13480 920
CONTENT_article 2509 16528 16320 56 152
CMS_DocumentAlias 8357 14256 2696 10080 1480
Newsletter_Subscriber 35375 13720 9952 3656 112
CMS_Document 6406 12608 6848 4184 1576
CMS_Tree 6990 9136 2496 4864 1776
CMS_PageTemplate 265 6920 6472 200 248
CMS_WebPart 281 4144 3864 112 168
CMS_Class 235 3720 3088 424 208
CMS_MetaFile 600 3104 2536 208 360
CMS_EventLog 2252 2888 1912 560 416
CMS_SearchTask 13426 2632 1616 664 352
Newsletter_SubscriberNewsletter 68206 2312 2280 16 16
CMS_Attachment 2851 2080 632 928 520
Export_History 21 2056 1896 56 104
I didn't even bother listing the next hundred tables...
This makes it quite clear what is taking up the space. Here's how we got here:
- We are near the end of developing a moderately-sized website.
- In the last week we've imported 6000+ Articles and 60,0000 users.
-
And we are using Content Staging.
-
And we are using workflows with Versioning.
-
And we have lots of the 60,000 users assigned to one or more Newsletters.
-
And we have ~50 page templates.
Cause
and effect.
Staging is obviously the real pig here. 1.6GB of piggishness. This morning I had over 180,000 staging tasks listed in the cmsdesk! That was easy enough to deal with, I deleted them using the cms desk. I'll pick and choose objects to move manually later; 1.6GB is just too crazy a size to keep.
AttachmentHistory is the next culprit. I'm not sure how to deal with this, I'd like some pointers from someone on what to do to minimize this - we don't really need much of a history of attachments. I'm guessing this is related to versioning somehow.
Users is big too, but we have lots of Users, so I doubt we can reduce the size.
VersionHistory is big... we are keeping the last 10 versions by default. I suppose we could reduce that to the last 5 or something to reduce that? If I change the setting to a lower number, will the older versions get deleted automatically? Or do we need to 'touch' a document to trigger the setting?
The rest of the table sizes look reasonable for what we are doing with Kentico.
Space diagnosis script for SQL Server
--NOTE: I can't remember where I got this from... it works really well and the author should get some credit.
CREATE TABLE #temp (Table_Name nvarchar (128), Rows char(11), reserved varchar(18), data varchar(18), index_size varchar(18), unused varchar(18))
declare @table_name sysname,
@sql_string nvarchar(1000),
@count nvarchar(1000);
set nocount on
declare all_tables cursor for
select TABLE_NAME from information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE'
open all_tables
fetch next from all_tables into @table_name
while @@fetch_status=0
begin
CREATE TABLE #tempTable (Table_Name nvarchar (50), Count int)
--set @sql_string = N'select count(*) as ['+@table_name+'] from ['+@table_name+']'
--set @count = 'select count(*) from ['+@table_name+']'
--set @sql_string = N'select count(*) as ['+@table_name+'] into #tempTable from ['+@table_name+']'
--set @sql_string = 'insert into #temp (Count)['+@table_name+'], select count(*) from ['+@table_name+'])'
--PRINT @sql_string
--exec (@sql_string)
set @sql_string = N'INSERT into #temp exec sp_spaceused ['+@table_name+']'
PRINT @sql_string
exec (@sql_string)
drop table #temptable
fetch next from all_tables into @table_name
end
close all_tables
deallocate all_tables
set nocount off
select table_name
, [rows]
, convert(int, substring(reserved, 1, len(reserved) -3)) as [reserved (KB)]
, convert(int, substring(data, 1, len(data) -3)) as [data (KB)]
, convert(int, substring(index_size, 1, len(index_size) -3)) as [index size (KB)]
, convert(int, substring(unused, 1, len(unused) -3)) as [unused (KB)]
from #temp
order by [reserved (KB)] desc
--drop table #temp