Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,106,067 hits
  • Select GETDATE()

    May 2024
    M T W T F S S
     12345
    6789101112
    13141516171819
    20212223242526
    2728293031  

Archive for the ‘SQLBangUG’ Category

Coverage Report – Another successful weekend spent with #SQLBangUG

Posted by blakhani on May 6, 2014


Last weekend, 3rd May 2014, was another remarkable day in history of SQL Bangalore User Group Meetings chapter. For those who are in Bangalore and have not attended the user group meeting has missed something. The meeting announcement was made on 18th April and I was one of the speaker along Sourabh, Amit, Pinal, Vinod and Kane (In order of the sessions). Amit has already given a summary and information about the sessions here (A full house–We could not have asked for more)

The theme of the meeting was “SQL Server 2014 – Community Launch” because this was the first UG after release of SQL Server 2014 to public. The response was overwhelming and till 2 May people were registering. We were sold out on eventbrite

image 

It was full day event with session lined up on SQL Server 2014 new features. We have booked a room with 150 capacity and had a clue that we are overbooked so food was ordered for higher quantity

image 

We have given enough warning to the folks who have registered about the timings.

image

At around 9:45, we were forced to close the registration because few non-registered used were allowed as they came much before 9:00 AM to make sure they don’t miss the event. Apologies to those who were sent back from reception.

image

While registrations were going on, I stated the event with some housekeeping announcement and people were still coming in

image

When I asked about repeat participants.. here was the response..

image

In less than 10 min, the room was FULL and people were standing.

image

More chairs were arrange and then once all were comfortable, Anupam started his keynote.

image

Followed by Sourabh

image

and then Amit..

image

Post lunch, we had Pinal come on stage

image

Followed by me..

image

Vinod

image

and Kane

image

These are few ripples on Facebook as feedback of the event

imageimage

imageimage

In case you missed this time, stay tuned. We would do another next month..

Cheers,
Balmukund

Posted in SQL Server User Group, SQLBangUG | Tagged: | Leave a Comment »

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 AlwaysOnPaperback, 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 AlwaysOnPaperback, Kindle
  • Posted in Security, SQL Server User Group, SQLBangUG | Tagged: , , | 3 Comments »