Unable to shrink MS SQL log file

Tan_Eng_Siong

Hi

I have a customer who is on Navision 3.7 SQL database. They are working fine except that their server is often running out of space. I tried to create more space for them by shrinking the log file by using dbcc shrinkfile. It worked well on one of the log file but there was a second log file that could not be shrank by using dbcc shrink file. Both the log files ar for the same database. All the ldf files and mdf files are on the same harddisk.

Question:
1) Do I have to shirnk the first log file to nothing before I can shrink the second log file?
2) Is there a reason why there is a second log file since I only created one log file in the database. Is it automatically created by the Navision?

Thanks

DenSter

You should consider advising your customer to purchase more hard disk space. I don't know what you bill your customer of course, but it seems that it is more expensive for them to pay you for the time that you spend trying to shrink the files than it would be to just buy a new pair of disks.

Fab

Have you tr log fiy to made a backup for the Log File?
Normaly the SQL server backup all the file and truncate it after?

bbrown

The standard Navision SQL database considt of 3 files:

DBName.ldf (Transaction Log File)
DBName.mdf (Primary Data File - Contains DB Catalog Info)
DBName.ndf (Secondary Data File - This is the big one)

It is possible for a SQL database to have multiple physical transaction log files. But only if someone created them. The system will not create them on its own. Look at the database in Enterprise Manager to confirm that it really has two log files. Just right click on the database and select 'Properties". The look at the transaction log tab.

The transaction log file should be on its own DEDICATED physical drive. Best choice is RAID 1. Placing the transaction log file on the same drive as the data file creates two issues.

1. The transaction log is written to whenever data is changed in the database (insert,modify,delete). The log file is always written sequentially. Meaning that the next write always occurs at where the last on ended. By having the log file on its own drive, this allows the drive heads to be in the correct position for the next log file write.

If the drive is also hosting other files, then the system must take the time to relocate the drive heads before it can begin writing the file.

2. One of the purposes of the transaction log file is to allow for recovery if the data files are lost/damaged due to a disk failure. If the transaction log is on the same disk, it would also be lost.

----
A few questions/coments:

1. How often is the log file backed up and truncated. Truncating the log file more often may resolve the space issue.

2. How large is the log file? database? Do not shrink the file to a size where SQL will just turn around and regrow it. The file should be large enough to hold the tranaction between each truncate without the system having to autogrow the file.

Hpoe this is helpful

Tan_Eng_Siong

Hi Folks

No, a full backup is not possible due to the lack of hard disk space. Nor is it possible to talk about hardware upgrades as the users are not having the budget for it.(They bought a simple package and a basic server. Then they decided to use more functionalities and have more users. )

The customer does not have any IT knowledge so their backup was strictly from Navision. They consider touching a server a dangerous task so they never touched the server until things cannot be ignored. There was no SQL task ever performed, not even to create new users. The database file is 16GB. We are trying to keep the log file at 0GB.

Thanks

bbrown

A few question come to mind:

If the client's budget is so tight and they have no IT knowledge or resources, why is this system is SQL? SQL is not a "Install and Forget" database.

Why do you feel responsible for your client's budget issue? If they bought a basic system and then decided to expand, were they not aware of the cost implications? When was the database started, how may months/years of transactions?

Consider presenting two solutions to your client. Solution one would be to add and configure appropriate hardware and reconfigure the database. Solution two would be to best utilize the existing hardware. This would allow you to let the client make their own budget decisions.

---

If solution two is truly the only option, there may be ways to reconfigure the existing system to address your issue. If the system is using mirrored drives, one option would be to break the mirrors and run on RAID 0. Set the database to "Truncate on CheckPoint" and do a nightly backup from Navision. Their recovery would be to restore the last backup.

Let me know what you have for hardware. I may be able to suggest configuration changes that could address your issue.

Tan_Eng_Siong

Hi

>If the client's budget is so tight and they have no IT knowledge or >resources, why is this system is SQL? SQL is not a "Install and Forget" >database.

They wanted a Point of Sales system and that required SQL. Of course the vendor who sold them the functionalities failed to point out to me(It was my first Navision Project) that the extra functionalities were resource intensive.

>Why do you feel responsible for your client's budget issue? If they >bought a basic system and then decided to expand, were they not >aware of the cost implications? When was the database started, how >may months/years of transactions?

They trusted all the vendors to guide them properly. The project has been running for less than a year so it is a bit difficult to go back and ask for more funds. It has less than a year's data.

As for the hardware, they have only a single harddisk with 35GB space. I am not sure if it is having mirrored drives. Will check and get back to you.

bbrown

I understand and can relate to your problem. I have been in this business since 1989, and have ssen many times where clients are promised features and functionallity, but not made aware of the budget and level of effort requirements. Then the task is left to the technical staff to deliver.

If their database is 16 GB with less than a years data, then the 35 GB drive is already to small. You may be able to buy some time, but you should start getting the client to prepare for the idea of a system upgrade.

You said in your original post that the client started with a basic system then added users and functions. This may be a chance to go to the client and say that the extra transaction load of these users was not anticipated when the server was sized. If the server has empty drive bays available, adding extra drives may not be that costly.

Remember, outgrowing your server is a good sign. It means your business is growing. I once had a client where we replaced the server four times in the first year. When the system was first put in there transaction volume was <100 orders a month. By the end of the first year, it as >3000 order a day. Each time we recommended a system with room for growth, but the client decided to "save money". Whcih in the long run they did not.

---
Back to your hardware

If there is only one drive, you could still set "Truncate on Checkpoint", but you will eventually run out of room. There are also some periodic maintenance task you could perform to prolong the life of the system.

kine

And it is highly dangerous to use such a big database without doing backups! Mainly if the HDD is not in RAID!