Solved

Clearing SQL Log files

Posted on 2004-03-30
13
1,183 Views
Last Modified: 2012-08-14
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
Comment
Question by:hydev
  • 5
  • 2
  • 2
  • +4
13 Comments
 
LVL 3

Accepted Solution

by:
ctcampbell earned 500 total points
ID: 10715123
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
 
LVL 13

Expert Comment

by:danblake
ID: 10715166
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
 
LVL 3

Expert Comment

by:ctcampbell
ID: 10715173
Oh, yeah: "ALTER DATABASE dbname SET RECOVERY SIMPLE"
0
 

Expert Comment

by:Zoya79
ID: 10715364
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
 
LVL 7

Expert Comment

by:jaryco
ID: 10715580
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
 
LVL 34

Expert Comment

by:arbert
ID: 10715649
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 4

Expert Comment

by:SurferJoe
ID: 10715763
arbert,

Please correct me if I am wrong, I believe shrinking the log file is also a byproduct of performing a complete backup.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10715821
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
 
LVL 13

Expert Comment

by:danblake
ID: 10715832
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
 
LVL 13

Expert Comment

by:danblake
ID: 10715875
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
 
LVL 13

Expert Comment

by:danblake
ID: 10715892
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
 
LVL 13

Expert Comment

by:danblake
ID: 10715898
Apologies for the double posting .
0
 
LVL 3

Expert Comment

by:debi_mela
ID: 10717123
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now