SQL 2005 log file

The log file was set at 50 meg and would use 5 to 15 meg and the other 45 to 35 meg would remain unused.  
Significant changes were made to the SQL database that required a lot of I/O.  This caused the log file to spike to 800 meg and after the significant changes were complete, the log file was left at 700 meg unused and 100 meg used.
Because I know what event caused it to grow to 800 meg should I shrink it back to the previous 50 meg instead of leaving it at 800 meg?  A second question is why does it keep 100 meg as used when previously it would many times leave 5 meg to 15 meg as used?  Does a 800 meg log file only parse itself into increments of around 100 meg so it will always consider 100 meg to be used?
Is there a command similiar to DBCC SHRINKDATABASE (EPDB, 50) but for Log files?
dastaubAsked:
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.

Anthony PerkinsCommented:
>>should I shrink it back to the previous 50 meg instead of leaving it at 800 meg? <<
You can.  However, I would not.  800MB is not big by any means.

>> A second question is why does it keep 100 meg as used when previously it would many times leave 5 meg to 15 meg as used?<<
It could be that the Transaction Log backup (in the case of a database using Full Recovery Model) has not run, yet.  Or CHECKPOINT when the database is in Simple Recovery Model.  Or their are open transactions.

>>Does a 800 meg log file only parse itself into increments of around 100 meg so it will always consider 100 meg to be used?<<
No.  The Transaction Log is divided in VLF's (Virtual Log File), the size is dependant on the increment size:
Increment < 64MB: 4 VLFs
Increment between 64MB and 1GB: 8 VLFs
Increment > 1GB: 16 VLFs

>>Is there a command similiar to DBCC SHRINKDATABASE (EPDB, 50) but for Log files? <<
If you have to shrink your data or Transaction Log files, always use DBCC SHRINKFILE, as in:
DBCC SHRINKFILE('YourTransactionLogLogicalFileNameGoesHere', 1000)                    -- 1 GB

But again, only shrink as an emergency.  Shrink --> Bad.  It will fragment your data files.  For example, in order to see if your Transaction Log is fragmented use:
DBCC LOGINFO()

If you get > 50 rows than it is considered fragmented and should be fixed.
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
dastaubAuthor Commented:
If you get > 50 rows than it is considered fragmented and should be fixed.

by fixed, do you mean run checkdb with parameters to automatically fix any issues?
0
dastaubAuthor Commented:
Is there a way to see if you have open transactions?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
DBCC OPENTRAN
0
Anthony PerkinsCommented:
>>by fixed, do you mean run checkdb with parameters to automatically fix any issues? <<
No.  Read this article:
http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
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.