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 |
|
mi007kes
Starting Member
2 Posts |
 Posted - 2007-12-06 : 12:14:33
|
| I'm developing a VB application to insert rows into a SQL server 2000 database. I can always select query the database but the problem is an insert query will work for awhile, then stop...giving me a timeout error. The only way to get an insert query, to work again is go into user permissions and uncheck then recheck the insert permission. After this, an insert query magically works, but only for awhile. Then i must do the recheck procedure again. Any ideas??(I've tired both ways of accessing the database, through Windows authentication, and sql user authentications- both have same symptoms. |
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-12-06 : 13:44:32
|
| My guess is it is not related to permissions at all. By checking and uncheking it you give your system some idle time.The question is, why do you need that time?What is a recovery model of your database? FULL or SIMPLE?Are you inserting rows in one big transaction or not?What are the autogrowth settings for log/data? (I suspect the timeout is caused by the autogrowth) |
 |
|
|
mi007kes
Starting Member
2 Posts |
Posted - 2007-12-06 : 14:58:05
|
| What is a recovery model of your database? - FULLAre you inserting rows in one big transaction or not? - Just single row at a time.Autogrow settings? was at 10%, we tired changing it to 2MB increase, and it magically worked again. But will it say that way...lets hope. |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-12-06 : 16:20:33
|
| If your database is huge, 10% can be a lot, and to add 10% of space could take >30 seconds, a standard timeout in .NetI recommend you to add space in advance. It is much better then to create little 2Mb segments.Also, if your recovery mode is FULL, you should back up your tr.log very often (once per few minutes) during the massive data load. |
|
|
|
|
|
|
|
|
Permissions Lost! must refresh to enable?