Does shrinking log file affects the indexing in SQL Server? 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 &lt; 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

Steps for better transaction log throughput

Transaction Log VLF too many too few