Request For Suggestions On Shrink Log File Under Low Disk Space Situation

[Former Member]

The log file of my production database is growing too large in size (almost 10GB) with the total hard disk space of 410GB.

The existing db is shown as below

Data1 107GB

Data2 106GB

Data3 107GB

Log1 10GB

We found that the available free% of log file is 99%. The free disk space is 27.3GB (6% of total space). We are planning to shrink the log file under this situation. Is it enough disk space for the shrinkage without increase the hard disk?

Does anyone have good suggestions for the shrinkage?

Edited by: Andrew Shek on Sep 29, 2010 11:06 AM

[Former Member]

Andrew,

We had exactly the same problem. If you find that the shrink process doesn't work, you will need to run a backup process first, and you can do this by sending the backup file to an attached drive or a network drive where you do have adequate space. Once complete, the trans log should shrink. (Do this through the SQL Server Management Studio)

However, I would also say that you may well run into disk space issues really soon (depending on how much free space you have in the DB files). You should think about what to do next fairly soon - for reference, we attached a second controller card and a separate disk subsystem to the server to give us the extra disk space required.

[Former Member]

Andrew,

Odds are the log grew during a period of heavy changes in your system. As was stated above, you will want to evaluate your transaction log backup schedule. I have worked with some ECC systems that generated 10GB of transaction logs per hour, but since your total DB size is 400GB, I doubt that is the case with your system. I generally advise SQL transaction log backups to be scheduled to run every 15 minutes. In most systems your size that would likely result in .trn files of less than 100MB, with some bigger files during peak times. You should be able to realistically set your log size to 2GB.

Also, you mentioned that this DB is sitting on a total of 410GB of disk. I would recommend you upgrade that. For locally-attached disk, you should make sure that your data and transaction log reside on separate arrays, because you do not want database read/write to contend with log-writing. The drives should also not be more than 50% full. Performance becomes an issue as drives have to read/write constantly from the center of the spindle. Also, you do not want to risk downtime due to a disk filling up. Disk space is relatively cheap when you compare it to system unavailability.

Hope this helps.

[Former Member]

Hi,

The following SAP noteas may help you.

Note 625546 - Size of transaction log file is too big

Note 1166117 - Steps to Shrink a Log File for the Database

Regards,

Varadharajan M

[Former Member]

Hi,

You need to consider how often you are backing up your transaction log and to what size it grows in between these (trn log) backups. Does the transaction log tend to grow upto 10 GB in between two (trn log) backups? If yes then it doesn't make sense to shrink the transaction log.

Otherwise you can shrink the transaction log to whatever maximum size it grows to in between two (trn log) backups.

Regards,

Rohan.