Post reply

Autogrow issues

  • March 2, 2005 at 11:18 am

    #164798

    Has anyone had SQL2000 not autogrow when there is plenty of drive space, causing data or logfile full errors? For that matter, it has happened when we set a fixed space, say 15gb for a 5gb db...

    This has been happening on various servers in our shop for the past 6-8 months during larger (6-8gb) nightly inserts. Also happens during distribution agent pushes at the subscriber.

    We're going to open an issue with MS, but you know how that generally goes.

  • March 2, 2005 at 11:53 am

    #544963

    This is actually a prett common occurrence.  Check out this article:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;305635

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • March 2, 2005 at 1:00 pm

    #545003

    If you have turned off autogrow for data, when inserts exceeded the restricted size, you will also get the error. This could be your case. You can take a look at SQL Server Log.

    Even though you think you have plenty of disk space, but do you really know how much space insert is using ?

    Talk to your developers and ask them how many rows they will be inserting so that you can estimate amount of space needed.

    -Jimmy

  • March 2, 2005 at 1:45 pm

    #545020

    Hmm, well, let's take the replication senerio. The publisher is pushing a subscription to another SQL2000 box. I can't believe that I'd have to change timeout parameters for a native distribution task. And, on one server where this has occured, the db size (data and log) is like 4-5gb, and there is over 30gb of actual free drive space above that.

    Again, we have even taken the approach to go ahead and set a filegroup to triple or even quadruple the size (above example, size set to 20gb) of what the data and log file could be, and have still received the error that there was no more space available.

     

  • March 3, 2005 at 12:59 am

    #545110

    Actually I was called into a client's site for that same problem.  Turned out that they had just recently implemented disk space quotas on the server, and the domain account that the SQL Server service was running under was hitting that quota limit.  Just had to take a few minutes to explain to the network admin why having disk space quota for this account was not such a good idea, and had him remove that from that account.  (But I expect he will forget, and reset it in 6 months or so, and I'll be back in there.)



    Mark

  • March 3, 2005 at 5:23 am

    #545164

    Do the errors occur at the publisher or at the subscriber?

    Is the db at the subscriber set to autogrow too?

    karl

    Best regards
    karl

  • March 3, 2005 at 11:26 am

    #545302

    Good thought on the user space quota, I'll verify that is not the case. I don't think it is.

    The errors occur at the subscriber(s), and yes they are set to autogrow, though again we have set large fixed filegroups as well to test.

  • March 3, 2005 at 3:42 pm

    #545350

    I have had this happen when using multiple files in a filegroup, when some of them have a maximum size. If the server places an object on a file with a maximum size, and the data in the object fills the file, autogrow will not work even though there is plenty of space on the disk. Although you can specify which filegroup to place an object in, you have no control over which file is used for the object (unless the filegroup has only one file).

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply