on 07-10-2015 2:17 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
Hi SS,
Please check Paul's post about autoshrink:
Auto-shrink – turn it OFF! - Paul S. Randal
Regards,
Eduardo
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
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
Hi Sriram,
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:
- 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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.