Darren weblog

Recently had a MSSQL 2005 server stop and required a windows server reboot The follow is the technical detail and explanation/resources I found/followed – hope it is of use to somebody!

 Current & relevant configuration:

• Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
• Microsoft SQL Server 2005 – 9.00.4207.00 (X64) = SP3 CU1; latest MS build is 9.00.4305 SP3 CU10
• 31.9GB physical RAM, 4092MB page file on c:; windows recommends 49,143MB; my normal recommendation is 2.5x physical = 81,920MB
• SQL Server configured for max memory 27,000MB ~= 26.36GB; not to use AWE; “Lock Pages in Memory” is disabled as x64/default

 Train of events:

• last logged normal operation which was a transaction log backup as per schedule
• Log message “A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): <size>, committed (KB): <size>, memory utilization: 0%.”
• server rebooted
• server starting services
• SQL recovery complete – server ready to accept and process connections; service resumed
• SQL reports database mirroring is disconnected
  • Error: 9642, Severity: 16, State: 3.
  • An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: ”)
• MSSQL restart resolves mirroring disconnect – details attached
• Full DR resumed

 Investigations into root cause of stop:

• Given the current configuration of page file it is understandable the server ran out of memory.
• Research (ref1, ref2 & ref3) into the memory page out error suggests the server basically ran out of memory
  • ref1 correlates that other people have had stops due to the same cause; refers ref2
  • ref2 gives background regarding memory working sets for SQL and concurs that a SQL server is simply not just the memory for sqlservr.exe
  • ref3 (KB918483) explains how to prevent the OS operations that lead to the stop
    • This article then refers a blog post (ref4) form the CSS engineers who have given background on memory trim’s; there are three (3) types of trim:
      • Signalled trim – does not generate our error message
      • Self trim – the OS requires additional RAM for pages not in memory, the “amount to trim can be ~1/4 the current working set size” – therefore we don’t have a page file large enough for this trim
      • Hard trim – system memory is very low and the “trim can be the entire working set” – again, we do not have a large enough page file
      • Therefore it is essential we increase the page file size.
    • ref4 gives several signals as to the cause of the memory trim. Those of immediate interest are as follows:
      • system file cache size (ref5)
      • “TCP Chimney” with Broadcom network adapters
    • ref5 discusses the performance counters that indicate whether there is an underlying OS issue with system file cache size

 Next Steps:

• Increase page file size; 2.5x is my own blanket rule of thumb, but given the drive capacities we may had to increase to a lower value initially and monitor:
  • A trusted source of information (ref6 and ref7) has the following guidelines for provisioning the page file size:
    • Based on the “Page File Bytes Peak” value (3.10E+10 bytes) monitored on and the 70% rule indicates a page file requirement of 20.1GB.
• Check Broadcom Network Adapter drivers are latest
• PerfMon and monitor the system file cache size issue

OK, so this is a late start, but better to start late than never. And within 90 seconds of writing my first powershell script (the 90 seconds included the google and modifications) I had the collated results from 20 servers – nice!

So, where did I start: credit is due to this post for the 90 seconds turn around: Why This SQL Server DBA is Learning Powershell. So, rather than “Hello World”, my first script this time is as follows:

# Execute, on servers in $args[0], script in $args[1]

$dt = new-object System.Data.DataTable

foreach ($svr in get-content $args[0])
{
  $con = "server=$svr;database=master;Integrated Security=sspi"

  $cmd = get-content $args[1]

  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
  $da.fill($dt) | out-null
  $svr
}

# $dt | Format-Table -autosize

$output_csv = $args[1] + ".csv"
$dt | export-csv -path $output_csv –noType

I’ll post further later, just wanted to get something out…which then means I will be motivated to blog/post further!

To get this category/section started, and what generated the restart of technical blogging, here is a summary of recent findings which, I know, I will need again one day!

• how to query a mail server (MTA) from telnet
• sample batch scripts
• serialize the XML dll for CLR sprocs

and a app store for winmo that only serves free apps!

so some time ago I thought I should write a technical blog, I started, then stopped – as do 99% of bloggers/twitters I reckon. Anyway, my latest thinking is that was because it was seperate to this (beloved) blog, so I am going to write here, in a seperate category, and try to exclude the category from the home page, bare with me…

From reading on how best to do this, I am starting with method 2.