February 25, 2011 at 1:06 am
On one of our servers runs Solarwinds SQL database. At the moment it has a 137gb log file. Both this log file and database are backed up by AVAMAR. I do not think we would use this log to run a point in time restore so is it really necessary?
Or is there a quick way I can truncate that file?
February 25, 2011 at 1:30 am
Hi,
the space the log is taken on disk is different to the used pages in the log file. So if you have backed up the log, the parts of log file can reuse the backed up log space (if there is no active transaction in this part), but it would not shrink the log file automatically. And I wouldn't recommend it, because the logfile would fragment to much if you always shrink the log.
Check the free space of the log file:
SQL Server Management Studio -> Right click on Database -> Tasks -> Shrink -> Files -> Choose Log instead of Data and see the Available Free Space
What size the databases have?
Greets
pitcher
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
February 25, 2011 at 1:36 am
First thing first why is your log at 137GB?
What is the size of the DB?
If you have no need for a point in time recovery look at resetting the DB recovery model into SIMPLE mode.
If there are BULK loads that occur you may want to put the recovery model into BULK-LOGGED that provides a bit of Point in Time recovery.
February 25, 2011 at 7:25 am
The DB is 43gb. I have now managed to shrink the LOG file, thanks to your instructions and it went down to 100mb.
I did not have the room to backup the ldf file (137gb) so I just went for it as it is backed up by AVAMAR.
All seems fine. The guys using SolarWinds are grateful and I am relieved, so once again thanks for your help.
February 25, 2011 at 11:08 am
Please read through this: http://www.sqlservercentral.com/articles/64582/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2011 at 11:10 am
aa.nichol (2/25/2011)
The DB is 43gb. I have now managed to shrink the LOG file, thanks to your instructions and it went down to 100mb.I did not have the room to backup the ldf file (137gb) so I just went for it as it is backed up by AVAMAR.
All seems fine. The guys using SolarWinds are grateful and I am relieved, so once again thanks for your help.
Before you go... it sounds like AVAMAR needs to be configured to do a BACKUP LOG statement as well as BACKUP DATABASE. Talk to your sysadmins and get that corrected, or you'll have the same problem in a little while.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 25, 2011 at 11:17 am
strange a monitoring company doesn't manage their db and db logs.
Please read the article Gail pointed to. It will clarify our concerns.
In many cases, people think they don't need PIT recoverability, until their db gets corrupt. Be it by HW errors, SW errors or human flaws.
Work out a decent DRP for your db, instance and db server.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply