Solved

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

Posted on 2007-04-07
26
74,180 Views
Last Modified: 2012-11-26
I am currently doing a Backup Plan for my Databases.(FULL Mode)

I have a database of 28MB but its log file is around 8000MB! How will i manage to clear the Logs?

I tried the following:
1. Full backup of the Database.
    (Size of Full Backup: 28MB)
2. Backing up the Transaction Log.(Radio button: 'Truncate the Trasaction Log' Ticked).
    (Size of Transaction Log Backup: around 8000MB)
3. Shrinking up Transaction Log to 4000MB and 2000MB repeatedly.
    (Right Click on the Database --> Tasks --> Shrink --> Files --> Data Type: Log --> Shrink To 2000MB

But the Transaction Remains around 8000MB!

is it safe to just delete the Log after i performed a Full backup of the Database even if its only 28MB?

Thanks in advance and Happy Easter.

David

0
Comment
Question by:KenshiroM
  • 7
  • 6
  • 5
  • +8
26 Comments
 
LVL 5

Accepted Solution

by:
rmaranhao earned 250 total points
Comment Utility
I think it's safe, once you performed a FULL database backup.

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'

Roberto.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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)

0
 
LVL 21

Assisted Solution

by:Kevin3NF
Kevin3NF earned 250 total points
Comment Utility
I have found that running a manual checkpoint command helps get a stubborn T-log to a point where it will shrink.  Run this multiple times (a dozen or more) in the query window:

Use YourDatabase
go

Checkpoint



--then run your shrink command
0
 

Expert Comment

by:rcicotte
Comment Utility
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.



0
 
LVL 18

Expert Comment

by:nigelrivett
Comment Utility
0
 

Author Comment

by:KenshiroM
Comment Utility
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
0
 
LVL 5

Expert Comment

by:rmaranhao
Comment Utility
KenshiroM
redeoba is my database name and MUST be replaced.
0
 

Author Comment

by:KenshiroM
Comment Utility

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
0
 
LVL 21

Expert Comment

by:Kevin3NF
Comment Utility
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'
0
 
LVL 5

Expert Comment

by:rmaranhao
Comment Utility
Kevin3NF is right. Do not delete the log FILE!
0
 

Author Comment

by:KenshiroM
Comment Utility
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
0
 
LVL 5

Expert Comment

by:rmaranhao
Comment Utility
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.
0
 
LVL 21

Expert Comment

by:Kevin3NF
Comment Utility
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
0
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.

 
LVL 5

Expert Comment

by:rmaranhao
Comment Utility
Sorry if I somehow said it as a bad thing, English is not my native language... I flush my logs every day...
0
 
LVL 21

Expert Comment

by:Kevin3NF
Comment Utility
Ah...ok.  Just wanted to make it clear for any that might read it down the road.  Cheers!
0
 

Author Comment

by:KenshiroM
Comment Utility
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
0
 

Author Comment

by:KenshiroM
Comment Utility
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
0
 
LVL 21

Expert Comment

by:Kevin3NF
Comment Utility
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.

0
 

Author Comment

by:KenshiroM
Comment Utility
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
0
 
LVL 21

Expert Comment

by:Kevin3NF
Comment Utility
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)

0
 

Author Comment

by:KenshiroM
Comment Utility
Thanks Kev
0
 
LVL 1

Expert Comment

by:ShadowMobo
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:Lord_Death
Comment Utility
  1. backup log [dbname] with truncate_only  
   2. go  
   3. DBCC SHRINKDATABASE ([dbname], 10, TRUNCATEONLY)  
   4. go  
0
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
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
0
 

Expert Comment

by:jingalls
Comment Utility
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.
0
 
LVL 22

Expert Comment

by:dportas
Comment Utility
The TRUNCATE_ONLY option is deprecated and does not work at all in SQL Server 2008 and later. In SQL Server 2005 the TRUNCATE_ONLY option has the potentially undesirable effect of breaking the chain of log backups - so you will be at increased risk of data loss until you do another full backup.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

772 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

10 Experts available now in Live!

Get 1:1 Help Now