Transaction Log is Big! How can i clear the Log? (SQL 2005 Std)

hi,

  transaction log has 2 possible configuration:
  * SIMPLE
     means that any transaction get's written to the transaction log, but once it is committed, it is overwritable in the log.
     you cannot perform transaction log backups
     you cannot perform restore to a point of time
     you should let the transaction log file size as it is, because if you shrink it (regulary) to a small size, it has to grow again.

  * FULL
     means that any transaction get's written to the transaction log, but only once the transaction log backup has been performed, it is overwritable in the log
     you have to (should) perform transaction log backups
     you can perform restore to a point of time
     you should let the transaction log file size as it is, because if you shrink it (regulary) to a small size, it has to grow again.

note: if you had a database in full recovery mode, but did not do any transaction log backups, the log file will indeed grow endlessy until it fills the hard disk(s) completely.
if that happened, you should change to SIMPLE, and try several times the DBCC SHRINKFILE ( log_name, <some size here) until the file shrinks (as the log file is internally a circular buffer, and in case the pointer is at the end of the file, the file won't shrink, hence the retries)

Look at the actual transaction log file to see if it is actually 8GB in size.  The interface could be reporting the size incorrectly.  The log file when shrunk to its minimum should be only 1 or 2k.  If you have done a complete Full backup of the database as you say then you can shrink your log file immediately after the backup and it should be no more than about 2k.  You can find the location of your database and log files in the management studio by selecting your database | right click | properties | files.
Use windows explorer to go to the drive location and verify the size of the file.  If the file size is being reported correctly and none of the above answers work then try restarting the server.  This will usually allow the shrink file to work.

see
http://www.nigelrivett.net/SQLAdmin/TransactionLogFileGrows_1.html

Thanks for your quick replies.

I Confirmed that the log file is actually 8GB

Will try the following tonight:

1.DBCC SHRINKFILE('logfilename', 1)
   BACKUP LOG redeoba WITH TRUNCATE_ONLY DBCC SHRINKFILE('logfilename', 1)

   LogfileName is usually databasename_log, I.E. 'northwind_log'

2.Use YourDatabase
go

Checkpoint



--then run your shrink command

KenshiroM
redeoba is my database name and MUST be replaced.

3. Will change to SIMPLE, and try several times the DBCC SHRINKFILE ( log_name, <some size here) until the file shrinks (as the log file is internally a circular buffer, and in case the pointer is at the end of the file, the file won't shrink, hence the retries)

If one of these will not work i would have to delete the log file! hoping that it won't do any damage to the Database in question.

Thanks. will get back with any news.

David

If you delete the log file, your database will become unuseable.

Run my Checkpoint suggestion BEFORE trying the shrink (and substitue the corerct database name for where I type 'YourDatabase'

Kevin3NF is right. Do not delete the log FILE!

Hi Guys

I did the CHECKPOINT and DNCC SHRINK commands and the log file is less than 1 MB!

How is that possible?

Is it normal that a Log file 8GB in size shrinks to just under 1 MB??

Thanks in advance.

David

All the information about the commands run on the database has been deleted. All the data is still there, but the information about how and when the records were created is gone.

Yes....that data had already been backed up by your t-log backup job.  You just had empty space between a couple of transactions.  Checkpoint/Shrink simply "defragged" the T-log (not the correct term, but conceptually the same) and allowed it to shrink.

The best idea is to set the T-log to a reasonable size (in your case, maybe 20MB?) and perform regular database and log backups.  You are not likely to see this issue again.


>>All the information about the commands run on the database has been deleted. All the data is still there, but the information about how and when the records were created is gone.<<
You say this like its a bad thing...all that information is supposed to be gone from the t-log eventually, once it has been committed to the database

Sorry if I somehow said it as a bad thing, English is not my native language... I flush my logs every day...

Ah...ok.  Just wanted to make it clear for any that might read it down the road.  Cheers!

Hi Guys,

Thanks for all your valid solutions.

These are the steps i followed:

1.(Kevin3NF)
   Use YourDatabase
   go

   Checkpoint

2.(rmaranhao):
   To truncate my log, I use the following commands:
   DBCC SHRINKFILE('logfilename', 1)
   BACKUP LOG redeoba WITH TRUNCATE_ONLY DBCC SHRINKFILE('logfilename', 1)

   LogfileName is usually databasename_log, I.E. 'northwind_log'

Thanks a million for your help

Dave

Hi Guys,

Just one other little question.

Where did the backup of the database log file go to with this command?

BACKUP LOG databasename WITH TRUNCATE_ONLY DBCC SHRINKFILE('logfilename', 1)

Regards,

Dave

NO_LOG | TRUNCATE_ONLY

Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.

After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE.

Thanks Kevin for clearing my doubts!

Here is my backup strategy and scripts(I Have 3 Steps). Could you kindly check it out and tell me what you think needs to be changed:

1.Full Backup on each Database will occur every Sunday at 10:00 pm. It will overwrite the previous Backup.

BACKUP DATABASE database TO [database-Data]
      WITH NAME = N'database-Full Database Backup', DESCRIPTION = N'Full Production Instance', MEDIANAME = N'databaseMedia', MEDIADESCRIPTION = N'database Backup Media Set', FORMAT, INIT

2.Log Backup on each Database will occur Daily at an interval of 4 Hours. It will not overwrite the previous Log Backups.

BACKUP LOG database TO [database-Log]
      WITH NAME = N'database-Transaction log Backup', DESCRIPTION = N'Full Production Instance'

3.Log Backup on each Database will occur every Sunday at 10:00 pm. It will overwrite the previous Log Backups.

BACKUP LOG database TO [database-Log]
      WITH NAME = N'database-Transaction log Backup', DESCRIPTION = N'Full Production Instance', MEDIANAME = N'databaseLogMedia', MEDIADESCRIPTION = N'database Log Backup Media Set', FORMAT, INIT

Thanks and regards,

Dave

From a recovery standpoint, if you lose the database on Saturday night, you will have to restore about 40 files using the above plan (Sunday Full plus 6 t-logs per day)

Change the Full backup to run daily, not weekly (keep one or two previous days on disk incase the .bak is corrupt)

Thanks Kev

Wow this is a great post, very informational when it comes to Transaction logs. Thanks everyone! You just helped me with a problem I was going to ask about.

  1. backup log [dbname] with truncate_only  
   2. go  
   3. DBCC SHRINKDATABASE ([dbname], 10, TRUNCATEONLY)  
   4. go  

sometime if you just truncate transaction log, it will not take effect in very big database then you have to follow

USE DBName
GO
DBCC SHRINKFILE(transactionlogfilename, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(transactionlogfilename, 1)

all details at http://www.sqlhub.com/2008/05/sql-server-truncate-transaction-log.html

Thanks Much Kevin3NF !

Your running the Checkpoint command has helped solve an issue I have had trying to run automated commands to shrink my T-Logs.  Adding this command in front of the shrink command has solved my issue.