| Author |
Topic |
|
mapperkids
Yak Posting Veteran
50 Posts |
 Posted - 2007-04-19 : 10:35:58
|
| Hi,I check the database, the MDF file allocated about 600MB, but the LDF log file allocated 1800MB, it is 3 times more than the MDF file. I checked the initial size for the log file is set 1818MB.Is that reason why the log file is allocated so much spaces? try to change the initial size to smaller MB, but it seems not working that way.Thanks! |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2007-04-19 : 13:08:06
|
| 1. I don't think you can change the initial size. (I am not sure)2. You can setup a logbackup routine. as well shrink the log file.------------------------I think, therefore I am - Rene Descartes |
 |
|
|
Kristen
Test
22859 Posts |
|
|
mapperkids
Yak Posting Veteran
50 Posts |
Posted - 2007-04-20 : 07:37:20
|
| Hi,Thanks for reply, but I still don't understand, if I hand calculated the size for all tables/index only took up 700MB, why the trans log took up 1818MB? If I do the full backup for the trans log , will that make the file size smaller after the backup? When I have to reindex the index file? The table only have few indexes and allocated about 100MB spaces in totals. |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-04-20 : 08:51:25
|
| Mapperkids, I have a db that is 4.5 GB, every sunday night after the re-indexing, the trans logs goes up to 4.5 gb. The indexes only account for about 10% of this space. immediately after this, the trans logs size goes down to about 200MB or so.Hope this helps. I don't think, unless you truncate the log at checkpoint, the size will get smaller.RegardsParesh MotiwalaBoston, USA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-20 : 09:04:17
|
| "if I hand calculated the size for all tables/index only took up 700MB, why the trans log took up 1818MB?"Because the trans log has nothing to do with the size of your data, but instead represents the max. size of the un-backed-up transaction at any previous time (or possibly some even larger size that the trans log was manual set to, or was extended to)Kristen |
|
|
|
mapperkids
Yak Posting Veteran
50 Posts |
Posted - 2007-04-20 : 16:01:01
|
| Thanks,But I try to shrink the log file, it saying 97% is freespaces, after running the file shrink, it still the same size, not smaller... |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-20 : 16:41:37
|
| Seems logical log files are not in begining of the file. Try it in SSMS, there is option 'reorganize files before releasing unused space...'. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-20 : 18:21:57
|
quote:
Originally posted by pareshmotiwala
Mapperkids, I have a db that is 4.5 GB, every sunday night after the re-indexing, the trans logs goes up to 4.5 gb. The indexes only account for about 10% of this space. immediately after this, the trans logs size goes down to about 200MB or so.Hope this helps. I don't think, unless you truncate the log at checkpoint, the size will get smaller.
Why are you even bothering to shrink the log file if every Sunday it needs the extra space? What benefit are you getting from shrinking it, besides temporary extra storage space? Hint: you are negatively impacting performance by shrinking the file.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
File size question?