Solved

MS Sql 2000 table is set to grow unlimited but table failed to accept new INSERTs

Posted on 2007-11-30
4
268 Views
Last Modified: 2011-09-20
using asp page and vbscript i was attempting to INSERT vis sql statement records to a table.
the database was set to grow unlimited and there was over 300 gig of space ont the drive where the mdf files resided.  however, i was getting a script timeout when trying to insert a record.  the table had about 89000 records in it.  after purging the table down to 16000 records, the asp page completed properly and added records.

what reason would cause a table to stop accepting new records?
0
Comment
Question by:mhamlett
  • 2
4 Comments
 
LVL 21

Accepted Solution

by:
Kevin3NF earned 250 total points
ID: 20382047
It didn't stop, it took too long.  Probably bad indexes (or no indexes) made the insert take longer than your application expected
0
 
LVL 28

Expert Comment

by:sybe
ID: 20382114
How do you insert a new record with ASP?

I have sometimes seen code that would pull out all records in a recordset, then add a new record to the recordset and save the recordset. Of course that will take a long time with many records.

0
 
LVL 25

Expert Comment

by:kevp75
ID: 20387155
i agree with all of the above, with the exception that it should not matter how many records the table currently has.  this should have no effect on the isert query whatsoever,,,since the only work being done is the insert.

I would take a look at the timeout settings you have for your connection string, and then take a look at the insert query itself....are you doing alot of pre-processing, are there 89000 fields, etc...
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 20388309
Once records were deleted, space was freed in data and index pages, so the number of records *can* have an impact.

The only real test is to run the same INSERT from Query Analyzer, which has no timeout value.  Remove ASP from the equation to eliminat the table as the source
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now