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

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

KenshiroMAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rmaranhaoCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Kevin HillSr. SQL Server DBACommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

rcicotteCommented:
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
KenshiroMAuthor Commented:
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
rmaranhaoCommented:
KenshiroM
redeoba is my database name and MUST be replaced.
0
KenshiroMAuthor Commented:

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
Kevin HillSr. SQL Server DBACommented:
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
rmaranhaoCommented:
Kevin3NF is right. Do not delete the log FILE!
0
KenshiroMAuthor Commented:
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
rmaranhaoCommented:
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
Kevin HillSr. SQL Server DBACommented:
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
rmaranhaoCommented:
Sorry if I somehow said it as a bad thing, English is not my native language... I flush my logs every day...
0
Kevin HillSr. SQL Server DBACommented:
Ah...ok.  Just wanted to make it clear for any that might read it down the road.  Cheers!
0
KenshiroMAuthor Commented:
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
KenshiroMAuthor Commented:
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
Kevin HillSr. SQL Server DBACommented:
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
KenshiroMAuthor Commented:
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
Kevin HillSr. SQL Server DBACommented:
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
KenshiroMAuthor Commented:
Thanks Kev
0
ShadowMoboCommented:
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
Lord_DeathCommented:
  1. backup log [dbname] with truncate_only  
   2. go  
   3. DBCC SHRINKDATABASE ([dbname], 10, TRUNCATEONLY)  
   4. go  
0
RiteshShahCommented:
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
jingallsCommented:
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
dportasCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.