SIMS DB increasing in size

[MrWu]

Hi all;

 

Looking at our SIMS SQL2012 DB, its gettting rather large (29Gb) - 1300 student school, 150 staff

 

Its in simple Mode and backed up by Capita script daily.

 

What could cause the increase in size (I reckon it goes up about 1GB per month) would reindex patch help (As far a sI know, never run)

 

Thanks as always

 

Wil

[round2it]

yes it would

[3s-gtech]

Yeah that has gone pretty mad. Our SIMS MDF is 7.5GB and we're a roughly equivalent sized school which uses SIMS heavily.

[Esteban_Child_of_the_Sun]

I'd log that with your SIMS support as it shouldn't be that big. We had a school with a similar issue and it turns out there is a bug in the Discover calculations/transfer routines that causes some tables in the SIMS databases to grow very quickly. It required a site-specific patch to shrink the database and stop it growing like that again.

[jutty1]

Do you use in touch?

[MrWu]

Do you use in touch?

 

hi Yes we do indeed

[jthompson]

Do you use in touch?

 

Was just going to suggest this. Our SIMS db was soaring up towards 30GB, and the majority of that was the InTouch messages table. We send out a lot of messages with attachments (e.g. reports) and it all gets stored in a table in the SIMS db. Purging the messages seemed like an all or nothing affair when we enquired, so we've stuck with our massive db for the time being.

[jutty1]

ok cool, you will find that in touch is dumping all of the messages into the Db now, it used to store them on the Capita servers but no longer because of data protection, there is a simple size on disk script you can run to determine how much it is taking up, and there is also a patch which will delete all messages older than a year old, (patch 22749) from capita, but after running the patch you need to shrink the database manually to claw back the size on disk

probably best to speak to capita also

 

Size on disk SQL script, run via SQL management studio and execute against the sims DB

 

SELECT

t.NAME AS TableName,

i.name as indexName,

sum(p.rows) as RowCounts,

sum(a.total_pages) as TotalPages,

sum(a.used_pages) as UsedPages,

sum(a.data_pages) as DataPages,

(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,

(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,

(sum(a.data_pages) * 8) / 1024 as DataSpaceMB

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

WHERE

t.NAME NOT LIKE 'dt%' AND

i.OBJECT_ID > 255 AND

i.index_id <= 1

 

GROUP BY

t.NAME, i.object_id, i.index_id, i.name

ORDER BY

TotalspaceMB desc

[MrWu]

Thanks guys

 

So after running intouch patch I run the following index patches:

 

22573 , 20647 then a shrinkDBlog routine?

 

They have never been run as far as I know so I might do this half term, from experience do they take a long time to patch through?

[jutty1]

Yeah those 2 are essential anyway, i run ours at least once a month half term is good, although if you use Solus 3 you can increase the timeout on the deployment and let it run over night, we set some of our schools to 120 min timeout so the patches run ok