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 |
|
bmcclurg64
Starting Member
6 Posts |
 Posted - 2014-10-28 : 08:24:38
|
| Hello All,Been looking at some issues having with my C# .NET app inserting/updating and found something kind of interesting. Someone was talking about creating database and was defining DB and Log file sizes. What is best approach in setting these files up? I have been using the default of DB=4MB initial and grow by 1MB, Log=4MB initial and grow by 10 %. Is this not the best way to do this? I am using TVP from C# to Stored Proc and in one article the person mentions that the TVP really uses the Log file a lot. So when I try to insert or update like 40,000 records it is taking a long time. Is this partially due to the Log file continuously getting increased from the TVP? Would I be best to define the DB and Log file to have a much larger initial size and larger growth? Or should I stay away from TVP?Thanks in advance,Brad |
|
|
Shanky
Yak Posting Veteran
84 Posts |
Posted - 2014-10-28 : 08:44:47
|
quote:
Originally posted by bmcclurg64
Hello All,Been looking at some issues having with my C# .NET app inserting/updating and found something kind of interesting. Someone was talking about creating database and was defining DB and Log file sizes. What is best approach in setting these files up? I have been using the default of DB=4MB initial and grow by 1MB, Log=4MB initial and grow by 10 %. Is this not the best way to do this? I am using TVP from C# to Stored Proc and in one article the person mentions that the TVP really uses the Log file a lot. So when I try to insert or update like 40,000 records it is taking a long time. Is this partially due to the Log file continuously getting increased from the TVP? Would I be best to define the DB and Log file to have a much larger initial size and larger growth? Or should I stay away from TVP?Thanks in advance,Brad
Yes 1 MB auto growth for Log files and 10 % for log file is not a good auto growth setting. Unfortunately its difficult to predict upfront what would be accurate value because you would need to check auto growth events from default trace to reach a value. I would also like you to make sure instant file initialization is there for data files(this can be done by giving SQL Server service account Perform volume maintenance tasks in user right assignment see the linkhttp://technet.microsoft.com/en-us/library/cc779312%28v=ws.10%29.aspxNow please read below article to know about and calculate autogrowth setting for database.https://www.simple-talk.com/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/You can start with 250 MB growth fro data file and 150 MB for log file and then refer to link for accurate valueHope this helpsRegardsShankySQL Server MVPhttp://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx |
 |
|
|
|
|
|
Initial Database Design (Files)