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.
| Author |
Topic |
|
psychotic_savage
Starting Member
25 Posts |
 Posted - 2010-10-26 : 09:20:35
|
| Good day all,I'm still new at the Database Administration game so please bare with me.I have four DB's Running on SQL Server 2008 R2 which is running on a Windows Web Server 2008 R2 (64-bit) rig.Two of the DB's are mirrored to another server in High safety with automatic failover mode. The other two are non critical archiving databases.During the expiring to the archives there was what I'm going to call a memory dump within SQL. I have attached the SQL logs here:[url]http://www.mediafire.com/file/eh5ic7eiurdn564/ERRORLOG.txt[/url]I have also setup data-collection on these DB's so I can tell that during this period there was a huge wait on the ThreadPool wait (580 waits, 13751ms\sec duration, 54% of total wait time) and also on the CPU Signal Wait (4250ms\sec Duration, 16% of total wait time).But the queries Stats history shows that all queries running at that time had minimal CPU, Dutation and I\O stats compared to the norm. Also when all this "goes down" CPU, Disk I\O and Network usage sky rocketed but SQL's used memory barely moved.All this obviously caused a failover. With the DB's now on the Mirror-Server showing as Principle, Disconnected. And the DB's on the original principle marked as not mirrored. I could only get the mirroring session re-established by running the Database Mirroring Monitor.After the DB's synchronized I failed them back to their relative server. (now that I think about it I shouldn't have until i knew the server was stable) I don't see the data-collector as causing the issue as it runs during times when there is as much as 100 times the load.All seems fine now.Now for my barrage of questions.1. From the attached ERRORLOG.txt file what does the dump mean. And what do I look for in the future in these Stack trace Dumps?2. Would a queries duration in the Data-collector be influenced by the Threadpool wait. Or is the queries duration only calculated after it has been assigned a thread?3. Why would the original principle servers DB's look like they are no longer mirrored? 4. Surely this would cause inconsistencies between the Mirror(which is now live) and the Principle(which should now be marked as the mirror)?If any more information is needed please let me know as I said I'm still new at this.Thanks. |
|
|
psychotic_savage
Starting Member
25 Posts |
Posted - 2010-11-04 : 08:17:45
|
| In case someone else has had this issue I'm posting my findings here.After a whole lot of googling I found out that the LazyWriter: warning, no free buffers found. Error is because there are no free pages available for the lazy writer to use to add to the free buffer pool. I ran: dbcc memorystatus to view the current available free buffer count.Under the buffer pool section I was returned a Free Buffer Pool count of under 200.After the server failed back the count was at over 900 000. Now it is fluctuating between 100 000 and 300.I have also set a max limit on the memory for SQL. Leaving 1gig for the OS.I will update if the change in memory allocation helped or not. |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2010-11-12 : 16:38:59
|
| open the .log for the memory dump..do you see above 90% memory usage?=memory pressurewhat is the message written for the cause of the dump?what does dbcc memorystatus give you?if it's sql server standard x64, did you provide lock pages in memory for the sql server account?download windbg and search for public mssql symbols that you can feed to read the .mdmp filei would run sqldiag or a profiler trace with perfmon to be thoroughi didn't open the error log, btwhth--------------------keeping it simple... |
|
|
|
|
|
|
ThreadPool wait causing failover\Stack Trace dump