November 3, 2004 at 12:53 pm
I'm still a bit of a newbie, so please bare with me.
SQL Server 2000 SP3 with 7 user DBs all set to autogrow with no limit.
Most mdf files grow at 100MB and the ldf files: 5 grow at 100MB and 2 at 10MB. Now for the past couple days at about 5:30AM I have been getting Error 17883: "The scheduler 2 appears to be hung..." I believe that one of the databases is attempting to autogrow and it is bring SQL Server to a screaching halt and causing user connectivity problems for hours. I do not believe it has to do with the maximum number of processors setting due to the time that this happens, since there are only a couple users in the system. I am having trouble determining which DB is causing the problem.
I would like to schedule a job to either shrink the DB or alter the size of the DB during off-peak hours. or maybe you could suggest an alternative.
Since I have never attempted this sort of thing before, I need your help. Does it sound like I am on the right track? Is it possible to (DBCC) shrink or expand (ALTER) the database with users in the system? (This is a hospital open 24-7 and users are always in the system) Do you think I need to add an additional file to the database? If so, how's this done, cause the BOL is a little confusing to me about that. I need you to walk me through it, please.
Thanx in advance for your help.
November 3, 2004 at 1:42 pm
I'm not sure your error has to do with autogrow.
Please see:
http://support.microsoft.com/?kbid=816840
and
http://support.microsoft.com/?kbid=867878
As far as shrinking/expanding/adding database files, it would be helpful to have more information about the current condition of the files, growth patterns, disk space, disk availability, etc.
Shrinking and/or expanding can occur with users on the system, but you'll want to minimize it as much as possible. Shrinking/expanding a database will cause a significant performance hit on the database. This is also why it is better to watch your file sizes and expand them manually rather than allowing autogrow to do it.
If you need to expand a database file, while you can certainly use the alter statement, its easy to do from Enterprise Manager. Just right click on the database, select Properties, select either the Data Files or Transaction Log tab, then just change the filesize there. You can only expand this way though, not shrink. To shrink a database file, use DBCC SHRINKFILE. But don't do this without careful consideration. You don't want to be shrinking a file, and cause it to grow again only a week later.
Steve
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply