SLQserver 2000 hang unexpected for ½ hour or more

Question

SLQserver 2000 hang unexpected for ½ hour or more

Claus Donner

Grasshopper

Points: 19
More actions
February 1, 2005 at 2:44 am

#92219

Hi
We have a sqlserver 2000 EE installed, running on a dual processor machine with 4 GB RAM and AWE enabled (using 3GB). We have expirenced that the server hangs for about ½ hour up to 1 hour, and no one can access the database. Our Database is about 1.7 Gb and have around 100 users all the time. We have a suspicion that it enlarging the database files but can not verify that as we don't know when it happens. We are also looking at disk defragmentation as a possible clue. We have no backup or any other jobs running at this time, that we have full control over
After ½ hour it start to work perfect again.
Any other surgestions ?
Regards Claus

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

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

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 1

It might be worth checking the File growth properties of the database.

The data and logs should ideally be set to Autogrow by 10%. If your database is set to autogrow by 10 MB it may be having to autogrow each day. It would also be worth looking whether the autoshrink option is ticked as it may be attempting to shrink the database during the day.

Hope this helps.

Carl

Claus Donner Grasshopper Points: 19 More actions · Answer 2

HI

Our settings are 10 % growth for both ldf and mdf files.

Would it help setting the size to ie 20 Gb for the datafile and 10 GB for the logfile. and autogrow with 10 % after that (we have diskspace enough)

What about the tempdb files. thay are also autogrowing would it be better to move this to another disk

Thanks

Claus

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 3

The settings of 10% autogrowth are fine. This way your database will grow by 170 MB at a time which I assume should be fairly infrequently.

Have you got autoshrink set on the database? If you have it could be shrinking the database during business hours.

It is recommended to store your tempdb on a seperate disk.You may want to check the size of your tempdb when you find it is hanging; if you run out of tempdb space it can cause the SQL Server to stop.

You may want to check for locking on the database when you find it is hanging. You can use a stored procedure called sp_who2 to look for blocking.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 4

As a side note, I would turn off AWE, it's not needed with only 4GB of RAM. Leave the /3GB switch in however.

Shamless self promotion - read my blog http://sirsql.net

EdVassie SSC Guru Points: 60476 More actions · Answer 5

I echo what has been said about removing autoshrink. This is a performance killer. Also make sure autoshrink is off on Tempdb.

You also need to avoid autogrowing your databases if end-user performance is an issue. This includes log files and tempdb.

In SQL, file growth is single-threaded and serialises against all other activity to the same physical file. In other words, if you have a 100GB database and autogrow it by 10GB, nothing else happens for about 20 minutes while SQL gets the extra space and formats it as database pages.

If you have a system that is growing daily, it is far better to manually grow the database during a quiet time. This allows your peak-time users to use the database without it hanging.

Other RDBMSs tackle this problem in different ways. e.g. DB2 for Z/OS will start an auto grow when a database file is 70% full, and the growth process is asynchronous. In this case users only hang if the final 30% gets used before the autogrow has finished. If only MS would do something similar for SQL...

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

barsuk SSChampion Points: 12280 More actions · Answer 6

Check also all available disk space. (It is nice to have a stored proc which monitores disk space accross all servers). On one of my servers, where tempdb is on the separate disk from master db, disk with tempdb got filled up and the server became non-responsive. Server rolled back all the changes automatically and, shortly, I gained some space.

Claus Donner Grasshopper Points: 19 More actions · Answer 7

Hi All

Thanks to you all for the replies, Disk space is not a problem for now as we have more than 50 % of the disk free. around 45 Gb, we have enlarged the databases and have changed the way autogrow was being used hoping that this would help for now. The information about filegrowth was interesting and not known to me.

We haven't seen the problem on the running system for a couple of days now but has been able to recreate at our testlab.

Thanks Claus