Lunch time for me, is time to refresh myself. It’s a time when I try to daily to take 10-15minutes out of my lunch time to close my E-mail client, stop all work and take my mind away from the day’s work. I spend the time learning about anything I am in the mood for – science, sports and SQL Server (of course!).

If you use a train or a bus to commute to and from work, you might be using your smart phone to check out your personal E-mail, listen to music or surf the Internet for social networking updates. Take 15 minutes from the journey to learn something new, and you will find yourselves charged up and ready to take on the day head-on.

Recently, we have a new kid on the block. Jacob Sebastian, the founder of BeyondRelational.com has introduced a new section on the website that allows reader to learn something new each day! The rules are simple – if you learnt something today, share it with everyone! The section is aptly called – Just Learned.

What’s more – by indicating whether you learned something (or knew a piece before hand) and whether you liked a tip or not, your knowledge score on BeyondRelational.com is boosted. You would not believe how much I learn by just spending 5 minutes on Just Learned each day! Here are some examples:

• SSMS Allows you to ask a question in the MSDN forum directly from SSMS
• A quick and easy alternative to print (output) strings (especially TSQL queries) longer than 800
• You can drop multiple tables using single DROP statement
• Semicolon can be used to execute a procedure
• Arithmetic operations with $
• $ is equal to zero
• Output table as HTML table
• Copy and paste a SQL Server database from one server to another using SSMS GUI Wizard

Isn’t hanging out on BeyondRelational.com’s Just Learned section cool?

A golden rule to remember when posting

There is a reason why the above heading is in Red. Whenever you post anything online, please ensure that you pass on due, visible credit to the original author. Also, Remember that some authors might require an express written permission in advance of you publishing their content somewhere. Even after passing due credit and obtaining the permissions, ensure that you do not publish more than 25% of the original content.

So, what are you waiting for? How will you use your lunch/post-dinner learning time today? If you learnt something today, Share it!

Until we meet next time,

Be courteous. Drive responsibly.

Ever since anyone starts learning Microsoft SQL Server, a misconception sets in. The misconception is that data and log files can be defined as under:

1. File with extension – .mdf – is a Primary data file
2. File with extension – .ndf – is a Secondary data file
3. File with extension – .ldf – is a log file

These extensions are the default extensions that are used by Microsoft SQL Server for the default system and sample databases available. Default extensions are good because it helps us to set some sort of uniformity across multiple Microsoft SQL Server environments.

But, the situation is more like the difference between a “recommended configuration” and “minimum configuration”. MDF, NDF and LDF are recommended configurations. But, SQL Server does not really care about the file extension. Here’s a little demo (while this demo shows screenshots of SQL 11 (“Denali”) CTP01, this is also valid for previous releases of Microsoft SQL Server):

/*************************************************************************************
CREATE A DATABASE SUCH THAT:
Primary Data File Extension   - prf
Secondary Data File Extension - sdf
Log File Extension            - dbl

NOTE: This script is provided "AS IS" and without Warranty. This script is for 
      demonstration purposes only.
*************************************************************************************/
CREATE DATABASE [ExtensionTestDB]
    ON 
    PRIMARY(NAME = [DB_DEFAULT], 
            FILENAME = 'E:DatabasesDB_DEFAULT.prf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 51200 KB), 
    FILEGROUP [DB_DATA_FG](NAME = [DB_DATA], 
                           FILENAME = 'E:DatabasesDB_DATA.sdf', 
                           MAXSIZE = UNLIMITED, 
                           FILEGROWTH = 102400 KB)
    LOG ON (NAME = [DB_LOG], 
            FILENAME = 'E:DatabasesDB_LOG.dbl', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 102400 KB) 
    COLLATE SQL_Latin1_General_CP1_CI_AS
GO

When we run the above script to create a new database, we find that the database creation is successful. In a new Query window connecting to the same SQL Server instance, we can fire a query using the sys.sysfiles catalog view to confirm that the SQL Server is indeed okay with the non-default extensions.

USE [ExtensionTestDB]
GO
SELECT * FROM sys.sysfiles

I hope that this post helps clear out the misconception around the default primary/secondary data and log file extensions.

Until we meet next time,

Be courteous. Drive responsibly.

SSMS, or SQL Server Management Studio is a wonderful tool, and those who frequent this blog will notice that I tend to try and make day to day task simpler using the SSMS. In a development environment, databases are created and backups restored often. Most developers have rights to the development instance, and sometimes we run into problems with application developers going out of their comfort zone and attempting to be “accidental DBAs”.

This true not only for software, but for the world around us as well. When we undertake transatlantic journeys, it takes a while for us to get used to the tools and technology available because it’s different. I am quite sure that most have caused the untimely death of a costly electronic device because we failed to realize that the US works on a 110V supply, while the UK on a 220-240V supply.

Similarly, not that there is anything wrong in being an “accidental DBA”, but minor product issues do cause a usability annoyance when somebody who is not familiar with the tool (SSMS) attempts to use it.

The scenario

Let’s create a test database on a given SQL Server instance such that the database has at least two data files, and one log file (you can have more, no issues).

Once the database is created, we will fire the following query to confirm the file names and file paths:

USE TestDB
GO
SELECT * FROM sys.sysfiles

Now, let’s take a full backup of the database, and move the backup to another server where another instance of Microsoft SQL Server is running. As mentioned in my previous post earlier this week, check the default location of the data files for that instance.

Now, invoke the Restore wizard to attempt a restore of the database. Select the backup file location and check the appropriate backup set in the “General” tab.

Now, navigate to the “Options” tab and notice the default file names provided as part of the restore.

Simply clicking “OK” without modifying these paths will result in an exception:

TITLE: Microsoft SQL Server Management Studio
——————————
Restore failed for Server ‘VPCW2K8DENALI’.  (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.1103.9+((SQL_PreRelease).100924-2125+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: File ‘E:Program FilesMicrosoft SQL ServerMSSQL11.DENALIMSSQLDATAMyTestDB.mdf’ is claimed by ‘NAV_SECONDARY'(3) and ‘NAV_DATA'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.1103.9+((SQL_PreRelease).100924-2125+)&LinkId=20476
——————————
BUTTONS:
OK
——————————

This exception is coming because we did not modify the default file names. As you can see in the screen-shot, both logical files – NAV_DATA and NAV_SECONDARY have the same physical file location & name – E:Program FilesMicrosoft SQL ServerMSSQL11.DENALIMSSQLDATAMyTestDB.mdf

Simply changing the name to the logical file names (NAV_DATA.mdf and NAV_SECONDARY.mdf) would rectify the issue and the restore will succeed.

Microsoft Connect Case #668566

I had filed a Microsoft Connect bug report here – https://connect.microsoft.com/SQLServer/feedback/details/668566#details

I am pleased to announce that the bug has been fixed by Microsoft and will be available in the next public release of Microsoft SQL Server (code named “Denali”). Thank-you, Microsoft!

Until we meet next time,

Be courteous. Drive responsibly.

Today’s post takes me back to the days when I was learning SQL Server at the engineering school about 8 years ago. I learnt SQL 7 and SQL 2000 and then as I joined my present employer, SQL 2005 came along. While SQL 7/2000 had the Enterprise Manager, SQL 2005 onwards we have the SSMS – SQL Server Management Studio. Irrespective of the tool, each of these had wizards for Creating and Restoring a database.

Whenever we create or restore a database, we can see that the SSMS fills in a default path for placing the data and log files. This path is set during the Server installation, and is normally set to %PROGRAMFILES%Microsoft SQL ServerMSSQL11.(InstanceName)MSSQLDATA on a SQL 11 (Code Named: “Denali”) CTP 01 instance (Previous releases of SQL Server also follow a similar path).

Generally, I prefer to place the data and log files on a different physical drives whenever possible. I won’t go into the details of why I do this right now, but it is important to know that for most cases, it is a recommended best practice. It becomes an annoyance to change the data and log file paths every time I need to create or restore a database. Hence, I typically change the default file paths at the server instance level.

Here’s how you can do it yourself:

1. Launch the SQL Server Management Studio
2. Open the Object Explorer by going to View –> Object Explorer and connect to the SQL Server instance under consideration
3. Right-click the instance name and click on “Properties”
4. In the server properties window, navigate out to the Database Properties tab
5. Change the Data & Log file paths under the “Database Default locations” section

Once changed, you can then see that these changes are used by default by the Create Database and Restore Database wizards. On my test instance, I don’t have multiple drives to spread the data/logs across, hence I have added the default locations as E:DatabasesData for the data files and E:DatabasesLogs for the Log Files.

Until we meet next time,

Be courteous. Drive responsibly.

A couple of months ago, I ran into a strange issue. Whenever I fired the “RECONFIGURE” statement, the SQL Server returned an error message – “Ad hoc update to system catalog is not supported.” I was able to trace this to the fact that somebody had set the “allow updates” setting on the server to 1. This setting is 0 by default on a new install of SQL Server. This setting was used in SQL Server 2000 to allow direct ad-hoc updates to system catalogs and tables. This setting is now deprecated since SQL Server 2005 and all SQL Server installations come with this set to 0 by default. Per Books On Line (read the BOL help on this option here), this setting has no effect, except that the RECONFIGURE statement will fail, just as it was doing in my case.

The root cause in my case was that it was a shared environment and because this configuration switch lies in full public view when sp_configure is run, some novice developer attempting to learn SQL Server inadvertently set the switch but forgot to reset the same. You can read about my experience here: http://beyondrelational.com/blogs/nakul/archive/2011/02/14/ad-hoc-update-to-system-catalogs-is-not-supported-when-using-reconfigure.aspx

Just recently, I ran into the problem again – this time on a friend’s test configuration. That’s what prompted me to open a case in Microsoft Connect. You can find the case filed here: https://connect.microsoft.com/SQLServer/feedback/details/662305/sp-configure-allow-updates-should-be-an-advanced-option

My belief is that if something is deprecated, and is not meant to be used – it should not be in obvious view. We don’t keep unused stuff on the front lawns – we stuff it away in the garages and therefore, this option should at least be made an advanced option.

If you agree with me, please vote for fixing this at: https://connect.microsoft.com/SQLServer/feedback/details/662305/sp-configure-allow-updates-should-be-an-advanced-option

Until we meet next time,

Be courteous. Drive responsibly.