SQL SERVER – SSMS: Configuration Changes History

As we go through each of the reports available in SQL Server Management Studio, I have an anecdote to share almost for most of them actually. I have used them extensively at different times and I am glad to share some of them via this blog series.

Once a friend of mine Joe pinged me over a chat. He was going to join a new organization and it was interesting to see the excitement and energy in his conversation. He was brimming with confidence about his new found job. After joining, he was back to ping me again. I was curious to know how his new job responsibilities were. After our initial greetings, Joe was quick to ask me a SQL Server related query.

Joe: Dave, in my new job we are supposed to have some standards to our servers.

Pinal: Ok, so what is the problem?

Joe: As I get access to some of the servers, I want to quickly know what changes were made to the server.

Pinal: Ok, so what is the problem? I still don’t get it.

Joe: Do you have any query that will give me this information?

Pinal: Now you are talking business.

Joe: I know you can help me, as you have done a number of times in the past.

Pinal: Thanks for your compliments, no need to search anywhere. It is already available with you.

Joe: You must be kidding me

Pinal: No Joe, Have you ever checked the Configuration Change history SQL Server Management Studio Report?

Joe: Ahhh that was a good catch.

Pinal: See, it was all along with you. Just that I helped you find it existed. Now enjoy!!!

To find this SSMS report, it is second in the list from Server node -> Standard reports.

The purpose of this report is to show two key information:

  • Changes made to Server Configuration using sp_configure
  • Changes to trace flags (Enable or Disable) done via T-SQL.

The information from this report is fetched from “default trace” which runs by default in every SQL installation. If the default setting is disabled by administrator by any chance, then we will not get any reports. We will get the below message in report when the default trace is disabled.

Currently, this table does not have any data to show, because default trace is not enabled.

To enable the default trace, below command can be used

sp_configure 'default trace enabled', 1

For most installations you don’t need to enable because it is running by default. Normally, a user is likely to see the below report with data like:

I am always intrigued when such reports every time. To look at the query fired for this report, we can fire up profiler to capture the same. Report uses fn_trace_gettable to read default trace and get event class 22 and 116.

FROM sys.trace_events
WHERE trace_event_id IN (22, 116)


From the trace events, it means whenever a change is made using DBCC TraceStatus or sp_configure, they are recorded in default trace under “ErrorLog” and “Audit DBCC Event”.  To display the SSMS reports additional filters have been applied to show only relevant information.

Do check these reports in your environment and let me know if you found any interesting information that you learnt today?

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – SSMS: Server Dashboard Report

The term dashboards come from the automobile world where it meant as instrumentation or controls for operating a vehicle. In the IT parlance, the use of dashboards meant as the state or health of system or organizations. Though this analogy cannot be taken too far because running a business is not same as running a vehicle. But the similarities of knowing the health of the system is bang-on.

In our previous post, I gave an introduction on where to find all the SQL Server Management Studio reports. Today we will look at the first and the most basic report all of us must be working with. It is called as “Server Dashboard” report under the Server node, standard reports node.

This report gives and overview of various settings in a SQL Server instance. As the name suggests, it shows the health of SQL Server instance level and has three sections to examine. We will investigate each of these sections next.

Configuration Details

This section is interesting because it can answer some interesting questions:

  • When was the SQL Server started / restarted?
  • What Edition of product are we running and version?
  • If the instance is a Clustered instance or not.
  • Number of logical processors used by the SQL Server instance.

When someone asks me to performance tune a system, I generally tend to ask some of these basic questions to customers. Lesser known to DBA is that instead of querying or relying on their Excel sheets, this report can give this information in an instant.

Most of the other values are self-explanatory. It should be noted that “Scheduler Agent Jobs” shows the number of jobs created, not what is running or enabled.

Non Default Configuration Options

Let us move to the next section. As an administrator, if you want to know the server level settings that were changed from default in a given instance, then this is the section to note.

By default the values are not visible. We need to click on (+) symbol to toggle it.

This part of the report can show various non-default configuration settings done via the executing sp_configure stored procedure. You can read about enabling / disabling sp_configure basics from my previous blog posts.

I always look into this section to see if the administrator or someone else in the system has limited the memory allocated to SQL Server on a given box. This comes under the “Max server memory(MB)” section as shown above.

This section also shows if any trace flag is enabled on the server. This information is normally not available when you just play around with sp_configure.

Activity Details

Next the busier part section with Activity Details come. This has a number of interesting information. A visual graph to show the CPU and IO performed on the instance. But most importantly there are sections on the top as a table which is worth a note.

  1. Shows the number of Active sessions and transactions in the system at the moment.
  2. Active databases available in the instance.
  3. Total server memory in the system.
  4. Logins connected to the server.
  5. Number of blocked transactions in the system
  6. Finally, worth to note is the server side traces running in the system.

Though the information is available in a nice report, the same can be got via various DMVs from SQL Server. I am outlining some of these for reference.

Title DMV and filter
Active session sys.dm_exec_sessions where is_user_process = 1 and status = ‘running’
Active Transactions sys.dm_tran_active_transactions where transaction_state = 2 or( transaction_type = 4 and dtc_state = 1)
Total Server Memory sys.dm_os_performance_counters where counter_name like ‘%Total Server Memory (KB)%’
Idle Sessions sys.dm_exec_sessions where is_user_process = 1 and status = ‘sleeping’
Blocked Transactions sys.dm_tran_locks where request_status = ‘WAIT’
Distinct Connected Logins on Sessions sys.dm_exec_sessions where is_user_process = 1
Traces Running sys.traces where status = 1

The query to generate the graph is shown below. This is based on historical data using sys.dm_exec_query_stats to show the graph for IO and CPU.

Query 1:
SELECT  total_worker_time,
CASE WHEN DB_NAME(dbid) IS NULL THEN 'Adhoc Queries'
FROM    sys.dm_exec_query_stats s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

Query 2:
SELECT  total_logical_reads + total_logical_writes AS total_io,
CASE WHEN DB_NAME(dbid) IS NULL THEN 'Adhoc Queries'
FROM    sys.dm_exec_query_stats s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

Though the reports are a great way to see this data visually, it is nice to see the power of DMVs and how they can be used with SQL Server. All these information are available out-of-box but these are visually represented nicely using the SQL Server Management studio dashboards.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix Error – Package ‘Microsoft SQL Management Studio Package’ failed to load in SQL Server Management Studio

I recently formatted my computer and reinstalled my machine and I end up on following error:

Package ‘Microsoft SQL Management Studio Package’ failed to load

I was able to fix the error quickly, but while I was searching online, I noticed quite a few of the people have similar error and they struggle to fix it. I have decided to list a few of the ways to fix the error here.

Method 1: Reinstall .NET Framework

Method 2: Start SSMS with Admin privileges

Method 3: Reinstall SQL client with either command prompt or with wizard

Method 4: Delete the registry HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools and try to load the server again.

Let me know if there is any method which works to fix this error and I have missed it.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Turning On Graphical Execution Plan After Enabling ShowPlan Text

This may be surprising to many, but I have seen quite a few times so far so decided to blog over here.

Here is the sequence of the action:

  1. The developer turns on Graphical Execution plan for any query with CTRL+M
  2. Now turns on the execution plan in the text format with SET SHOWPLAN_TEXT ON command
  3. After this, the developer does his/her task to analysis the execution plan
  4. Now turns on the execution plan in the XML format with SET SHOWPLAN_XML ON command

Now when a developer has to turn back Graphical Execution Plan, he/she gets confused. We know that we can turn on TEXT and XML with the help of SET commands, but how to turn on graphical execution plans from T-SQL. Well, the matter of facts is there is no way to turn the graphical execution plan from T-SQL.

The only way to get the Graphical Execution plan back in SQL Server Management Studio (SSMS) is to turn off TEXT and XML plan and it will automatically turn on the graphical execution plan.

For example, in above situation when we have turned off TEXT and XML plan, set them off as described in the following steps

  1. Turn off XML execution plan with SET SHOWPLAN_XML OFF command
  2. Turn off TEXT execution plan with SET SHOWPLAN_TEXT OFF command

It will automatically turn on the graphical execution plan. Remember, if you have turned off either of XML or TEXT, you will need to turn off that one only. However, if you have turned on both of them, you will have to turn off both of them to get back your graphical execution plan.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running

Here is one of the very common interview questions I often ask people -

Q: What is the default Port SQL Server running on?

A: 1433 for TCP/IP and 1434 for USD connection.

I always get the correct answer for this question. However, when I ask a follow up question, so far I have yet not got a single correct answer.

Q: Great, now assumes that there is more than one instance of SQL Server running on the same server. What will be the port of the second SQL Server running on the server as the default port is already used by default instant?

A: (I have yet to get the correct answer for this one in interview).

Because of the same reason, I have decided to blog about this.

Here are different ways one can figure out on which port the second instance of SQL Server is running.

Method 1: using xp_readerrorlog

Execute following stored procedure on the instance where you want to find out port on which SQL Server is running.

xp_readerrorlog 0, 1, N'Server is listening on'

The above query will return results something very similar to the following:

Method 2: Configuration Properties

Go to Windows >> Start >> SQL Server Program Folder >> SQL Server Configuration Manager

Now go to SQL Server Network Configurations >> Protocols for Your Server and go to TCP/IP and right click over it.

Now over here when you scroll down you will notice Port Details.

It is that easy to find the port of the second instance of SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Find Anything in Object Explorer in SSMS

This blog post is a follow up post of an excellent write up by my friend Vinod Kumar. I suggest you read his blog post here SSMS Tips–Object Explorer Details. After reading his blog post, I called him up and had an interesting conversation with him. Here is our conversation:

Pinal: It is easy to find anything in Object Explorer but it is not possible to find everything in Object Explorer.

Vinod: What do you mean? Explain me with example.

Pinal: For example, if I want to find anything related to any schema or table, I can go to Object Explorer and can search about that schema and find relevant information.

However, if I want every single object where any string exists, how do I find all of those objects.

Vinod: That is easy as well, remember SQL Server is Microsoft product and will follow the simple rules of the search which you are familiar. Just try to wrap your string with % and see the magic.

Pinal: Never thought of it. Let me try.

After the conversation was over, I tried to wrap my search string with % and it works, indeed it works. SSMS retrieved every single object where the string appeared.

Thanks Vinod. It is so cool that when we have questions we can reach out to experts.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Rename a Table Name Containing [ or ] Identifier in the Name – Part 2

Yesterday I posted article where we discussed how to rename a table name where identifier is part of the tablename. Read the blog post Rename a Table Name Containing [ or ] in the Name – Identifier in the Table Name. If the table name contains an identifier, it is not easy to rename a table, the default method will show an error as displayed below.

sp_rename '[]', 'ProjectA';

The above query will give us following error:

Msg 15253, Level 11, State 1, Procedure sp_rename, Line 107
Syntax error parsing SQL identifier ‘[]’.

This is because our table name contains Identifier [ as well as Identifier ]. One of the method was to rename table was to use Double Quotes around the identifier.

sp_rename '"[]"', 'ProjectA';

When we run above query, it will give us success message and rename our table to the new name.

At the end of the blog post, I asked if there is any other way to the same task.

SQL Expert Parth Malhan answered in the comment with alternative solution where he demonstrates that we can use identifier around the name of the table and rename the column as well.

EXEC sys.sp_rename '[[]]]','ProjectA'

Thanks Parth, very cool trick!

Reference: Pinal Dave (http://blog.sqlauthority.com)