Link to home
Start Free TrialLog in
Avatar of hydev
hydevFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Clearing SQL Log files

Hi,

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.

Mike
ASKER CERTIFIED SOLUTION
Avatar of ctcampbell
ctcampbell

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of danblake
danblake

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:
http://www.sqlservercentral.com/scripts/contributions/26.asp

and not a DBCC SHRINKFILE in sight, its using a different mechanism....
Oh, yeah: "ALTER DATABASE dbname SET RECOVERY SIMPLE"
Mike,
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! ;)
-Zoya
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
AS BEGIN
      DECLARE @DBName sysname,
                  @logName sysname
      -- Obtener nombre de base de datos
      SET @DBName = DB_NAME()
      -- Truncar logicamente y preparar para truncado físico
      BACKUP LOG @DBName WITH NO_LOG
      -- 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
      WHILE @@FETCH_STATUS = 0 BEGIN
            SELECT @logName = RTRIM(@logName)
            DBCC SHRINKFILE (@logName)

            FETCH NEXT FROM logCursor
            INTO @logName
      END
      CLOSE logCursor
      DEALLOCATE logCursor
END
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.
arbert,

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 .
Hi,

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.