Slow SQL Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Slow SQL Server

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-02-09 : 10:09:51

Greetings,

Our app running on sql server suddenly slowed so much that it is timing out.

I troubleshooted the asp files and server and found nothing.

It turns out that sql server is the culprit.

Now, I ran sql profiler and I am getting something like this:

ApplicationName CPU DURATION
Audit Logout 16 20000
Job Manager 16 19034
Alerts Engine 15 25000

I went into alerts and found 2 lines indicating:
Demo: Full msdb log error 9002; Last occurred: Never

Immediately after these 2 lines, the rest of the lines would say:

Demo: Sev. 19 errors; error: 0; serverity: 19
then 20, 21, up to serverity 25

Any ideas what these mean and what I need to do to fix this bottleneck?

Much appreciated

sshelper
Posting Yak Master

216 Posts

Posted - 2007-02-09 : 10:19:14

Based on the following error message:

Demo: Full msdb log error 9002; Last occurred: Never

Your msdb log file is already full. Try backing up your msdb database then shrink your database so that it will free up space on the log file.

SQL Server Helper
http://www.sql-server-helper.com

Kristen
Test

22859 Posts

Posted - 2007-02-09 : 10:34:43

If your MSDB is full / large see:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=MSDB+Massive

for ways of removing stale data (the MS SProc will take forever!)

Kristen

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-02-09 : 10:42:31

Thank you for your prompt response.

I have backed both up and when I attempted to shrink, I get this:

Maximum free space in files after shrinking. By default, this is 0.

First what does this mean?

And what is your recommendation?

Thank you (again)

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-02-09 : 11:27:14

sorry guys,

I am a bit confused now.

Alert is saying that tempdb is full.

However, when I checked, I get the following:

tempdb allocated size=9mb

space free= 7.76 (86%)

Why am I still getting tempdb full?

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-02-09 : 11:33:44

Is your disk drive full?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-02-09 : 11:48:07

this may be an ignorant question but I will ask anyway.

Do you mean hard drive?

If yes, then no, it isn't full.

Infact, there is plenty of space left

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-02-09 : 15:51:17

Hold on a second. Alerts <> Errors. What you are looking at are the default alert setups to CATCH these conditions. These are not actual errors. To see if there are any server level problems, check the SQL Server Errorlog.

Now, on to the original problem, set up perfmon, or task manager, and check to see if the SQL Server is running up the CPU or not. It is expected that SQL Server takes up a lot of memory (by the way, what are the specs on this box, and how many SQL batch/sec are you running?). Check also for avg disk queue (may require reboot) and memory pages/sec (should be less than 20-50/sec excluding spikes).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources