When was the SQL Server instance restarted?

Most production environments have documentation/logs and checklists maintained by a DBA that would be able to tell precisely when and why a SQL Server restart was issued. But this is not the case in development/quality assurance environments. In fact, as I was writing this post, I realized that my test instance has not been restarted since more than a month!

I was faced with this question at the office recently. One of our nightly jobs failed in quality assurance because the SQL Server service appeared to have had restarted, and we wanted to know when this had happened. I knew one method, which I used that day.

Upon further experimentation, I also found another method (using nothing but T-SQL) to determine when a SQL Server instance had restarted. Today, I will share with you both these methods:

Method 01(A): Reading the SQL Server logs through the UI

What a DBA would do in production (i.e. maintain logs) is also done by SQL Server itself. The SQL Server logs would therefore be the ideal place to go to when searching for information on when the SQL Server service restarted.

To navigate to the SQL Server logs using the SQL Server Management Studio (SSMS) UI, connect to the SQL Server instance in the Object Browser (Covered in part #2 of 10-part tutorial on SSMS), and navigate to the Management node.

Under the Management node, you will see the “SQL Server Error Logs” which would contain a set of log files (the number of log files to maintain is configurable hence; you may have more than that illustrated in the image below).

Double-click on the log file marked “Current”. Generally, in one of these log files, you would find an entry similar to:

“SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.”

The first occurrence of this entry when going in reverse chronological order will indicate the date/time when the SQL Server was started.

The only case when the logs would fail to get the restart date/time is when the SQL Server has been up and running for a time long enough for the logs to have recycled over themselves.

Method 01(B): Reading the SQL Server error logs through T-SQL

In method #1A, we read the SQL Server error logs through the SQL Server UI. We can achieve the same through T-SQL also. The system stored procedure sys.sp_readerrorlog (undocumented, to the best of my knowledge) can be used to read the contents of the SQL Server error log. This system stored procedure takes up to 4 parameters:

• Error log file to read (0 = Current, 1 = Archive #1, and so on)
• Log File Type (NULL/1 = SQL Server Error log, 2 = SQL Agent Log)
• String 1 (first search string)
• String 2 (string to further refine the search)

We need to read the current file in the SQL Server Error Log, and only need to search for one string – “SQL Server is starting”. The stored procedure would therefore be:

EXEC sys.sp_readerrorlog 0, 1, 'SQL Server is starting'

The output is similar to what is shown in the graphical UI of the error log reader:

Method 02: Using Dynamic Management Views – sys.dm_server_services

Reading and working with error logs requires some amount of skill because they provide the user with an ocean of information – all of which is useful. For the apprentice, the error log might be a little too much to swallow and therefore, I would recommend using Dynamic Management Views (DMVs) to get to the required data. In fact, I would recommend DMVs for the experienced DBA too.

Why? They can be used in a T-SQL query just as any other table (so querying them is not a foreign concept) and they provide “real-time” information about the SQL server instance.

The DMV that provides information about the SQL Server and the SQL Server Agent services in the current instance of SQL Server is sys.dm_server_services. Along with information about service status, service account, process_id and clustered information, this DMV also provides information about when the SQL Server and the SQL Server Agent services were last started up!

SELECT * FROM sys.dm_server_services

Isn’t that simple? Just a single query and you have the answer to one of the most common questions of all time!

Until we meet next time,

Be courteous. Drive responsibly.

The moment you would have read the title of this post, you would have thought – “What’s so special in this post?” Well, did you know that there are not one, not two, but at least three (3) distinct methods that you can use to get the definition/script for any database object in Microsoft SQL Server using T-SQL?

Surprised? Read on…

Method #01 – sys.syscomments (obsolete)

For anyone who has been around since (or perhaps before) the days of SQL 2000, sys.syscomments must be familiar. This system table contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. Here’s how one would use it:

SELECT sc.text

FROM sys.syscomments AS sc

INNER JOIN sys.objects AS so ON sc.id = so.object_id

WHERE so.name = ‘uspUpdateEmployeePersonalInfo’

  AND so.schema_id = SCHEMA_ID(‘HumanResources’)

GO

For the purposes of this demonstration, I have switched the query results output to Text and changed the max. number of characters for text output to 8000. The output is shown below:

System tables have already been marked for deprecation in future releases of Microsoft SQL Server. Hence, starting SQL Server 2005, it is no longer advised to use the sys.syscomments system table.

Method #02 – sp_helptext

One of the drawbacks of using sys.comments is that the formatting of the script may be lost. Another hot favourite – a system stored procedure sp_helptext, returns a fairly formatted version of the script. Here’s how to get the definition for HumanResources.uspUpdateEmployeePersonalInfo.

USE AdventureWorks2012

GO

EXEC sp_helptext ‘HumanResources.uspUpdateEmployeePersonalInfo’

GO

/*Alternate methods:

EXEC sp_helptext [HumanResources.uspUpdateEmployeePersonalInfo]

GO
–The following does not work, both the schema name and the object name need to be one identifier:

EXEC sp_helptext [HumanResources].[uspUpdateEmployeePersonalInfo]

GO

*/

The output looks like the one shown below:

sp_helptext continues to use the sys.syscomments system table. However, the only good thing about using a system stored procedure rather than directly querying a system table is that whenever Microsoft changes the implementation of sp_helptext, any existing scripts that use the system stored procedure would not need to change.

Method #03(A) – OBJECT_DEFINITION()

This T-SQL function returns the object data using the meta-data information associated with the specified object. Instead of worrying about using the schema name and the object name as one identifier, the usage here is much simpler because one has to deal only with the OBJECT_ID. Getting the definition of any object is as simple as executing a simple SELECT statement now:

USE AdventureWorks2012

GO

SELECT OBJECT_DEFINITION(OBJECT_ID(‘HumanResources.uspUpdateEmployeePersonalInfo’)) AS ObjectText

GO

The result is as shown below. OBJECT_DEFINITION does not query sys.syscomments, instead directly queries the metadata associated to the database.

sp_helptext v/s OBJECT_DEFINITION: A Comparison

Given that sys.syscomments is obsolete and should not be used, I believe it would be a good time to compare the two remaining options – sp_helptext and OBJECT_DEFINITION.

Object type	sp_helptext	OBJECT_DEFINITION
CHECK constraint	Y	Y
Default (or stand-alone) constraint	Y	Y
Default, unencrypted stored procedure	Y	Y
SQL Scalar functions	Y	Y
Rules	Y	Y
Replication filter procedures	Y	Y
Triggers	Y	Y
In-line TVFs	Y	Y
Multi-line TVFs	Y	Y
Views	Y	Y
Computed Columns	Y	N

All is well until the computed columns show up. OBJECT_DEFINITION clearly lags behind sp_helptext in this area.

Method #03(B) – Using sys.computed_columns

To get the definition of computed columns, a separate metadata visibility view exists, named the sys.computed_columns. Here’s a sample implementation:

USE AdventureWorks2012

GO

SELECT object_id,

       name,

       definition,

       column_id,

       system_type_id,

       max_length,

       precision,

       scale,

       collation_name

FROM sys.computed_columns

GO

The results is as shown below:

Now that we have seen all the 3 methods that I know of, I would like to say that my favourite one (probably out of habit) is sp_helptext. The question to you is:

Which method do you use to fetch the object definition?

Do leave a note as you go.

EDIT [09/10/2012]: My dear friend, Chintak just wrote a post extending this topic. It’s a great read and you can find it here: http://beyondrelational.com/modules/2/blogs/115/posts/17659/sphelptext-vs-objectdefinition.aspx

[END EDIT]

References

• sys.syscomments: http://msdn.microsoft.com/en-us/library/ms186293.aspx
  
• sp_helptext: http://msdn.microsoft.com/en-us/library/ms176112.aspx
  
• OBJECT_DEFINITION: http://msdn.microsoft.com/en-us/library/ms176090.aspx
  
• sys.computed_columns: http://msdn.microsoft.com/en-us/library/ms188744.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Those who have been following this site regularly would know that a 10-part tutorial series on the SQL Server Profiler, authored by me just was recently published. You can find the master page here. This week, I would try to address some of the questions that have come from you, the kind readers as part of this series or in the “ASK” module of this site.

The question that I would address today is:

How can one filter trace events based on the Windows Login/User Name?

This is a very important requirement, especially when troubleshooting terminal-server based environments. Normally, in development environments, simply filtering on the host name is sufficient. However, in a terminal server configuration, multiple users may be logged on to the same host, which makes filtering on the windows login/username important.

Preparing the Environment

Before I execute the tests, allow me to prepare my test environment. I will be creating one stored procedure, which will be executed by 3 different windows users.

USE AdventureWorks2012
GO
CREATE PROCEDURE HumanResources.proc_GetEmployes
    @businessEntityId INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT SYSTEM_USER,
           e.BusinessEntityID,
           e.BirthDate,
           e.HireDate,
           e.JobTitle
    FROM HumanResources.Employee AS e
    WHERE e.BusinessEntityID = @businessEntityId
END
GO

As seen in the screenshot below, we have 3 Windows Users which have been granted access to SQL Server. (NOTE: In practice, it is not advisable to add users to the sysadmin fixed server role. This has been done here for demonstration purposes only):

1. WIN2K8Administrator
2. WIN2K8SQLTestUser01
3. WIN2K8SQLTestUser02

Executing the Test

To execute the test, I have already prepared the SQL Server Profiler to use a custom template based on the “TSQL_SPs” default template as shown in the tutorial: getting-started-with-sql-server-profiler-part-2-profiler-templates-template-types-and-creating-c. As part of the customization, I have chosen to capture and display the following four columns:

• LoginName
• LoginSid
• NTDomainName
• NTUserName

Now that the SQL Server Profiler is ready, I will execute the stored procedure under the context of the 3 users referenced above. To switch the execution context, I have used the EXECUTE AS LOGIN clause.

USE AdventureWorks2012
GO
--Executing as the first test user - WIN2K8SQLTestUser01
EXECUTE AS LOGIN = 'WIN2K8SQLTestUser01'
EXEC HumanResources.proc_GetEmployes @businessEntityId = 1
GO
--Revert to original logged-in user
REVERT
GO

--Executing as the first test user - WIN2K8SQLTestUser02
EXECUTE AS LOGIN = 'WIN2K8SQLTestUser02'
EXEC HumanResources.proc_GetEmployes @businessEntityId = 2
GO
--Revert to original logged-in user
REVERT
GO

EXECUTE AS LOGIN = 'WIN2K8Administrator'
EXEC HumanResources.proc_GetEmployes @businessEntityId = 3
GO
--Revert to original logged-in user
REVERT
GO

Looking at the Profiler, I can see that I have been able to capture the fact that the statements were executed under 3 different user contexts.

To filter the results, all one has to do is to filter one of the four columns mentioned below (as applicable):

• For both SQL & Windows Logins:
• LoginName
• LoginSid
• For Windows Logins:
• NTDomainName
• NTUserName

Trust that the above exercise has cleared the queries of all my dear readers. If you still have any queries, do send them across – I will be happy to help you out.

References:

• EXECUTE AS: http://msdn.microsoft.com/en-us/library/ms181362.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Those who have been following this site regularly would know that a 10-part tutorial series on the SQL Server Profiler, authored by me just was recently published. You can find the master page here. This week, I would try to address some of the questions that have come from you, the kind readers as part of this series or in the “ASK” module of this site.

The question that I would address today is:

How can I filter multiple objects of the same type?

For example, add a condition like ObjectName NOT LIKE ‘%SP1%’ AND ObjectName NOT LIKE ‘%SP2%’

The SQL Server Profiler is a very powerful performance analysis tool, and this is a fairly common requirement. Here’s a step-by-step guide to realize this requirement. For this demo, let me create 3 very stored procedures. Before we execute these procedures, we will use the Profiler to filter out two of them from the trace.

USE AdventureWorks2012
GO
CREATE PROCEDURE HumanResources.proc_GetEmployes1
    @businessEntityId INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT 'SP1',
           e.BusinessEntityID,
           e.BirthDate,
           e.HireDate,
           e.JobTitle
    FROM HumanResources.Employee AS e
    WHERE e.BusinessEntityID = @businessEntityId
END
GO

CREATE PROCEDURE HumanResources.proc_GetEmployes2
    @businessEntityId INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT 'SP2',
           e.BusinessEntityID,
           e.BirthDate,
           e.HireDate,
           e.JobTitle
    FROM HumanResources.Employee AS e
    WHERE e.BusinessEntityID = @businessEntityId
END
GO

CREATE PROCEDURE HumanResources.proc_GetEmployes3
    @businessEntityId INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT 'SP3',
           e.BusinessEntityID,
           e.BirthDate,
           e.HireDate,
           e.JobTitle
    FROM HumanResources.Employee AS e
    WHERE e.BusinessEntityID = @businessEntityId
END
GO

Now that these procedures are created, let us setup the Profiler to capture the stored procedure execution, filtered such that only the procedure is seen in the trace.

1. Follow the steps provided in the Profiler Tutorial Part #2: http://beyondrelational.com/modules/12/tutorials/631/tutorials/15252/getting-started-with-sql-server-profiler-part-2-profiler-templates-template-types-and-creating-custo.aspx to create a customized template
   • I generally prefer using the “TSQL_SPs” default template as the base and then customize it to suite the requirement
2. When customizing the column filters, add the first line:
3. 
4. Now, hit enter to add a new line
5. 
6. Now, add the second line of filter
7. 
8. Click “OK” to complete configuration of the template

Now, start the Profiler trace and execute the following query:

USE AdventureWorks2012
GO
EXEC HumanResources.proc_GetEmployes1 @businessEntityId = 1
GO
EXEC HumanResources.proc_GetEmployes2 @businessEntityId = 2
GO
EXEC HumanResources.proc_GetEmployes3 @businessEntityId = 3
GO

The Results tab of the SQL Server Management Studio (SSMS) will show the results from the execution of all 3 procedures.

The Profiler would capture the following trace, which only captures the activity from the stored procedure HumanResources.proc_GetEmployees2 and not from the other two procedures. Adding new lines to the Profiler template column filters makes the Profiler to apply an “AND” condition on the filter.

I hope the above simple steps help you to filter your Profiler Results efficiently.

Until we meet next time,

Be courteous. Drive responsibly.

It’s close to a week-end, and hence, I have a very short post for today.

Very often I find teams and SQL Server enthusiasts coming up to me and asking me some of the following questions:

• How many databases can I host on a SQL Server instance?
  
• How large can these databases be?
  
• Is there a limit to the number of records in a table?

I often point them to Books On Line/MSDN where they can find the official specification which would be able to answer all of their questions. Today, I thought of putting an end to this almost endless cycle of same question-answer sessions and provide the direct links to the Books on Line/MSDN maximum capacity specification for SQL Server. So, here goes:

Maximum Capacity Specifications for SQL Server

• SQL 2012: http://msdn.microsoft.com/en-us/library/ms143432.aspx
  
• SQL 2008 R2: http://msdn.microsoft.com/en-us/library/ms143432(v=sql.105).aspx
  
• SQL 2008: http://msdn.microsoft.com/en-us/library/ms143432(v=sql.100).aspx
  
• SQL 2005: http://msdn.microsoft.com/en-us/library/ms143432(v=sql.90).aspx

How do you calibrate your systems?

Whenever someone asks a minimum/maximum capacity specification related question, I always wonder how they would calibrate their systems and more importantly, certify their product against these numbers. So, here’s what I would like to learn from you, my kind readers:

How do you derive minimum/maximum capacity specifications for your systems?

Ideally, the situation would be that a production system would have the same limitations as the underlying platform. But, that would not happen in practice due to the choice of the architecture and/or technical design decisions.

Do leave a note as you go. It’s a very interesting question, with no fixed answer – every system and every team would have their own methods, which is why I am sure it would be a great discussion.

Until we meet next time,

Be courteous. Drive responsibly.