August 2, 2011 at 3:02 pm
Ninja's_RGR'us (8/2/2011)
Do you have dbs on auto-close. I just saw gail saying this is what happens in that particular case.How did the server go down? Anything else in the logs (windows logs as well)
No, none of the db's have auto-close set to true.
Other error messages are as follows:
1) Logon failed for login 'usBlahBlah' due to trigger execution. [CLIENT: 192.168.100.141]
2) The client was unable to reuse a session with SPID 383, which had been reset for connection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
3) Error: Maintenance plan 'Backup - Litespeed [TL Logs]'.LiteSpeed(R) for SQL Server Version 6.5.0.1460
Copyright 2011 Quest Software, Inc.
Processed 4 pages for database 'Jeff', file 'jeff_Log' on file 1.
BACKUP LOG successfully processed 4 pages in 1.245 seconds (0.022 MB/sec).
Msg 60901, Level 16, State 1, Line 2883584: Failed to login to SQL Server
4) The server will drop the connection, because the client driver has sent multiple requests while the session is in single-user mode. This error occurs when a client sends a request to reset the connection while there are batches still running in the session, or when the client sends a request while the session is resetting a connection. Please contact the client driver vendor.
The trigger above is a trigger to record SA logins. It has been running for 6 months without issue prior to this.
Fraggle
August 2, 2011 at 3:04 pm
george sibbald (8/2/2011)
Ninja's_RGR'us (8/2/2011)
I don't remember.[/ too easy to ignore]
😀
Fraggle, I read a good article on setting max memory taking other factors into account recently, but damned if I can find it. there are dmvs which track perfmom counters and also take a look at sys.dm_os_memory_clerks.
the perfmon counter that comes closest to the total amount of memory being used by a process is PROCESS: Private bytes.
Is the available memory counter low?
I probably read the same article, which is why the Max Memory Setting is at 58GB, leaving 3GB for non buffer pool and 1 for the OS.
Fraggle.
August 2, 2011 at 3:11 pm
The only thing that comes to mind is Hyperthreading being enabled. I have seen cases where the server hangs due to memory pressure and starts throwing the AppDomains being unloaded due to memory pressure when hyperthreading is enabled.
-Roy
August 2, 2011 at 3:12 pm
Roy Ernest (8/2/2011)
The only thing that comes to mind is Hyperthreading being enabled. I have seen cases where the server hangs due to memory pressure and starts throwing the AppDomains being unloaded due to memory pressure when hyperthreading is enabled.
Not sure on Hyperthreading. Where can I go to find that on the DB? Or is that strickly a .NET thing?
Fraggle
August 2, 2011 at 3:14 pm
The article had details on memory usage by memory outside the buffer pool. Might have been an MS engineer blog.
1Gb for the OS does not sound enough for a 64Gb server. I would not go below 2Gb.
---------------------------------------------------------------------
August 2, 2011 at 3:15 pm
Fraggle-805517 (8/2/2011)
Roy Ernest (8/2/2011)
The only thing that comes to mind is Hyperthreading being enabled. I have seen cases where the server hangs due to memory pressure and starts throwing the AppDomains being unloaded due to memory pressure when hyperthreading is enabled.Not sure on Hyperthreading. Where can I go to find that on the DB? Or is that strickly a .NET thing?
Fraggle
BIOS, maybe computer properties.
August 2, 2011 at 3:15 pm
As per MSDN
"To determine if hyperthreading is enabled for the processor, compare NumberOfLogicalProcessors and NumberOfCores. If hyperthreading is enabled in the BIOS for the processor, then NumberOfCores is less than NumberOfLogicalProcessors. For example, a dual-processor system that contains two processors enabled for hyperthreading can run four threads or programs or simultaneously. In this case, NumberOfCores is 2 and NumberOfLogicalProcessors is 4. "
-Roy
August 2, 2011 at 3:20 pm
Where do we go fetch that info? Any dmvs?
August 2, 2011 at 3:22 pm
Roy Ernest (8/2/2011)
As per MSDN"To determine if hyperthreading is enabled for the processor, compare NumberOfLogicalProcessors and NumberOfCores. If hyperthreading is enabled in the BIOS for the processor, then NumberOfCores is less than NumberOfLogicalProcessors. For example, a dual-processor system that contains two processors enabled for hyperthreading can run four threads or programs or simultaneously. In this case, NumberOfCores is 2 and NumberOfLogicalProcessors is 4. "
I am not sure that is completely reliable depending on hardware used. Best just to ask the server admin.
---------------------------------------------------------------------
August 2, 2011 at 3:32 pm
george sibbald (8/2/2011)
The article had details on memory usage by memory outside the buffer pool. Might have been an MS engineer blog.1Gb for the OS does not sound enough for a 64Gb server. I would not go below 2Gb.
Nope, not the article I read. Mine was more of a MemtoLeave type of article.
Fraggle
August 2, 2011 at 3:35 pm
george sibbald (8/2/2011)
Roy Ernest (8/2/2011)
As per MSDN"To determine if hyperthreading is enabled for the processor, compare NumberOfLogicalProcessors and NumberOfCores. If hyperthreading is enabled in the BIOS for the processor, then NumberOfCores is less than NumberOfLogicalProcessors. For example, a dual-processor system that contains two processors enabled for hyperthreading can run four threads or programs or simultaneously. In this case, NumberOfCores is 2 and NumberOfLogicalProcessors is 4. "
I am not sure that is completely reliable depending on hardware used. Best just to ask the server admin.
'
Server Admin say "NAY"!
Fraggle
August 2, 2011 at 4:22 pm
did you see the url link i posted earlier?
---------------------------------------------------------------------
August 2, 2011 at 5:32 pm
Yes, I was just sitting down to read it now actually.
Fraggle
August 3, 2011 at 10:28 am
So as a followup question to this thread, is there a way to tell inside of sql server 2005, to tell where every single byte of memory is being used. In looking at sys.dm_os_memory_clerks, it shows cache and a number of other objects, but the summation of that doesn't equal the amount of memory that is being shown in Task Manager. I assume that this is because it is missing the actualy Data that is cached, but this is only a guess.
Basically, if task manager shows me using 60,000,000 kb, then what inside of SQL Server do I need to do to get that same 60,000,000 KB?
Thoughts?
Thanks,
Fraggle
August 3, 2011 at 4:52 pm
Can you run @@version for this instance?
Since you are running the Standard Edition - you will not have the option to lock pages in memory unless you are on the right service pack and cumulative update.
Also, if you do not set lock pages in memory - you need to allocate a lot less memory to SQL Server than is available on the server. When I configured a system with 32GB of memory - I had to restrict SQL Server to less than 25GB of memory to get rid of issues.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply