Question Re: Question about shrink the log file in the SQL Server database in Technology Q&A

Question about shrink the log file in the SQL Server database

Former Member — Mon, 19 Jan 2015 13:32:45 GMT

Hi Guys,

The log file in my development server is going to be full soon as presented below:

I did already a backup of the log file using the command:

BACKUP LOG DEV TO DISK='D:\BACKUP\DEVLOG1.BAK'

My question is, In the screen below which option is the better one to select?

I already shrinked the file to 10MB but is not releasing much space.

Could I select the first option named "Release unused space" to release more space in this log disk or what I could do?

Thanks for the help!

Re: Question about shrink the log file in the SQL Server database

Former Member — Mon, 19 Jan 2015 13:46:20 GMT

Yes, you can select the option as mentioned to release the space.

Re: Question about shrink the log file in the SQL Server database

giri_ayyagari — Mon, 19 Jan 2015 15:32:04 GMT

Hi Carlos

I suggest read below blog before shrinking logs..

Frequently Asked Questions we heard on the SAP on SQL 2008 Training course this year - Running SAP Applications on Micr…

-giri

Re: Question about shrink the log file in the SQL Server database

S_Sriram — Tue, 20 Jan 2015 05:06:46 GMT

Hi Carlos

1. You can run the log truncate backup, Every day once the backup is competed you can run the Truncate log backup it will free the space.

2. You can check the transaction code ST04 to see the over all db & log free space.

3. On the snapshot you can select the "Release unused space"

Re: Question about shrink the log file in the SQL Server database

Matt_Fraser — Tue, 20 Jan 2015 17:01:07 GMT

Hi Carlos,

First, you need to analyze why your transaction log is filling up so much. Presumably it has been auto-growing to reach 100 GB in size. Are you regularly running transaction log backups? If not, you should schedule them, at least once a day (for a development server; much more frequently for a production server), using either the SQL Server Management Studio or the DBACOCKPIT tools in your SAP system. The transaction log backup will automatically truncate the log of committed transactions after successful completion, thus preventing it from continuously filling up.

It will not, however, shrink the file after truncation. You should determine the appropriate size for your transaction log file so that it is large enough to manage typical transaction load on the system between backups, but not so large that it locks up an unreasonable amount of disk space. I think 10 MB is going to be far too small. You might start with about 10% of the size of your database and then see how it goes from there, fine-tuning it.

Shrinking the log to the appropriate size may take a few iterations. The shrink file operation releases the space in the log at the tail end, after the most recent transaction. However, there may be 'empty' log segments in the file ahead of that transaction. Thus, it may take a couple of iterations of truncating the log (via backup), then shrinking it, then truncating it again, then shrinking it again. Sometimes you can do these operations one right after the other, in a row, to get the result you want. Sometimes you may need to let some activity occur in the log, then truncate and shrink it again.

You can read up on more technical detail about why shrinking the log works this way here: Shrinking the Transaction Log.

Regards,

Matt

Re: Question about shrink the log file in the SQL Server database

Former Member — Fri, 23 Jan 2015 10:25:14 GMT

Change the backup mode to Simple and do shrink and revert to full mode

Regards,

Sudarshan