MSSQLWIKI

Karthick P.K on SQL Server

Archive for June, 2010

I cant not backup my database from SSMS….

Posted by Karthick P.K on June 26, 2010

When i backup my SQL Server database from SSMS i get

Error:

Cannot show requested dialog.

Cannot show requested dialog. (SqlMgmt)

 

Resolution:

This happens if you have two databases with same name in Case sensitive SQL Server Instance

Example: 1. USERDB   2. userdb

 

USE   T-SQL to backup your database

Posted in Backup/Restore, SQL Server Tools, SSMS | Tagged: , , , , | 2 Comments »

We do not see the SQL server and SQL Agent status from management studio. When we right click the instance, start, stop and resume options is disabled.

Posted by Karthick P.K on June 20, 2010

From  management studio we do not see the SQL server and SQL Agent status. When we right click the instance, start, stop and resume options is disabled.

 

1. Check if are running the management studio with elevated permissions (Right click SSMS—>Run as administrator)

 

2. WMI Permission could be a cause. Enable below permission in WMI.

 

–Open Dcomcnfg

–Expand Component Service -> Computers -> My computer

–Right-click and go to the Properties of My Computer

–Select the COM Security Tab

–Click on "Edit Limits"

–Under launch and activation permissions : Add both Startup account of SQLServer service and Logged on windows account.

 

 

–On the Start menu on, click Run.

–Type wmimgmt.msc, and then click OK.

–In the Windows Management Infrastructure program, right-click WMI Control (Local), and then click Properties.

–In the WMI Control (Local) Properties dialog box, select Security tab, expand Root, and then click CIMV2.

–Click Security to open the Security for ROOT\CIMV2 dialog box. Add startup account of SQLServer service and Windows Logon account and select Remote Enable permission.

 

 

3. Use the following command to give rights to query service .

 

subinacl /SERVICE MSSQL$SQLINST /GRANT=everyone

 

NOTE: MSSQL$SQLINST > Replace With your SQLServer service name.

 

 

4. We can use below command to reset the Security policy to default.

 

secedit /configure /cfg C:\WINDOWS\inf\defltsv.inf /db defltsv.sdb /verbose

 

–>If you still have the same error even after running this.

 

5. We can ran the following WMI query to check if we get any errors:

Select * from Win32_service where name="SQLServiceName"

 

 

Steps to check SQL Service status from WMI:

–Open “WBEMTEST”

–Connect to    “root\cimv2”

–Click on notification Query

–Run the below query

–SELECT * FROM __InstanceModificationEvent WITHIN 10 WHERE TargetInstance isa ‘Win32_Service’

–Stop SQLAgent or SQLServer service

–check if you get notification query:

 

 

6. Run the below Port query and check  if any of Instance is misidentified as cluster

 

a. "IsClustered "    Should be no

b.Check browser properties to see if it Standalone

 

7.Compare the HOST name ,select serverproperty (‘servername’)  , and select @@Servername

All the above three has to match.

–> If query “select serverproperty (‘servername’)” returns wrong name

 

Check the environment variable’s and see if we have below variables defined. Below keys should be present only for clustered SQLServer and  Key  "_CLUSTER_NETWORK_NAME_" should have SQLVirtual server name in case of cluster and this Environment variable should not be present for Standalone instance.

 

_CLUSTER_NETWORK_DOMAIN_=XXXXXXXXXXXX

_CLUSTER_NETWORK_FQDN_=XXXXXXXXXXXX

_CLUSTER_NETWORK_HOSTNAME_=XXXXXXXXXXXX

_CLUSTER_NETWORK_NAME_=XXXXXXXXXXXX

 

Note: Environment variable will be defined for service under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\MSSQL$InstanceName\Environment

 

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and

Posted in Connectivity, SQL Server Tools | Tagged: , , , | 2 Comments »

How to Analyze Deadlocked Schedulers Dumps?

Posted by Karthick P.K on June 15, 2010

How to Analyze "Deadlocked Schedulers" Dumps?

Do you see "Deadlocked Schedulers" errors similar to one below and stuck?

From SQL Server Errorlog

**Dump thread – spid = 0, PSS = 0x0000000000000000, EC = 0x0000000000000000

***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0001.txt

* BEGIN STACK DUMP:

* Deadlocked Schedulers

* Short Stack Dump

Stack Signature for the dump is 0x00000000000003D0

New queries assigned to process on Node 0 have not been picked  up by a worker thread in the last 60 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time.  Use the "max worker threads" configuration option to increase number  of allowable threads, or optimize current running queries.  SQL Process Utilization: 0%. System Idle: 69%.

New queries assigned to process on Node 3 have not been picked up by a worker thread in the last 300 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 6%. System Idle: 90%.

Cause

We get Deadlocked Schedulers error (New queries assigned to process on Node n have not been picked up by a worker thread in the last 300 seconds) and dump when Scheduler Monitor detects threads(workers) are not Progressing on schedulers of one (or) all nodes for 60 seconds (300 seconds in Denali) and there are new work request (tasks) but there is no idle threads to pick up the the work request and SQL Server could not spawn new thread to process the new work request (Max worker threads reached).

Some of common causes are most of the tasks are waiting on a single resource because of resource bottleneck, excessive blocking, excessive parallelism, very long running Queries executed by all workers, all threads waiting on trace write waits etc..

SQL Server will not accept new connections or cannot process new requests  when there is deadlocked scheduler condition (Exception: deadlocked condition on single node). 

Note: In systems with multiple nodes (NUMA)  If all the threads which belong to schedulers of single node is exhausted (or) Schedulers not progressing on single node can cause deadlocked scheduler condition.

Detailed explanation about dead lock schedulers can be found in This blog from my mentor J.P

Steps to analyze "Deadlocked Schedulers" Dumps.

To analyze the dump download and Install Windows Debugger from This  link

Step 1:

Open Windbg .  Choose File menu –> select Open crash dump –>Select the Dump file (SQLDump000#.mdmp)

Step 2:

on command window type
.sympath srv*c:\Websymbols*http://msdl.microsoft.com/download/symbols;

Step 3:

Type .reload /f and hit enter. This will force debugger to immediately load all the symbols.

Step 4:

Verify if symbols are loaded for  SQL Server by using the debugger command lmvm

0:002> lmvm sqlservr
start             end                 module name
00000000`01000000 00000000`03679000   sqlservr T (pdb symbols)          c:\websymbols\sqlservr.pdb\21E4AC6E96294A529C9D99826B5A7C032\sqlservr.pdb
    Loaded symbol image file: sqlservr.exe
    Image path: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe
    Image name: sqlservr.exe
    Timestamp:        Wed Oct 07 21:15:52 2009 (4ACD6778)
    CheckSum:         025FEB5E
    ImageSize:        02679000
    File version:     2005.90.4266.0
    Product version:  9.0.4266.0
    File flags:       0 (Mask 3F)
    File OS:          40000 NT Base
    File type:        1.0 App
    File date:        00000000.00000000
    Translations:     0000.04b0 0000.04e4 0409.04b0 0409.04e4

Step 5:

Type  ~*kL 20   and look at the stack of all the threads  to find what majority of threads are doing.

Note: If you find most of your threads are waiting on stack which is not listed below please paste the stack in comments session of this blog (or) In MSSQLWIKI  face book group we will try to answer you. If you don’t get prompt reply from the community, you may need to open a support ticket with Microsoft.

1. If it is blocking issue and If most of the threads are  waiting to acquire a lock you will find the most of the stack similar to one below. (We try to acquire lock and go to wait, since someone is holding a lock)

ntdll!ZwSignalAndWaitForSingleObject

kernel32!SignalObjectAndWait

sqlservr!SOS_Scheduler::SwitchContext

sqlservr!SOS_Scheduler::Suspend

sqlservr!SOS_Event::Wait

sqlservr!LockOwner::Sleep

sqlservr!lck_lockInternal

sqlservr!GetLock

2. If most of threads are stuck while trying to write profiler events to the destination you might find stack similar to one below

ntdll!ZwSignalAndWaitForSingleObject

kernel32!SignalObjectAndWait

sqlservr!SOS_Scheduler::SwitchContext

sqlservr!SOS_Task::Sleep

sqlservr!CTraceRowsetIoProvider::GetFreeBuffers

sqlservr!CTraceWriteRequest::InitForRowsetTrace

sqlservr!CTraceRowsetIoProvider::InitializeWriteRequest

sqlservr!CTrace::WriteRecord

sqlservr!CTraceController::ProduceRecord

sqlservr!CTraceData::TracePreBatchEvent

sqlservr!CSQLSource::Execute

sqlservr!process_request

sqlservr!process_commands

sqlservr!SOS_Task::Param::Execute

sqlservr!SOS_Scheduler::RunTask

sqlservr!SOS_Scheduler::ProcessTasks

3. If your stack’s  are like one below refer http://support.microsoft.com/default.aspx?scid=kb;EN-US;974205

sqlservr!SpinlockBase::Sleep
sqlservr!SpinlockBase::SpinToAcquire
sqlservr!TSyncHashTable_EntryAccessorsqlservr!CQSIndexStatsMgr::AddNewMissingIndex
sqlservr!CIdxSuggestion::Register
sqlservr!COptExpr::PqteConvert
sqlservr!CPhyOp_Top::PqteConvert
sqlservr!COptExpr::PqteConvert
sqlservr!COptExpr::PqteConvertTree
sqlservr!COptContext::PcxteOptimizeQuery
sqlservr!CQuery::Optimize
sqlservr!CQuery::PqoBuild
sqlservr!CStmtQuery::InitQuery
sqlservr!CStmtSelect::Init

4. If you see many stacks like the one below it could be BPOOL memory pressure (or) Lazy writer  waiting on I/O

sqlservr!BPool::Steal

sqlservr!SQLSinglePageAllocator::AllocatePages

sqlservr!MemoryNode::AllocatePagesInternal

sqlservr!MemoryClerkInternal::AllocatePages

sqlservr!IMemObj::PbGetNewPages

sqlservr!CSlotPageMgr::PbAllocate

5. If you see many stacks like the one below it should be because of excessive parallelism

sqlservr!CQScanXProducerNew::Open

sqlservr!FnProducerOpen

sqlservr!FnProducerThread

sqlservr!SubprocEntrypoint

6. If you see many stacks like the one below (Many threads waiting to flush log) it should be because of disk bottleneck’s. Check if you see "I/O requests taking longer than 15 seconds" messages in Errorlog before Deadlocked Schedulers Dumps. Refer Troubleshooting sql server I/O for troubleshooting I/O issues.

sqlservr!SOS_Event::Wait

sqlservr!SQLServerLogMgr::WaitLCFlush

sqlservr!SQLServerLogMgr::LogFlush

sqlservr!SQLServerLogMgr::WaitLogFlush

sqlservr!XdesRMFull::Commit

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group MSSQLWIKI to post your SQL Server questions to SQL Server experts

Related posts:

 

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted in Debugging, Performance, SQL Server Engine | Tagged: , , , , , , , , , , | 83 Comments »

Configuring SSL for SQL Server using Microsoft Certificate Authority Server

Posted by Karthick P.K on June 12, 2010

Configuring SSL for SQL Server using Microsoft Certificate Authority Server 

Refer attached document  for detailed steps

1. Install IIS Server from ADD/Remove Windows Components (if it is not installed already)

2. Install Certificate Server ADD/Remove Windows Components (if it is not installed already)

3. OPEN Certsrv browser console by either of below mentioned ways,

A.  IIS Manager and browse to Machine Name — Web sites — CertSrv
B.  IE open
http://localhost/certsrv
C.  From IE open
http://<machinename&gt; /certsrv
e.g., http://pjhome1/certsrv

4. To Install CA (Root) Certificate

A. Click on ‘Download a CA Certificate, Certificate Chain, or CRL’

B. Click on Install this CA certificate chain

C. Click YES

D. CA chain (Root Certificate) installed successfully

5. Create a SERVER Side Authentication Certificate

A. Go to Certsrv site and click on ‘Request a certificate’

B. Click on ‘Advanced Certificate request’

C.Cick on ‘Create and submit a request to this CA’

D. Enter the certificate information

  • 1. Type the FQDN (Fully Qualified Domain Name) for the name
  • 2. Select ‘Server Authentication Certificate’ for Type of Certificate Needed.
  • 3. Check the ‘Mark Keys as exportable’ option
  • 4. Click on Submit

E. Click on YES to complete

F. We need to make a note of the ‘Request Id’ from the below screen.

6. Issue the certificate.

A. In MMC add ‘Certificates’&’Certificate Authority’ using ‘Add/Remove Snap-in’ options.

B. Click on ‘Pending requests’ in ‘Certificate Authority’.
(We would see certificate with Request ID which we generated in STEP 5.i.e., 7 here)

C. Right click on the certificate –> All Tasks –> ISSUE

D. Now we should see the certificate under ‘Issued Certificates’

7. Install the certificate
A. Click on ‘View the status of a pending certificate request’

B. Click on the certificate.

C. Click on ‘Install this certificate’

D. Click on YES

E. We will see the successfully installed screen.

8. Assign the certificate to the SQL Server instance.

A. Open SQL Server Configuration Manager
B. Right click on ‘Protocol on <instance name>’
(for the instance which we need, here it is STANDARD)

C. In the certificate tab and select the certificate we created earlier.

D. Click on Apply and restart the SQL Server instance to get this change applied.

9.After the successful deployment of the certificate (Server side) we should see the below message in our SQL Error Log file during the server startup.
The certificate was successfully loaded for encryption.

 

Regards

Karthick P.K

Posted in Configuration, Connectivity, Security | Tagged: , , , , | 5 Comments »

 
%d bloggers like this: