October 19, 2005 at 3:50 am
After a specific time (2 to 4 days) SQL hangs and it is impossible to connect to the database for a few minutes. After those few minutes SQL hangs again for a few minutes..
There is nothing special in Performance Monitor. We are running windows 2003 server, SQL Server 2000 and our server has 512MB of memory (could it be that?).
Thank you in advance
October 20, 2005 at 7:47 am
Check out your Windows Application and System Logs and also your SQL Server errorlog. There must be a clue somewhere. Do you have to restart SQL Server or reboot to clear it up? In my opinion 512MB just isn't enough memory for a database server.
Linda
October 20, 2005 at 6:01 pm
Thank you Linda, I have to reboot the server. There's nothing strange in the (error) logs.. Restarting SQL does not help. Any idea what the optimal amount of memory is running a big SQL database linked to the internet? I suggested at least 2 gig but the guy who buys the memory says 1 gig extra is more then enough..
October 20, 2005 at 10:43 pm
Do you run any batch jobs at this time?
Do you run DBCC Check DB or other maint jobs at this time?
What is the size of your Database?
How many total\concurrent users you have?
try to run server side traces for 5 minutes (not to stress your server) every hour and see if you can determine the problem.
October 21, 2005 at 6:45 am
Are there other applications on the server other than SQL Server? If so, you could check or increase the pagefile size. Also, you may want to set up a SQL Server Alert on the "SQL Server Memory Manager", "Total Server Memory", "rises above", "498073" and see how many times it reaches this limit. (which is approximately 95%).
If your server has server monitoring tools, anti virus, backup software, etc. and your database is very busy then your request for 2 GB is practical.
Linda
October 24, 2005 at 4:01 pm
The minimum anmount of memory for a SQL Server all depends ... at out site we use 4 Gb minimum. 2 Gb for the OS and 2 Gb for SQL Server. One additional thing, if you are using a server with 2 Gb or less memory I'd strongly suggest that you set min/max SQL memory to the same value. e.g. if you have 1.5 Gb of RAM, leave 768 Mb for the OS and set SQL min/max memory to 768 Mb. The delays that you are alluding to could be 'paging' (or in your case 'thrashing' between the OS and SQL for memory). You see SQL Server will take all the memory that it can (up to 2 Gb for 'standard' edition, 3 Gb for 'enterprise' edition') if you do not set min/max SQL memory'.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
November 12, 2005 at 1:36 pm
thank you for your post. it helps me a lot to get the server upgraded.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply