Home Forums Discussions General Discussion SQL Express Database too large

While trying to upgrade to version 6 I experienced the problem of the database tuner timing out. I contacted support for assistance. It was discovered that my SQL Express Database is greater than 4 GB. Apparently this is causing a problem. I have only been using AC about 19 months. Has anyone else encountered this problem of a too large sql express database? I am puzzled why the the database would increase in size so quickly. I have Sage Medware as my practice management system on the same Windows SBS 2003 server. I was wondering if this might be adding to the size of the sql database.

Sage Medware, uses .tps database files self-contained in its own folder. Medware is unique in that you can just copy the folder and move it anywhere and there are no installs on the clients (or shouldn't be).

So, anyway, the answer there is it does not. In fact, nothing affects the size of your database except AC. But, see below for your solution hopefully.

I am actually going to start a new thread for a solution to this.

One possible cause is your SQL Server database log file has not been truncated in some time. Run the SBS 2003 backup tool. That should truncate your log file.

JamesNT

It will, but SQL Server will only look at the .mdf file as far as the 4GB is concerned.

Bert,

Excellent point. So the obvious question is what is the current size of the mdb file. From there we can form a solution for our esteemed forum member.

JamesNT

My mdb file is only 0.5 GB and I have been using Amazing Charts for 10 years (solo practice). Question is to why file is getting so large so quickly? To the new users: the mdb file does not contain your imported items, only pointers.

It is 4.19GB. Wow, Ken that is a VERY small database for 10 years.

SQL stores its data in a variety of ways. I don't know why it would necessarily be different for Ted. But, there if Form Filling percentages, and page splitting, running on full rather than simple. But, these are all set by default when one installs.

Of course, it is the Form Filling that allows him to keep using the database.

Bert,
I've got some really sick patients. So not that many patients.
S:Very sick
O: All Abnormal
A: Train Wreck
P: Refer to Bert

How old is your server? What are the spec- RAM, processor(s), HD memory, etc?

@Ken

So that's why my database is so big.

In the event any other users have a large SQL database I advise you to contact technical support. They were very helpful to me. The tech was able to "prune" my audit log files to a fraction of its size and archive the old file. My .mdf file was reduced to 1/6 the size and version 6 works like a charm for me--except the Rx writer function is slower than 5.1.7. I followed the instructions to turn off hardware acceleration and that helped a lot. I was told that future versions of AC will allow the user to archive the audit log files.

But, the question still remains why you would have 3.3GB of audit files.

I think AC 6.10 beta solves that issue.

That's good. Still doesn't explain > 3GB of audit files. Sorry.

How does 6.0.10 solve this issue?

6.0.10 saves less to the audit log - will hopefully get the log from going so fast and they are working on a utility to trim the log. I think probably the records that your EMR keeps behind the scene contributes to this - any time a staff member looks at a pt. record, any time you print chart notes, e-mail things, send scrips by printer or ERX I think it all saves in the background.

How did Ted get > 3GBs of audit logs. I wish they would explain that. I have used AC longer than he has and my MAs and receptionists tend to every once in a while open a chart.

Sorry to beat a dead horse, but may I ask how the size of Ted's (or anyone's for that matter) audit log file was determined? I am really looking for the specific file name so I can try to hack into it using some other program since I'm waiting for the next non-beta release before I move from 5.0.29. I've timed out so many hundreds of times trying to access this data thru the Administrator tool kit. Anyone?

Look in the Amazing Charts folder for AmazingCharts.Idf file.

My practice has 3 doctors, two lab techs, 3 nurses and 7 office staff using Amazing Charts every day. A gazillion documents come in every day and are scanned into the system and forwarded to the doctor for sign off. Every request for medications, test results, call backs, etc are attached to a chart and forwarded to the physician. This may be the reason why our audit log files became so large so quickly.

Technical support was able to solve my problem and the upgrade to 6.0.9 went without a hitch. The system works great except for the slow down in Rx writing- especially when the interaction checker is running slow. GNicoll, perhaps you should let the technical support help you with your system.

My guess is you would go to:

SERVER_NAME\AMAZINGCHARTS| Databases | AmazingCharts | Tables and then look in the properties of the database object: Audit Log and see the file size. I'm not sure if that is the right way, but I think if I did do it that way, I would see that my Audit Log file was 296MBs. I have no idea how Ted's got that large.

I am not sure I would use the word "hack" on a user board, though.

Thanks kindly to both Ted and Bert for your counsel.

Without the repeated time outs I'd have little concern about the size of the database as I have gobs of storage in my AC dedicated Raid server, but each time-out requires a restart of the application. As far as tech support, the staff [are] really good but cannot do anything about issues that need to be fixed by programming. I've three distinct sessions with tech support on this time-out issue over the course of 5.0.29 and thus far have no resolution.
I'm happy to hear Ted has successfully upgraded even with the size of his database, I sort of feel we're all in this together; it's the slowness of the RX module he's experienced following the upgrade and which consistently appears in this user forum that keeps me from adopting the newest version, we generate several hundred if not a thousand prescriptions per week thru the AC Rx interface.

Oh, and BTW, re: the "H" word, what I meant was to copy (not cut) the audit log and import it into my laptop and then retrieve the raw data using some other database program, hopefully mitigating the time-out problem. I'll watch my language in the future.

GNicholl,

Why are you getting repeating timeouts? What is the size of your .mdf AND .ldf files?

Great question, maybe somebody will have an answer. If you're asking about amazingcharts.mdf and amazingcharts.ldf, they are 1,528 mb and 136 mb respectively. There are also some meds and codes .mdf files which are 161 mb and 13 mb respectively, and same .ldf files are negligible.

The last tech support encounter I was told that there was no solution but that the issue was known in certain configurations, the specifics of which I cannot recall.

I appreciate your interest in the issue, thanks.

Those sizes are fine. If you think your time-outs are due to SQL, then I can help you there.

Well I just timed out 10 consecutive times, sitting at the server. I would gladly accept any help with regards to SQL, thank you for your offer.