SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Shrinking Transnational Log File


Shrinking Transnational Log File

Author
Message
Raghavender Chavva
Raghavender Chavva
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5475 Visits: 1391
Hi All,

I have posted similar question here 7 or 8 years ago. Now I am unable to find that post. So asking the same question again.

We have a database with 80GB T-Log file(This is due some unexpected transactions from application for which we have already raised a concern with them) Where as data file is only 100MB.

So I have took a log backup of that database, checked the free space of the log file and it is 98% free.
Then I tried to shrink the file but it did not shrinked.

So I took almost 7 to 8 Log backups. Now I am able to shrink the log backup.

Now My question is why I am not able to shrink the log file after 1st log backup even though 98% free space is there. Why I am able to shrink the log file after n number of log backups.

Thanks and Regards!!

Raghavender Chavva
Beatrix Kiddo
Beatrix Kiddo
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19789 Visits: 6037
There can be many reasons. Try checking the log_reuse_wait_desc next time. It will tell you if (e.g.) there's a long-running transaction in progress, or maybe the database is in an Availability Group and the secondary hasn't caught up yet. Or mirroring, or replication...

select name, log_reuse_wait_desc from sys.databases

Alternatively, it could just be that your database isn't very active, and all of your records are in the same VLF .
Raghavender Chavva
Raghavender Chavva
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5475 Visits: 1391
After 1 st log backup free space on the log file was 98%.
I checked for any open transactions are running on that database but nothing was found. nothing shown in select name, log_reuse_wait_desc from sys.databases.

2nd log backup onwards backup file size was around 100KB from


Thanks and Regards!!

Raghavender Chavva
Sue_H
Sue_H
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41580 Visits: 11523
Raghavender Chavva - Monday, April 16, 2018 9:17 AM
After 1 st log backup free space on the log file was 98%.
I checked for any open transactions are running on that database but nothing was found. nothing shown in select name, log_reuse_wait_desc from sys.databases.

2nd log backup onwards backup file size was around 100KB from


Did you check the vlfs as Beatrix suggested?
The issue can happen for different reasons. You can find some of those in the link Beatrix provided. On that same site, if you search on: DBCC LOGINFO you can find additional information about the vlfs. You would want to look where the active portion of the log is.

Sue



Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114767 Visits: 21426
A bit of a pun, but I have never heard of a "Transnational Log File", would like to know more, having a trilingual family, might be usefulTongue
Cool
prettsons
prettsons
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5527 Visits: 1499
What is the recovery mode?

SQL Database Recovery Expert :-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (670K reputation)SSC Guru (670K reputation)SSC Guru (670K reputation)SSC Guru (670K reputation)SSC Guru (670K reputation)SSC Guru (670K reputation)SSC Guru (670K reputation)SSC Guru (670K reputation)

Group: General Forum Members
Points: 670830 Visits: 48432
Raghavender Chavva - Monday, April 16, 2018 9:06 AM


Now My question is why I am not able to shrink the log file after 1st log backup even though 98% free space is there. Why I am able to shrink the log file after n number of log backups.

Most likely because the active portion of the log was at the end of the file, and NOTHING may move log records around in a log file. At a later point, the log had looped around and the active portion was at the beginning of the file, meaning the empty space at the end could be trunctated.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Lincoln Burrows
Lincoln Burrows
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12934 Visits: 1457
First of all, Shrinking is bad. Shrinking log file on regular basis can cause issue in the fragmentation. You can simply shrink your transaction log file by following simple steps in http://www.sqlserverlogexplorer.com/how-to-clear-transaction-with-dbcc-shrinkfile/

I
f you have taken log backup, then run DBCCLOGINFO command and look entries with Status=2. Status 2 indicates the portion of log is active. If your active portion of logs is at or near the end of log file, then this will prevent the log from being shrunk.You need to setup and run frequent transaction log backups.From this the active portion will again be at the beginning and you will be able to take shrink your file!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (670K reputation)SSC Guru (670K reputation)SSC Guru (670K reputation)SSC Guru (670K reputation)SSC Guru (670K reputation)SSC Guru (670K reputation)SSC Guru (670K reputation)SSC Guru (670K reputation)

Group: General Forum Members
Points: 670830 Visits: 48432
Lj Burrows - Wednesday, April 18, 2018 12:45 AM
First of all, Shrinking is bad. Shrinking log file on regular basis can cause issue in the fragmentation.

Shrinking data file causes fragmentation, not the log file.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search