Have you ever encountered a situation where multiple SQL Server instances have gone down and only a few of them came back online? It can be a challenging task to find the causes behind the failure of the SQL Server service to start. One of the best places to start troubleshooting is the SQL Server Error log. However, when the SQL Server Service is not running, the normal methods of displaying and analyzing the Error Logs are not available.

In such cases, a manual method can be used to locate the Error Log directory, open all the log files in a text editor, and go through each line. This can be a time-consuming task, especially when dealing with multiple servers. To simplify this process, I have developed a simple batch file and a collection of search pattern files that can help in quickly identifying error entries for each severity level.

Compatibility is a crucial factor when dealing with SQL Server instances, as they can vary in terms of version and architecture. To ensure compatibility, I have used DOS/CMD commands in the batch file, as they do not require any additional tools or libraries to be installed on the server. The commands that are particularly helpful for this purpose are DIR, FINDSTR, and the control flow, IF.

FINDSTR is especially useful, as it allows us to compile search patterns in a file and pass the file name as a parameter. However, it has one drawback – if the file being searched is a Unicode file, no match will be found unless every character in the search pattern file is appended with a period, “.”. Depending on the SQL Server version, the Error Log files can either be Unicode or ASCII text files. To accommodate all scenarios, two pattern files are used for each error level – one in ASCII format and one in Unicode format.

Here’s how the batch file works: when invoked with the drive letter of the drive to search, it recursively iterates through all directories and sub-directories of the drive until it locates the ERRORLOG directory. It then performs a pattern matching on all the log files in that directory. It’s a simple yet effective solution.

To use this solution, you need administrative access to the system being diagnosed. The first step is to upload/copy the zip archive and expand it on the target system. If the system doesn’t have a command line unzip program installed, you can use a USB key with the directory uncompressed or a network share.

Next, open a command prompt, navigate to the directory containing the TSSQLEX.bat file, and run it with the desired parameters/switches. To get a list of applicable switches, run the batch file without any parameters or switches. One useful switch is the /O switch, which writes the output to a file in the batch file directory. This eliminates the need to scrape the command prompt output to collect the results.

Here’s the syntax for using the batch file:

TSSQLELX.bat [Drive letter] [/S] [/P] [/V] [/H] [/O]

Parameters:

• The first parameter must be a single character drive letter of the drive to search, or the /H switch to print the syntax.

Switches:

• /S: Skip the functionality tests. This is useful when you want to skip the search functionality tests.
• /P: Print the individual commands before executing. This is helpful for repeating searches for individual error levels by copying the command and re-running it from the command prompt.
• /V: Verbose, print additional information for each error level. This prints the error level description text from BOL (Books Online).
• /H: Prints the full help message. The message can be found in the README.txt file in the batch file’s directory.
• /O: Writes the output to a file called COMPUTERNAME.USERDNSDOMAIN_TSSQLEX.txt. The file is created or overwritten in the batch file’s directory.

By following these steps and using the provided batch file, you can easily diagnose “dead” SQL Server instances and identify the causes behind their failure to start. This solution is designed to be compatible with various SQL Server versions and architectures, making it a versatile tool for any SQL Server administrator.