While most would be very meticulous about ensuring that their servers are running software with the latest and greatest service/cumulative update packs. However, most developers that I have worked with only deploy a patch if their IT forces them to (via group policies and automatic deployment of updates) or if they face a challenge and the patch resolves the issue. In such a scenario, developers often come up to me and ask this question:

The Intelli-sense feature in the SQL Server Management Studio (SSMS) on my local SQL Server 2008 R2 instance stopped working. Can you fix it?

My reply is always a counter-question: “What changed?”

After the denials, they finally admit that they installed Visual Studio 2010 SP1 and that resolves the issue. After Visual Studio 2010 SP1 is installed on a workstation with SQL Server 2008 R2 (any release from RTM to CU6), the Intelli-sense feature in the SQL server Management Studio (SSMS) may stop working.

This is a  known, documented issue with Microsoft (KB2531482)and the fix is quite simple – deploy the latest service pack/cumulative update of SQL Server 2008 R2. To know about the latest available build of SQL Server, please refer my post: http://bit.ly/SiOVIw.

Further Reading:

Here are some other posts about Intelli-sense that you may be interested in:

• Enable/Disable Intelli-sense [by Madhivanan (Blog)]: http://beyondrelational.com/modules/2/blogs/70/posts/10965/exploring-ssms-enabledisable-intellisense.aspx
• Intelligent Use of Intelli-sense [by Madhivanan (Blog)]: http://beyondrelational.com/modules/2/blogs/70/Posts/18053/intelligent-use-of-intellisense.aspx
• Intelli-sense and hyperlinks: http://bit.ly/VR4qbF
• Intelli-sense Completion mode: http://bit.ly/VdlXw6
• Intelli-sense uses a “LIKE” search from SQL 2012 onwards: http://bit.ly/10HBo23

Until we meet next time,

Be courteous. Drive responsibly.

SQL Server Catalog Views & Dynamic Management Views (DMVs) take an administrator to the very heart of SQL Server. DMVs expose vital diagnostic information which are useful to indicate the overall health of the SQL Server. The list of DMVs that changed in SQL Server 2008 R2/2012 is available in one of my previous posts here: http://bit.ly/XobXup.

The SQL Server Operating System (SQLOS) is a layer that lies between the host operating system (Windows OS) and the SQL Server. All interactions of the SQL Server with the underlying operating system are carried out through the SQLOS. SQLOS manages all the operating system services such as memory management, processor and disk interaction, buffer management, hosting external components (e.g. SQL-CLR) etc. Today, we will look at the available SQLOS DMVs and study in detail, the ones introduced in SQL Server 2008 R2/2012.

A full list of documented SQL Server 2012 SQLOS related DMVs is available at: http://msdn.microsoft.com/en-us/library/ms176083.aspx. The DMVs introduced in SQL Server 2008 R2/2012 are:

• sys.dm_os_cluster_properties
• sys.dm_os_memory_broker_clerks
• sys.dm_os_server_diagnostics_log_configurations
• sys.dm_os_windows_info

sys.dm_os_cluster_properties

Introduced In: SQL Server 2012

Purpose: Returns one record with the current SQL Server resource properties (failover response & logging, detection time, etc). When executed on a stand-alone instance (as my test environment is), no records are returned.

Required Permissions: VIEW SERVER STATE

BOL Link: http://msdn.microsoft.com/en-us/library/gg471591.aspx

sys.dm_os_memory_broker_clerks

Introduced In: SQL Server 2012

Purpose: Memory brokers are internal memory management objects that help the SQL Server regulate the memory allocations across all SQL Server components. Please note that the memory allocations are done by the SQLOS – the memory brokers only document and track memory allocations. Generically speaking, the components that consume memory are called memory clerks. This DMV therefore provides details about the memory allocations of SQL Server’s internal memory consumers.

Required Permissions: VIEW SERVER STATE

BOL Link: N/A – undocumented.

SELECT mbc.clerk_name,
       mbc.total_kb,
       mbc.simulated_kb,
       mbc.simulation_benefit,
       mbc.internal_benefit,
       mbc.external_benefit,
       mbc.value_of_memory,
       mbc.periodic_freed_kb,
       mbc.internal_freed_kb
FROM sys.dm_os_memory_broker_clerks AS mbc;

sys.dm_os_server_diagnostics_log_configurations

Introduced In: SQL Server 2012

Purpose: Returns one record for the SQL Server failover cluster diagnostic log. By default, the log is available in the SQL Server default log folder and the DMV also indicates whether diagnostic logging is enabled, number of logs and default size.

Even on a standalone server, one record is returned.

Required permissions: VIEW SERVER STATE

BOL Link: http://msdn.microsoft.com/en-us/library/gg471697.aspx

SELECT dlc.is_enabled,
       dlc.path,
       dlc.max_size,
       dlc.max_files
FROM sys.dm_os_server_diagnostics_log_configurations AS dlc;

sys.dm_os_windows_info

Introduced In: SQL Server 2008 R2

Purpose: Returns the service pack/release information of the underlying operating system.

Required Permissions: VIEW SERVER STATE

BOL Link: http://technet.microsoft.com/en-us/library/hh204565.aspx

SELECT wi.windows_release,
       wi.windows_service_pack_level,
       wi.windows_sku,
       wi.os_language_version
FROM sys.dm_os_windows_info AS wi;

Here’s the output from my Windows 8 test system:

Until we meet next time,

Be courteous. Drive responsibly.

A while ago, I read a post from Aaron Bertrand (B|T) regarding an appeal to start using semi-colons actively as statement terminators. Semi-colons as statement-terminators has been around for as long as I can remember, however, they have only been made mandatory in the newer statements:

• A semi-colon is required before the WITH clause (as in Common Table Expressions, CTE)
• The MERGE statement must end with a semi-colon
• In SQL 2012, the THROW statement also requires that the preceding statement ends with a semi-colon

Because the semi-colon is not mandatory, most developers do not use semi-colons in the T-SQL queries that they write. However as Aaron points out, it is already documented in Books On Line that the semi-colon will be a required feature:

Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.

For most teams, this would mean to modify almost every line of code and a huge testing & development effort. To mitigate the high development effort and bring reliability in the process, teams may decide to write a small program that would add semi-colons after each statement. But there’s a small catch.

Exception to the rule: The batch separator cannot be suffixed by a semi-colon.

USE AdventureWorks2012;
GO;

The code above would simply fail to compile with the following error.

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ‘GO’.

The reason is quite simple – a T-SQL statement is a sub-set of a batch. Because “GO” is a batch separator, it cannot be suffixed by a statement terminator because an active T-SQL batch would not exist at that time.

Reference:

• Ladies and Gentlemen, start your semi-colons! by Aaron Bertrand (B|T)
• T-SQL Syntax Conventions
• “GO” as Batch Separator – Customize batch separator in SSMS & SQLCMD

Until we meet next time,

Be courteous. Drive responsibly.

Of late, the changes made to the Catalog Views & Dynamic Management Views (DMVs) in SQL Server 2012 have attracted my attention. One of the major factors in this attraction is the rate at which new DMVs and changes to existing DMVs in Microsoft SQL Server are being rolled out. DMVs are some of the the most powerful features of Microsoft SQL Server. The list of DVMs that changed in SQL Server 2008 R2/2012 is available in one of my previous posts here: http://bit.ly/XobXup.

In the post today, I will be discussing the DMVs related to the SQL Server installation introduced new to SQL Server 2008 R2/2012:

• sys.dm_server_services
  
• sys.dm_server_registry
  
• sys.dm_server_memory_dumps

SQL Server 2008 R2/2012 Installation DMVs

sys.dm_server_services

I have written about this DMV in the past as well. 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;

On my test system, the output of this query is shown in the screen-shot below, and consists of:

1. Service Name
   
2. Startup Type (enumeration)
   
3. Startup Description
   
4. Status (enumeration)
   
5. Status description
   
6. Process ID
   
7. Last Startup Time
   
8. Service Account
   
9. Service File Name & startup parameter
   
10. Is instance clustered?
    
11. If clustered, the cluster node name is also present

sys.dm_server_registry

For a given SQL Server instance, this DMV returns one row for each configuration and installation related registry key. For example, the following query fetches the current version of the given SQL Server instance from the registry.

SELECT dsr.registry_key,

       dsr.value_name,

       dsr.value_data

FROM sys.dm_server_registry AS dsr

WHERE dsr.value_name = ‘CurrentVersion’;

The output consists of the following three columns:

• Registry Key
  
• Value Name
  
• Value data

sys.dm_server_memory_dumps

This DMV returns one row for each memory dump file generated by the SQL Server Database Engine. This can be useful in troubleshooting potential issues with the SQL Server installation and/or operation. No records returned by this DMV is the ideal scenario.

SELECT smd.filename,

       smd.creation_time,

       smd.size_in_bytes

FROM sys.dm_server_memory_dumps AS smd;

In my environment, no records were returned by the DMV because my SQL Server has not yet crashed. However, the following columns are available for the results to be displayed in:

• File Name
  
• File creation time
  
• File Size in Bytes

Permissions

All the three DMVs require that the user must have at least VIEW SERVER STATE permissions. This is because they not only expose the details about the SQL server, they also reach out into the operating system of the host to read the registry.

Further Reading

• sys.dm_server_services – http://msdn.microsoft.com/en-us/library/hh204542.aspx
  
• sys.dm_server_registry – http://msdn.microsoft.com/en-us/library/hh204561.aspx
  
• sys.dm_server_memory_dumps – http://msdn.microsoft.com/en-us/library/hh204543.aspx
  
• Using sys.dm_server_services to know when was the SQL Server instance restarted? – http://bit.ly/UilzuG

Until we meet next time,

Be courteous. Drive responsibly.

Recently, one of my colleagues asked me an interesting question. They had a database with multiple files spread across various file-groups. The logical file names of these files were based on the product/company name. During a re-branding effort, they wanted to know if there was a way to change the logical file names and avoid transferring data over to a new database.

Here’s an example of the requirement. The script below creates a database with 3 files – a primary & secondary data file and a log file – all prefixed by the company name “AdventureWorks”.

/******************************************************************************
Create a test database
******************************************************************************/
USE master;
GO
IF EXISTS (SELECT * FROM master.sys.databases WHERE name = 'FileRenameTest')
BEGIN
    ALTER DATABASE FileRenameTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE FileRenameTest;
END;
GO

CREATE DATABASE [FileRenameTest]
    ON 
    PRIMARY(NAME = [AdventureWorks_Default], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAFileRenameTest.mdf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 51200 KB),
           (NAME = [AdventureWorks_Data], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAFileRenameTest_Data1.mdf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 51200 KB)
    LOG ON (NAME = [AdventureWorks_Log], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAFileRenameTest.ldf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 102400 KB);
GO

The requirement now is to rename the database logical files such that they begin with the new name – “Contosso”. To do so, we will be using the MODIFY FILE clause of the ALTER DATABASE statement:

/******************************************************************************
Rename logical files
******************************************************************************/
--Primary Data File
ALTER DATABASE FileRenameTest
MODIFY FILE (NAME = [AdventureWorks_Default], NEWNAME = [Contosso_Default]);
GO

--Secondary Data File
ALTER DATABASE FileRenameTest
MODIFY FILE (NAME = [AdventureWorks_Data], NEWNAME = [Contosso_Data]);
GO

--Log File
ALTER DATABASE FileRenameTest
MODIFY FILE (NAME = [AdventureWorks_Log], NEWNAME = [Contosso_Log]);
GO

Now, checking the file names using sys.database_files helps us to confirm that the logical file names have indeed changed.

/******************************************************************************
Check the logical file names
******************************************************************************/
USE FileRenameTest;
GO
SELECT file_id AS FileId,
       type AS FileType,
       name AS FileLogicalName,
       physical_name AS FilePhysicalName
FROM FileRenameTest.sys.database_files;
GO

References:

• ALTER DATABASE, File & Filegroup options: http://msdn.microsoft.com/en-us/library/bb522469.aspx
• sys.database_files: http://msdn.microsoft.com/en-us/library/ms174397.aspx

Until we meet next time,

Be courteous. Drive responsibly.