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 |
|
ourspt
Starting Member
33 Posts |
 Posted - 2005-09-23 : 01:43:36
|
| Hi,I am working on a new development project that uses SQL Server database and VB.NET. The project went live recently. When we created the database, we create with some initial database size and transaction log size and gave the 'auto grow' option. The database size is under control and not a problem. But the transaction log seems to be growing day by day and is reaching alarming proportions. How do I truncate/reduce the size of the transaction log file to eliminate the unwanted portion and at the same time cause no harm to the usual database processes?Also, how and at what interval should I do this in future? Is there any way I could automate this process (like scheduling it as a batch process to run every three months etc.)?Thanks for your helpourspt |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-23 : 02:36:21
|
"alarming proportions"Is the LDF bigger than 120% of the MDF? If not then I'm not alarmed  Are you making transaction log backups? If not the LDF file will grow forever - two remedies: if you need to restore to point-in-time then enable TLog backups, if not and restoring from your Full Backups (Daily?) will be good enough then change the Recovery model for the database to SIMPLE.if you are already doing both those things then I expect the LDF is establishing its "normal operating size"Shrinking the logs: This can be done, but is no a good idea in normal usage - it will take CPU power for the server to re-acquire the space and it will fragment the files. Leave them at their normal operating size.Having said that, if you have done a massive delete, or changed your Recovery Model to Simple, etc., then you might want to do a one-off shrink.Start with Enterprise Manager - Right Click the database in question and choose "SHRINK" - use the "Move pages to the front of the file" option and make a note of size of MDF and LDF before & after. Then LDF may still be huge after, in which case ask back here 'coz there are ways to fix that.Kristen |
 |
|
|
|
|
|
Transaction Log Size