We help IT Professionals succeed at work.

Shrinking MS SQL Log file

Hi Team,

My Db is in simple recovery mode.  This is not PROD. So backups are not important for us.

What if i shrink .ldf files? will it cause fragmentation?
Comment
Watch Question

Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
No.  The log file is sequential and can't be fragmented.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Shrinking will increase fragmentation. Data that is moved to shrink a file can be scattered to any available location in the file. This causes index fragmentation and can slow the performance of queries that search a range of the index. To eliminate the fragmentation, consider rebuilding the indexes on the file after shrinking.

From Microsoft - https://technet.microsoft.com/en-us/library/ms190757%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Btw, you need only one log file.  Multiple log files would only be used sequentially anyway.  Log file(s) don't affect data file allocation in any way at all.

Author

Commented:
Hi Pawan,

You are right. But we are not shrinking data file.

We are only planning to shring .ldf filse?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
>>We are only planning to shring .ldf filse?
For ldf there should not be any fragmentation issue. The above comment is for mdf files.

Author

Commented:
Excellent answer Scott.