April 6, 2012 at 3:03 pm
One of the very common tickets I deal with are "XBVFG0P789 has <5% free disk space on G Drive"
When I log in I see blown up ldfs. Surprisingly all LDF files when looked up in properties say 99% free space available.
What I do:
Rt click DB, Shrink Files --> LOG --> Free unsused space --> saves me 0.5 GB to 1 GB on each LDF
Free space in disk .. happy me 🙂
What I think I can do better:
Should I not schedule something on the server on a regular basis which checks to see if there is a plausibility of shrinking and SHRINK such growing logs daily.
Example would be this.
What should I do:
🙂 Please suggest 🙂
Also Please comment and what I'm doing & what I think I can do better.
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
April 6, 2012 at 3:08 pm
You shouldn't be shrinking the log at all.
If the log is regularly reaching a certain size, then it needs to be that size for regular database activity. If you keep shrinking it, you just force the log to grow again, that slows down the database while the log is growing and probably causes internal log fragmentation that will slow down backups, database recovery, replication and anything that needs to read the log.
Please read through this - Managing Transaction Logs[/url]
p.s. There's no good reason to want free space on a disk. Free space on a disk doesn't make SQL faster, doesn't make windows faster, it's just plain unused space that could be used for something.
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
April 6, 2012 at 3:08 pm
It has some reason that log file grows to that size.
If I were you, I would like to know why the log file grows that big, and then take action accordingly.;-)
====================
Edit: Gail wins. :w00t:
April 7, 2012 at 10:56 am
Thank you. I'm trying gain some insight as to the role of Model DB, Managing Tx log back ups etc.
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
April 10, 2012 at 4:21 am
Hi,
If frequently DML operations applied on the DB (Insert, Update & Delete), the Log will grow as per the transactions. So whenever any Delete operations applied on the DB, the space in the Log file will remain same, eventhough the record deleted. This is one of the reasons for Log file to reach big size.
It doesn't mean that the complete size of Log file has fulfilled with DML records, so whenever we Shrink the Log file (means free up the Physical space), it releases its unused space. Because of this, the Physical size of Log file will get into less size for further operarions.
If you think, heavy transactions applied to the DB, you can schedule a job or M-Plan to shrink the Log file for every 7 days or 14 days etc...
Nothing will happens by shrinking the Log file, it just relaeses the free space and make availbale space for further Transactions.
April 10, 2012 at 4:26 am
ajay.g (4/10/2012)
So whenever any Delete operations applied on the DB, the space in the Log file will remain same, eventhough the record deleted.
???
If you think, heavy transactions applied to the DB, you can schedule a job or M-Plan to shrink the Log file for every 7 days or 14 days etc...
Scheduled shrinking of anything, log or data is a very bad idea.
Nothing will happens by shrinking the Log file, it just relaeses the free space and make availbale space for further Transactions.
Not true. It's not required to shrink a log to make space for further transactions. Space in the log is made available whenever the log is truncated (a bad term, since nothing's shrunk). That happens on checkpoint in simple recovery or log backups in full or bulk-logged recovery.
When the log is shrink, the physical file is reduced in size, the free space returned to the OS. If further transactions need that space in the log, it's no longer available and the log will have to grow again.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply