Tag Archives: sql 2000

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.

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.

100 percent restored, what else is going on?

Starting this Monday some of my team members are working on a Disaster Recovery activity. This involves restoring some of the biggest Data Warehouse databases onto the DR server.

Today they were supposed to restore a database which is ~3 TB in size from disk backups. This instance is running SQL Server 2000. As usual they started the restore script using a SQL Agent job. The job output file showed the progress steadily and finally after 12 hours the output showed 100 percent completed. The output file looked something like this.

100 percent restored. [SQLSTATE 01000]
Processed 53369168 pages for database 'dbname', file 'dbname_Data' on file 1. [SQLSTATE 01000]
Processed 2945917 pages for database 'dbname', file 'dbname_Log' on file 1. [SQLSTATE 01000]

But the database restore is not complete until it prints RESTORE DATABASE successfully processed xxx pages in xxx seconds in the logs. When the DBA announced that the log had 100 percent completed in it, the Application Team were keen to do their part of testing and be done with the DR activity.

What's going on?

DBA told everyone on the bridge that it will take some “more” time for the database to be restored successfully. The obvious question was “How long?”. Since this instance is running SQL Server 2000, there is no way to estimate the time it takes to restore the database. If the instance was running SQL Server 2005 or above, it can be easily be tracked as I posted here. Three hours past, the database was still restoring and the nagging from the Application Team started getting more serious. The DBA on call was at the mercy of sysprocesses to check the status. All he could see was a restore session in which the waitresource column was changing. This confirmed that the Restore process was not hung but it was actually doing something.

What exactly the RESTORE session is doing? The RESTORE process has three phases.

  1. Data Copy Phase
  2. Redo phase
  3. Undo phase

In the Data Copy phase, all the data from the backup set is moved to the database files. Once this phase is complete, SQL Server reports that the restore is 100 percent complete and Processed xxx pages for database ‘dbname’, file ‘datafiles’ on file x.

In the Redo phase, all the committed transactions present in the transaction log when the database was being backed up are rolled forward.

In the Undo phase, all the uncommitted transactions in the transaction log while the database was being backed up are rolled back. If the database is being restored with NORECOVERY, the Undo phase is skipped. Unfortunately SQL Server does not print the progress during the Redo and Undo phases as it does in the Data Copy phase. This article on MSDN has good information regarding the Restore process.

With this information it is easy to understand why the restore was not showing any progress. Restore had progressed beyond the Data Copy phase and it was in the Redo/Undo phase. The database being restored was in simple recovery mode and the transaction log of the database inside the backup file was showing a size of 30 GB. The database backup had taken 12 hours to complete. Understandably the Redo/Undo phase had to roll forward/roll back the transactions that got logged in the transaction log during those 12 hours.

The DBA knew this and tried his best to convince the guys on the call. But there was no evidence to prove DBA’s argument. Finally the database restored completely after ~16 hours (12 hours of data copy + 4 hours of redo/undo). Everyone lived happily ever after 😉

SQL Server Agent does not start | StartServiceCtrlDispatcher failed (error 6)

Since yesterday morning SQL Server Agent on one of the SQL Server 2000 instances is not starting. This problem started ever since the Server had an unexpected reboot. The problem is SQL Server service starts but not the Agent. The errors in the Event Log are

The SQLSERVERAGENT service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.
Application popup: sqlagent.exe - Application Error :
The application failed to initialize properly (0xc0000142).
Click on OK to terminate the application.

These errors are very generic. The next step that I did was to take out the executable from the SQLSERVERAGENT service and run it manually from the command prompt.

When I ran this from the command prompt, the error message read StartServiceCtrlDispatcher failed (error 6).

I could not make sense even with this error. Then I started the agent in verbose mode to check if it gives more information as to why it is not starting.

sqlagent.exe -c -v

-C indicates that sqlagent.exe is running from command prompt and not as a service.

-v Enables the verbose logging mode on

To my surprise, this time there were no errors at all and the sqlagent.exe continued to run in the command prompt. The Agent Error Log (sqlagent.out) file did not have any errors at all.

Waiting for SQL Server to recover databases...
Microsoft SQLServerAgent version 8.00.2282 (x86 unicode retail build) : Process ID
Microsoft SQLServerAgent version 8.00.2282 (x86 unicode retail build) : Process ID 3072
SQL Server [servername] version 8.00.2282 (0 connection limit)
SQL Server ODBC driver version 3.85.1128
NetLib being used by driver is DBMSSHRN.DLL; Local host server is
8 processor(s) and 2048 MB RAM detected
Local computer is [servername] running Windows NT 5.0 (2195) Service Pack 4
The Messenger service has not been started - NetSend notifications will not be sent
SQLSERVERAGENT starting under Windows NT service control
Using MAPI32.DLL from C:WINNTSYSTEM32 (version 1.0.2536.0)
Attempting to start mail session using profile 'Notification'...
Mail session started (using MAPI1)
An idle CPU condition has not been defined - OnIdle job schedules will have no effect

Since I was able to start sqlagent.exe, I ran sqlagent.exe under the credentials of the Service Account. Here as well sqlagent.exe executed successfully. With this it was clear that there was nothing wrong with the setup of SQL Agent, it had to do something with the service. Quickly checked if the Service Account had privileges on the Service, SQLServerAgent key in the registry.

Nothing wrong here as well. It had FULL permissions on all the registry keys and the Service Account is part of the Local Administrators group on the box and on the instance it has sysadmin privileges. I also checked in the Local Security Settings (secpol.msc) if this account was denied access somewhere. It was not the case. Strangely if I changed the Service Account to Local System and grant it sufficient privileges, the service starts normally. This clearly indicates that somewhere in the system there is a deny on some object for this account. Process Monitor tool would surely have helped in finding out this. But this server is running on Windows 2000 Server, I cannot run the Process Monitor.

No smiley, eureka this time 🙁 With this I ran out of all options. As a final resort, a case has been raised with MS PSS. They surely have the old FileMon, Regmon tool which would work on Windows 2000 servers. Once they fix the issue, I will post here how it was fixed. Since there are no jobs running from SQL Server Agent there is no business impact and we have time to resolve this issue.

Am I missing something here? Do you have any suggestions to fix this?

Update on 3rd December 2010:

The issue started when the Server had an unexpected reboot. Today again the server had a unexpected reboot and guess what, SQL Server Agent started successfully 😀

The root cause analysis is still on.

SQL Server exists and access is not denied, Cluster group does not come online

Yesterday evening an engineer from some other team pinged me to inform that they had a high severity incident and sought my assistance to resolve it.  He told me that the Application was not able to connect to the Database Server. However the instance was up and all the databases were accessible within the server hosting SQL Server. With this information I had a remote support session with him, without any knowledge about the environment.

Two SQL Server 2000 instances were running on a Windows 2000 Server Fail-over Cluster. The Application was getting SQL Server Does not exist or Access is denied error message when connecting to this instance. I logged onto the server and checked Cluster Administrator to see that the SQL Server Resource was in failed state! Now I realized that the engineer was under pressure and started gathering the information myself.

Even I failed to bring the SQL Server Resource online. All the dependencies of  SQL Server were online. The System Event Log had only the generic Cluster Resource failure messages. The Cluster.log file (c:\winnt\cluster\cluster.log) had these messages.

000008fc.00001460::2010/11/29-14:50:42.359 SQL Server <SQL Server (GWS1_INSTANCE)>:
[sqsrvres] ODBC sqldriverconnect failed
000008fc.00001460::2010/11/29-14:50:42.359 SQL Server <SQL Server (GWS1_INSTANCE)>:
[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 11;
message = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
000008fc.00001460::2010/11/29-14:50:42.359 SQL Server <SQL Server (GWS1_INSTANCE)>:
[sqsrvres] ODBC sqldriverconnect failed
000008fc.00001460::2010/11/29-14:50:42.359 SQL Server <SQL Server (GWS1_INSTANCE)>:
[sqsrvres] checkODBCConnectError: sqlstate = 01000; native error = 3;
message = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
000008fc.00001460::2010/11/29-14:51:03.281 SQL Server <SQL Server (GWS1_INSTANCE)>:
[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 11;
message = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

Even the Cluster Manager was unable to connect to the SQL Server instance. Since SQL Services were down, I got the Error Log path from the registry.

The SQL Server Error Log did not have any error messages. It looked as if the SQL Server service started and stopped normally. SQL Server was configured to connect using TCP/IP and Named Pipes. To verify it myself I started the SQL Server service from the Services applet (services.msc). As expected the SQL Server service started normally. But I was not able to the instance (ServerName\InstanceName) using Query Analyzer. Same error message. Since I knew the SQL Server Error Log location, I got the port number from it. Then tried connecting to the instance with the port number (ServerName\InstanceName, PortNumber). Voila! I was in!

I wanted to rule out the possibility of Cluster Service service account not having access to SQL Server. I checked the Startup account of Cluster Service. LocalSystem. Eh! LocalSystem account to start Cluster service? I came to know that even though this is on a Cluster, it is not Fail-over Cluster. The other node will always be offline. Alright, I did not have any say on this. I am here to fix it. I saw that NTAUTHORITY\SYSTEM the start up account of Cluster Service had sysadmin privileges on the instance. Hence the permissions issue was ruled out.

I was able to connect to the instance using the port number. It had to be the missing port number information causing this issue. I can add the port number in Query Analyzer to connect, but how to make the SQL Server Resource to use the port number? The only option to force any client applications to use the port number when connecting SQL Server are the Network Utility applications in SQL Server 2000. Since this is on a cluster, I could not use Server Network Utility. The only option that I had was to use SQL Server Client Network Utility. Even the Cluster Service is a client application for SQL Server, like any other application it needs to have the correct SQL Server details in its “connecting string”. Here is how I configured the Cluster Service to make use of the port number while trying to connect to the SQL Server instance.

Clicked on Ok here and went back to Cluster Administrator. The SQL Server Resource came online within seconds!

There may be better ways of fixing this issue, but this one did the trick for me when needed most.

SQL Agent does not start | Login failed for user sa

Yesterday early morning I got a call that the SQL Server Agent on a SQL Server 2000 instance was not starting after a scheduled reboot of the server. I was told that there was an error message in SQLAgent.out file which read Login failed for user ‘sa’. Instead of relying on that information I got on to a remote help session with my team member.

I could see that whenever we tried starting the SQL Server Agent service, SQLAGENT.out file had this information in it.

This server belongs to a Line of Business where we have been hardening the servers of late. Hence the first thing I checked that the service account had “sa” privileges on that instance, assuming that we had revoked access to BuiltinAdministrators and did not grant the new service account sufficient privileges. Since this instance is SQL Server 2000 this article explains that lack of privileges can result in such errors.  But the SQL Server and Agent services were running under the credentials of a Domain Account which had “SA” privileges on that instance.

Since the first option was ruled out, the next step was to check the “Connection” tab in SQL Server Agent –> Properties. Here is what I saw.

Gotcha! For some unknown reasons someone had configured SQL Server Agent to connect to the SQL Server instance using “SA” credentials. I remembered that the SA account was moved into a “Vault” last night. The “Vault” application resets the password for the first time and then it manages the password on its own by changing it every 15 days or when someone fetches the password through it. This is the reasons why SQL Server Agent was failing to start with login failed for sa error. Now all I had to do was to change the connection property to Use Windows Authentication. Thats all. SQL Server Agent started without any errors after I did this change.

There are many more servers that we are “hardening” and I will not be surprised to get few more unpleasant surprises in the coming days.

sp_helpdb error | Cannot insert the value NULL into column

Yesterday was one of the most hectic days at work after a long time. The server having high CPU utilization mentioned here, was at its best yesterday. CPU at 100% consistently. We are still fire fighting that issue. I will write about it once it is fixed.

I was on this call till 5 AM today morning and after 4 hours of sleep back in office. Too tired to come to office but I had to because of the special Friday situation we have in our team. The moment I entered Office one of the DBAs greeted me with “Hey Pradeep, before you open you laptop come on here. I have something to be fixed“. Oh no, not know.

Reluctantly I walked up to his desk to see the error message on the screen. He was running sp_helpdb on an instance and it was failing with the below error.

This was on an instance running SQL Server 2000. I had worked on this year around 2 years back. Here is how I had fixed it the first time I had seen this error.

While trying to generate the output sp_helpdb is unable to insert NULL values into a table. Where is it inserting? A peek into the content of sp_helpdb stored procedure would reveal it.

sp_helptext 'sp_helpdb'

Executing the above query would give the actual script that executed when sp_helpdb is called. The first occurrence of insert statement is where it was failing. That portion of sp_helpdb is as below.

/*
**  Initialize #spdbdesc from sysdatabases
*/
insert into #spdbdesc (dbname, owner, created, dbid, cmptlevel)
		select name, suser_sname(sid), convert(nvarchar(11), crdate),
			dbid, cmptlevel from master.dbo.sysdatabases
			where (@dbname is null or name = @dbname)

What I did was took out only the select statement and executed it manually.

select name,
suser_sname(sid),
convert(nvarchar(11),
crdate),
dbid,
cmptlevel
from
master.dbo.sysdatabases

The value of suser_sname(sid) column in the output of this script had several NULL entries in it. It meant that the Owner for those databases were NULL. I had wrote about a similar issue on SQL Server 2005 here. This happens when the Owner Login is dropped or that user is deleted in the Active Directory. The resolution is simple. Change the Owner of those databases using the sp_changedbowner stored procedure.

A very good start to my sleepy day.