Solved

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

Posted on 2007-04-07
26
74,248 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 5
  • +8
26 Comments
 
LVL 5

Accepted Solution

by:
rmaranhao earned 250 total points
ID: 18870001
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18870054
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
ID: 18870167
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Expert Comment

by:rcicotte
ID: 18870589
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
ID: 18871968
0
 

Author Comment

by:KenshiroM
ID: 18875682
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
ID: 18875696
KenshiroM
redeoba is my database name and MUST be replaced.
0
 

Author Comment

by:KenshiroM
ID: 18875697

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
ID: 18875733
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
ID: 18875755
Kevin3NF is right. Do not delete the log FILE!
0
 

Author Comment

by:KenshiroM
ID: 18877120
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
ID: 18877157
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
ID: 18877194
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
 
LVL 5

Expert Comment

by:rmaranhao
ID: 18877218
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
ID: 18877235
Ah...ok.  Just wanted to make it clear for any that might read it down the road.  Cheers!
0
 

Author Comment

by:KenshiroM
ID: 18878222
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
ID: 18878471
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
ID: 18878555
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
ID: 18881499
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
ID: 18881769
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
ID: 18881953
Thanks Kev
0
 
LVL 1

Expert Comment

by:ShadowMobo
ID: 20493708
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
ID: 22547020
  1. backup log [dbname] with truncate_only  
   2. go  
   3. DBCC SHRINKDATABASE ([dbname], 10, TRUNCATEONLY)  
   4. go  
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24000423
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
ID: 25998523
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
ID: 36349615
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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
return value based on substr 10 44
online  environment for testing sql queries 5 29
how to remove error in database 6 28
sql query 5 43
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

735 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