• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1274
  • Last Modified:

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
0
hydev
Asked:
hydev
  • 5
  • 2
  • 2
  • +4
1 Solution
 
ctcampbellCommented:
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.
0
 
danblakeCommented:
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....
0
 
ctcampbellCommented:
Oh, yeah: "ALTER DATABASE dbname SET RECOVERY SIMPLE"
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Zoya79Commented:
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
0
 
jarycoCommented:
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
0
 
arbertCommented:
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.
0
 
Greg RowlandSoftware Designer, SysDBA, WebMaster OwnerCommented:
arbert,

Please correct me if I am wrong, I believe shrinking the log file is also a byproduct of performing a complete backup.
0
 
arbertCommented:
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....
0
 
danblakeCommented:
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..

0
 
danblakeCommented:
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.
0
 
danblakeCommented:
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.
0
 
danblakeCommented:
Apologies for the double posting .
0
 
debi_melaCommented:
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.


0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now