sims.net SQL Server CPU Spike

[itgeek]

Hi I have had a problem this morning with CPU hitting 100% for about 5 mins. This was the first lesson and having trouble logging into sims.net. After the 5 mins people where into Sims and working fine. How can we stop the spike, can we configure sql or change setting?

Thanks

[BatchFile]

I'm a little rusty having left the SIMS helpdesk a couple of years ago, but...
The first thing to try is to do a backup from system manager - this seems like an odd thing to say but it trims the SIMS.LDF file down to size and increses perfomance.
Failing that, there's a reindex patch available - request it from your local support team and apply it - then do another backup as that inflates the LDF too (allow up to an hour for DbUpgrade to run it).

What version of SQL Server are you running and how many users have you got?

[jamesb]

Was there a scheduled task or anything similar running at that time which might've caused the load?

[siuko]

The 2 biggest times when we get CPU spikes is...

1) When I am running Database Diagnotics (so this will only happen if you are running it).

2) When the admin staff delete a student from Sims - This stops anyone logging onto Sims and lasts about 20-30 mins on our system.

[BatchFile]

Another thought drifted into my head...

when you say users can't log in, do you mean they hang for ages but get in eventually, or do they actually fail with a timeout fault code?

if it's the latter, adding
commandtimeout=99999
to the connect.ini file, just below the "databasename=" line, will stop it from timing out

[itgeek]

Thanks Tony for the info, it sound very interesting. i will have a play and let you know how I get on. The user waits about 90 seconds then gets in. We are running SQL 2005 Full verison.

[BatchFile]

no probs

We are running SQL 2005 Full verison.

Just checking as the lesser versions don't use all available server resources