Database Log File becomes very big, What's the best practice to handle it?

[Former Member]

The log of my production Database is getting very big, and the harddisk is almost full, I am pretty new to SAP, but familiar with SQL Server, if anybody can give me advice on what's the best practice to handle this issue.

Should I Shrink the Database?

I know increase hard disk is need for long term .

Thanks in advance.

[Former Member]

We had the same issue.

You will need to backup the database first - probbaly to a network location. Use the MS SQL Server Management studio and run a backup routine. Once complete, you can then run the "shrink" function from there as well.

Once you have reduced it to manageable size, you need to create a backup routine and shrink routine. Ours is done hourly, but tahts uits us - you may need to work out what time frame is best for you.

[former_member524429]

Hi,

Please refer the following SAP Notes for your situation.

[SAP Note 421644 - SQL error 9002: The transaction log is full|https://service.sap.com/sap/support/notes/421644]

[SAP Note 625546 - Size of transaction log file is too big|https://service.sap.com/sap/support/notes/625546]

The possible solutions with detail are well mentioned in the above mentioned SAP Notes.

Regards,

Bhavik G. Shroff

[Former Member]

Hello Finkie!

I will recommend to make hourly trasnaction log backup and full backup twice a day or according your company backup policy.

SAP and MS also recommending to have trasnaction log backup hourly instead of Shrink trancation log backup.

Regards

Anwer Waseem

[Former Member]

Hi Finke,

Usually the log file fills up and grow huge, due to not having regular transaction log backups. If you database is in FULL recovery mode, every transaction is logged in Transaction file, and it gets cleared when you take a log backup. If it is a production system and if you don't have regular transaction log backups, the problem is just sitting there to explode, when you need a point in time restore. Please check you backup/restore strategy.

Follow these steps to get transactional file back in normal shape:

1.) Take a transactional backup.

2.) shrink log file. ( DBCC shrinkfile('logfilename',10240)

The above command will shrink the file to 10 GB.(recommended size for high transactional systems)

>

> Should I Shrink the Database? .

"NEVER SHRINK DATA FILES", shrink only log file

3.) Schedule log backups every 15 minutes.

Thanks

Mush