May 29, 2008 at 11:55 am
Friends,
I am planning to use SQL Server Express 2005 as the back end database for one of the VB applications which runs continuously on the client's machine.The data that will be stored in the database will be very large and it will increase day by day. Because of which the transaction log file (".ldf" file) for the corresponding database will increase.
I read some articles which say, huge size of transaction log file may cause the application crashes. Is there any way by which I can control the size of log file through VB code may be. Any way to remove the old transactions from the log file while the database is being used by the application.
I would appreciate your help.
Regards,
Swarada
May 29, 2008 at 12:14 pm
swarada1783 (5/29/2008)
Friends,I am planning to use SQL Server Express 2005 as the back end database for one of the VB applications which runs continuously on the client's machine.The data that will be stored in the database will be very large and it will increase day by day. Because of which the transaction log file (".ldf" file) for the corresponding database will increase.
I read some articles which say, huge size of transaction log file may cause the application crashes. Is there any way by which I can control the size of log file through VB code may be. Any way to remove the old transactions from the log file while the database is being used by the application.
I would appreciate your help.
Regards,
Swarada
Swarada -
You need to read up on recovery models and backup processes for SQL Server.
In short - you need to find the right recovery model for this database. If you choose "full", then setting up Log backups (which are NOT the same as the DB backup) will keep the size under control (backing up the transactions will truncate the transactions that were backed up, freeing up the space to be reused).
For what it's worth - I'd be curious to see those articles about the transaction file size having any effect on stability. I could see huge transactions being problematic, but transaction files being problematic doesn't make much sense to me. Anyway - if you should come across those, please post them - I'd like the opportunity to read them.
Finally - if the database is supposed to get as big as you're saying it will - Express is likely not the best solution, since there are limits on the resources available to Express (like 1GB RAM and 1 processor). You may need to consider having them upgrade to a "real" version of the product.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 30, 2008 at 8:13 am
Thanks a lot Matt.
One more doubt. I set the AutoShrink option on the database to True. But unless I explicitly shrink the database it is not working. What could be the problem.
Hey and here is the article...there was one more, if I find it I will definitely post it.
May 30, 2008 at 8:18 am
swarada1783 (5/30/2008)
Thanks a lot Matt.One more doubt. I set the AutoShrink option on the database to True. But unless I explicitly shrink the database it is not working. What could be the problem.
Hey and here is the article...there was one more, if I find it I will definitely post it.
Why do you want to shrink the database? That has a strong tendency to make a mess of the internals to the DB, not to mention promotes OS-level fragmentation of the files. You usually end up having to rebuild all of the indexes, etc... just to get your perf back after shrinking the DB. In short - it's not something you should do routinely. As a matter of fact - if you know the DB will grow regularly - make it big from the get go so it doesn't need to auto-grow. Give it room to grow into. Auto-growth always seems to happen at the worst possible time (usually during heavy usage), so avoiding it will help avoid those delays during your busy times.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply