Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Two days ago the transaction log filled up and my user's couldn't update or insert. One of my techs truncated the log, shrank the db, then did a full backup. The next day (Monday) we noticed that whenever our applications try to do a large number of multiple updates or inserts we get an S1008 "operation cancelled" error. You can do a small number of updates or inserts but after some limit is reached we get the error.

Here's a example of what is returned.enter image description here

We've been running this db for probably ten years and never had an issue like this. Did the shrink db cause it? And what can I do to fix it?

Edit: I just discovered the "space available" on the db properties says 1.93 mb free when there's 14gigs left on the drive. I would think this could be the problem but I don't know why the db thinks it only has that much space left.

enter image description here

SELECT name, size, maxsize FROM sysfiles; returns:

name         size       maxsize 
CENTRAL_dat  6254000    -1 
CENTRAL_log  128        -1 
CENTRAL      1593       -1

enter image description here

The db file seems to be set so that it can grow as large as needed. The partition it's on has about 14 gigs free. I don't think it's running out of room.

share|improve this question

closed as off-topic by Max Vernon, Mark Storey-Smith, RolandoMySQLDBA, Jon Seigel, Michael - sqlbot Nov 9 '13 at 21:05

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, Mark Storey-Smith, RolandoMySQLDBA, Jon Seigel, Michael - sqlbot
If this question can be reworded to fit the rules in the help center, please edit the question.

Space available inside the database file is not the same as space available on the disk. Perhaps your data file has a max setting that is much lower than the space available on the disk, or perhaps SQL Server 7.0 can't see the full drive so it doesn't think it can grow. That software was written before many people on this site were alive, after all. – Aaron Bertrand Nov 5 '13 at 15:19
Is there a reason you are Still using SQL Server 7? You do realize that is ancient software? – Max Vernon Nov 5 '13 at 15:19
There's actually 2 .ldf's. One is 12mb and the other is 1mb. There's about 12 gig free on the partition the ldf's are on. Both are set to auto-grow. – Killakeys Nov 5 '13 at 15:21
The only error on the event log is when SQL Server is starting up 17122 : initdata: Warning: Could not set working set size to 1775936 KB. – Killakeys Nov 5 '13 at 15:22
Is the autogrowth set to 10%? It's possible that SQL Server 7.0 can't handle a growth > 4 GB. (Or, not sure what the underlying OS is, but maybe the problem happens there.) Try manually growing the file. – Aaron Bertrand Nov 5 '13 at 15:36