isames

The More I know, the more I don't know

David Johnson, CD

<div>
I'd reduce the size so it doesn't take so long to do (unless you have a very fast drive system), or you have that much data being added to that db at one time.<br />
<br />
Keep in mind that the entire db waits until the log is extended, and log space <b>must</b> be <b>pre</b>-formatted, which means the entire gb must be written to disk. &nbsp;<br />
<br />
Therefore, in general, you don't want the log to grow dynamically at all. &nbsp;Instead, pre-size the log to a good size. &nbsp;Pick an off time and:<br />
1) shrink the log as much as possible<br />
2) reallocate the space in large chunks such that you have no more than say 6-8 increases in log size. &nbsp;For example, if your total log size needs to be 8GB, you might do 4 2-gig extensions, 6 1.5-gig extensions or 8 1-gig extensions.<br />
<br />
You don't want too many very small extensions, because that will give you far too many virtual logical files in the log, which will slow down processing as well. &nbsp;Try to keep the total VLFs to no more than 100.<br />
<br />
This command will show you how many VLFs the log currently has:<br />
<br />
USE &lt;your_db_name&gt;<br />
DBCC LOGINFO
</div>


<div>
You should also look at the Recovery model (in Database Properties - Options) and coordinate them with the backup.<br />
If you are using Full Recovery model without Log backups then the log will grow indefinitely (up to 2 TB in your case). <br />
<br />
If you have Simple Recovery model then the space is cleared once the transaction is finished. Remember each data update is first written to the log so e.g. indexes rebuilding can cause the unexpected log growth.
</div>


<div>
<div class="content wysiwyg-content">
All,<br />
<br />
If a SQL ldf file is set to grow by 1GB, what does that really mean? <br />
<br />
Does it mean that it will allocate 1GB every time it writes to the file? <br />
<br />
See attachment.<a href="https://filedb.experts-exchange.com/incoming/2018/11_w45/1400335/SQL-file-growth-size.png" target="_blank" title="SQL Auto growth (13 KB)"><img alt="SQL Auto growth" class="file-block lazyload" style="max-width: 800px;" data-src="https://filedb.experts-exchange.com/incoming/2018/11_w45/800_1400335/SQL-file-growth-size.png" /></a>
</div>
</div>


SQL-file-growth-size.png

All,

If a SQL ldf file is set to grow by 1GB, what does that really mean? 

Does it mean that it will allocate 1GB every time it writes to the file? 

See attachment.

LDF file is growing rapidly all of a sudden

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.