April 16, 2018 at 9:06 am
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
April 16, 2018 at 9:12 am
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 .
April 16, 2018 at 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
Thank You.
Regards,
Raghavender Chavva
April 16, 2018 at 10:53 am
Raghavender Chavva - Monday, April 16, 2018 9:17 AMAfter 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
April 16, 2018 at 12:54 pm
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 useful😛
😎
April 17, 2018 at 12:40 am
This was removed by the editor as SPAM
April 17, 2018 at 3:51 am
Raghavender Chavva - Monday, April 16, 2018 9:06 AMNow 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
April 18, 2018 at 12:45 am
This was removed by the editor as SPAM
April 18, 2018 at 1:40 am
Lj Burrows - Wednesday, April 18, 2018 12:45 AMFirst 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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply