Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Surfer513
Starting Member
29 Posts |
 Posted - 2011-05-23 : 16:34:12
|
| If I run dbcc sqlperf(logspace) I see that the log file size is, say, 30 GB. But if I navigate to the actual file on disk I see that the .ldf file is actually 31.2 GB. Is this just because of header information, or is there any other factors causing this?Thanks in advance! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Surfer513
Starting Member
29 Posts |
Posted - 2011-05-24 : 09:42:35
|
| Ah, ok you were right. I was thinking base 10 which was throwing me off.Just to work off of this question, I know it can get really tricky shrinking a log file size. It was initially set way too large, and it is utilized to anything less than 2% (t-log backups every hour). What's the best way to get that log file size down? Also, what's best practice for % utilization? I know autogrowth is recommended, but taking that a step back what should be the guidelines when setting a log file size?Another random question here. In windows explorer, you see the .ldf file for the database. Obviously it is getting written to because of the constant transactions and backups. How come the "dat modified" field in winexpl doesn't update with the frequency that transactions happen? For instance this is a production database, with constant transactions. But for some reason date modified in winexpl is a few weeks ago. |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2011-05-24 : 10:12:55
|
| "date last updated" - only changes when the file is closed => SQL service stopped.re changing log file sizes, look at member TKizers blog. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-05-24 : 21:58:14
|
| Since you have been taking regular transaction log backups - review the sizes of those files and find the largest one. Go back at least a month, longer if you can. You should be able to query the backup tables in MSDB to get the sizes.Once you have identified the largest file - that will tell you the minimum size for your transaction log. I would round the size up from there to an even multiple based upon the actual size (e.g. if the largest size is 935MB - round up to 1000MB, if 93MB round up to 100MB), and then set the autogrowth on the file to an even multiple of that size.Google Kimberly Tripp and/or search the blogs at http://www.sqlskills.com for her articles on tuning the transaction logs. One of the things you want to make sure is that you do not have too many VLF's - nor too few.Jeff |
|
|
|
|
|
|
|
|
Log File Size vs. LDF File Size