SQL 2016 - A severe error occurred on the current command. SQL Server Error Log / SQL Services Not coming online

The SQL Server Error log fails with the below error:

A severe error occurred on the current command.  The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)

Solution 1: Repair SSMS tools of SQL Server 2016.
Solution 2: Repair the SQL Server Instance.

Solution 3: Create alias under SQL Server network configuration.

 =======================================================================

Solution 3 will be more appropriate when we see the below error in windows event viewer along with the above mentioned error.

Severity: 16 Error:-1, OS: -1 [Microsoft][ODBC Driver 13 for SQL Server]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Solution:

https://blog.sqlauthority.com/2016/12/12/sql-server-clustered-instance-online-error-sql-server-network-interfaces-error-locating-serverinstance-specified-xffffffff/

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

Always on 2016

Use Full:

Thanks to blogger:

https://blogs.technet.microsoft.com/dataplatforminsider/2015/12/15/enhanced-always-on-availability-groups-in-sql-server-2016/

Enhancements in SQL Server 2016 Availability Groups

Use Full Link:https://blogs.technet.microsoft.com/dataplatforminsider/2015/12/15/enhanced-always-on-availability-groups-in-sql-server-2016/

Time For Excel -An error occurred when sending commands to the program

Error Screen:

Two entries in the registry solves the issue

Solution:

1. Go to HKEY_CLASSES_ROOT\Excel.Sheet.8\shell\Open\command
2. Double click default
3. Change value from
"C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE" /dde
to
"C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE" /e "%1"

Repeat the above for Excel.Sheet.12 

Thanks:
https://www.youtube.com/watch?v=xhtRoIP33H0
https://support.microsoft.com/en-in/kb/3001579

The server network address “TCP://SQLServer:5022” can not be reached or does not exist.

Thanks to Pinal Dave:

Fix/WorkAround/Solution: Try all the suggestions one by one.

Suggestion 1: Make sure that on Mirror Server the database is restored with NO RECOVERY option (This is the most common problem).

Suggestion 2: Make sure that from Principal the latest LOG backup is restored to mirror server. (Attempt this one more time even though the full backup has been restored recently).

Suggestion 3: Check if you can telnet to your ports using command TELNET ServerName Ports like “telnet SQLServerName 5023”.

Suggestion 4: Make sure your firewall is turned off.

Suggestion 5: Verify that the endpoints are started on the partners by using the state or state_desc column the of the sys.database_mirroring_endpoints catalog view. You can start end point by executing an ALTER ENDPOINT statement.

Suggestion 6: Try the following command as one of the last options.

GRANT CONNECT ON ENDPOINT::Mirroring TO ALL

Suggestion 7: Delete the end points and recreate them.

Thanks : http://blog.sqlauthority.com/2010/01/11/the-server-network-address-tcpsqlserver5023-can-not-be-reached-or-does-not-exist-check-the-network-address-name-and-that-the-ports-for-the-local-and-remote-endpoints-are-operational-microso/

SP4 on SQL Server 2005 Fails_your account Information could not be verified

SP4 on SQL Server 2005 Fails 

1. On the active node, use failover cluster manager to pause all passive nodes
2. On the active node, run and complete the SQL 2005 SP4 installation
3. On the active node, use failover cluster manager to resume all previously paused nodes
4. On all passive nodes, run and complete the SQL 2005 SP4 installation using the /passive command line switch
5. Reboot all node

This will ensue that any necessary system databases are updated as well, as opposed to only the binaries. This can be verified using the sql code below:

SELECT SERVERPROPERTY('productversion') as [SQL Version],
SERVERPROPERTY('productlevel') as [level],
SERVERPROPERTY('edition') as edition,
SERVERPROPERTY('resourceversion') as [Resource Version]

Reference: https://social.msdn.microsoft.com/Forums/en-US/89159030-1f3d-4f13-a693-8122b6bb6aae/error-installing-sql-2005-sp4?forum=sqlsetupandupgrade

Change SQL Server Collation @ Instance Level

To change the collation at the instance level we need to follow below steps:

1. Go to Setup.exe path, this will be normally as follows:
     C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\

2. Take the SQL Services Offline.

3.  Go to command prompt and choose above location:

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME= (INSTANCE NAME) /SQLSYSADMINACCOUNTS= (SVC ACC) /SAPWD=(SAPWD) /SQLCOLLATION=SQL_Latin1_General_CP850_BIN 

Instance name:

To verify the instance name  use below query
select SERVERPROPERTY('instancename')

SVC Acc:

Use service account or any domain account which has full permissions on email.

SAPwd:

If its a mixed authentication mode then we need to use SA pwd to complete the rebuild.

4. Check the summary log once its completed for verification, we can verify under instance properties also.

Imp Note: Please take system db backups to restore once we complete rebuild. Else we will loose all SQL Server jobs and Logins. 

Availability Groups SQL Querys

SQL instance is part of Availability Groups details...
=======================================
SELECT
AG.name AS [AG_Group_Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
    ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
    ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
    ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
ORDER BY [Name] ASC


To get some information about the databases in the Availability Group:
=======================================================
SELECT
AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole],
dbcs.database_name AS [DatabaseName],
ISNULL(dbrs.synchronization_state, 0) AS [SynchronizationState],
ISNULL(dbrs.is_suspended, 0) AS [IsSuspended],
ISNULL(dbcs.is_database_joined, 0) AS [IsJoined]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
ORDER BY AG.name ASC, dbcs.database_name

List of databases that are secondary in the Availability Group,  to be excluded when writes are required
================================================================================
SELECT DISTINCT
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
WHERE ISNULL(arstates.role, 3) = 2 AND ISNULL(dbcs.is_database_joined, 0) = 1
ORDER BY  dbcs.database_name

Monitoring AGs in WSFC Cluster
==========================
select * from sys.dm_hadr_cluster

select * from sys.dm_hadr_cluster_members

select * from sys.dm_hadr_cluster_networks

select * from sys.dm_hadr_instance_node_map

select * from sys.dm_hadr_name_id_map

Monitoring AGs
============
select * from sys.availability_groups

select * from sys.availability_groups_cluster

select * from sys.dm_hadr_availability_group_states

monitoring availability replicas

select * from sys.availability_replicas

select * from sys.availability_read_only_routing_lists

select * from sys.dm_hadr_availability_replica_cluster_nodes

select * from sys.dm_hadr_availability_replica_cluster_states

select * from sys.dm_hadr_availability_replica_states

select  sys.fn_hadr_backup_is_preferred_replica ('DBNAME') -- Used to determine if the current replica is the preferred backup replica.

Monitoring Availability databases :-
==============================
select * from sys.availability_databases_cluster

select * from sys.databases

select * from sys.dm_hadr_auto_page_repair

select * from sys.dm_hadr_database_replica_states

select * from sys.dm_hadr_database_replica_cluster_states

monitor the availability group listeners
=================================

select * from sys.availability_group_listener_ip_addresses

select * from sys.availability_group_listeners

select * from sys.dm_tcp_listener_states

Please refer the below queries for AlwaysOn Availability Groups monitoring reference:

    SERVERPROPERTY (Transact-SQL)

    sys.availability_group_listener_ip_addresses (Transact-SQL)

    sys.availability_group_listeners (Transact-SQL)

    sys.availability_databases_cluster (Transact-SQL)

    sys.availability_groups (Transact-SQL)

    sys.availability_read_only_routing_lists (Transact-SQL)

    sys.availability_replicas (Transact-SQL)

    sys.dm_hadr_availability_replica_cluster_nodes (Transact-SQL)

    sys.dm_hadr_availability_replica_cluster_states (Transact-SQL)

    sys.database_mirroring_endpoints (Transact-SQL)

    sys.dm_hadr_auto_page_repair (Transact-SQL)

    sys.dm_hadr_availability_group_states (Transact-SQL)

    sys.dm_hadr_availability_replica_cluster_states (Transact-SQL)

    sys.dm_hadr_availability_replica_states (Transact-SQL)

    sys.dm_hadr_database_replica_states (Transact-SQL)

    sys.dm_hadr_database_replica_cluster_states (Transact-SQL)

    sys.dm_hadr_cluster (Transact-SQL)

    sys.dm_hadr_cluster_members (Transact-SQL)

    sys.dm_hadr_cluster_networks (Transact-SQL)

    sys.dm_hadr_database_replica_cluster_states (Transact-SQL)

    sys.dm_hadr_database_replica_states (Transact-SQL)

    sys.dm_hadr_instance_node_map (Transact-SQL)

    sys.dm_hadr_name_id_map (Transact-SQL)

    sys.dm_os_performance_counters (Transact-SQL)

    sys.dm_tcp_listener_states (Transact-SQL)

    sys.fn_hadr_backup_is_preferred_replica (Transact-SQL)

MSDN Reference :- https://msdn.microsoft.com/en-nz/library/ff878305.aspx
==============
AlwaysON Dashboard :- http://www.sqlskills.com/blogs/joe/answering-questions-with-the-alwayson-dashboard/
=================

Tempdb Configuration in SQL Server 2016

More on link :

http://mostafaelmasry.com/2015/11/04/new-tempdb-configuration-in-sql-server-2016/

Thanks for nice post: 

Always ON Query's

SQL instance is part of Availability Groups details:

SELECT
AG.name AS [AG_Group_Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
    ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
    ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
    ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
ORDER BY [Name] ASC


To get some information about the databases in the Availability Group:

SELECT
AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole],
dbcs.database_name AS [DatabaseName],
ISNULL(dbrs.synchronization_state, 0) AS [SynchronizationState],
ISNULL(dbrs.is_suspended, 0) AS [IsSuspended],
ISNULL(dbcs.is_database_joined, 0) AS [IsJoined]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
ORDER BY AG.name ASC, dbcs.database_name

List of databases that are secondary in the Availability Group,  to be excluded when writes are required:

SELECT DISTINCT
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
WHERE ISNULL(arstates.role, 3) = 2 AND ISNULL(dbcs.is_database_joined, 0) = 1
ORDER BY  dbcs.database_name

Monitoring AGs in WSFC Cluster:

select * from sys.dm_hadr_cluster

select * from sys.dm_hadr_cluster_members

select * from sys.dm_hadr_cluster_networks

select * from sys.dm_hadr_instance_node_map

select * from sys.dm_hadr_name_id_map

Monitoring AGs:

select * from sys.availability_groups

select * from sys.availability_groups_cluster

select * from sys.dm_hadr_availability_group_states

monitoring availability replicas

select * from sys.availability_replicas

select * from sys.availability_read_only_routing_lists

select * from sys.dm_hadr_availability_replica_cluster_nodes

select * from sys.dm_hadr_availability_replica_cluster_states

select * from sys.dm_hadr_availability_replica_states

select  sys.fn_hadr_backup_is_preferred_replica ('DBNAME') -- Used to determine if the current replica is the preferred backup replica.

Monitoring Availability databases :

select * from sys.availability_databases_cluster

select * from sys.databases

select * from sys.dm_hadr_auto_page_repair

select * from sys.dm_hadr_database_replica_states

select * from sys.dm_hadr_database_replica_cluster_states

Monitor the availability group listeners:

select * from sys.availability_group_listener_ip_addresses

select * from sys.availability_group_listeners

select * from sys.dm_tcp_listener_states

Please refer the below queries for AlwaysOn Availability Groups monitoring reference:

    SERVERPROPERTY (Transact-SQL)

    sys.availability_group_listener_ip_addresses (Transact-SQL)

    sys.availability_group_listeners (Transact-SQL)

    sys.availability_databases_cluster (Transact-SQL)

    sys.availability_groups (Transact-SQL)

    sys.availability_read_only_routing_lists (Transact-SQL)

    sys.availability_replicas (Transact-SQL)

    sys.dm_hadr_availability_replica_cluster_nodes (Transact-SQL)

    sys.dm_hadr_availability_replica_cluster_states (Transact-SQL)

    sys.database_mirroring_endpoints (Transact-SQL)

    sys.dm_hadr_auto_page_repair (Transact-SQL)

    sys.dm_hadr_availability_group_states (Transact-SQL)

    sys.dm_hadr_availability_replica_cluster_states (Transact-SQL)

    sys.dm_hadr_availability_replica_states (Transact-SQL)

    sys.dm_hadr_database_replica_states (Transact-SQL)

    sys.dm_hadr_database_replica_cluster_states (Transact-SQL)

    sys.dm_hadr_cluster (Transact-SQL)

    sys.dm_hadr_cluster_members (Transact-SQL)

    sys.dm_hadr_cluster_networks (Transact-SQL)

    sys.dm_hadr_database_replica_cluster_states (Transact-SQL)

    sys.dm_hadr_database_replica_states (Transact-SQL)

    sys.dm_hadr_instance_node_map (Transact-SQL)

    sys.dm_hadr_name_id_map (Transact-SQL)

    sys.dm_os_performance_counters (Transact-SQL)

    sys.dm_tcp_listener_states (Transact-SQL)

    sys.fn_hadr_backup_is_preferred_replica (Transact-SQL)


Thanks To: RAJU RASAGOUNDER NZ

SQL Server Version List

	RTM (no SP)	SP1	SP2	SP3	SP4
SQL Server 2016      codename ?	CTP2.3
SQL Server 2014      codename Hekaton SQL14	12.0.2000.8	12.0.4100.1 or 12.1.4100.1
SQL Server 2012      codename Denali	11.0.2100.60	11.0.3000.0 or 11.1.3000.0	11.0.5058.0 or 11.2.5058.0
SQL Server 2008 R2      codename Kilimanjaro	10.50.1600.1	10.50.2500.0 or 10.51.2500.0	10.50.4000.0 or 10.52.4000.0	10.50.6000.34 or 10.53.6000.34
SQL Server 2008      codename Katmai	10.0.1600.22	10.0.2531.0 or 10.1.2531.0	10.0.4000.0 or 10.2.4000.0	10.0.5500.0 or 10.3.5500.0	10.0.6000.29 or 10.4.6000.29
SQL Server 2005      codename Yukon	9.0.1399.06	9.0.2047	9.0.3042	9.0.4035	9.0.5000
SQL Server 2000      codename Shiloh	8.0.194	8.0.384	8.0.532	8.0.760	8.0.2039
SQL Server 7.0      codename Sphinx	7.0.623	7.0.699	7.0.842	7.0.961	7.0.1063

The version of the report server database is either in a format that is not valid, or it cannot be read.

Problem : 

The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is '163'. The expected version is '162'. (rsInvalidReportServerDatabase)

Solution:

1. Verify the ReportServer Database compatibility.

2. Change the Compatibility to previous version and restart the services.

3. If the above fails check the RSEXEC role has been granted for the DBO

1. Open SQL Server Management Studio and connect to the database that hosts the ReportServer and ReportServerTempDB databases.
2. In Object Explorer, expand the following nodes: Databases, ReportServer, Security, Roles, and Database Roles.
3. Right-click db_owner, and click Properties.
4. On the Database Role Properties page, click Add.
5. On the Select Database User or Role page, type RSExecRole, and then click OK twice.
6. Repeat these steps with the ReportServerTempDB.
7. Restart the SSRS Services.

4. If above is already in place then it could be the latest security patch applied on the SQL Server.

5. Verify any CU/Hotfix  which has been applied and caused the issue.

6. Rollback the patch or if its on cluster apply the same patch on the other node too.

Note: Even though SSRS is not a cluster aware component But this security patch will also apply on the SQL Server and makes SSRS to stop working.

COM Surrogate has stopped working" while viewing photos in Windows Photo Viewer Win7 64 Bit

ERROR:

Solution : 

Step 1: open CMD with elivated privilages

Step 2: Run the below commands one by one.

C:\>regsvr32 vbscript.dll

C:\>regsvr32 jscript.dll

regsvr32 vbscript.dll regsvr32 jscript.dll

Could not redo log record - DB is SUSPECT Mode

We recently faced this issue after the maintenance window. The user database went to suspect mode and it was unable to bring online due to below error.
This case we rebuilt the logfile, to bring the DB online.

ERROR
Could not redo log record (1327929:29486:115), for transaction ID (1:1223281085), on page (1:1333024), database 'TestDB2 ' (database ID 8). Page: LSN = (1327558:113348:307), type = 2. Log: OpCode = 5, context 29, PrevPageLSN: (1327929:29486:18). Restore from a backup of the database, or repair the database.

Msg 3313, Level 21, State 2, Line 1

This case has only below options:

1. Restore the DB from the backup strategy.
2. Try DBCC REPAIR with REPAIR_FAST | REPAIR_REBUILD (No Data Loss)
3.  Final is REPAIR_ALLOW_DATA_LOSS

Alternate Solution:
===================

ALTER DATABASE TestDB2 REBUILD LOG ON (NAME=TestDB2_Log, FILENAME='D:\SQLLog\TestDB2_Log.ldf')
dbcc checkdb (testdb2)
ALTER DATABASE testdb2 SET MULTI_USER

Note: This approach might lead to data loss. Make sure we have enough backups in place.

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)

Solution :

1. Go to  C:\Windows\System32\drivers\etc
2. Open HOSTS File in notepad (Run as Admin)
3. Add below lines:

127.0.0.1 localhost
127.0.0.1 mycomputername -- this will be computer name

4. Save and close the notepad.
5. Connect SQL Server


Source:

http://blog.sqlauthority.com/2012/09/06/sql-server-fix-error-cannot-connect-to-login-failed-the-login-is-from-an-untrusted-domain-and-cannot-be-used-with-windows-authentication-microsoft-sql-server-error-18452/

 

How to find PID

Here in the TSQL Script to find PID Key:

USE master
GO
EXEC xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft
SQL Server\80\Registration','CD_KEY'
GO

References:

http://blog.sqlauthority.com/2007/02/28/sql-server-t-sql-script-to-find-the-cd-key-from-registry/

https://gallery.technet.microsoft.com/scriptcenter/Get-SQL-Server-Product-4b5bf4f8

Upgrades from Earlier Versions to SQL Server 2014

We can find the supported upgrades to SQL Server 2014 from the below link:

https://msdn.microsoft.com/en-us/library/ms143393.aspx

SSRS Upgrade: 

• Upgrade: You upgrade the Reporting Services components on the servers and instances where they are currently installed. This is commonly called an “in place” upgrade. In-place upgrade is not supported from one mode of Reporting Services server to another. For example, you cannot upgrade a Native Mode report server to a SharePoint mode report server. You can migrate your report items from one mode to another. For more information, see the ‘Native to SharePoint Migration’ section later in this document.
  
• Migrate: You install and configure a new SharePoint environment, copy your report items and resources to the new environment, and configure the new environment to use existing content. A lower level form of migration is to copy the Reporting Services databases, configuration files, and if you are using SharePoint mode, the SharePoint content databases.

https://msdn.microsoft.com/en-us/library/ms143747%28v=sql.120%29.aspx

Well defined steps of SSRS to new Server:

http://www.mssqltips.com/sqlservertip/2692/migrating-sql-reporting-services-to-a-new-server-by-moving-the-reporting-services-databases/

In Place Upgrade Of SQL Server:

http://sqlmag.com/sql-server-2014/migrating-sql-server-2014

Known Issues:

http://www.sanssql.com/2013/11/upgrade-error-valid-database.html

Grant execute permission to all SP's in the DB

/*
Below code will grant execute permission to all SP's in the DB.
Using below script we can grant permission to all other objects/other permission
 in the DB to diffrent users.


*/
select name into #temp_sp_list from sys.objects where type ='P' order by 1

declare @sp_count int,@sp_name varchar(50)

--select top 1 @sp_name=name from #temp_sp_list
--print @sp_name

select @sp_count=count(*) from #temp_sp_list

while(@sp_count>0)
begin
select top 1 @sp_name=name from #temp_sp_list

EXEC ('grant execute on [' + @sp_name + '] to SBUReportStaging') -- Change the user name/permission;

delete from #temp_sp_list where name=@sp_name

select @sp_count=count(*) from #temp_sp_list

print @sp_count;
end

drop table #temp_sp_list


-- Thanks to Rakesh Rao :)