[sims] SIMS Apply Timetable failed

[RogerCorrigan]

Hi All,

 

We'd just finished the timetable in NT6, sent it to the SQL Database successfully and went to apply the timetable to the new academic year when it failed with several errors.

 

An easy fix, I assume, would be to just delete the academic year and start again, HOWEVER, since the operation failed, we are now unable to login to SIMS.

 

Unfortunately, a backup was not made before the timetable was updated (as it wasn't a DB patch or Upgrade etc), we do have backups to restore from though.

 

How can I fix this?

[minimoo]

what was the error(s) it gave?

[RogerCorrigan]

The errors were from the apply timetable wizard

 

Off the top of my head, A few "dates" were unable to have the timetabled applied to them, some "DELETE" operations failed (due to a referential integrity error).

[vikpaw]

What is the login error, or do you not even get that far?

Stop and start SQL...

Reboot?

[RogerCorrigan]

Restarted SQL and the database is now "In Recovery"

 

Our last backup was only a month ago, so it's not too big an issue

[minimoo]

The database being in recovery after a restart isn't necessary a problem (as it goes through that phase as part of it's startup) - https://blog.sqlauthority.com/2015/03/04/sql-server-database-stuck-in-in-recovery-mode-after-restart/

[RogerCorrigan]

@minimoo even if it's been In Recovery all day?

[minimoo]

@minimoo even if it's been In Recovery all day?

 

That's sounding rather excessive - what's the SQL server error.log files showing in terms of the status (of the 'recovery')?

[RogerCorrigan]

Continuously "0%" recovery

[minimoo]

well, i'd expect it to show an estimated time to start up - e.g. from the logs on https://www.sqlshack.com/sql-server-database-recovery-process-internals-database-startup-command/

 

At the moment, you've got a SQL server that apparently failed to do it's last operation and now won't start up - which on one hand might just be a timeout on the apply academic year and now it struggling/taking a long time to roll up a transaction log before starting up - or a SQL server problem that is causing both operations to fail. In the first case, i'd expect it to have completed by now, in the 2nd case, i'd expect something in a log file.

 

I'd probably get the most recent database backup file saved somewhere and let Capita/Sims/LA support have a look. When you said the last backup is 'only a month ago' - do you mean '30 days old' or more, it was from the 31st July? i.e, are there statutory registration marks for instance that would be permanently lost if you were to do a restore.

 

In terms of the applying academic year failing there are a couple of KB articles relating to bugs that can cause the apply to fail - for instance, if you run capita's lesson observation module and someone has incorrectly used a future date in the next academic year. However in that scenario, i'd expect the apply academic year to have failed but SIMS to still have been accessible; and even Capita's workaround/solution I believe tells you to do some things in sims.net.

 

Can you post some snippets from the logs from yesterday/today?

[Banjo]

That sounds quite serious to me. I've never heard of a SIMS issue around timetables that kills the DB so that you can't login. What version of SQL are you using? I would ensure you have the latest SP installed on the server as this might be a bug that has a fix.

 

How long did you wait before the reboot/stop start - if SQL was still working on its transaction this might cause a wobble.

Is you ldf file massive?

Are you sure the backup is from a month ago - SIMS is usually backed up every night, but if the last upgrade was a month ago then you will have .bak from then.

Sorry lots of questions.