We have implemented customized Navision application at one of our client’s place. The par day transaction is very high. The approximate estimation is as follows:
Customer Card creation: 150-200 ,Sales Order Booking: 400 .
Initially the performance was good but now the retrieval of date and inserting the date through application is getting very slow. Client is using SQL Server 2005. Total disk space allotted for Navision Database is 97 GB, now the free disk space is only 6 GB. One log file Database_log.ldf is taking maximum disk space of 66 GB (approx.). Please help me out if anybody has any idea how to handle this situation to increase the performance issue. Can the log file be compressed / delete to increase the database space? Is there any option in SQL Server 2005 to handle this situation?
Please, search the forums for “SQL Performance” and you’ll see, that these issues are actually permanetly discussed. Performance Optimization of NAV on SQL Server is a complex task, and you will get “gazillions” of valuable advice!
As stated above you will find lots of information on SQL performance. If the post don’t answer all your questions, or raise new ones, then post additonal questions.
I would not consider 400 Sales Orders a day to be a very high transaction level. Unless you are talking about orders with several hundred lines.
Slowing down databases is a classic sign that your database is not maintained. For sure you need to set up proper maintenance on your SQL Server database. There’s tons of information about proper maintenance on this forum and on mibuso.com, and there are plenty of freelancers around here who will be happy to help you out for a fee.
As far as your database file… What can I say… if your database is 97 GB and growing, and your disk space is running out, you need to expand your disc space. We could talk about possibly reducing the size of your data files by doing index tweaking and running routines to remove empty SIFT records, but that won’t solve the issue of you not having enonugh disk space. Especially in the short run, you will need to increase your disc space, or have your database come to a halt when it reaches the maximum size.
As far as your log file… Transaction logs should not grow to that size (of course depending on transaction volume and frequency of backups), and it is easy to control the transaction log size by implementing proper backup plans. In simple recovery model, a full SQL Server backup truncates the transaction log, and in the other recovery models you need to run a transaction log backup to accomplish this. Also check out Books Online (Start, All Programs, SQL Server, Books Online) and search for ‘truncate transaction log’, and it will give you an idea of how to empty out the transaction log. Be careful though, you can really screw up your database is you don’t know what you’re doing.
It sounds to me though that you would be better served with a person who can help you with all of this. Get an expert into your office and have them walk you through these things, and help you understand what to do and how to do it.