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.

In the first couple of months after I started blogging, I wrote about Catalog Views & Dynamic Management Views (DMVs). While DMVs are some of the most powerful features of Microsoft SQL Server, they are still under-utilized due to the lack of awareness around them. Nevertheless, each release of Microsoft SQL Server comes with a new set of DMVs being introduced and existing DMVs being modified to expose more valuable information/details, and therefore, I decided to focus on DMVs once a week for the next couple of weeks.

To kick-off the series, I will be presenting today a comparison of the DMVs in SQL Server 2008 v/s those in SQL Server 2012. A total of 382 DMVs, catalog views, compatibility views and system tables exist in SQL Server 2012. While it may note be possible to discuss each and every one of these DMVs, I will be discussing the new DMVs and significant changes to existing ones.

I hope you find the series useful. Useful scripts presented as part of the series will also be made available on the SCRIPTS module (http://beyondrelational.com/modules/30/scripts.aspx?s=stream&tab=scripts) of BeyondRelational.com.

[EDIT: February 10, 2013; 10:30PM IST]: Rectified categorizing inconsistencies in the table.

Until we meet next time,

Be courteous. Drive responsibly.

I was recently discussing database design with one of my friends. We were working on file & file-group design and one of the things that came up almost immediately was the designing of log files (number, initial size, growth factors, etc) for the database.

During this exercise, we noticed that the first transaction log file for the database always got a file_id value of 2. We knew the primary data file always received a file_id = 1 because that’s the first file being created, but if the database has multiple data files, we expected them to be created before the log files were created.

Here’s a reproduction of the tests we did:

Create the databases

Let us first create 3 databases, such that the following configurations are covered:

1. One Data, One Log file (simplest configuration)
2. One Data, Multiple Log files
3. Multiple Data, Multiple Log files

/******************************************************************************
Scenario 01: One Data, One Log file
******************************************************************************/
USE master;
GO
IF EXISTS (SELECT * FROM master.sys.databases WHERE name = 'OneDataOneLogDB')
BEGIN
    ALTER DATABASE OneDataOneLogDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE OneDataOneLogDB;
END;
GO

CREATE DATABASE [OneDataOneLogDB]
    ON 
    PRIMARY(NAME = [DB_DEFAULT], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAOneDataOneLogDB.mdf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 51200 KB)
    LOG ON (NAME = [DB_LOG], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAOneDataOneLogDB.ldf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 102400 KB);
GO

/******************************************************************************
Scenario 02: One Data, Multiple log files
******************************************************************************/
USE master;
GO
IF EXISTS (SELECT * FROM master.sys.databases WHERE name = 'OneDataMultiLogDB')
BEGIN
    ALTER DATABASE OneDataMultiLogDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE OneDataMultiLogDB;
END;
GO

CREATE DATABASE [OneDataMultiLogDB]
    ON 
    PRIMARY(NAME = [DB_DEFAULT], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAOneDataMultiLogDB.mdf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 51200 KB)
    LOG ON (NAME = [DB_LOG], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAOneDataMultiLogDB.ldf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 102400 KB),
           (NAME = [DB_LOG1], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAOneDataMultiLogDB1.ldf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 102400 KB);
GO

/******************************************************************************
Scenario 03: Multiple Data, Multiple log files
******************************************************************************/
USE master;
GO
IF EXISTS (SELECT * FROM master.sys.databases WHERE name = 'MultiDataMultiLogDB')
BEGIN
    ALTER DATABASE MultiDataMultiLogDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE MultiDataMultiLogDB;
END;
GO

CREATE DATABASE [MultiDataMultiLogDB]
    ON 
    PRIMARY(NAME = [DB_DEFAULT], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAMultiDataMultiLogDB.mdf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 51200 KB),
           (NAME = [DB_DATA], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAMultiDataMultiLogDB_Data1.mdf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 51200 KB)
    LOG ON (NAME = [DB_LOG], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAMultiDataMultiLogDB.ldf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 102400 KB),
           (NAME = [DB_LOG1], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAMultiDataMultiLogDB1.ldf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 102400 KB);
GO

Select the file_ids of the databases

Now, let us select the file_ids for the files in the database. To do so, we will be using the sys.database_files view.

USE OneDataOneLogDB;
GO
SELECT 'Scenario 01 - One Data, One Log File' AS Scenario,
       file_id AS FileId,
       type AS FileType,
       name AS FileLogicalName,
       physical_name AS FilePhysicalName
FROM OneDataOneLogDB.sys.database_files;
GO

USE OneDataMultiLogDB;
GO
SELECT 'Scenario 02 - One Data, Multiple Log Files' AS Scenario,
       file_id AS FileId,
       type AS FileType,
       name AS FileLogicalName,
       physical_name AS FilePhysicalName
FROM OneDataMultiLogDB.sys.database_files;
GO

USE MultiDataMultiLogDB;
GO
SELECT 'Scenario 02 - One Data, Multiple Log Files' AS Scenario,
       file_id AS FileId,
       type AS FileType,
       name AS FileLogicalName,
       physical_name AS FilePhysicalName
FROM MultiDataMultiLogDB.sys.database_files;
GO

The results are shown in the image below.

If you observe the high-lighted area, you will notice that the file_id for the primary log file is always “2”. Essentially, the file_ids are assigned in the following manner:

1. Primary Data File –> file_id = 1
2. Primary Log File –> file_id = 2
3. Secondary Data Files (in order of creation) –> file_ids = 3 to n
4. Secondary Log Files (in order of creation) –> file_ids = (n+1) to m (where n = file_id of the last data file)

So, what’s going on?

After a bit of thinking, we realized that we were running short of coffee! That’s because this behaviour is obviously working as designed!

All user databases (and the tempdb) are copies of the model database – that’s one of the many the universal truths that Microsoft SQL Server functions on.

The model database comes out-of-the-box with every SQL Server installation with two (2) files – one data, and one log file (having Ids 1 & 2 respectively). That’s why whenever a new user database is created, the primary data file always receives a file_id = 1 and the primary log file always receives a file_id = 2.

References:

• sys.database_files: http://msdn.microsoft.com/en-us/library/ms174397.aspx
• TempDB – Is it a copy of the model database?: http://bit.ly/T6Ikjq
• TempDB v/s Model database – minimum size considerations: http://bit.ly/RMAMPq

Until we meet next time,

Be courteous. Drive responsibly.

Today’s post is prompted by a question I was asked by one of the fellow team members recently. Most enterprise applications have a “comments” or “memo” field used to store free-form text comments entered by the users, and the product that I work on is no different. Depending upon whether or not Unicode support is required, VARCHAR(MAX) or NVARCHAR(MAX) are the general choice for storage of these fields. As part of a troubleshooting exercise, one of the engineers was attempting to query this data in SSMS for exporting to a text file for further analysis. However, the team member immediately ran into a problem and approached me.

Team member: I am facing an issue with the storage of comments in our database.

Me: What happened? Is the data not being stored as expected?

Team member: No, it appears that the storage is correct, but I am unable to view it in SSMS.

Me (stumped): Okay…..let’s take a look right away.

(We walk over to the team member’s desk to take a look at the issue).

Team member: Here’s the research I have done till now. As you can see, the LEN and DATALENGTH functions show the correct length of the comments entered, but when I execute a SELECT from the table with results in text mode (Ctrl + T), I only get 256 characters in text results mode of SSMS. Can you help me get the complete data?

USE tempdb;
GO
SET NOCOUNT ON;
--Temporary table variable for demonstration of the issue
DECLARE @textModeTest TABLE (RecId INT, RecText VARCHAR(MAX));

--Inserting sample data
INSERT INTO @textModeTest (RecId, RecText) 
VALUES (1, REPLICATE('a',2000));

--Selecting the data lengths and associated data
SELECT LEN(RecText) AS LENValue, 
       DATALENGTH(RecText) AS DATALENGTHValue, 
       RecText
FROM @textModeTest;

--Trying to get the length of the data selected
--To do this, copy the output from the "RecText" column in the query above
--and attempt to fetch the length for that
SELECT LEN('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') 
       AS VisibleTextLength;

Me: Okay, now I understand your confusion. As a matter of fact, the SSMS is working as expected.

Team member: If it’s working as expected, how can I view the actual, i.e. non-truncated value of the column? Are there any alternatives?

Me: Sure! There are not one, but at least two ways in which you can view the non-truncated data. First I will show how to fix up SSMS, and will later share a method that I prefer over SSMS for these purposes.

Method 01: Changing the maximum number of characters displayed

A simple configuration change in the SSMS can fetch upto 8192 (8K) characters when the results are displayed in Text mode (Ctrl + T). To fix this in SSMS:

1. Go to Tools –> Options
2. Navigate out to Query Results –> SQL Server –> Results to Text
3. Change the value under “Maximum number of characters displayed in each column” from 256 to 8192
4. Restart SSMS
5. Next time when the query is executed, one can notice that the SSMS displays upto 8192 characters in the related columns of the result set

Method 02: Using BCP

The method shown above has a major limitation around the number of characters being displayed – they are limited to 8192 characters. Instead of this, I generally use BCP/bulk-copy mechanisms to extract full-length data from the database.

More information about BCP and how to extract information into text files using BCP is available on my historical posts at:

• BCP & Bulk Insert
• Query Result configuration – Generate comma separated output without BCP or query changes

Me: So that’s it! Now you know not one but two methods of viewing and exporting full-length character data in SSMS or any other text file.

Team member: Thank-you so much! I never knew that SSMS was so flexible! A simply configuration change made my day!

Me: You are welcome! I am glad I could help.

Until we meet next time,

Be courteous. Drive responsibly.

Besides moving over user data, what is the second biggest task that makes one resist moving over to a new workstation? Moving and reconfiguring the user settings in various tools and IDEs.

Whenever IT rebuilds a machine for me, one of the first things I set about doing is to customize the environment settings to my preference. Recently, I had to setup a new workstation for myself and accidentally discovered a hidden gem inside of the SQL Server Management Studio for SQL Server 2012 – The Import/Export Settings wizard.

Using the Import/Export Settings Wizard

The Import/Export settings wizard allows one to export and share the SSMS settings between computers including, but not limited to the following:

• General Settings
• Code snippet locations
• Object Explorer options
• Menu & command line customizations
• Options
• Debugging
• Environment
• Text Editor

To demonstrate the usage of this feature, I have customized some of the keyboard, text editor and object explorer settings.

• Text Editor -> Tab & Indentation options for all languages – Indentation set to ‘smart’ and set tabs to save as spaces
• Designer -> Table & DB Designers –> Set ‘Auto generate change scripts’ to ON
• Object Explorer options –> Value for select (n) rows command – changed from 1000 to 10

Exporting user settings

The series of screenshots below explain exporting of user settings to a file.

To launch the Import/Export settings window in the SSMS for SQL Server 2012, go to Tools –> Import and Export Settings

The Wizard provides 3 options: 1. Export selected environment settings 2. Import selected environment settings 3. Reset all settings

Clicking on “Next” allows the user to choose which settings should be exported. (The wizard even indicates which settings might expose intellectual property information.)

The next window allows the user to choose the path and file name to store the settings to.

Once ready, click “Finish” to export the settings to the file.

Resetting all settings

The screenshots below show how to reset user settings (used in this case to simulate a workstation movement).

Choosing to reset settings allows a user to save the current settings, just in case one doesn’t have a backup. Since I just exported the settings, we will choose to proceed without exporting them again.

Clicking “Next” allows the user to choose whether to reset the settings to either SQL Server 2008 R2 (default configuration) or VS2010. We will choose to continue with the default and click “Finish”. Once the settings are reset, we find that SSMS immediately returns to a SQL Server 2008 R2 default configuration (Object Explorer and Properties windows are open). We can also confirm that all customizations are reverted back. Sometimes, it may be required to restart SSMS for the changes to take effect.

Importing User Settings

The screenshots below show the procedure to import user settings on another workstation.

Before importing, the system allows the user to save the current configuration, if required. We will proceed without saving the configuration.

Using the “Browse” button, one can select the file exported earlier and then click “Next”

From the exported file, one can choose the settings to import while choosing not to import any settings affected intellectual property information. In this example, I have selected to import everything. Clicking on “Finish” completes the import and (after restarting SSMS in some cases) one can confirm that user settings have been imported successfully.

Concluding

Versions of SSMS prior to SQL Server 2012 did not come with the Import/Export Settings wizard. However, starting SQL Server 2012, it becomes very easy to move user customizations and settings between workstations in a matter of minutes.

I have already generated my configuration backup file via the Export wizard – have you?

Question for you:

• Do you know of any other such cool tools & utilities that come built-in with SQL Server which you had planned to explore? Do share the list with me – I will be happy to explore them for you and present a step-by-step guide for their usage

Until we meet next time,

Be courteous. Drive responsibly.