SQL SERVER 2005 strange event log error
-
HI,
I manage this server and i checked the task manager to see where the most of my resources using and it's sqlserver. I have also ran sp_who2 to see if there are any locks but it's not any locks appearing at all. I also ran sysprocesses query to analyze the issue. The ONLY I have not done yet is restart the sql server services or reboot. I wante to troubleshoot this issue before I do that.
As far as certificate, we don't have that. Sql server makes connection using tcp/ip.
Hi,
My production sql server's CPU at 100% for last 2 days and I am trying all the options to bring down the cpu. I checked the error log to my event log:
This is error message appearing for last 2 days:
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 sequences or all communications;depanding how the administrator has configured the server. The error message 0x2746 [client: 10.1.51.26].
Has anyone seen this error before? I would really appreciate if somone has any suggestiong what's causing this error and why the CPU at 100%..
thanks
-
I would talk to your sysadmins..that's an issue with the server not SQL Server. As to why your CPU is at 100% - we can't tell you that. We would have to be able to have access to the server. It can be lots of things. A poorly written query, too much activity on the server (and not just SQL Server activity). But for a start - right click on your taskbar and select task manager. Click on the process tab and then order by the CPU column. See what process is using the most CPU. That should give you an idea as to where the problem is.
-SQLBill
-
Did someone enable/force SSL encryption on the server without installing a certificate, or is the installed certificate expired/not able to validate? Check the Server Network Utility (SQL 2000) or the SQL Server Configuration Manager (SQL 2005).
Hope that helps,
Enjoy!
A Brown
Manage all of your backups in one simple job using this script. -
Hi, I just reply but actually added to my post.
-
You probably clicked 'Edit Post', the 'Reply to post' option is all the way at the bottom of the thread.
Start looking at the connections made to SQL Server (sp_who2 or in Enterprise Manager, Current Processes). Look for ones that have been running for a long time. That might indicate a poor query. Also look for ones that are connected but not doing anything. If there's two many idle connections it might 'tie' stuff up.
-SQLBill
-
hi,
thanks for reply. I ran sp_who2 stored procedure but i don't see it anything running for a long time. yeah, I have users makes connection but not doing anything. It's just idle but I have had problem before. The way my application works everytime users connects to pc, it makes connection to my sql server. I have program which checks if the connection is still or not so users don't lose connection.
is something I should look for?
-
A huge amount of idle connections can be a problem... but I mean huge (10K maybe even 100K). Unless you server is extremely short on ressources where 500 connections can eat up some valuable ram and start a cascade reaction to slow the server (less cache hit ratios, less data in memory, longer queries, more disk io, even longer queries, users start hitting refresh 3-4 times...)!
-
BTW just ran a check on my server. A single connections seems to occupy around 32K of ram. So 1000 idle connections would take around 32mb or ram. It's a lot when you are short on ram... but not a huge deal. However this is something I feel you should adress before it goes to 1000 connections
. -
Hi,
I have about 1gig memory occupied to sql server for only this reason. however, i was searching regarding this error message and found it from microsoft forum that, it required to upgrade more memory to server. I am thinking of doing that tonight and let's see if problem arrise again or not?
also, i was reading about dbcc reindex and defrag. if you don't mind, how this works in sql 2005. I saw in SQL 2005, there is rebuild all option and i am not sure what this does? have you ever used this tool?
-
In 2005, you have the option to rebuild indexes online as well as offline. Books Online provides a ton of information for how to do this.
Enjoy!
A Brown
Manage all of your backups in one simple job using this script. -
hi,
thanks, I found the article. I wanted to know more about fill factor. I have table which is heavily for inserting rows. I wanted to use fill factor on this. I believe when I set the index, it was just default and I would like to set a fill factor for this particular table. How would I accomplish this?
-
The following is from BOL for use in CREATE INDEX ... WITH ...:
PAD_INDEX = { ON | OFF }
Specifies index padding. The default is OFF.
ON
The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.
OFF or fillfactor is not specified
The intermediate-level pages are filled to near capacity, leaving sufficient space for at least one row of the maximum size the index can have, considering the set of keys on the intermediate pages.
The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. If the percentage specified for FILLFACTOR is not large enough to allow for one row, the Database Engine internally overrides the percentage to allow for the minimum. The number of rows on an intermediate index page is never less than two, regardless of how low the value of fillfactor.
In backward compatible syntax, WITH PAD_INDEX is equivalent to WITH PAD_INDEX = ON.
FILLFACTOR = fillfactor
Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild. fillfactor must be an integer value from 1 to 100. The default is 0. If fillfactor is 100 or 0, the Database Engine creates indexes with leaf pages filled to capacity, but some space remains within the upper level of the index tree to allow for at least one additional index row.
Basically, if you're never going to use UPDATE's ,such as with a log-type table, then you can set your fill factor to 100/0. The default is 80, which means that so long as less than 20% of the data stored on that page changes, it won't have to create a new page. Something else to keep in mind is the size of each row. This can create issues if you have a number of varchar columns or even a single, large varchar column. Most of the time, the default fillfactor works fine.
Enjoy!
A Brown
Manage all of your backups in one simple job using this script. -
Has anyone use this commnd?
osql -E -S "<serverName> ,1433
-
Is this even related to this question??
I think you may be better off starting a new thread if it's not. that'll give you a much better chance of getting an answer to your problem!
-
Firstly, if you are using SQL 2005 then you should use "Activity Monitor" (under Management) for keeping track of the locks, waits/wait types, blockings, CPU, memory etc.
For checking any long running transaction: DBCC OPENTRAN
For checking the index/pages fragmentation:
select t.[name],ips.avg_fragmentation_in_percent,ips.page_count,ips.index_type_desc,ips.alloc_unit_type_desc,ips.index_depth
from sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,'LIMITED') ips
join
sys.tables t
on t.[object_id]=ips.[object_id]
where ips.database_id=6 and left(t.[name],3)='STG' and ips.avg_fragmentation_in_percent>10
order by ips.avg_fragmentation_in_percent desc
The indexes can be rebuilt online only in Enterprise edition.
Good luck!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply