Dear Guru's,

I am using Windows 2000, SQL SERVER 2000 combination. On my servers one of the partition log file is grown to 19 GB and there is no space left in that Drive(Drive E:). Hence Baan is not able to insert any more records. That is we are getting the error.

Now I have to either get the SQL Sever log shrunk or move this LOG FILE on other partition (Drive G),where I have 50 GB space free.

Can anybody tell me the procedure to do this? Let me update you, I am new on SQL server, so please send me detailed procedure.

Thanks in Advance.

Sachin Joshi

Hi Sachin,
First and foremost, you should have regular clean up of your transaction log and shouldnt let it grow to this much of a size. Therefore, the first step to do is to try and shrink the transaction log file. Carry out the following command..(supposing your baan database is called baandb)
1) Open Query Analyzer and type the following command.

Use baandb
dump transaction baandb with no_log
go

2) After this command is executed successfully, Open Enterprise Manager and open up the Database Group.
3) Right click on baandb database and select All Task=>Shrink Database. Click on the Ok button in the resulting window without changing any setting.
4) Once this is done go to the partition where the log file is on and check the size of the log file. Usually it would reduce to 1mb(1024kb).

Do let us know if this helps..

sk

Note: The shrinking might take time..so be prepared..Shouldnt affect the normal database activity however...

Thanx Sukesh,

As per your solution, i workout and now Baan is running fine.

As per your slution, my 18 GB space is free.

Thanx once again for prompt reply.

Regards,

Sachin

Glad to hear that. To avert such a situation in the future, open Enterprise Manager and create a SQL Agent Job which carries out the "dump transaction baandb with no_log" command on a periodic basis. The period would be upto you and can range from per minute to per hour to per day...You could also do this without a job that is when(if) you are taking a Db backup then you can clean the transaction log after the backup...

sk

hi,

what about for informix 7.3 DB... how can i check whether it retain the log..

Steven,
I suggest you check the Baan Installation documents thats related to the Informix DB just to be sure if there are any steps to clear up the transaction logs...

sk

I have to disagree with sukesh75. Transaction logs allow you to restore to a point in time, you do not want to dump them. Especially don't create a job to dump it daily. If you don't care about point in time restore, then make your database type 'simple'. It will not use transaction logs at all. If you need a point in time restore functionality then backup the transaction log. The backup will clear the entries in the log and start over. The size will not shrink, but it will not continuously grow either.

If you have smaller database with lower transaction levels, take a full backup and then backup the transaction log throughout the day. If you have larger transaction levels, take a full backup daily, differentials a different points in the day and backup transaction logs in between differentials.

To shrink a transaction log file - first back it up, then run this sql
use baandb
go
checkpoint
go
DBCC SHRINKFILE('baandb_log', 30)

30 = the size in mb that you wish to shrink the log to

Andy