In database log file grows from 32 GB to 70 GB and % of log space used become > 97%. After I took the log backup then %of log space used comes < 1% but size remains 70 GB .
Now I want to shrink the file size , but having following doubts : -
Is shrinking log file is good, to reuse empty VLF as well as reducing size of file .
How to determine that up to what % I need to shrink the file size ...
Does shrinking log file remove the indexing of database?
Any guidance please.
I will answer this specific part
Does shrinking log file removes the indexing of database.
I believe you meant does shrinking of log file brings index fragmentation, not it does not causes index fragmentation. It definitely does not removes indexes I am not sure what made you asked this but rest assured no indexes would be removed.
For other parts of your question
Shrinking of log file is as bad as shrinking of data file. The reason is, when you log file grows again after shrinking and autogrowth event is triggered which forces the process to stop until space is made available via autogrowth event. So now think this autogrowth happening frequently on large OLTP databases you can see how many a times a process would have to wait for autogrowth to complete. The best way is to presize the log file. Please refer Autogrowth article from Simple Talk. This will help you in presizing the log file and setting appropriate autogrowth value. Both are equally important.
If space is really a problem and you anyhow need it now and have no option you my shrink the log file but DON'T make it a habit. Find out process which forced it to grow more than expected and size your log file accordingly.
Some blogs for reading
It is better not to shrink the log.
Obviously, there is a reason why the log grew, and it will probably happen again. If you shrink it, you will face autogrows which might not be good as well.
By the way - make sure the autogrowth is in MBs, not in percent.
Perhaps, you should do log backups more frequently.
Anyway, monitor the size of the log and the internal usage over time , and then if it needs shrinking, use DBCC SHRINKFILE.
Always leave some additional space in it.
Just to expand on previous answer that was posted.
Just like George said, shrinking in general is not something that should be part of maintenance job. Rather log size miscalculations, some unexpected scenarios (such as uncommitted transaction, large and intensive DMLs etc etc) or insufficient amount of log backups can cause excessive log growths.
If your log size does not seem large enough, you should monitor it during busy hours, or during night time ETLs(If you have some) to see the average log size and change it if needed. Also make sure to set log size auto growth in specific MB size, which will mostly depend on your initial log size. More info could be found here Database log VLFs
Now to answer your questions:
1) No in general. But in scenarios i mentioned above, it could be helpful. Which is the only time when it should be used - out of ordinary situations.
2) If you determined you want to shrink your log file, you should be aware that the log file is made out of VLFs(Virtual log files), which are gradually filled one at the time. Once all of them are filled,if you reached your log maximum size, log auto growth will happen and depending on size will grow in 4/8/16 VLFs. Once the log is backed up, these VLFs will become empty again (you will always have some in use, so it can track current LSN). To keep it short, once you backup you log, you can check used and unused VLFs using DBCC LOGINFO command
Fields to note here that are useful for shrinking are File Size which is the size in bytes, and Status which can be either 0(unused) or 2(used). The amount of % or MBs that could be shrunken is sum of unused VLFs - 1VLF. Be aware that shrinking only happens in VLF amounts, you cannot shrink it in desired MBs such as 5,10,12 etc, unless it fits the summed size of used VLFs. Always make sure to check messages after shrinking occurs.
3) Shrinking does not remove indexes, since shrinking removes only unused(empty) VLFs. If you might have thought, if shrinking database files defragment indexes - yes it does but its off the topic.
