Log file growing in amazing manner

I started my database in last week to user with transfer data from Sybase to sql 2000 server. Intitally log file size was few MB near to 20 MB for each cos, within 8 days it has reached upto 300 MB still datafile size in few MB , approximately 40MB for each cos, why log file growing in such manger, how I can manage it?

Thanking You

R.MallAre you performing any database maintenance?

How did you "transfer" the data from sybase?|||You need to control the transaction size in your load. Doesn't matter what tool you use (unless you have a linked server and doing INSERT...SELECT FROM) you can specify the number of rows to be viewed as 1 transaction. This option is available in DTS, BCP, and BULK INSERT utilities (DTS uses a combination of those)

log file filling up

Let me start by saying I haven't touched Sybase or SQL Server in a LONG time, but I've been doing Oracle off and on for a while.

Ok, so, here we go. I have an SQL database where the data is 2Gb and the log is 18Gb. The system keeps complaining about the log file being full even though I've set the mode to SIMPLE and trunc on chkpt. I'm out of disk space so I can't, and don't want to grow the log file any further. How can I tell exactly how full that log file is? We don't plan on recovering the database, so as far as I'm concerned, I can turn logging completely off.

Any ideas or suggestions?

You cant make Logging off completely . yes ofcourse you can make it minimally logged when u make it simple recovery model. Once you made it simple ,since your log is of 18 GB , now you have to truncate & shrink it to get the size reduced.

The growth of Transaction Log(TL) is depends on couple of things... (a) the Recovery Model (b) TL Backup freqency.

(a) first check whether you need Full recovery model , if not change it to Simple

(b) WHat is the Backup policy. if its in FUll recovery model and if you are taking the TL backup then increase the frequency of the TL backup

(c) If you want to shrink the file then you will have to truncate and shrink it. After truncating and shrinking the first step should be a full backup. otherwise the backup chain will break and u will not be able to restore from TL bakcup.

Refer :

http://support.microsoft.com/kb/873235.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1542414&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1630021&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1623857&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1447193&SiteID=1

madhu|||a) It's set to SIMPLE - now.
b) We don't backup. We really don't have a need to.
c) the truncating seems to work, BUT in the Console if I select Tasks, Shrink, Files I see no change in the available free space. Should I?

Keep in mind that I have truncate on checkpoint set to true, and if I do a checkpoint, I get an error back saying the transaction log is full - even AFTER I've run backup with no_log.
|||

(a) Truncate the log first

BACKUP LOG yourdatbasename WITH TRUNCATE_ONLY

(b) Shrink the file after truncation

DBCC SHRINKFILE(LogicalLogfilename, Size)

read about this in BOL

Madhu

|||Many thanks for the replies.

I've done the backup several times. I just tried the dbcc command and recieved the following:

Cannot shrink file '2' in database because of minimum log space required.

Current size 2277232
USed pages 2277232

I tried using a Mb size and it choked. I forget the relation between pages and Mb. It's been too long........
|||1page = 8 kb i believe..........just use as specified earlier .........

|||That did it! I was trying to give it a size and it didn't like it. I just dropped off the size parameter and she shrank. Thanks, guys!