[sims] SIMS Database suddenly increased in size by 50%

[BKGarry]

Hello All,

 

So today, when running the indexing patches for SIMS, I have noticed that the backup file size (and the SIMS MDF on closer inspection) has suddenly jumped up in size from 15gb to 22gb!

 

I narrowed this down to it happening during the day last Thursday, as the morning backup it was the old size and the evening 6.5-7gb greater in size.

 

Got a ticket logged with our local support unit, but can anyone thing of any reason for SQL to increase the DB size like that at all??????

[vikpaw]

Check your sql settings, it can be set to grow by x% so it might not be data, just a structural expansion and it may not grow again for a long time til all the space is used.

 

Backup size growing is interesting as usually a backup would compress the data and therefore you would not notice such a big change because the empty space will reduce to nothing.

 

A legitimate data increase could be due to a big intake of data, such as reports, assessments etc. Of the size you are talking about, I would check the photos. You are able to import way larger than the recommended size and 1000 students at 5MB is the right order of magnitude.

[BKGarry]

Check your sql settings, it can be set to grow by x% so it might not be data, just a structural expansion and it may not grow again for a long time til all the space is used.

 

Backup size growing is interesting as usually a backup would compress the data and therefore you would not notice such a big change because the empty space will reduce to nothing.

 

A legitimate data increase could be due to a big intake of data, such as reports, assessments etc. Of the size you are talking about, I would check the photos. You are able to import way larger than the recommended size and 1000 students at 5MB is the right order of magnitude.

I will check that tomorrow morning, but it deffo wasn't photos, as I did that weeks before (I checked)

[vikpaw]

Standard growth settings are x% which can escalate. I prefer a fixed growth in MB.

 

The concern is the backup size growth. Hopefully it’s not by as much.

[BKGarry]

The Sims backup was, that's what caused me to notice it in the first place.

I can't see any amount of physical data which should cause a jump that big. I also had an LDF of 72gb, but resolved that.

[BKGarry]

To add sim_DAT is set to grow by 1mb, sims_LOG is by 10%

 

If this is assessment data, it is a hell of a lot for one day is it not?

[vikpaw]

That's crazy. Keep that ticket open, just in case.

That can't just be assessment data. I'd say reports if you generated and uploaded, but they aren't stored in the DB.

The only thing I can think of is images.

[BKGarry]

Yeah, pity is the local support unit is super understaffed. I am going to assume this needs a copy of the DB sent to capita, but it took 4 days to get the indexing patches.

 

I have never seen a jump like this before, I am worried what it could be. Because I have been having performance issues with SQL for a week now.

[jthompson]

Do you have InTouch? If you have, and a mailshot with large attachments has gone out, that's all stored in the db.

[BKGarry]

Do you have InTouch? If you have, and a mailshot with large attachments has gone out, that's all stored in the db.

We do have InTouch. But only 20 emails that day. 102 SMS messages.

 

I thought that too.

[jthompson]

When we were trying to work out why our db was so large, we ran some SQL that listed the sizes of the largest 10 or so tables. The InTouch messages table was in a league of its own. I don't think I have that SQL query saved anywhere, but it may not be too difficult to put together something similar to see if there are any sore thumbs.

[BKGarry]

@jthompson that wasn't a bad shout.

 

I have just ran the following SQL Script/Query

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 
   SUM(a.total_pages) DESC

 

I have just found the following tables by row count:

 

No. 5 - ASM_Result - 3,162,840 - Rows

No. 4 - att_mark_hist_normalised - 3,325,224 rows

No. 3 - att_lesson_mark - 10,476,308 Rows

No. 2 - att_mark_normalised - 13,940,344 Rows

and the massive jump to number 1:

asm_result_tpa_delete: 90,630,006 rows

 

that is 90 million rows!!!!!!!!

 

Can't wait for a call back this morning

[jthompson]

Is that table also huge in terms of MB?

 

Is asm assessment manager?

[BKGarry]

nohuge huge, we are talking 3,766mb so it could be part of it, but not all of it

[BKGarry]

Update, Local Support Unit have said they are researching this and that they will call back on Monday.

 

They do not have a copy of my data, so I wonder how they're going to do that

[vikpaw]

Update, Local Support Unit have said they are researching this and that they will call back on Monday.

 

They do not have a copy of my data, so I wonder how they're going to do that

 

Sit around with their colleagues at lunchtime and see if anyone else has reported it.

[BKGarry]

I don't think that is the case, it is quite hard for them, they have had their team cut from 10/12 people to 3 in recent months. The "reshuffle" has made it hard from them.

 

With this I would really want them to go, we will take a copy of the DB and send it to capita.

 

I am thinking about loading the DB from backup onto a different SQL server and run the largest table report again and see if I can see the differences there but that is a big job and needs to be done carefully I feel

[jthompson]

Perhaps run your query on a regular (hourly?) basis and collate the results.

[vikpaw]

I was semi-serious. It’s one of the first diagnostic techniques to see if it’s just you or others as well.

 

If it’s not affecting your backups then at least there is no rush. Whatever the cause it should be resolvable or legitimate.

 

A large transaction log that won’t shrink is more of an issue.

[BKGarry]

That is a good idea, I am on 24gb today, log file isn't increasing as bad, that does make sense though.

 

Yay a fresh set of eyes.

[vikpaw]

What are your top tables by used space?

[BKGarry]

What are your top tables by used space?

 

In MB

 

1. asm_result_tpa_delete - 3766

2. itc_message_out - 1009

3. itc__message_out_attachment - 1006

4. db_audit_header - 988

5. att_lesson_mark - 849

[BKGarry]

Ticket is being raised with capita and copy of the database going that way,

[Esteban_Child_of_the_Sun]

Out of curiosity, I've just looked at 3 of our high school's SIMS databases and in all of them the asm_result_tpa_delete table is empty. Do you use any other Capita products other than the core SIMS (as ours don't?)

[BKGarry]

We have InTouch and Lesson Monitor.

 

I think these are a legacy thing from when they used to give every child a target grade for every subject and then deleted the ones for the subjects they are not in