Tag Archives: sql 2005

When was SQL Server restarted?

Over the weekend, I interacted with some of the “wanna be” DBAs. During the interaction I checked with them how they would find when was the SQL Server instance restarted? Everyone had a different approach to find out the information. Here are some of the ways to find out the date & time on which SQL Server service was last restarted.

SQL Server Error Log

By making use of sp_readerrorlog, first time stamp in the SQL Server Error Log can be treated as the time when SQL Server service was restarted.

sp_readerrorlog 0,1,'Copyright (c)'

This approach is not useful if the SQL Server Error Log is cycled manually.

Using sys.dm_os_sys_info DMV

Starting SQL Server 2008, sys.dm_os_sys_info DMV has a column named sqlserver_start_time. This column as the name suggests stores the time when SQL Server was restarted.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

But this query does not work in versions which are lower than SQL Server 2008.

Using sys.dm_exec_sessions DMV

The following query will get the Login time of the Session Id 1.

SELECT login_time FROM sys.dm_exec_sessions
WHERE session_id = 1;

session_id is created when the SQL Server is started and the login time remains the same until the service is restarted again.

Start time of the Default Trace

The Default Trace is started when the SQL Server is started. The start_time of this trace can also be taken as the time when the SQL Server service was restarted.

select start_time from sys.traces
where is_default = 1

This approach is useful except for the instances where the Default Trace is explicitly disabled.

Creating date of tempdb

The creation date of tempdb database will also be the time when SQL Server service was restarted. This is because the tempdb database is re-created whenever the SQL Server service starts up.

SELECT create_date FROM sys.databases WHERE name = 'tempdb'

The above query works on SQL Server 2005 and above. For SQL Server 2000 the same query will vary a little bit.

SELECT crdate FROM sysdatabases WHERE name='tempdb'

This is the query which I always use to find out when the SQL Server service was restarted.

SQL Server Service won’t start because I forgot to move the Resoruce database

On Sunday I got a call from a DBA from another team who had an issue that the SQL Server service was not starting up. It was a SQL Server 2005 instance. The SQL Server Error Log had the following error messages.

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:mssqlsystemresource.mdf'. Diagnose and correct the operating system error, and retry the operation.
File activation failure. The physical file name "E:mssqlsystemresource.mdf" may be incorrect.
Error: 945, Severity: 14, State: 2.
Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

The SQL Server service was unable to find the files of mssqlsystemresource (a.k.a. resource) database. The SQL Server service was not starting up after the server was rebooted. Here is what happened a couple of days ago. The E drive hosting the the system databases had to be re-provisioned and the DBA had to move all the SQL Server databases to a new drive. The DBA moved all the system databases but forgot to move the mssqlsystemresource database. This came into light when the SQL Server service got restarted. But by that time the E drive was already out of the server.

I could not continue to support this case due to some other reason. Here is how I had planned to resolve this issue.

  • Copy the files of the mssqlresource database from another SQL Server 2005 instance which is of the same build to the new drive on this server.
  • Start SQL Server from the command prompt using sqlservr.exe. The -f (minimal configurtion) or -m (single-user mode) parameters have to be passed on to sqlservr.exe along with the Trace Flag 3608 (recovers only the master database).
  • Issue ALTER DATABASE command to move the files of mssqlresource database to point to the new location.

The actual command would be
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsqlservr -f -T3608

This command would start the SQL Server in minimal configuration mode and recover only the master database. Then executing the below command in sqlcmd would move the resource database to the new location.

ALTER DATABASE mssqlsystemresource
    MODIFY FILE (NAME=data, FILENAME= 'X:mssqlsystemresource.mdf')
ALTER DATABASE mssqlsystemresource
    MODIFY FILE (NAME=LOG, FILENAME= 'X:mssqlsystemresource.ldf')

The output of this command would be similar to the one below.

After making these changes, the SQL Server service would start gracefully.

Script to check database user permissions

On Sunday I was responding to a question in one of the forums where the jobs were failing with permission denied error. During the troubleshooting I wanted to check if the permission for those stored procedures were explicitly denied for the SQL Server Agent/Job owner account.

On SQL Server 2005 and above, I usually use the below script to check the permissions granted/denied for database users.

USER_NAME(dppriper.grantee_principal_id) AS [UserName],
dppri.type_desc AS principal_type_desc,
OBJECT_NAME(dppriper.major_id) AS object_name,
dppriper.state_desc AS permission_state_desc
FROM    sys.database_permissions dppriper
INNER JOIN sys.database_principals dppri
ON dppriper.grantee_principal_id = dppri.principal_id

The output of this script would be like the one in this example.

But on that instance, the permission was not denied for any of the stored procedures. Still trying to figure out the possibilities.

Hide SQL Server Instance on all versions

It is holiday season everywhere. It means that Freeze on Change Controls. Nothing changes so nothing breaks. Also the instances my team supports have been very supportive this holiday season. Hence most of my recent posts have been inspired by the questions that I respond in the forums, not from the issues that I face. This one is also a detailed version of the answer that I had posted.

While trying to connect to an instance using SQL Server Management Studio (SSMS), it *looks* for all SQL Server instances available on the network. Similarly sqlcmd -L command lists the SQL Server instances available on the network.

Due to some reasons you may want to hide these instances so that they do not get listed in SSMS or sqlcmd. Here is how to do it on various versions of SQL Server.

SQL Server 2000

  • Open Server Network Utility
  • Select the properties of TCP/IP protocol
  • Select the Hide Server option.

For this change to take effect, the SQL Server service needs to be restarted. This hides the instance alright but there is a drawback. The Default port gets changed to 2433 and this cannot be changed! Certainly not an good thing to try out on Production Instances.

SQL Server 2005 & above

Starting SQL Server 2005, the feature to “hide” an instance is available in SQL Server Configuration Manager.

Unlike SQL Server 2000, there is no drawback with this. However the service needs to be restarted after making this change. The instance can also be hidden through Registry.

The location of the key (MSSQL.1 in this example) changes according to the Instance Name.

It should be noted that *hiding* the instance will only prevent it from getting listed in the SSMS or in sqlcmd/osql. It does not prevent the users from connecting to it by directly entering the instance name.

The error is printed in terse mode because there was error during formatting

On Wednesday, the monitoring tool sent an alert on an instance which was running low on memory. The alert read

Error: 14667, Severity: 16, State: 1. (Params:).
The error is printed in terse mode because there was error during formatting.
Tracing, ETW, notifications etc are skipped.

This instance is running SQL Server 2005 and I realized that we just bumped on a bug. As per this BUG listed for SQL Server 2005, this error message is generated because of an incorrect line the code of sp_readrequest stored procedure in MSDB database.

SET @localmessage = FORMATMESSAGE(14667, convert(NVARCHAR(50), @mailitem_id))

should be changed to

SET @localmessage = FORMATMESSAGE(14667,  @mailitem_id)

This error message does not mean anything critical, I started receiving few more extra alerts in my already flooded mailbox. Hence a Change Control has already been processed to modify this stored procedure as suggested.

I quickly looked up in SQL Server 2008 to see if this BUG has been fixed or not. Not yet. Even SQL Server Denlai still has the same line in sp_readrequest stored procedure. I am trying to inform about this BUG on MS Connect but due to some reasons I am unable to create a new feedback. Not a great BUG to report but fixing this would surely reduce some unwanted emails in the DBA’s inbox.


Aaron Bertrand (blog | twitter) has just posted a BUG on MS Connect regarding this. Please vote up for it here. Thanks Aaron for your help!

SQL Server 2005 installation fails with MSXML 6 SP2 error

I have responded to numerous questions in forums where the SQL Server 2005 Installation would fail because MSXML 6 Service Pack 2 was already installed on the computer. This error would only occur on computers running Windows XP. Yesterday one of my team members faced the same issue on his laptop, so I thought of putting together the information here. The error message in the SQL Server Setup log would be something like this.

This package is not supported on this operating system.
SkipInstallCA. Return value 3.
Fatal error during installation
MainEngineThread is returning 1603
Error code 1603
Product: MSXML 6.0 Parser (KB933579)
Installation failed.
Created Custom Action Server with PID 2868 (0xB34).
Running as a service.
Hello, I'm your 32bit Impersonated custom action server.
This package is not supported on this operating system.
SkipInstallCA. Return value 3.

Till recently, the following were the only workarounds to fix this issue.

  • Install Windows Installer Cleanup Utility (MSICUU2.exe) from this KB article and remove MSXML 6 SP2. But this utility caused some irreversible issues to other applications in some occassions. Subsequently this utility was removed from this page.
  • Uninstall MSXML 6 SP2, reboot the computer. Then install MSXML parser from here. This workaround used to work sometimes.
  • If both of these does not work, the only option left was to reach out to Microsoft PSS and ask for a script that they would provide to work around this issue (which of course would come with a disclaimer).

Finally on November 8, 2010 Microsoft had published this KB article in which they have provided MSXMLFix.exe which easily fixes this issue quickly. Even though this fix is a bit late, better late than never.

Get graphical Execution Plans for all the active sessions

Last week someone wanted to know How to get the execution plans of all the sessions which are active on the instance? Starting SQL Server 2005, there are plenty of DMVs which makes the DBA’s life less complicated. The DMV that answers this question is sys.dm_exec_query_plan. With this DMV, the Execution Plans of currently running sessions or cached sessions can be easily obtained. All this DMV needs is a plan_handle as parameter. The below query gets the session_id and the query_plan (in XML format).

er.session_id ,
FROM sys.dm_exec_requests er
CROSS APPLY sys. dm_exec_query_plan ( er.plan_handle ) qp

The output looks like this.

If SSMS version is 2005, then clicking on the query_plan will open the Execution Plan in XML format.

This file in turn needs to be saved as a .sqlplan file to view the Execution Plan in Graphical format. The “Save as” option in SSMS or any text editor can be used for this purpose.

The SSMS in SQL Server 2008 goes one step ahead. Clicking on the query_plan in the query output directly opens the Execution Plan in Graphical format.