cancel
Showing results for 
Search instead for 
Did you mean: 

having a bit trouble in shrinking the transaction logs

former_member203029
Participant
0 Kudos

Hi all,


I am using Sql 2008 r2 database and I want to shrink the Transaction logs by giving the script.



DBCC SHRINKFILE(PEMDMPRD2_m000_Log, 1)






But we are unable to achieve the desired result, kindly guide me how I can achieve my desired result.




NOTE: I want to perform the shrink to at-least 50% but am able to achieve only 2% and I don’t want the database to be disturbed as it comprises of production data. Thanks and regards, Kaushik G

Accepted Solutions (1)

Accepted Solutions (1)

former_member203029
Participant
0 Kudos

Hi Matt/Sri Ram, Thnak you for you imminence support and guidance , the issue has been resolved as I simply changed    --ALTER DATABASE model SET RECOVERY SIMPLE--  from Full typed this query "DBCC SHRINKFILE (PEMDMPRD2_m000_Log, 1);" and changed it to Full Recovery. Now I have 95% free space available with me.  Thanks , Kaushik G

Answers (2)

Answers (2)

Sriram2009
Active Contributor
0 Kudos

Hi Kaushik,

Normally once the daily backup completed then you can start the transaction log truncate backup. and also you can enable the option at DB level "Auto Shrink - True " it will take care the  DB & Transaction log shrinking, Kindly refer the snapshot.

BR

SS

Matt_Fraser
Active Contributor
0 Kudos

This is not a recommended practice. Auto-shrinking the transaction log is likely to cause performance degradation on the server, as it will forever be growing and shrinking.

A better option would be to identify the correct size of the log, one that is large enough that it doesn't autogrow between log backups, but small enough that it doesn't unnecessarily consume a lot of disk space. Then leave it that way.

My guess is that Kaushik's scenario is that he has a transaction log that has gotten away from him and autogrown to a large size, but now he would like to get it back to a reasonable and stable size. In this case, he probably wants to shrink it once (off-hours, hopefully), and then let normal backup processes take over from there. In this circumstance, Eduardo's suggestion is the right one.

Cheers,

Matt

former_member184473
Active Contributor
0 Kudos

Hi SS,

Please check Paul's post about autoshrink:

Auto-shrink – turn it OFF! - Paul S. Randal

Regards,
Eduardo

Sriram2009
Active Contributor
0 Kudos

Hi Eduardo / Matt

Thanks for your information.

We have enable the Auto Shrink option in our environment more than 10 SAP DB instances( MS Sql 2008 R2 servers in Standalone & Windows Failover cluster environments ), We don't have any performance issue in DB & SAP CI level. Every day we are starting the MS Sql DB transaction log truncate backup and it will performing the autoshrink log DB.

Regards

SS

Matt_Fraser
Active Contributor
0 Kudos

Why? What benefit does autoshrinking the log provide?

Sriram2009
Active Contributor
0 Kudos

Hi Matt

1. Main benefit is Space management to release the unused space in DB Log,

2. Performance improvement to read & write the transaction log

3. If the DB log space is full then SAP system may become inaccessible. because of this we have enable the " Auto Shrink" option in DB level.

This is how we have followed in our SAP landscape environment.

Regards

Sriram

Matt_Fraser
Active Contributor
0 Kudos

Hi Sriram,

  1. Transactions are just going to fill up that log space again (until the next backup), and if the log is too small it's just going to autogrow again, so that disk space is not really reclaimed in a useful way, i.e. you can't use it for anything else, so I don't see how you are effectively managing space here. Since the transaction log is supposed to be on a dedicated disk volume, it doesn't really matter if the log takes up most of the volume while empty -- that has no negative impact. I generally set the log to be less than the full volume, though, by a significant margin, so that if some transaction gets away from me and fills the log, there's headroom for it to grow (and when this happens, an alert is kicked off so I can get in there and find out why it's growing). A better way of managing space inside the log is with frequent backups, which will truncate the log (but not shrink it).
  2. Do you have any statistics on performance improvements on log access? On my production ERP system, I have a 10 GB log (on a dedicated 50 GB SAN volume, so lots of headroom), backed up hourly, and I get sub-millisecond write access times. I'd call that pretty good in my book. Before upgrading to SQL 2012 and enabling database compression, I used to keep the log at about 25 GB, but now I no longer seem to need that much space. This is for a database that was ~500 GB before compression, but is now about 130 GB after compression.
  3. I don't understand how autoshrinking can help with a full transaction log. If the log is full, you can't shrink it. I think you mean if the log has grown to fill the volume it's on, but in that case, as long as the log is not full (even if its allocated space fills the volume), it will not impact database operations. You just won't be able to grow it further. Log space should be managed with frequent backups, plus alerts. For instance, I like to set a 'performance condition' alert on my database for 'Percent Log Used,' such that if it rises above 80% it kicks off the backup job and sends an email to the administration team. So, with that in place, I may never even need autogrow, as long as I have enough space to contain the backups.

I'll conclude by pointing you to two of Microsoft's own documents on the subject:

In the Technet article Shrinking the Transaction Log, they have the note:


Shrinking database and log files can be set to occur automatically. However, we recommend against automatic shrinking, and the autoshrink database property is set to FALSE by default. If autoshrink is set to TRUE, automatic shrinking reduces the size of a file only when more than 25 percent of its space is unused. The file is shrunk either to the size at which only 25 percent of the file is unused space or to the original size of the file, whichever is larger.

There is some discussion about how this feature can cause shrink operations to not occur when DBAs expect them, and thus it is not a reliable way to "manage" log space.

In the Support article Considerations for the "autogrow" and "autoshrink" settings in SQL Server, the following points are made:

What are the performance implications?


  • If you run a transaction that requires more log space than is available, and you have turned on the autogrow option for the transaction log of that database, then the time it takes the transaction to complete will include the time it takes the transaction log to grow by the configured amount. If the growth increment is large or there is some other factor that causes it to take a long time, the query in which you open the transaction might fail because of a timeout error. The same sort of issue can result from an autogrow of the data portion of your database.
  • If you run a large transaction that requires the log to grow, other transactions that require a write to the transaction log will also have to wait until the grow operation completes.
  • If you combine the autogrow and autoshrink options, you might create unnecessary overhead. Make sure that the thresholds that trigger the grow and shrink operations will not cause frequent up and down size changes. For example, you may run a transaction that causes the transaction log to grow by 100 MB by the time it commits. Some time after that the autoshrink starts and shrinks the transaction log by 100 MB. Then, you run the same transaction and it causes the transaction log to grow by 100 MB again. In that example, you are creating unnecessary overhead and potentially creating fragmentation of the log file, either of which can negatively affect performance.
  • Physical fragmentation from changing the size of the data or log files can have a severe affect (sic) on your performance. This is true whether you use the automatic settings or whether you manually grow and shrink the files frequently.
  • If you grow your database by small increments, or if you grow it and then shrink it, you can end up with disk fragmentation. Disk fragmentation can cause performance issues in some circumstances. A scenario of small growth increments can also reduce the performance on your system.
  • In SQL Server 2005 or in later versions, you can enable instant file initialization. Instant file initialization speeds up file allocations only for data files. Instant file initialization does not apply to log files. (emphasis added)
  • If you have many file growths in your log files, you may have an excessively large number of virtual log files (VLF). This can lead to performance problems with database startup/online operations, replication, mirroring, and change data capture (CDC). Additionally, this can sometimes cause performance problems with data modifications.

The article goes on to describe Microsoft's own recommended best practices, which does not include using autoshrink.

Cheers,

Matt

Sriram2009
Active Contributor
0 Kudos

Hi Matt

Thanks for your valuable support & information about the DB Auto shrink. I will check and get back to you incase of any query's

Regards

Sriram

former_member184473
Active Contributor
0 Kudos

Hello Kaushik,

If your log is 98% used and there is any active transaction you will only be able to shrink 2%.

You can check how much of your log is used and the active transactions:

DBCC SQLPERF(LOGSPACE)

DBCC OPENTRAN

Ensure you don't have any active transaction and perform a log backup before before the log shrink.

One question from my side, why do you want to shrink your log file?

Regards,

Eduardo Rezende
SAP Support