SQL Server log files (LDF files) are growing large. How can I shrink them?

KB Home   |   SQL Server log files (LDF files) are growing large. How can I shrink them?

Main.LogFileSizeOnSqlServer History

Hide minor edits - Show changes to output

Changed lines 23-24 from:
 Could not allocate space for object 'x' in database 'y' because the 'PRIMARY' filegroup is full.\
to:
 Could not allocate space for object 'x' in database 'y' because the 'PRIMARY'
 
filegroup is full.
Changed lines 28-30 from:
 The transaction log for database 'papercut' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

to:
 The transaction log for database 'papercut' is full. To find out why space in the 
log cannot be reused, see the log_reuse_wait_desc column in sys.databases

April 26, 2012, at 01:30 AM by ian - added extra error message
Changed lines 23-25 from:
 Could not allocate space for object 'x' in database 'y' because the 'PRIMARY' filegroup is full.

to:
 Could not allocate space for object 'x' in database 'y' because the 'PRIMARY' filegroup is full.\

or

 The transaction log for database 'papercut' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases


September 21, 2010, at 11:27 PM by matt - include error message to make this article easier to search for
Changed lines 1-2 from:
(:title I'm running SQL Server. Why is the database ldf file using so much disk ? Can I truncate/remove it?  :)
to:
(:title SQL Server log files (LDF files) are growing large.  How can I shrink them?  :)


Changed lines 15-16 from:

to:
!!! Disk Full Errors

If your log files are full or you've run out of disk space on your database server you may receive errors like the following within PaperCut:

 The transaction log for database is full.

or

 Could not allocate space for object 'x' in database 'y' because the 'PRIMARY' filegroup is full.


If you receive these errors:
* Check the database recovery mode (as described above)
* Cleanup any old transaction log files that are no longer needed.
* Verify that your SQL Server installation has enough disk to cater for database growth.

Changed lines 1-2 from:
(:title I'm running SQL Server. Why is the papercut_log.ldf file using 5 GB ? Can I truncate/remove it ?  :)
to:
(:title I'm running SQL Server. Why is the database ldf file using so much disk ? Can I truncate/remove it?  :)
Changed line 16 from:
[-keywords: large log file, SQL log file size, shrink log-]
to:
[-keywords: large log file, SQL log file size, shrink log, logging-]
May 16, 2010, at 11:39 PM by Tim - fix grammar or typo.
Changed lines 3-4 from:
The log file is maintained by SQL server, and managing it's size is a SQL Server administration issue.  The LDF file is the transaction log that allows you to perform "point in time" restores if you have system failures.  The way it works is that you can restore from your last full backup and "replay" the transactions from the transaction log file.  All of this sounds great, but you really need to be geared up to support it.  For example, if your transaction log files are stored on the same disk as your database, then if something goes wrong you'll lose the database and the logs.    So you have really want to use these, then you need to implement "transaction log shipping" which moves transaction logs to another system.
to:
The log file is maintained by SQL server, and managing it's size is a SQL Server administration issue.  The LDF file is the transaction log that allows you to perform "point in time" restores if you have system failures.  The way it works is that you can restore from your last full backup and "replay" the transactions from the transaction log file.  All of this sounds great, but you really need to be geared up to support it.  For example, if your transaction log files are stored on the same disk as your database, then if something goes wrong you'll lose the database and the logs.    So if you really want to use these, then you need to implement "transaction log shipping" which moves transaction logs to another system.
Changed lines 11-16 from:
If you do want to use transaction logs.  You need to manually "truncate" them after your backup procedures.  Various issues around handling and truncating log files can be found in the SQL server KB article [[http://support.microsoft.com/kb/873235| here]].
to:
If you do want to use transaction logs.  You need to manually "truncate" them after your backup procedures.  Various issues around handling and truncating log files can be found in the SQL server KB article [[http://support.microsoft.com/kb/873235| here]].



----
[-keywords: large log file, SQL log file size, shrink log-]
Changed lines 1-2 from:
(:title Why is the file papercut_log.ldf using 5 GB ? Can I truncate it ?  :)
to:
(:title I'm running SQL Server. Why is the papercut_log.ldf file using 5 GB ? Can I truncate/remove it ?  :)
May 19, 2008, at 02:07 AM by 218.214.136.161 -
May 15, 2008, at 05:59 AM by 218.214.136.161 -
May 15, 2008, at 12:04 AM by 218.214.136.161 -
Changed lines 1-2 from:
(:title Log file size on SQL server :)
to:
(:title Why is the file papercut_log.ldf using 5 GB ? Can I truncate it ? :)
May 14, 2008, at 11:27 PM by 218.214.136.161 -
Changed lines 8-10 from:


to:
Attach:log_file_size.jpg

May 14, 2008, at 11:22 PM by 218.214.136.161 -
Changed lines 3-4 from:
The log file is maintained by SQL server, and managing it's size is a SQL Server administration issue.  The LDF file is the transaction log that allows you to perform 'point in time" restores if you have system failures.  The way it works is that you can restore from your last full backup and "replay" the transactions from the transaction log file.  All of this sounds great, but you really need to be geared up to support it.  For example, if your transaction log files are stored on the same disk as your database, then if something goes wrong you'll lose the database and the logs.    So you have really want to use these, then you need to implement "transaction log shipping" which moves transaction logs to another system.
to:
The log file is maintained by SQL server, and managing it's size is a SQL Server administration issue.  The LDF file is the transaction log that allows you to perform "point in time" restores if you have system failures.  The way it works is that you can restore from your last full backup and "replay" the transactions from the transaction log file.  All of this sounds great, but you really need to be geared up to support it.  For example, if your transaction log files are stored on the same disk as your database, then if something goes wrong you'll lose the database and the logs.    So you have really want to use these, then you need to implement "transaction log shipping" which moves transaction logs to another system.
May 14, 2008, at 11:19 PM by 218.214.136.161 -
Changed lines 5-10 from:
If you don't want to use point-in-time recovery then you can disable it completely.  This means that SQL won't bother to create the transaction logs at all.  You can do this by changing the recovery model to @@Simple@@ .See the description [[http://msdn.microsoft.com/en-us/library/aa173563%28SQL.80%29.aspx|here]]. See screenshot below.




to:
If you don't want to use point-in-time recovery then you can disable it completely.  This means that SQL won't bother to create the transaction logs at all.  You can do this by changing the recovery model to @@Simple@@. See the description [[http://msdn.microsoft.com/en-us/library/aa173563%28SQL.80%29.aspx|here]]. See screenshot below.




May 14, 2008, at 11:18 PM by 218.214.136.161 -
Changed lines 5-10 from:
If you don't want to use point-in-time recovery then you can disable it completely.  This means that SQL won't bother to create the transaction logs at all.  You can do this by changing the recovery model to @@Simple@@ (see the description of simple here).  See screenshot below.




to:
If you don't want to use point-in-time recovery then you can disable it completely.  This means that SQL won't bother to create the transaction logs at all.  You can do this by changing the recovery model to @@Simple@@ .See the description [[http://msdn.microsoft.com/en-us/library/aa173563%28SQL.80%29.aspx|here]]. See screenshot below.




May 14, 2008, at 11:16 PM by 218.214.136.161 -
Changed lines 1-2 from:
(:title Log file size on SQL server )
to:
(:title Log file size on SQL server :)
May 14, 2008, at 11:15 PM by 218.214.136.161 -
Added lines 1-10:
(:title Log file size on SQL server )
The log file is maintained by SQL server, and managing it's size is a SQL Server administration issue.  The LDF file is the transaction log that allows you to perform 'point in time" restores if you have system failures.  The way it works is that you can restore from your last full backup and "replay" the transactions from the transaction log file.  All of this sounds great, but you really need to be geared up to support it.  For example, if your transaction log files are stored on the same disk as your database, then if something goes wrong you'll lose the database and the logs.    So you have really want to use these, then you need to implement "transaction log shipping" which moves transaction logs to another system.

If you don't want to use point-in-time recovery then you can disable it completely.  This means that SQL won't bother to create the transaction logs at all.  You can do this by changing the recovery model to @@Simple@@ (see the description of simple here).  See screenshot below.





If you do want to use transaction logs.  You need to manually "truncate" them after your backup procedures.  Various issues around handling and truncating log files can be found in the SQL server KB article [[http://support.microsoft.com/kb/873235| here]].

Comments

Share your findings and experience with other PaperCut users. Feel free to add comments and suggestions about this Knowledge Base article. Please don't use this for support requests.

Article last modified on March 12, 2013, at 12:49 AM
Printable View   |   Article History   |   Edit Article