sql 2005 error message

Last Post 08 Dec 2006 11:28 AM by puja24. 46 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
puja24
New Member
New Member

--
13 Nov 2006 10:53 AM
Hi,
I just checked that my production sql server right now at 100%cpu. I checked the event log, I receive the following error message and not sure how do i go by troubleshooting this issue;

this is error message i get: "The server was unable to load the SSL provider library needed to log in the
connection has been closed. SSL is used to encrypt either the login sequence or all communication,
depanding on how the administrator has configured the server. see books online for information on this error message: 0X2746[Client : 192.168...]

can someone suggest me if they've seen this error before and what steps to proceed to resolve this issue?

I googled this error message but did not find similar issues to answer or suggestion..

In one article, it talked about SQL Browers but i have this service running..my named pipe is enable.

tcp/ip enabled..
so, not sure where to look in sql 2005?

i would appreciate somone help or suggestions...
thanks
puja24
New Member
New Member

--
13 Nov 2006 01:16 PM
no, i am able to log into sql instance and my other application too.

but it's stil at 100% and I am not sur what is causing that?

I have no job running on that server..
puja24
New Member
New Member

--
13 Nov 2006 09:07 PM
hi,
I ran sp_who2 and nothing is blocking. then i ran sp_lock

and i saw one ras has "TAB" under Type column and under Mode is "
if you don't mind, can you please tell me how would I read sp_lock table?

Is this something I should look into it?

also, I did dbcc indexdefrag on my indexs too. I had question regarding shrink.

i would like to perform shink on one of table but i never took the back up of the database.
is it Okay to perform the shring the log files if there were no backups performed?

i have about 3gig of log can be free if i perform shring..
can you please suggest?
puja24
New Member
New Member

--
14 Nov 2006 06:59 AM
hi,
I am really sorry for asking non-sense question but I would like to bring my sql server down from 100%.

I just ran the sp_who2 stored procedure but nothing is locking;however, I saw "sa" as login id and it's status is "suspended" nothin under "blkby" column, log witer under command column and last batch was 8/9/2006

is something casuing CPU to be at 100%..

I used sp_spaceused but it seems to be okay..

on my event log, i don't have error on hard dirve or any other application except the "SSL error which i posted earlier.

do you think if I restart the sql server, would it make any diff?

i have not done this yet since it's production server and we can't bring it down but wanted to know if it's makes any diff??
puja24
New Member
New Member

--
14 Nov 2006 09:49 AM
yes, i have

the most it uses sqlserver

i also check the memory on my server and it has about 1gb or ram

ONLY thing i have not done is restart the sql services and i am not sure if it would do any help..

puja24
New Member
New Member

--
14 Nov 2006 12:41 PM
i have ran sql profile too but nothing seems to be taking long.. what do you mean by
hogging all the memory starts again>..

can you explain?

do you suggest if If restarting the services would take care of this?
puja24
New Member
New Member

--
15 Nov 2006 07:30 AM
hi,

I have done the reboot for a while and restart the services for a while. nothing is installed on server.

I also ran sysprocesees query agianst my server and tying to see if CPU taking lot of resources on any particular id.

Actually, nothing on my frontend getting error. Every user connects to my server without any errors or slow..

puja24
New Member
New Member

--
15 Nov 2006 12:28 PM
hi,

I ran the sp_who2 stored procedure. Here is what it looks when I serch the rows and highest cputime

spid=3
status=suspended
login = sa
Dname = Null
Command: log writer
cputime = 174453
lastbatch = 8/09/06

2nd entry i found with highest cpu

spid = 1
login = sa
status = background
commnad = Resoruces monitor
cputime = 382466953
lastbatch = 8/09/06

on the other spid ids' the cputime looks normal.
can you please tell me if I should look into this ids? these are sa login id and I am not sure if it has do with causing cpu at 100%.

i would really appreciate your help..

puja24
New Member
New Member

--
16 Nov 2006 06:08 AM
hi, NO i have not rebooted yet. i wanted to see if I can troubleshoot other areas then reboot the server.
we have policy to reboot the server. so you're saying that <50 the system processes should not be considered right? No, the system is up since June 2006. The 8/9/06 which I provided is from stored precedure i ran sp_who2 and i gave you the result.

I just checked my event log today in the morning and it does not say error message but as informataion

stating: Instances of sql server has been using a process ID 428 since 8/09/2006 am. This is an information message only; no user action required.

what does it mean? can someone please tell me?
puja24
New Member
New Member

--
16 Nov 2006 12:20 PM
I found out that it's sql server id. I believe it was just regular message..
puja24
New Member
New Member

--
17 Nov 2006 08:39 AM
Hi,

The 428 is sqlserver spid. I just wanted to thank everyone for providing me the possible solution to resolve this issue. After this post, I learned little more about locks, processes, and sysmessages.
I just performed the reboot of server and defragment the system using windows utility and everything is back to normal.

I also have to run the rebuild utility from sql 2005 to rebuild indexes however, how would you know if your index or table is fragmented? is there a script to run to find out?

thanks for all the help
puja24
New Member
New Member

--
21 Nov 2006 07:20 AM

Hi,
thanks for the reply. I ran the query to see fragmented index on my server. However, I was checking my event log and i get the following error message.

2005-08-11 12:46:04.29 Logon Error: 17806, Severity: 20, State: 2.
2005-08-11 12:46:04.29 Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: <IP address>]
2005-08-11 12:46:04.29 Logon Error: 18452, Severity: 14, State: 1.
2005-08-11 12:46:04.29 Logon Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: <IP address>]


I am not sure why I get this error message. I have configured my sql server connection properties correctly so client can connect the server without any issues. I have tcp/ip ports open, shared memory is enabled, Sql Browser service is running using UDP port 1434, named pipes is enabled..
remote connection is enabled.
I can't seem to find why i get this error message.

can you please suggest if you ever seen this error before?
puja24
New Member
New Member

--
21 Nov 2006 11:11 AM
yes it's windows authentication. User is coming from our secure domain.

puja24
New Member
New Member

--
22 Nov 2006 06:23 AM
yes, it is valid sql login.

puja24
New Member
New Member

--
28 Nov 2006 06:53 AM
hi,
Domains are trusted too. My sql box is back to 100% and getting same SSL error message.
I am not sure what i need to look at??
puja24
New Member
New Member

--
28 Nov 2006 08:42 AM
Hello rm ,
I am monitoring my prod server and i had question regarding the update something on production server. I found that one of my table using lot of cpu to read the data and i wanted to make changes on the production server but I am not sure if it's okay to apply the changes on production box if many users are connected. I have to delete some column from table and apply the primary key on table.
I would like to perform this change but wanted to know if it's okay to apply on prod box?
puja24
New Member
New Member

--
28 Nov 2006 10:55 AM
i tested on my development box. Well, the way my application works is that it makes connection to my sql server so it's like disconnecting 1000 users from sql server. I was reading somethere that if i import data the table is not available for few minutes to read the data. Is it true?


also, I rebooted my server, increase the memory last week, defrg the server using windows utility. Ran dbcc defragment on index and everything was normal. I am just checking my error log and i started getting error message regarding SSL error which I posted earlier.

do you have any ideas why this is happening??? I would appreciate any help...

puja24
New Member
New Member

--
28 Nov 2006 12:51 PM
Hi,

I will be using import option to import the data. I usually don't do this offen. This only occurs when we have new updated data.

also, i know if it's bad query, i need to apply index on it. I had question on one of my table has lot of inserts and I would like to perform reindex and use the fill factor of 70%. How would i go by doing this?

can you please explain?
puja24
New Member
New Member

--
28 Nov 2006 01:14 PM
also,
would you by chance know why do I get SSL Provider error messge? I googled regarding this error message but can't seem to find solution. It talked about TCP/IP, named pipe, remote connection. I have everything enabled and also 1433 port open too.

can you please tell me if I am missing to check something or overlooked?

I started getting this error back and I am not sure how to resolve this?

puja24
New Member
New Member

--
29 Nov 2006 02:03 PM
HI Russellb & Rm,

You guys were right that it was my bad query running. I actually was reading the data from old table which was never updated and never had any index on it. Lol, finally scan through each sp and figured it out. MY CPU is at normal but little spikes get there so i will monitor. Thanks so much for all the help for High cpu utilization.
However, I ran the followin rebuild script to rebuild the index on my table. I had question regarding rebuild about nonclustered index. I know if i have clustered index on table + non-clustered, it will rebuild the both index

BUT if I have ONLY non-clustered on table because of the data, how would I run the rebuild on that?

Here is what I ran againt my sql table:

USE TEST;
GO
ALTER INDEX PK_sid" on test.dbo.employee
REBUILD;
go
puja24
New Member
New Member

--
29 Nov 2006 02:03 PM
HI Russellb & Rm,

You guys were right that it was my bad query running. I actually was reading the data from old table which was never updated and never had any index on it. Lol, finally scan through each sp and figured it out. MY CPU is at normal but little spikes get there so i will monitor. Thanks so much for all the help for High cpu utilization.
However, I ran the followin rebuild script to rebuild the index on my table. I had question regarding rebuild about nonclustered index. I know if i have clustered index on table + non-clustered, it will rebuild the both index

BUT if I have ONLY non-clustered on table because of the data, how would I run the rebuild on that?

Here is what I ran againt my sql table:

USE TEST;
GO
ALTER INDEX PK_sid" on test.dbo.employee
REBUILD;
go
puja24
New Member
New Member

--
08 Dec 2006 08:58 AM
Hi Russellb,

puja24
New Member
New Member

--
08 Dec 2006 08:59 AM
hi russell,

one more question regarding fillfactor on table. I just ran the query on my table which occurs many inserts with fill factor of 70. but i wanted to how would your verify or check the fill factor?

thanks in advance..
puja24
New Member
New Member

--
08 Dec 2006 11:28 AM
hi,
i just ran this query but no result...
puja24
New Member
New Member

--
11 Dec 2006 11:11 AM
thanks, I see it now..

this is stupid question but wanted to know if it's okay to delte the trace file from log folder?

and error log file..
puja24
New Member
New Member

--
12 Dec 2006 09:56 AM
thanks for the update. I also had question regarding shrinking file, I will be doing the shrink file on my production db while not many users will be logged on. but my question is that if i am running shrink log file and all of sudden i have to stop it due to any reasons, is it Okay to stop in middle?

does it cause any erros to sql server to function correct?

thanks for all the advise..

puja24
New Member
New Member

--
12 Dec 2006 12:10 PM
hi,

yeah, I have disk space issues going on.. I want to shrink log file since we don't use it at all. ALL of my database is set to SIMPLE mode. also,

i ran the query to see fillfactor but i don't see it under origfillfacter column.. i see 0 listed..

I just created a table and then I ran

alter index all on dbo.tablename
rebuild with (fillfactor=70)
go

but I don't see it..
puja24
New Member
New Member

--
13 Dec 2006 09:53 PM
Hi,
thanks for the update but it's just my production sql now back to 100% and sorry for asking stupid questions however, i just want to troubleshoot and bring the cpu down.

I have machine table which does the inserts and update. I have primary key on machine since it should be only one machine entry. so there is pk_machine index created. I also create IX_machine non-clustered index too. actually it was there before becuase i had duplicated on my table. after cleanning the data;now i added primary key.

My question is that should i leave non-clustered index? i checked the fragmentaion on non-clustered index and it's at 30.766666 [this is sql 2005 box]

Do I really need non-clusterd key if i have already have unique clustered key?

does this make any impact on performance??? i have about 10,800 entries...
puja24
New Member
New Member

--
22 Dec 2006 08:41 AM
HI rm,

it's me back again and my sql server is back to 100%..I am not sure what's causing this.
i checked the event log and i don't get any system errors..

I ran sp_who2 query and i am seeing how much cpu is taking up and
i found the spid =1 command=resource monitor and cputime=60072406

can you please tell me what's resouce monitor?

is it something i should look at?
puja24
New Member
New Member

--
22 Dec 2006 08:42 AM
HI rm,

it's me back again and my sql server is back to 100%..I am not sure what's causing this.
i checked the event log and i don't get any system errors..

I ran sp_who2 query and i am seeing how much cpu is taking up and
i found the spid =1 command=resource monitor and cputime=60072406

can you please tell me what's resouce monitor?

is it something i should look at?
puja24
New Member
New Member

--
22 Dec 2006 11:20 AM
I know you mention that <=50 are system process but this is just stupid question.. do they cause any performance??
also, I found the entry: dormant when I ran sp_who2 procedure.

I am just trying so hard to find the root cause of why it's goes back to 100%..

puja24
New Member
New Member

--
22 Dec 2006 11:57 AM
hi,
I also had question about shared memory. does it really have to be enabled?
puja24
New Member
New Member

--
28 Dec 2006 11:15 AM
hi,

Thanks so much for all the advice and help. I am monitoring my server and running display estimated excuation plan for all my queries and applying appropriate index

I have one table which does lot of inserts and this audit log table. I have index on datetime and datestamp column.
I ran a query which inserts all the entries to table and did the display estimted plan,
it shows me 100% on query and clustered index insert

The reason I ask is because everytime it does the insert, my cpu goes to 100% and drops down.

do you you think if I need to add more index on this table? any suggestions would greatly appreciate

puja24
New Member
New Member

--
30 Dec 2006 09:59 AM
Hi,

I have set the fill factor to this table at 70 and recreated the index..

how would i find out if there are page splits on this table?

any help would be appreciated?
it's just that server keeps going back to 100% and trying to get the root cause of it?

one more question on tempdb database? does tempdb has any cause to sql server rise to 100%
puja24
New Member
New Member

--
05 Jan 2007 05:48 AM
Hi,
sorry for asking any non-useless questions. However, I was reading about sql 2005 servic pack 1.

I am not sure if i installed the service pack 1 or not but how would i check it?

it could be that i have not applied the service pack1 and it might resolve my issue.

puja24
New Member
New Member

--
09 Jan 2007 06:23 AM
thanks,

you mean the cpu which hogs to 100% will fix the issue.

puja24
New Member
New Member

--
09 Jan 2007 07:19 AM
Hi,

I just checked my sql box and it's back to 100%. I also saw the error message in event log which is

SQL SERVER Failed with error code 0xc000000 to spawn a thread to process a new login or connection. check the sql server error log and the windoes event logs for information about this issue.

can you please what does it cause to generate this error?

thanks so much for all the advise and support..

puja24
New Member
New Member

--
09 Jan 2007 10:38 AM
what do you mean by remote connections enabled in SAC?
puja24
New Member
New Member

--
10 Jan 2007 08:24 AM
Yeah,

I have enabled the remote connectiona and also my sql browser service is running.
puja24
New Member
New Member

--
10 Jan 2007 11:27 AM
I set it to unlimited connection.

My database size is : 180MB

puja24
New Member
New Member

--
18 Jan 2007 12:23 PM
Hi Russellb

sorry for asking non-stupid question but i am trying to locate why my CPU goes to 100%.

I have sql 2005 running on windows 2003. I have users which makes connections to my sql server .
I don't have front-end website. I am monitoring the sql profile to see if any long queries causing the cpu to hogs @100%. I have reapplied index and tune the query using index tunning wizard.
I don't have any other apps installed except sql.

puja24
New Member
New Member

--
18 Jan 2007 12:23 PM
Hi Russellb

sorry for asking non-stupid question but i am trying to locate why my CPU goes to 100%.

I have sql 2005 running on windows 2003. I have users which makes connections to my sql server .
I don't have front-end website. I am monitoring the sql profile to see if any long queries causing the cpu to hogs @100%. I have reapplied index and tune the query using index tunning wizard.
I don't have any other apps installed except sql.

puja24
New Member
New Member

--
24 Jan 2007 12:05 PM
Hi,

Thanks for the all the possible solution to resolve the issue.

I did ran a profile and trace to duration >=4000 and It did not give me any query which
casued more 4 second.

I also have one more question regarding SQL Server Services

I have the SQL SERVER FullTEXT search service disabled.

does it require this service to run?

also, I know it's odd to find that server has not slow down due to high cpu Usage. The application makes connection to sql server and it does not slow down.

puja24
New Member
New Member

--
25 Jan 2007 08:40 AM
thanks rm

also, had question regarding my sql server. I am running profiler and I see
RPC:Completed Eventclass has cpu 47

why the cpu takes longer when this run?

puja24
New Member
New Member

--
30 Jan 2007 11:40 AM
Hi thanks for all the reply. if you don't mind, how would set in profile which will tell me why rpc:completed event cpu high..

also, I applied the servic pack 1 the other day and I am monitoring the server for any cpu usage.
puja24
New Member
New Member

--
30 Jan 2007 01:17 PM
HI,

My server is now back to 100%CPU and I see many errors on SSL into my event log. I am not sure what is causing that error. I checked if any of the spid >=53 causing any high cpu usage but I did not find any ids..

I feel I tried looking into everything but no luck..

I just ran sp_who2 and found out the few spid has high cpu but thy're less than 52 so no need to look at it..

puja24
New Member
New Member

--
01 Feb 2007 08:24 AM
Hi,

I checked if the indexing causing any issues but I performed the reindex on table. however, i see the errors on event log appears a lot regarding "The server was unable to load the SSL provider libray needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communctions, depanding on how the administrator has configured the server. SEE books online for information on this error message: 0x2746 [Client: 10.101.52.54]

I am not sure why this errors generates. I believe I have configured the server properly but not sure if i am misssing something. I checked SQL Broweser is running properly and also it's set to active, remote connection is enabled and also set to unlimited, I also have port 1434 is open too. The sqlbrowser is open under firewall exception list, named pipes is also enabled..

I am not sure if these errors is causing the 100% or not?


Acceptable Use Policy
---