Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,037,789 hits

  • Select GETDATE()

    March 2023
    M T W T F S S
     12345
    6789101112
    13141516171819
    20212223242526
    2728293031  

Posts Tagged ‘startup’

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
    • Advertisement

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

    A-Z of In-Memory OLTP : Troubleshooting database Startup and recovery failures

    Posted by blakhani on January 2, 2014


    As promised in my previous blog, I am going to demonstrate few failures of database startup and technique to find the cause and fix them. First trick which you should always remember is that whenever you see any “operating system error” with some number (2, 5, 32 etc.) try to find the text of that error number. The trick here is to use “Net HelpMSG” windows command to convert the number to text. Here are few quick samples.

    C:\WINDOWS\system32>net helpmsg 2
    The system cannot find the file specified.

    C:\WINDOWS\system32>net helpmsg 5
    Access is denied.

    C:\WINDOWS\system32>net helpmsg 32
    The process cannot access the file because it is being used by another process.

    image

    Now, we would first cause DLL generation failure during startup of database. As learned in earlier post, database startup (a.k.a. recovery) process would fail.

    image

    Here is what we would see in ERROLROG.

    2013-12-30 03:25:44.280 spid51       Starting up database ‘SQLSeverHelp_IMO’.
    2013-12-30 03:25:44.520 spid39s      restoreHkDatabase: DbId 8, Msg 41312, Level 16, State 4, Unable to call into the C compiler. GetLastError = 2.
    2013-12-30 03:25:44.520 spid39s      [ERROR] Database ID: [8]. Failed to load XTP checkpoint. Error code: 0x82000008. (e:\sql12_main_t\sql\ntdbms\hekaton\sqlhost\sqlmin\hkhostdb.cpp : 2558 – ‘RecoverHkDatabase’)

    Now, you would understand why I spoke about Net Helpmsg command. Error 2 = The system cannot find the file specified. Now, next task is to find out which path/file. To find any activity done by a process, one of the famous tool to use is Process Monitor (free tool by Mark Russinovich). I have run the tool and applied filter for Process Name = sqlservr.exe. Here is the filtered information from process monitor.

    image

    It means that SQLServr.exe is looking for CL.EXE under C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\Xtp\VC\bin folder and unable to find the path. Indeed that is the problem. To do this failure, I have renamed bin folder to bin_xxx as shown below.

    image

    Once I renamed the folder back to “bin”, I was able to bring the database online.

    I have simulated another failure of DLL generation by making some changes on operating system level and restarted SQL Server.

    image

    Always check errorlog and here is what we would see.

    2013-12-31 20:01:35.040 spid20s      SetNamedSecurityInfoW failed for directory C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\xtp for reason 5.

    2013-12-31 20:01:35.040 spid20s      Error: 41334, Severity: 16, State: 2.
    2013-12-31 20:01:35.040 spid20s      The code generation directory ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\xtp’ cannot be created or set up correctly.
    2013-12-31 20:01:35.060 spid20s      Error: 41334, Severity: 16, State: 2.
    2013-12-31 20:01:35.060 spid20s      The code generation directory ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\xtp’ cannot be created or set up correctly.
    2013-12-31 20:01:35.060 spid20s      Error: 41334, Severity: 16, State: 2.
    2013-12-31 20:01:35.060 spid20s      The code generation directory ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\xtp’ cannot be created or set up correctly.
    2013-12-31 20:01:35.170 spid19s      Error: 41334, Severity: 16, State: 2.
    2013-12-31 20:01:35.170 spid19s      The code generation directory ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\xtp’ cannot be created or set up correctly.

    Important piece of error message is reason 5. If you recall what you read above, net helpmsg 5 = Access Denied.

    How would you troubleshoot? Again same tool, process monitor. If we filter for all access denied (right click on Access Denied text and say "Include"), it’s clear that Service account is trying to generate the DLL of table but unable to do so.

    image

    Checking properties of XTP folder reveals the same. To break the things, I given “deny” on that folder to SQL Service account.

    image

    Once I removed “deny” and gave “allow”, I was able to bring database online using below command

    Alter database SQLSeverHelp_IMO set online
go

    Another possible reason of database not able to recover would be out-of-memory errors. Since all data needs to be loaded into memory before in-memory tables can be accessed. In-memory engine needs to have all the memory available to hold the data at startup of the database. If the system is low on memory or max server memory is set to too low, we might encounter recovery failure. In such situation, you may want to stop other processes running on the server so that SQL is having enough RAM to load in-memory tables.

    I hope that this blog has given you some hints about possible failures, troubleshooting tricks and their resolutions.

  • 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: , , , , , , , , , , , , | 11 Comments »