I am running SQL Server 2000 with SP3 and all avail hotfixes and security patches since then. I try to keep very current on patches and security hotfixes. It is on a rather large IBM Win 2K server box with all the latest SP and security hotfixes.
I have read the problem when the file can not grow fast enough to keep up and I agree with you that does not seem to be the problem. If that was the case I should still be able to grow the file manually with Enterprise manager or Alter Database command. When I try to change the file size in Enterprise manager it acts like it changes it but if I change it press OK then open it back up it is set back to what it was previously, and puts the following error in the SQL error logs:
fcb::ZeroFile(): GetOverLappedResult() failed with error 2.
The same error happens in the SQL error logs when running the Alter Database command even though it says it completed successfully it never changes the size.
I do think this error is the source of my problem but I can find it no where on the web, on Microsoft’s site, in this or any other message boards even. Either I have something set up very wrong or I found a new bug.
The real thing that gets me is that the transaction log grows just fine but the data file will not. Although if I try to shrink the transaction log file it will not shrink, which maybe related to this or it could be it’s own problem.
I have tried to set it to grow in MB rather than % I agree this should be the way it is always done, but that does not seem to help either.
Is this a clustered system? We are seeing the same problem after failing over our cluster, running SQL 2000 Enterprise w SP2. Prior to the failover last week, when our server guys installed some new OS patches, everything was normal. I added a second data file on the Primary filegroup as a short-term measure, but I'm going to call MS soon if I can't figure it out.