Patch city and large mdf file

[GrumbleDook]

It was bound to happen eventually.

We are still chasing up issues with Learning Aims before the Census. 5 patches were released by .ICT late this afternoon (in the hope that 2 of them would fix our final issues!).

They were dutifully applied and the server was happy.

I have just gone to test the issue (duplicate entries of students as members of courses, and knackered end / planned end dates!) and it takes forever to try to save ... i am used to this ...

But then it times out and the connection to the DB goes ...

restart SQL ... no joy ... restart the box ...I go to log into SIMS and I get incorrect password error.

hmmm ... I try the backdoor

no joy.

I have a look in the error logs ... and spot

CREATE/ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 2048 MB per database.

Yes ... you've guessed it ... we are still running MSDE for it whilst we waited for a suitable time to upgrade to MSSQL 2005.

Not being a DB admin can anyone suggest quick methods to compact the DB (I don't have the sa account here) or any other utils.

Upgrading to 2005 is not an option today / tomorrow. I just need a quick fix to go in a purge some stuff.

[Geoff]

There are no quick fixes. It is not possible to run a compact job on an MSDE database instance. The only solution is to upgrade to SQL server.

[GrumbleDook]

I had a really horrible feeling that would be the answer.

Oh well ...

[kingswood]

Now if it was a JET 4.0 engine DB you could follow this:

http://support.microsoft.com/kb/303528

We are having the same issue with the errors Tony and we run SQL 2000 right now (waiting for the update to 2005- should be on the 19th of this month). I'm not sure that the most of the issues you saw were anything to do with MSDE, because when I left work yesterday Allan was still applying patches to fix whatever caused the 89 errors we got re-running the census check before today. On first run we got over 70 errors (same as yours); we fixed them. Re-ran and got no errors. Great (we thoughts) all ready!

We re-ran to make sure (validation process) and got the 89 errors (more than before). We have a print out of the first successful run to prove to ourselves that it actually did work...

We are down to 15 warnings which apparently is OK. But that's before doing the same thing today to get it sent off. And Allan isn't in- and I'm no DB admin either.

Hopefully the move to SQL 2005 will sort a lot of this out.

Good luck mate!!

Paul

[theriver]

Hi

OK, so it would seem the errors you are seeing are happening as a result of the database trying to increase in size, having filled up.

You might find that someone logging in causes a log record to be written that tips the DB over the edge, although as Geoff says it could be coincidental. Depends on how good their error handling is ;-)

Although you have MSDE there, you can manage it via the SQL Server 2005 Express Management Studio. This *should* (never tried it, take a backup first blah blah) be able to connect you to the DB such that you can change the growth property (properties > files > autogrowth > elipsis > blah blah > may need a restart, I don't know). By default this setting will be 10% of the current DB size, so you might be able to knock the number down or change to an MB increase, so that when someone logs in (if the above *is* the case) the critical size isn't hit.

[DMcCoy]

Even compacted my database is 2.1GB It went over the 2GB mark with the august update, I assume due to the preperation for the sql. I've since migrated anyway, but I was using the full version so it wasn't an issue.

[GrumbleDook]

The bullet was bitten and SQL 2000 was put on. It runs ... but the ldf file needs some serious truncating ... it grows at the rate of 1GB per hour in use.

I am looking at the 3rd party call on the database used by Sleuth (actually the groupcall service) to see if that is hitting it hard but I don't think it is ... it only runs the extract once a day at 7pm.

We are putting in notice to County that the return will be late and the reasons why ... and see what happens.

[webman]

Don't you just love microsoft and licensing

[bossman]

@grumbledook: yes sleuth hardly impacts but why only move to sql2000 and not 2005?

[GrumbleDook]

We just needed to get it up and running, without introducing any other problems

The main problem is that some tasks for sorting CENSUS data grew the DB and took ages .. that led to too large a DB but if we upgrade then other things may break.

[bossman]

what have your LEA said in response to your predicament?