I noticed that my transaction log file is HUGE (39 GB) how do i make it so it doesn't get that big?

I noticed that my transaction log file is HUGE (39 GB) how do i make it so it doesn't get that big?
vbnetcoderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

vbnetcoderAuthor Commented:
...and shrink the current one
0
PadawanDBAOperational DBACommented:
Switch the database into simple recovery model or back up the transaction log at regular intervals.  To shrink it, check out Steve's article: http://www.experts-exchange.com/Database/MS-SQL-Server/A_11077-How-to-shrink-a-bloated-log-file.html
0
Steve WalesSenior Database AdministratorCommented:
Beat me to it! :)

I do need to update that article - one thing it doesn't note ... after the initial shrink, you may need to do it again immediately to get best results, because of any transactions that are in flight at the time of the shrink.

If you elect to switch to Simple recovery mode to do the shrink, IMMEDIATELY switch back to full and then perform another full backup since you've just blown your recovery chain out of the water.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PadawanDBAOperational DBACommented:
You've made it so quick to answer these questions! =)
0
vbnetcoderAuthor Commented:
This article tells me what to do but not sure who to do it?

What steps do i take so the file is smaller and so that it doesn't get big again?
0
Steve WalesSenior Database AdministratorCommented:
Your DBA would perform the steps.

Decide if you want to perform a log backup then do the shrink (twice) or switch to Simple recovery mode, do the shrink (possibly twice), set to full recovery mode and then do a full backup.

By adding regular transaction log backups to your backup plan you will ensure this never happens again.
0
vbnetcoderAuthor Commented:
I had meant to say how to do it .... i am going to have to perform these steps since we don't have a dba
0
Steve WalesSenior Database AdministratorCommented:
Start SQL Server Management Studio.
Expand the Object Exporer on the Left
Find your database.
Right Click / Properties - select Files.
Note the logical Name of your logfile (usually something like mydb_log)

Now decide if you want to backup your transaction log now (all 40 GB of it) or switch to simple recovery mode.

If you are going to backup the LOG, then just perform a log backup.  

If switching to simple:

Assuming you still have the Preferences Dialog open, click on "Options" and change recovery mode to "Simple".

Click OK and close the dialog.

Now, open a new query window and run "DBCC SHRINKFILE (mydb_log, TRUNCATEONLY)"

You may have to run that twice.

Immediately return your database to Full recovery mode and take a full backup.

Then start scheduling regular transaction log backups as well as your full backups.  (You are taking backups, right?)
0
Steve WalesSenior Database AdministratorCommented:
If you'd like to actually see some examples on the above reference the following two Video Micro Tutorials:

How to Shrink a bloated Log File: http://www.experts-exchange.com/Database/MS-SQL-Server/VP_473.html

Implementing a Backup Strategy in SQL Server: http://www.experts-exchange.com/Database/MS-SQL-Server/VP_459.html
0
vbnetcoderAuthor Commented:
What is the advantage of simple recovery mode?
0
Scott PletcherSenior DBACommented:
Take a step back.

Do you need full recovery, and will you actually use the capability it gives you?

That is, if a db becomes irretrievably damaged -- rare, but it can happen, most often due to a physical disk issue -- how you plan to handle it?:

A) just restore last night's backup

B) restore to a pre-determine time of day, for example, "if a failure occurs, we will go back to the last full backup or 1:00pm [or whatever specific time], whichever is the last available"

C) get as close as possible to the time of failure
0
Steve WalesSenior Database AdministratorCommented:
There is no "advantage".

Full Recovery mode exists so that you can recover a database to any given point in time (subject to appropriate backups being available).

If you're in Simple Recovery mode, you can only restore a failed / corrupt database to the point of your last backup.

Say you backup once a night at 1AM.  If you have a failure at 12:59 AM, you lose 23 hours and 59 minutes of data.  It's just gone, forever.  No chance to get it back.

If you're in full recovery mode and you have a good backup strategy in place, you can restore right up to the point of failure in most cases.

Say you're taking a full backup nightly and transaction log backups every 30 minutes... in the event of failure you can recover up to the point of the last log backup (at most 30 minutes of loss) and under some circumstances, you can recover right up to the point of failure.

In fact, there is another Video Micro Tutorial available that should help explain those concepts too: http://www.experts-exchange.com/Database/MS-SQL-Server/VP_470.html
0
vbnetcoderAuthor Commented:
This database is being used by a third party application and it gets replaced every minute with new data. So, everything could be easly replaced.  So, i guess i don't need a full backup?
0
Scott PletcherSenior DBACommented:
In that situation, you really don't.  And you certainly don't want, or need, to be in full recovery mode.

Put it in simple mode asap, then do a CHECKPOINT to truncate the log, then shrink the log.  If necessary, do it again.
0
vbnetcoderAuthor Commented:
OK

How do i do a CHECKPOINT to truncate the log

and

do a CHECKPOINT to truncate the log?
0
Scott PletcherSenior DBACommented:
USE <your_db_name>
CHECKPOINT
DBCC SHRINKFILE ( 2, <nnnn> ) --where nnnn is the desired final log size, in mb
CHECKPOINT
DBCC SHRINKFILE ( 2, <nnnn> ) --where nnnn is the desired final log size, in mb
0
Steve WalesSenior Database AdministratorCommented:
Stick this in a query window in management studio.

use [yourdbname]
go
checkpoint
go

Truncating the log is something that happens internally in the logfile that will enable your log file to be able to be shrunk.

Read the article linked earlier.  It talked about truncation vs shrinking.
0
vbnetcoderAuthor Commented:
I run this:
DBCC SHRINKFILE (mydb_log, TRUNCATEONLY)"

and i get an error that "the transaction log for the database  is full due to log_backup
0
vbnetcoderAuthor Commented:
I ran the following

--Use Master
--ALTER DATABASE mydatbase  SET RECovery Simple

 

USE mydatbase
 CHECKPOINT
 DBCC SHRINKFILE ( 2, 1000 ) --where nnnn is the desired final log size, in mb
 CHECKPOINT
 DBCC SHRINKFILE ( 2, 1000 ) --where nnnn is the desired final log size, in m


Let me know if i should do anything else
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
vbnetcoderAuthor Commented:
ty
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

From novice to tech pro — start learning today.