I am using Evaluation edition for testing and writing blog. Evaluation Edition works for 180 days from the date of installation. Thus, it’s very import for me to know
- When was my SQL Server Instance Installed
- when it is going to stop working
Script to get this Information
SELECT create_date as 'SQL Server Install Date', DATEADD(dd,180,create_date) as 'Will Stop Working ON' FROM sys.server_principals WHERE name='NT AUTHORITY\SYSTEM'
Logic, "NT AUTHORITY\SYSTEM", is SQL Server principal, which gets created during the installation. So we are just checking a creation date of that security principal.
How to check when were SQL Server Patches / Services Pack installed ?
Whenever we install or apply a patch / Service pack on SQL Server, SQL Server maintains a log file for every such events.
All these logs are located on "%ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\", in my case this location is "C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log".
We can see a list of folders for an every instance of installation / upgrade.
Every folder hosts multiple files, which can used for checking when was a patch / setup got run.
These logs files are also very useful to troubleshoot setup / installation issues.
A brief Intro to these files.
|Summary.txt||Purpose: This log is the clean and user-friendly log file that contains the basic information about the problem. This file shows which SQL server components were detected, the operating system environment, the command-line parameters that were specified, the values that were specified, and the overall status of each MSI and MSP file that was executed. Troubleshooting: To find errors in this file, you can generally search for “error” or “failed” keywords. For more information about details of the failure, open the file that is listed in the line starting with “Log with failure”.|
|Purpose: This log file contains the same information as the Summary.txt file contains. Additionally, this log may also contain previously specific to this SQL Server Setup attempt.
Troubleshooting: To find errors in this file, you can generally search for “error” or “failed” keywords. For more information about details of the failure, open the file that is listed in the line starting with “Log with failure”.
|Detail.txt||Purpose: This log file provides a detailed log of the execution. It is organized on lines that begin with the time stamp followed by the extension that produced the log. This is one of the most important log files because it can be used to identify the failures that occur. The logs are generated on a time basis. This means that the actions are logged here not by the component that generated them but by the time at which they are invoked. This is useful to determine the execution process step by step, the order in which actions are executed, and the dependencies between actions. This file is generated for the main workflow such as the install, or the upgrade workflow. Troubleshooting: If an error occurs in the setup process the exception or error will be logged at the end of the file. To locate errors in this file, you must locate the very end of the file and read it in the reverse of the actions from bottom to top to find where the exception or error is logged. After you have located the line starting with “Exit facility code” that is shown in your Summary.txt file, you can search for “error,” “Watson bucket,” or “exception” keywords in the Detail.txt file. The search results near the exit facility code will help you to identify when and where the error first occurred.|
|Detail_ComponentUpdate.txt||Purpose: This log file resembles the Detail.txt file. This file is generated for the component update workflow.
|Detail_GlobalRules.txt||Purpose: This log file resembles the Detail.txt file. This file is generated for the global rules execution. Troubleshooting: This log file is used only if the Summary.txt file shows failures in the SystemConfigurationCheck section. Generally, you can open and use the SystemConfigurationCheck_Report.htm file to obtain all the information that is needed. However, if you want additional information from this log, search for the “Rule evaluation done : Failed” phrase in this file.|
|SystemConfigurationCheck_Report.htm||Purpose: This file contains a friendly version of the rules execution status. It also provides a short description of each executed rule. Troubleshooting: You can open the SystemConfigurationCheck_Report.htm file and look for the “Failed” keyword and examine if any “Warning” entries apply to your environment.|