Maximum Worker Thread

About Worker Thread:

When a request is received, SQL Server will try to locate an idle thread to schedule the request. If an idle worker was not located, new worker will be created. If all workers have been created and none is idle, then the request is queued which will be picked up by any worker thread from the pool of worker threads created.

The following table shows the automatically configured number of max worker threads for various combinations of CPUs and versions of SQL Server.

MWT Reference Range:

CPU CORE COUNT	MWT- 32 BIT	MWT-64 BIT
Up-to 4 processors	256	512
Octa-Core 8 processors	288	576
16 processors	352	704
32 processors	480	960
64 processors	736	1472
128 processors	4224	4480
256 processors	8320	8576

-- show Max number of worker threads on the SERVER

select serverproperty('instancename') as instancename, max_workers_count  as 'AllowedOn64Bit' from sys.dm_os_sys_info 

-- show number Current no of worker threads

select serverproperty('instancename') as instancename, COUNT(*) as MAXCOUNT  from sys.dm_os_workers

-- show the THREADPOOL wait time

select * from sys.dm_os_wait_stats where wait_type = 'THREADPOOL'

---- most of the sessions are waiting for LCK_M_S

select * from sys.dm_os_waiting_tasks

SQL Server 2000 - the application failed to start because msvcr71.dll or msvcp71.dll was not found

Error:The application failed to start because msvcr71.dll or  msvcp71.dll  was not found  

These files must be present on C:|Windows\System32 folder and also they should be present on SQL\Binn folder, either any software installation/upgrade /Anti virus might bombed these files.

Solution:

1. Search these files on the computer.
2. Paste them to SQL Binaries folder  - C:\Pf\MS\MSSQL\Binn
3. Paste them to C:\Windows\System32 folder also
4. Bring SQL Services online

Hope it helps someone! Thanks.

SQL Server Agent can't Connect-Comes Online - 2016

ERROR: SQLServerAgent could not be started (reason: Unable to connect to server 'WIN\Group'; SQLServerAgent cannot start).


Solution:

1. Connect to SQL Server Instance
2. Go to SQl Server Agent
3. Select -> right Click -> properties -> choose Connection
4. paste instance name as follows
    servername.domain.biz\instance_name
5. Click ok 

Now go to failover cluster manager and bring agent online. Issue resolved.

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Error:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Solution:

There are several solutions on this, but for us the below solution worked great.

Step 1:

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Step 2:

USE [master]
GO


EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

Step 3:

Full permissions on C:\Users\MSSQLSERVER\AppData\Local\Temp and C:Windows\ServiceProfiles\NetworkService\AppData\Local  for windows nt groups

Thanks to:

http://stackoverflow.com/questions/26267224/the-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null

Your account information could not be verified - SQL Server 2005 - SP4

ERROR:

"your account information could not be verified. Press OK to return to Authentication Mode screen to determine reason for failure.For setup to verify your credentials the services must be startable,connectable,and you must be a SQL Server Administrator"

Solution:

1) Go to the active node where SQL Server 2005 is running
2) Run the setup from CMD, SQLServer2005SP4-KBXXXXXXX-x86-ENU.exe /passive 
3) Repeat same on all nodes.

Reporting Services - Unable to Connect to Remote Server Error

This issue occurs due to the values mismatch in the RSWebApplication.config

Go to path :

C:\Program Files\Microsoft SQL Server\\Reporting Services\ReportServer

Edit the above mentioned file.

Add these 2 values:

1. http://Server/ReportServer/

 2. Change the value for SecureConnectionLevel to 0 

Issue resolved!!!

Script To Find Disk and Mount Point Details

Here you go:

https://gallery.technet.microsoft.com/Script-To-Find-Disk-and-95a0785b