Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,106,067 hits

  • Select GETDATE()

    February 2014
    M T W T F S S
     12
    3456789
    10111213141516
    17181920212223
    2425262728  

Archive for February, 2014

« Previous Entries

Information : Why we can’t use system database files from “Template” folder rather than rebuilding system databases

Posted by blakhani on February 27, 2014


In my recent conversation on SQL Bangalore User Group page, I had interesting discussion about rebuilding system database in SQL Server 2008. One of the community member mentioned that we can take the master database files from below location

C:\Program Files\Microsoft SQL Server\<InstanceName>\MSSQL\Binn\Templates

and keep them to the current location of master.

I have explained that why above will not work but there is so much a write about it. So, thought of writing a blog post (for better search because Facebook comments are not shown as result by search engines)

Let’s first understand why we keep those file in that location. In earlier version of SQL (2000 and 2005) if there is a need to rebuild system databases, we need to get the installation media (DVD or network share). In SQL 2008 onwards, we don’t need DVD or media to rebuild system databases. During installation of SQL, setup does the caching of setup.exe (and other files needed for setup) and also the MDF and LDF file in Template folder. In this directory you will find master, model, and msdb database and log files that were copied from your installation source as part of setup. The path of setup.exe for SQL 2012 is “C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012”. You can easily locate similar folder for SQL 2008 or R2 as well.

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\Templates

So the logic in rebuilding the system databases is to get these files and copy them into your DATA directory to obtain your new system databases. Now, you may ask, what would happen if I just copy those files from Templates to data folder? I’ll explain then in a bit. Setup does few more things other than copy paste.

image

If we rebuild the system databases, setup would move the files from above location to the proper location and (this is important) it modifies the path of other databases which is stored in master database. When SQL was packaged, we didn’t know you are going to use F drive (or any other drive) to keep system databases so the path is from a machine where SQL product was packaged.

Let’s see what would happen if I just move the files and start SQL Server. Well, you might have guessed it, we won’t be able to start SQL. Notice the path and filename which I highlighted below.

2013-12-06 16:00:51.61 spid9s      Starting up database ‘mssqlsystemresource’.
2013-12-06 16:00:51.62 spid5s      Error: 17204, Severity: 16, State: 1.
2013-12-06 16:00:51.62 spid5s      FCB::Open failed: Could not open file e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBData.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
2013-12-06 16:00:51.62 spid5s      Error: 5120, Severity: 16, State: 101.
2013-12-06 16:00:51.62 spid5s      Unable to open the physical file "e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBData.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
2013-12-06 16:00:51.62 spid5s      Error: 17207, Severity: 16, State: 1.
2013-12-06 16:00:51.62 spid5s      FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBLog.ldf’. Diagnose and correct the operating system error, and retry the operation.
2013-12-06 16:00:51.62 spid5s      File activation failure. The physical file name "e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBLog.ldf" may be incorrect.
2013-12-06 16:00:51.67 spid9s      The resource database build version is 11.00.3000. This is an informational message only. No user action is required.
2013-12-06 16:00:51.98 spid9s      Starting up database ‘model’.
2013-12-06 16:00:51.98 spid9s      Error: 17204, Severity: 16, State: 1.
2013-12-06 16:00:51.98 spid9s      FCB::Open failed: Could not open file e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\model.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
2013-12-06 16:00:51.98 spid9s      Error: 5120, Severity: 16, State: 101.
2013-12-06 16:00:51.98 spid9s      Unable to open the physical file "e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\model.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
2013-12-06 16:00:51.99 spid9s      Error: 17207, Severity: 16, State: 1.
2013-12-06 16:00:51.99 spid9s      FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\modellog.ldf’. Diagnose and correct the operating system error, and retry the operation.
2013-12-06 16:00:51.99 spid9s      File activation failure. The physical file name "e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\modellog.ldf" may be incorrect.
2013-12-06 16:00:52.02 spid9s      Error: 945, Severity: 14, State: 2.
2013-12-06 16:00:52.02 spid9s      Database ‘model’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

This is one of the error which you might also see when SQL installation is not complete and then SQL Service not getting started. People have posted on various forums about this problem. The root cause of the issue was that SQL Setup couldn’t alter the location for other system databases and hence looking for some weird path. If its SQL 2008 you might see path as e:\sql10_main_t\sql\mkmastr\databases\objfre\i386\

Here is the information taken from SQL Installation logs which confirms our theory. They are from my own machine

  • Files getting copied

(01) 2013-07-02 10:24:27 SQLEngine: : Installing system database files
(01) 2013-07-02 10:24:27 Slp: Sco: File ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf’ does not exist
(01) 2013-07-02 10:24:27 SQLEngine: : Copying database file ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Templates\master.mdf’ to ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf’
(01) 2013-07-02 10:24:27 Slp: Sco: Attempting to copy file ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Templates\master.mdf’ to file ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf’
(01) 2013-07-02 10:24:27 Slp: Sco: Attempting to get security descriptor for file ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Templates\master.mdf’
(01) 2013-07-02 10:24:27 Slp: Sco: Returning security descriptor O:SYG:SYD:AI(A;ID;FA;;;SY)(A;ID;FA;;;BA)(A;ID;0x1200a9;;;BU)(A;ID;0x1200a9;;;S-1-5-80-3880718306-3832830129-1677859214-2598158968-1052248003)

  • Same operation done for mastlog.ldf, model.mdf, modellog.ldf, MSDBData.mdf and MSDBLog.ldf as well.
  • Then SQL is started with various trace flags, parameters as shown below.

(01) 2013-07-02 10:28:08 SQLEngine: –SqlServerServiceSCM: Starting SQL via SCM (-mSqlSetup -Q -qSQL_Latin1_General_CP1_CI_AS -T4022 -T4010 -T3659 -T3610 -T8015)…

  • A connection is made and the various scripts would be executed to modify the information in files picked from template folder.

(01) 2013-07-02 10:28:19 SQLEngine: –SqlDatabaseServiceConfig: Connection String: Data Source=\\.\pipe\SQLLocal\MSSQLSERVER;Initial Catalog=master;Integrated Security=True;Pooling=False;Connect Timeout=300;Network Library=dbnmpntw;Application Name=SqlSetup
(01) 2013-07-02 10:28:19 SQLEngine: : Checking Engine checkpoint ‘ServiceConfigConnect’
(01) 2013-07-02 10:28:19 SQLEngine: –SqlDatabaseServiceConfig: Connecting to SQL….
(01) 2013-07-02 10:28:19 Slp: Sco: Attempting to connect script
(01) 2013-07-02 10:28:19 Slp: Connection string: Data Source=\\.\pipe\SQLLocal\MSSQLSERVER;Initial Catalog=master;Integrated Security=True;Pooling=False;Connect Timeout=300;Network Library=dbnmpntw;Application Name=SqlSetup
(01) 2013-07-02 10:28:19 Slp: Connected successfully…
(01) 2013-07-02 10:28:19 SQLEngine: –SqlDatabaseServiceConfig: Running queries SQL….
(01) 2013-07-02 10:28:19 SQLEngine: : Install script parameters:
(01) 2013-07-02 10:28:19 SQLEngine: :  Instance Name: MSSQLSERVER
(01) 2013-07-02 10:28:19 SQLEngine: :  Sql Collation: SQL_Latin1_General_CP1_CI_AS
(01) 2013-07-02 10:28:19 SQLEngine: :  System Data Dir: Microsoft.SqlServer.Configuration.Sco.SqlDirectory
(01) 2013-07-02 10:28:19 SQLEngine: :  Tempdb Dir: Microsoft.SqlServer.Configuration.Sco.SqlDirectory
(01) 2013-07-02 10:28:19 SQLEngine: :  Tempdb Log Dir: Microsoft.SqlServer.Configuration.Sco.SqlDirectory
…

 

To conclude, you should not use system database files from “Templates” folder as it is by using copy paste method. You must use /ACTION=REBUILDDATABASE

Hope this would explain a little about usage of template folder.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in SQL Server User Group, SQLBangUG | Tagged: , , , , , | 8 Comments »

    Did you know? How non-sysadmin can change their own password in SQL Server?

    Posted by blakhani on February 25, 2014


    How would you feel if you are not able to change password of your own account? In almost all website, every login/user can change his/her own password. An Administrator need not change password for everyone and keep whispering via email. Login in the SQL Server should also be able to do so. Correct? Let’s try.

    I have created a SQL login in SQL Server using below T-SQL.

    USE [master]
GO
CREATE LOGIN [SQLServer-Help]
    WITH password = N'Initial@Password'
GO

    There is no super power given to the account SQLServer-Help. He is part of pubic role. I have logged into SQL Server using this account and the password. I was surprised to see that my attempt to change my own password failed. 

    USE [master]
GO
ALTER LOGIN [SQLServer-Help] WITH PASSWORD=N'pass@word1'
GO

    Here is the error which I received

    Msg 15151, Level 16, State 1, Line 1

    Cannot alter the login ‘SQLServer-Help’, because it does not exist or you do not have permission.

    if I make the account SysAdmin then it works. As per http://msdn.microsoft.com/en-us/library/ms189828.aspx

    {

    A principal can change the password, default language, and default database for its own login.

    }

    There is something I am doing which is not right. Why should a Sysadmin rights be given to change password and that too my own password. Now, try to co-relate with the other websites where you change the password. You have been asked for old password… and that was it! There is a parameter in ALTER LOGIN called old_password. 

    USE [master]
GO
ALTER LOGIN [SQLServer-Help] WITH PASSWORD=N'pass@word123' old_password = N'Initial@Password'
GO

    After learning this I realized that why there is a textbox called “Specify old password” in the Login Properties Screen (Highlighted below)

    image

    By default checkbox is unchecked and that’s why I never used it.

    This brings up and interesting question. “If I know someone’s old password can I change it via logging from my account?” To test this, I created new account called FarFarAway and tried changing from SQLServer-Help login. 

    select ORIGINAL_LOGIN()
go
USE [master]
GO
ALTER LOGIN [FarFarAway] WITH PASSWORD=N'pass@word123' old_password = N'sa'
GO

    Here is the error I received.

    Msg 15151, Level 16, State 1, Line 1

    Cannot alter the login ‘FarFarAway’, because it does not exist or you do not have permission.

     

    image

    So the answer is “No. To change someone else’s password, login should have “ALTER ANY LOGIN” permission”

    Well, I didn’t know this till someone posted in SQL Bangalore User Group Facebook page. There is a lot of action there. It’s like a Mini SQL Forum. Join there for more learning!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in Security, SQL Server User Group, SQLBangUG | Tagged: , , | 3 Comments »

    A-Z of In-Memory OLTP : Enhancement in Catalog Views

    Posted by blakhani on February 20, 2014


    During my interaction with customers, catalog views and DMV words are used interchangeably. In reality, there is a difference between the two. Till SQL Server 2000, there were system table like sysdatabases, sysobjects etc. SQL 2005 and above the new concept of catalog view was introduced which is replacement of system tables. It is the way to see the catalog metadata of SQL Server. The data showed in the catalog view is physically stored on the disk. On the other hand, DMV is a dynamic management view. It’s similar to sysprocesses which we have used in SQL 2000. As soon as query is fired, the data is read from memory structures and showed in row-column format.

    In this blog, I’ll try to show the changes to existing catalog view to support In-Memory OLTP (code name Hekaton)

    sys.tables

    There are few new columns introduced in sys.tables to get details about In-Memory Tables.

    Column Name Values
    durability  0 = SCHEMA_AND_DATA
    1 = SCHEMA_ONLY
    durability_desc  Description of value in durability Column
    is_memory_optimized  1 – Yes, 0 – No

     

    In my database, I have created two tables; One disk based and one in-memory optimized.

    SELECT name
    ,durability
    ,durability_desc
    ,is_memory_optimized
FROM sys.tables

    image

     

    sys.table_types

    There is a new column is_memory_optimized is introduced. I would create a type using below T-SQL taken from Books online

    Use SQLSeverHelp_IMO
go
CREATE TYPE dbo.SalesOrderDetailsType AS TABLE
(
       so_id int NOT NULL,
       lineitem_id int NOT NULL,
       product_id int NOT NULL,
       unitprice money NOT NULL,

       PRIMARY KEY NONCLUSTERED (so_id,lineitem_id)
) WITH (MEMORY_OPTIMIZED=ON)
GO

    Now, look at catalog view.

    select  name, is_memory_optimized 
from    sys.table_types

    image

    sys.indexes

    With the introduction of Hash Indexes, there is new value for type_desc column as shown below.

     SELECT object_name(object_id) TableName
    ,name 'IndexName'
    ,type_desc
FROM sys.indexes
WHERE object_id IN (
        SELECT object_id
        FROM sys.tables
        WHERE is_memory_optimized = 1
        )

    image

    sys.index_columns

    No Change in the columns but it’s important to note that since there is no sorting order in HASH Indexes, the column is_descending_key is shown as 0 which is ignorable.

    sys.data_spaces

    New values available in type column as below.

    Column Name Values
    type  FX = Memory-optimized tables filegroup
    type_desc  MEMORY_OPTIMIZED_DATA_FILEGROUP

     

    use SQLSeverHelp_IMO
go
select  * 
from    sys.data_spaces

    image

     

    sys.sql_modules

    A new column uses_native_compilation has been added to identify if it’s a natively compiled procedure. Same is true for sys.all_sql_modules as well. We already have a proc in the database. 

    Select object_name(object_id), 
    uses_native_compilation  
from  sys.sql_modules

    image

     

    In next post, we would explore DMVs related to In-Memory OLTP.

     

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in A - Z Series, In Memory OLTP, In-Memory OLTP, SQL Server 2014 | Tagged: , , , , , , , , , , , , | Leave a Comment »

    « Previous Entries