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 (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7588 Visits: 1483
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.

Thank You.

Regards,
Raghavender Chavva
Beatrix Kiddo
Beatrix Kiddo
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27418 Visits: 7211
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 (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7588 Visits: 1483
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


Thank You.

Regards,
Raghavender Chavva
Sue_H
Sue_H
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69698 Visits: 14486
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 (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)

Group: General Forum Members
Points: 156836 Visits: 23036
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 (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6652 Visits: 1559
What is the recovery mode?

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

Group: General Forum Members
Points: 896174 Visits: 48655
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16452 Visits: 1993
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 (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)

Group: General Forum Members
Points: 896174 Visits: 48655
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