Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,644,765 hits
  • Select GETDATE()

    September 2014
    M T W T F S S
    « Aug   Nov »
    1234567
    891011121314
    15161718192021
    22232425262728
    2930  
  • Advertisements

Archive for September, 2014

Tips and Tricks : Why TempDB files lesser than Configured?

Posted by blakhani on September 16, 2014


TempDB is one of the special system database which has special characteristics. Recently I was trying to simulate a problem and demonstrate the solution to tempdb PFS page contention. While doing something I realized that number of tempDB files were only 2 as opposed to 4 which I configured.

Here is the query which I used to find configured files vs actual files.

use master
select    name, physical_name 
from    sys.master_files
where    database_id = 2
go
use tempdb
go
Select    name,physical_name 
from    sys.database_files

 

Then I looked into the SQL Server ERRORLOG and found below messages during startup.

2014-09-16 17:45:56.330 spid11s      Starting up database ‘tempdb’.

2014-09-16 17:45:57.380 spid11s      Error: 5123, Severity: 16, State: 1.

2014-09-16 17:45:57.380 spid11s      CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file ‘E:\TempDB\AnotherTempDBFile.ndf’.

2014-09-16 17:45:57.380 spid11s      Error: 5123, Severity: 16, State: 1.

2014-09-16 17:45:57.380 spid11s      CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file ‘E:\TempDB\AnotherTempDBFile.ndf’.

Why this happened? Well. I had done some goof-up and pointed two instances to use same file. Which means that it is expected behavior that if tempdb database is able to initialize the primary files, it would come up and use only two files. This would also happen if there is a problem with the location of newly added files.

2014-09-16 15:31:14.290 spid11s      Starting up database ‘tempdb’.

2014-09-16 15:31:14.890 spid11s      Error: 5123, Severity: 16, State: 1.

2014-09-16 15:31:14.890 spid11s      CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘E:\TempDB\AnotherTempDBFile.ndf’.

2014-09-16 15:31:14.890 spid11s      Error: 5123, Severity: 16, State: 1.

2014-09-16 15:31:14.890 spid11s      CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘E:\TempDB\AnotherTempDBFile.ndf’.

To repro above, I have renamed the folder TempDB to something else. Note that same thing might happen if we have space issues to create new files.

 

Hope this helps.

 

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    Posted in Tips and Tricks | Tagged: , , | 3 Comments »

    Solution : Cannot add new node – Rule "SQL Server Database Services feature state" failed.

    Posted by blakhani on September 11, 2014


    While deploying SQL Server 2014 cluster in my lab I ran into this problem when I was trying to add second node (Node 2) to SQL cluster.

    —————————
    Rule Check Result
    —————————
    Rule "SQL Server Database Services feature state" failed. The SQL Server Database Services feature failed when it was initially installed. The feature must be removed before the current scenario can proceed.
    —————————
    OK
    —————————

    Above error means that when I installed SQL Server on first cluster node, it didn’t install properly. There were some errors while creating cluster and due to that AddNode is blocked. In my case also this is true because I had an issue with SQL Server Network Name Resource and it didn’t come online earlier. Below was the original error on Node 1

    Cluster network name resource ‘SQL Network Name (Balmukund)’ cannot be brought online. The computer object associated with the resource could not be updated in domain ‘MyDomain.com’ for the following reason:
    Unable to update password for computer account.

    The text for the associated error code is: Access is denied.

    The cluster identity ‘WinCluster$’ may lack permissions required to update the object. Please work with your domain administrator to ensure that the cluster identity can update computer objects in the domain.

    I fixed above error by giving proper permission to cluster computer object on Balmukund (which was the network name of SQL) After fixing that I created SQL Server and SQL Agent manually. Sometime it may also happen that SQL Agent didn’t come online on Node 1 due to some failure and it was fixed after setup was completed.

    So, if we see above rule failure then we should go to the first node and check setup log files to see if the installation succeeded or failed. If the first node installation had some failures which were fixed later then we should do a repair of the installation. Repair option can be found under the "Maintenance" page in SQL Server Installation Center (setup).  This action will clear the failure state and allow the node addition action.

    If you are lazy like me to do repair then you can also check registry key
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\ConfigurationState

    If you see value as 2 for any component then there were some failures of those components. In most of the cases since issue would be with SQL Server or SQL Agent, we might see 2 for MPT_AGENT_CORE_CNI, SQL_Engine_Core_Inst, SQL_FullText_Adv, SQL_Replication_Core_Inst

    Note: that we MUST fix the error on first node before taking shortcut of registry value. Once error is fixed, we can make the value of those component to 1 (means success)

    The highlighted value MSSQL12.SQL2014 might vary based on SQL version and Instance name. The first piece for SQL 2008 it would be MSSQL10, for SQL 2008 R2 it would be MSSQL10_50, for SQL 2012 it would be MSSQL11 and for SQL 2014 it would be MSSQL12. Second piece is instance name (for me instance name is SQL2014)

    Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Installation, Troubleshooting | Tagged: , , , | 2 Comments »

    Tips and Tricks: One Query – Multiple Servers – One Result Set

    Posted by blakhani on September 9, 2014


    How many times you have been into a situation where you need to run one query across many servers? I have asked his question from one of the DBA and his answer was I will create a linked server and then modify the query to refer linked server. Well this needs modification to server configuration and most of the production server don’t allow changed without change request.

    The better approach would be to use an under-estimated feature of SQL Server Management Studio called as Multiserver query. This can be done via by creating a group and registering one or more registered servers within the groups. Once group is created then we can query the complete group and the result format can be customized. Pinal (b|t) has written a blog here. This is a extension of that blog in little more details. 

    To get to below option we need to open “Registered Servers” Window. If you don’t see it hit Ctrl+Alt+G or go to View > Registered Servers. I have registered two server under a group called “Fake Production”. We can created your own hierarchy to logically group the servers. Display Name can also be changed in this windows. I have given a name as “HiddenName” to one of my instance for display purpose. Effectively I have 4 Servers under “Local Server Groups” and 2 Servers under “Fake Production Group”

    We can right click on any of the node and choose “New Query” as shown below.

    First thing you would notice is that the color of status bar is changed. Also notice that we can see how many servers are connected out of total servers. For demo purpose I have used different account to connect each server that’s why we are seeing “<various logins>”

    Color Customization can be done by going to Tools > Option > Text Editor > Editor Tab and Status Bar as shown below.

     

    From above, we can also change other setting for status bar but they are not specified to multi-server.

    If we ran a query in this query window, it would run on all the servers and give results.

    We are seeing same number of databases on 1st and 4th row in my lab because they are same servers. dot (.) and (local) both means local server.

    This result can be customized using “Tools > Option > Query Results > SQL Server > Multiserver Results” as below

    Note: We can’t join two tables across the servers using multiserver query feature.

    Hope this would help in getting some quick reports from various servers.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Tips and Tricks | Tagged: , , | Leave a Comment »