DB Will not Grow

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:

Source spid65
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 Microsofts 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 its 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.Try to capture the events using PROFILER during this process.

And refer to this KBA (http://support.microsoft.com/default.aspx?scid=KB;en-us;Q305635) for more information.|||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.

Thanks,
James

DB does not autogrow

Good Morning,

I am having a problem where I have a process that seems to fill up the database but the database does not seem to grow like it should. The database is set to autogrow by 10%... the database is around 15-16GB and it is right over 16GB when it fails. This happens once a week lately. nothing on the server has changed.

the strange thing is when you look at the database properties through EM it shows as no space available but when you do a shrink file and look at the log and data files there are over almost 2GB free on the data file and 1GB free on the log... it does no make sense...

in order for EM to show there is free space I have to add over 5GB to the data file and 1.5 GB to the log file. then processes continue...

is there something that I am missing?

I saw exactly the same problem years ago with a large DB.

The problem is that the SQL statement that is causing the Auto grow is timing out, so the grow rolls back !

Next statement does the same thing.

Never keep the default 10%, always set to some size that can be acheived in under 10 seconds.

I use 100Mb on data and 10mb on transaction.

You should not really be relying on auto-grow, a DBA should monitor it and manually grow during off-peak time.

|||

Hi William,

the best practice for data files isn't to use the Growth in Percent, but rather the Growth in Megabytes, and select a value that will work for your database.

For example, small databases might set the Growth in Megabytes to 1 Gig, where very large databases may set it to 5 Gigs or more. The reason for this is that the default percent growth (10%) for a 60 Gig database will be 6 Gigs – a large uncontrolled jump at any one time. Setting it to 1 Gig or 2 Gigs will create auto growths in a much more controlled manner.

To size your transaction log files you should do the following:

1-Shrink the log as much as possible.

2-Dump every 10-15 minutes.

3-Monitor its growth over a week - nothing the maximum size.

4-Lastly, dump the log, shrink it one more time to the minimum size and then manually size it for this observed maximum size.

Note: Use Performance Monitor to monitor the performance objects SQL Server Databases ( Log Growths, Percent log Used, and Data File Size).

Regards,

Tarek Ghazali

SQL Server MVP

http://www.sqlmvp.com

|||

I never thought about changing the growth to not be in percent... the database is a "load" database so it typically does not change too much but we have been adding more tasks to it so I am sure it will be growing steadily. I am going to give the change of growth a shot and see how it is...

I am also trying to track down where the shrinking is being done from so once the database is set it will not be shrunk.

Thanks guys... sometimes the easiest solutions are overlooked.

|||

Check the properties of the database, 'options' tab

there is an 'auto shrink' option