Clearing SQL Log files


I have an SQL server database and the log file has grown to a fantastic 10GB. Obviously I need to reduce this as disk space is not infinite.  I have read around the internet and I understand that by backing up the log it will reduce the log file size.

I have thus used the BACKUP LOG  SQL command and it has been backed up, however the log file is still 10GB, how can I get this down?

Btw suggesting a bigger hard disk won't get the 500 points... :-)

Any help greatly appreciated.

Who is Participating?
ctcampbellConnect With a Mentor Commented:
You need to truncate and shrink the log.

"BACKUP LOG dbname WITH TRUNCATE_ONLY" will wipe the log without needed to save it to a file (not recommended if your backup strategy includes log backups, which it must not in this case).

Then "DBCC SHRINKFILE ( file_name, target_size)".  file_name is the logical file name of the transaction log, which by default is "dbname_log" for simple databases.  The easiest way to find it is database properties in Enterprise Manager.  target_size is the size you want in MB.  Keep in mind that the transaction log sometimes can't be shrunk past a certain point because of the way SQL allocates the space.  See Books Online for more info.

If you aren't already performing regular log backups for point-in-time recovery, you can prevent this problem in the future by changing the database to the simple recovery model.
Alternativly look at shrink log (custom written sql tool -- this generally allows a shrink below the usual DBCC SHRINKFILE (file_name,target_size)) :

Forcibly Shrink the Transaction Log -- Steve Jones:

and not a DBCC SHRINKFILE in sight, its using a different mechanism....
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

you can try to re-create the log file by following these steps:
This requires SA privilages and everyone to be logged off of the server, and out of SQL Enterprise Manager

--Step 1
--Backup the current database (just in case)

--Step 2
in Query Analyzer do the following:
EXEC sp_detach_db '<database name>'

--Step 3
--Copy LDF file into a different directory or drive (also just in case) =)

--Step 4
--Manually Delete the LDF file from the C:\MSSQL7\Data directory  (or the appropriate directory where the LDF is located on your machine)

--Step 5
EXEC sp_attach_single_file_db '<database name>', 'C:\MSSQL7\Data\<database name>.mdf'
(second parameter may be different -- use the same directory as step 4)

--Step 6
--Verify database's integrity (by running a few queries against it)

--Step 7
--Delete the LDF file from Step 3

Good luck! ;)
Set the Recovery Model to SIMPLE in Enterprise Manager/databaseName(right click)/properties/Options

"Simple Recovery requires the least administration. In the Simple Recovery model, data is recoverable only to the most recent full database or differential backup. Transaction log backups are not used, and minimal transaction log space is used. After the log space is no longer needed for recovery from server failure, it is reused.

The Simple Recovery model is easier to manage than the Full or Bulk-Logged models, but at the expense of higher data loss exposure if a data file is damaged."

Then you can create and run (in your database) the following stored procedure to reduce the LOG file(s) size. (sp comments in spanish, sorry).

CREATE PROC pr_truncateLog
      DECLARE @DBName sysname,
                  @logName sysname
      -- Obtener nombre de base de datos
      SET @DBName = DB_NAME()
      -- Truncar logicamente y preparar para truncado físico
      -- Obtener lista de archivos LOG
      DECLARE logCursor CURSOR FOR
      SELECT name
      FROM sysfiles
      WHERE groupid = 0
      -- Para cada archivo LOG, ejecutar DBCC SHRINKFILE
      OPEN logCursor
      FETCH NEXT FROM logCursor
      INTO @logName
            SELECT @logName = RTRIM(@logName)
            DBCC SHRINKFILE (@logName)

            FETCH NEXT FROM logCursor
            INTO @logName
      CLOSE logCursor
      DEALLOCATE logCursor
Personally, I think ctcampbell's solution is the "tried and true" method of doing it.  That and implementing a good backup plan so the logfile doesn't get out of control.

I would never trust deleting a logfile and allowing SQL Server to recreate it--not a good practice.
Greg RowlandSoftware Designer, SysDBA, WebMaster OwnerCommented:

Please correct me if I am wrong, I believe shrinking the log file is also a byproduct of performing a complete backup.
Just because you backup the database doesn't mean the logfile will reclaim the space used on disk (shrink).  Backing up will mark the entries in the log as inactive--you still have to shrink the physical file....
P.S.  I have tested the SP version of this sproc, to backup log files -- I havn't found too many problems with this mechanism yet.  If anybody does, please let me know..

The transaction log being shrunk after a backup -- Are you using auto shrink (enabled) ?
Not recomended for large database....  (Not too much of a problem if < 5 GB total DB size.)

I believe this is the only other option to allow the transaction log to be shrunk automatically allowing sql-server control of when it decides to perform the operation.
The transaction log being shrunk after a backup -- Are you using auto shrink (enabled) ?
Not recomended for large database....  (Not too much of a problem if < 5 GB total DB size.)

I believe this is the only other option to allow the transaction log to be shrunk automatically.
Apologies for the double posting .

I had simillar issues but solved by doing the following..

1) Take complete backup of database.

2) Take bakup of transaction log . This freed up space but was reserved for that db.

3) Shrink the transaction log file alone..

try doing this through Enterprise manager.

Please check the recovery model of the database. It should be FULL or Bulk logged, only then you will be able to take transaction log backup.

Simple recovery will not allow you to take transaction log backup. This model is similar to setting the trunc. log on chkpt.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.