Shrinking transaction log file

I'm a beginner with SQL.  I'm running SQL server 2008 R2.  I have a transaction log that has grown to 49gb.  I run transaction log backups nightly but this hasn't been truncating the log probably due to the fact that I didn't set a growth limit on the file (??).  I've run a transaction log backup manually and when I go to shrink the file, the available free space shows as 99%.  The initial size of the file is set to 49gb, which means that it isn't shrinking.  I saw some previous posts which talked about running the command SHRINKFILE({logical file name}, target_size) in order to shrink the file to the target size and reset the initial size to the target size.  I'm wondering if it is okay to do this during production hours?  Also, is the target_size in mb?  So, if I want to shrink the file to 1gb and the logical name of the trans log file is "castlegar_Log" then I would run the query SHRINKFILE({castlegar_log}, 1000).  Does this look correct?  Does anybody have other suggestions for how I should deal with this?  Thank you.
davidrobertbristowAsked:
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.

Deepak ChauhanSQL Server DBACommented:
what do you see in this column, The value should be nothing.

select log_reuse_wait_desc from sys.databases where name = '<DBNAME>'

Shrink command is

GO
DBCC SHRINKFILE (N'castlegar_log' , 1000)
GO
davidrobertbristowAuthor Commented:
The results of select log_reuse_wait_desc from sys.databases where name = '<DBNAME>' shows nothing.  Can I issue the shrink command while  the database is in use?
davidrobertbristowAuthor Commented:
Also, running the command DBCC LOGINFO(castlegar_live) shows that the last record has a status of 2 (see attached jpg file).  Does this mean that I can't yet shrink the file?  Thank you.
translogpic-1.jpg
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

davidrobertbristowAuthor Commented:
I ran a transaction log backup and cleared the above status of 2 as a result of running DBCC LOGINFO(castlegar_live).  Again, can I run the shrink command while the database is in use?  Thank you.
Robin CMSenior Security and Infrastructure EngineerCommented:
You need to backup the transaction log and then shrink it immediately.
You should not normally shrink the log, just back it up more frequently to clear it down internally and thus keep the file size low(er).
Robin CMSenior Security and Infrastructure EngineerCommented:
Yes, you can shrink it while it's in use. There will obviously be IO and CPU load implications, but whether you notice these is dependant on how heavily utilised the server is relative to its specs.

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
davidrobertbristowAuthor Commented:
I will go ahead now and backup the trans log file then run the shrinkdb command.  However, the initial size of the log file is set to 49GB.  It only needs to be 1gb.  Do I change the initial file size after I run the shrinkdb command or do I need to try and do this beforehand?  Thanks.
Robin CMSenior Security and Infrastructure EngineerCommented:
Change the size beforehand, it'll only shrink to the size you set.
davidrobertbristowAuthor Commented:
I tried changing the size but it keeps changing it back to the previously set initial size.  However, it has lowered the initial size from 49gb to 10gb after the shrink.  Any ideas to try and get the size down to 1gb?  Thanks.
Robin CMSenior Security and Infrastructure EngineerCommented:
Do you know what a sensible size for the log should be, based on the number of transactions going through the server and how often you are backing it up? 10GB might be good size to leave it at.
davidrobertbristowAuthor Commented:
Okay, I ran another backup on the log file and then a subsequent shrinkdb and have gotten the file size down to 200mb from 50gb!  I set the growth on the log file to 10% with a limit of 2gb.  The database file is about 7.5gb.  Does the log file size limit seem appropriate?  Thanks.
davidrobertbristowAuthor Commented:
Sorry but I didn't see your post before submitting mine, Robincm.  I'm not sure how to determine the limit for the log file.  I'm taking a guess that if I run a nightly trans log backup then the 2gb limit should be okay given a 7.5gb db size.  Your thoughts?
davidrobertbristowAuthor Commented:
Great advice because it worked!  Saved my behind today so that my users can get their work done again on a test database that I couldn't previously create because of db file size problem.  Thank you!!!!
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 2008

From novice to tech pro — start learning today.