Koldov Posted July 16, 2019 Posted July 16, 2019 After a report from the admin staff that our SIMS is running a little slow on one or two reports, I thought I would look into the size of our sims.ldf. We are a very small Junior School and the sims.ldf is about 40GB. After reading a few posts on here I decided that over the summer holidays I would run the shrink database patches, admittedly I haven't done any of this for a while (when you used to get a zip file from Capita and use DBattach)... So I found a little info and the latest ones I could see are Patch 22573 and 20647.: It is advisable to run Patch 22573 on a monthly basis to help keep performance optimal and also to schedule Patch 20647 to run over night on a monthly basis. These are both already available to all schools via SOLUS3. Also a bonus that they could just be deployed from SOLUS3, at least they have moved on a little! So I contact Capita support and receive an email with a zip file attached..... Bearing in mind we are running SQL2014, the instructions (created 18 March 2014) state that: On Demand Datafix Tool is available to resolve this issue. - This is tool is compatible with SQL 2008 and 2012. Instructions on running ShrinkDBLog.bat ShrinkDBLog.zip will need to be unzipped onto a local drive on the machine SQL is installed. The batch file will need to be edited - right hand click select Edit Edit the following line:- osql -S %1 -d %2 -Usa -P -n -i'%temp%\shrink.sql' The SA password needs to be added into the osql command line which comes immediately after the -P for example, if the SA password is PASS the OSQL line will need to read as follows:- osql -S %1 -d %2 -Usa -PPASS -n -i'%temp%\shrink.sql' Once this line has been edited, the batch file needs to be run from a Command Prompt. Type 'ShrinkDatabase {Server}\{Instance} {DatabaseName}' into Command Prompt. A proper example is: ShrinkDblog AdminServer\SIMS2008 SIMS This will truncate the .LDF file. I mean I'm no coder for sure... but what?! What happened to Patch 22573 and 20647 that could be deployed from SOLUS3...?
Greg Posted July 16, 2019 Posted July 16, 2019 You can use the bat file and then schedule this, however, that will just shrink the log file - it won't optimise the database as the patches would Both of these patches are still available in SOLUS, if you don't see them then ask whoever you get your SIMS support from to release them 1
Koldov Posted July 16, 2019 Author Posted July 16, 2019 (edited) Both of these patches are still available in SOLUS, if you don't see them then ask whoever you get your SIMS support from to release them Yes, believe it or not that was what I actually asked them to do... then I got that email.... Well, I have been reading further threads on here about it and it seems that there are a few things 'wrong' with our SIMS setup. As it was installed by Capita themselves, I have foolishly left it alone believing it would have been done properly... Firstly there are quite a lot of threads that mention the 'Recovery Model' should be set to simple... Ours = Full: Also that the Log File is sorted out after the backup (or an upgrade/patch application)... Ours = Never: Then there is the method of backup... Ours appears to run a Scheduled Task which runs 'backupsims.bat' which calls another file called 'backupsims.sql' and I end up with a file called 'Monday_sims_Daily_Backup.bak.zip' then this is backed up with the rest of the server. I'm guessing this is not the best way to do it...? Edited July 16, 2019 by Koldov
Esteban_Child_of_the_Sun Posted July 16, 2019 Posted July 16, 2019 With an LDF of 40 GB I assume you are using InTouch and have been for a while (as InTocuh stores all messages in the SIMS DB). Capita have a separate set of resources for removing old InTouch communications form a database which I would recommend you request a copy of. Given your database is over 10GB I'm assuming you are running full SQL so you would be better off following Capita's guide on setting up SQL maintenance plans rather than using the patches/bat file. For shrinking the database Capita say "It should be fine to run a shrink on the Database. For ease we would suggest using the shrink process in SSMS as highlighted in the below guide. https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-database?view=sql-server-2017 We would advise running the reindex and update statistics patches after shrinking the database, and of course, taking a backup prior to shrinking the database. Regards 2nd Line Technical Analyst Capita SIMS" Capita may have finally written the KB for this so again I'd recommend requesting this from Capita or attempting to Search MyAccount yourself if you are feeling brave. 1
Koldov Posted July 17, 2019 Author Posted July 17, 2019 It looks like we may have cross posted and you may not have read my post on the other issues I've found... With an LDF of 40 GB I assume you are using InTouch and have been for a while No, we don't use InTouch. Given your database is over 10GB I'm assuming you are running full SQL Yes, we run a full SQL instance (and Full Recovery Model). you would be better off following Capita's guide on setting up SQL maintenance plans rather than using the patches/bat file. Unfortunately, I don't have much to do with the SIMS/SQL side of things. We paid Capita for an engineer to come and set this up, so I was hoping this would have been done for us (especially SQL best practise, maintenance plans and backup routines which are supposed to help with the LDF size)... Capita say "It should be fine to run a shrink on the Database. For ease we would suggest using the shrink process in SSMS... Do Capita actually advise using Management Studio now? We would advise running the reindex and update statistics patches after shrinking the database, and of course, taking a backup prior to shrinking the database. Yes I would like to run the patches, but it seems I am having a bit of trouble getting Capita support to understand I need them releasing to our SOLUS3...
Esteban_Child_of_the_Sun Posted July 18, 2019 Posted July 18, 2019 If you don't use InTouch, then I'd log a call with Capita to investigate why it's so big. as a "very small Junior School" I'd expect it to be no more than 8-10GB unless you have a lot of add-ons When I say Full SQL I mean not the express version but a fully licenses version, you are correct that the database should be in simple recovery mode. Which bit of Capita set up your SIMS server, was it Capita Children's services or another bit like Capita IT Services (they are not the same). They advised me to use management studio, but I think it depends on which analyst you get on your call at Capita as to the exact response. 1
Koldov Posted July 18, 2019 Author Posted July 18, 2019 (edited) If you don't use InTouch, then I'd log a call with Capita to investigate why it's so big. as a "very small Junior School" I'd expect it to be no more than 8-10GB unless you have a lot of add-ons. No other add-ons. Yes you're right, I think I am going to log a call in the holidays as it appears there are a few things that need to be looked at in a bit more depth. When I say Full SQL I mean not the express version but a fully licenses version, you are correct that the database should be in simple recovery mode. Fully licensed/Full version of SQL. I have seen that certain patches/upgrades appear to have switched 'Recovery Modes' for other people, but it's something I have never checked on, so can't be sure if that's how it was set-up when first installed. Which bit of Capita set up your SIMS server, was it Capita Children's services or another bit like Capita IT Services (they are not the same). I'm not certain, it was done quite a while ago now. I told the Business Manager that Capita needed to come in to swap SIMS to the new server and he made the calls. They advised me to use management studio, but I think it depends on which analyst you get on your call at Capita as to the exact response. Yes, that seems to be the case - very little continuity... The first person I spoke to wanted to know why I needed the patches released, went off to speak to a colleague and then sent me the 'ShrinkDBlog.bat' file... The next time I spoke to someone, they took the patch numbers I requested and put them on SOLUS3 no other questions asked... Edited July 18, 2019 by Koldov
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now