SQL Server Agent Not Coming OnLine After Reboot

This issue occurred recently whenever the server was rebooted SQL Server Agent was not coming online. Noticed the below error message in event viewr.

"The SQL Server Agent service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion."

Resolution As follows:

https://support.microsoft.com/en-in/help/922918/a-service-does-not-start-and-events-7000-and-7011-are-logged-in-window

1. Click Start, click Run, type regedit, and then click OK.
2. Locate and then click the following registry subkey:
   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control
3. In the right pane, locate the ServicesPipeTimeout entry.
   
   Note If the ServicesPipeTimeout entry does not exist, you must create it. To do this, follow these steps:
    
   1. On the Edit menu, point to New, and then click DWORD Value.
   2. Type ServicesPipeTimeout, and then press ENTER.
4. Right-click ServicesPipeTimeout, and then click Modify.
5. Click Decimal, type 60000, and then click OK.
   
   This value represents the time in milliseconds before a service times out.
6. Restart the computer.

Upgradation of SQL Server 2008r2 to 2014 Fails - Valid DSN, Valid Database compatibility level

The below error screen will appear during installation process, the fix is very simple step we need to configure the SSRS database, below screens are self explanatory.

ERROR SCREEN

Open SSRS Configuration Manager and  configure the report server database.

Once its configured we can see below screen.

Re-run the rules and it should go through.

SQL Server 2014 SP3 Fails - User Data Directory in registry is not valid

Below is the error screen we receive when the SQL Server SP3 Fails, please follow below steps to resolve the issue.

Resolution:

Step1: Go to regedit

Step 2: Crawl to below location
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer

Step3: If there is no DefaultDATA and DefaultLog entries, please add with the path of DATA and Log Files. If exists please verify the location details and correct it.

Step4: Restart the server.

Step5; RUn the setup again.

General - GDR, SP, CU

What's the difference in a GDR, a Cumulative Update, CTP, RC, and a Service Pack?

CTP:
Microsoft changed the terminology for SQL Server pre-releases.
They are now referred to as CTPs (Community Technology Previews).

RC:
As the product enters it's final stages before release, the feature set is complete and the product is undergoing final testing, it's called an RC (Release Candidate).

GA:
After a product has undergone significant testing and it's determined that no more changes will be made to the product before release, it's sometimes said that the product has gone golden. It's also called a GA (General Availability) release.

RTM:
Once the bits been turned over to a company to mass produce the media (CDs, DVDs, etc), it's RTM'd (Released To Manufacturing).

GDR:
Over time, Hot Fixes are created by the dev team to address specific product issues affecting certain customers. Sometimes the issue is so wide spread, a GDR (General Distribution Release) is issued so that all customers will receive the updates.


CU:
CU (Cumulative Update) is created that contains all of the applicable hot fixes.

SP:
Once a large enough collection of changes have been gathered, an SP (Service Pack) will be issued.

Latest:

An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems:

https://support.microsoft.com/en-us/help/935897/an-incremental-servicing-model-is-available-from-the-sql-server-team-t

Going ahead we need to apply only CU to SQL Server to bring the system into servicing support cycle.

More On:  https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-the-modern-servicing-model-for-sql-server/

https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ism/

EXECUTE permission was denied on the object 'sp_enable_sql_debug'

The SQL code was failing for one DB User with DBO permissions on the databases it was accessing. 

Problem: There was one stored procedure under database DB1 at some point it used to call other stored procedures from DB2. This caused failure when tried to analyse the issue using DEBUG method.

SOLUTION: Its fixed for us by doing following changes at code and DB level.

Code Level:

11)      Impersonate method

·         With execute as [login]

·         Execute as login=[login name]

22)      Fully qualified database object names

Administration Side:

11)     Grant user granted public role on master to read objects.

22)      Granting View Server State at instance level.

SQL Server 2017 - Parallel redo is started for database

There is a change from Microsoft in the servicing model, going ahead from SQL 2017 there will No Service Packs.

Any vulnerabilities hotfixes will be roll out on need basis and issues will be addressed only through CU and GDR.

Going ahead we need to apply only CU to SQL Server to bring the system into servicing support cycle. J

More On:  https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-the-modern-servicing-model-for-sql-server/

https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ism/

 Discussion Thread:

https://social.technet.microsoft.com/Forums/en-US/71eae9b7-41f4-48fd-a0e9-4c1c67bfdf65/parallel-redo-is-started-for-database-reportservertempdb-with-worker-pool-size-2?forum=sqldatabaseengine

Data and Log File Move to New Location (Move Script)

Below is the script we can use to move the data and log files for the databases except master.


DECLARE
    @counter    INT = 1,
    @max        INT = 0,
    @NEW_DATA_PATH VARCHAR(500)='E:\XXXX\Data',
    @NEW_LOG_PATH VARCHAR(500)='F:\XXXX\Log'
  
DECLARE @FILE_LIST TABLE (
    [ID]    int IDENTITY,
    [DBNAME] VARCHAR(100),
    [LNAME]   VARCHAR(100),
    [PNAME]   VARCHAR(500),
    [TYPE_DESC]  VARCHAR(50)
    )  
INSERT INTO @FILE_LIST
SELECT n.NAME,f.NAME,f.PHYSICAL_NAME,f.TYPE_DESC
FROM MASTER.SYS.MASTER_FILES f,MASTER.SYS.DATABASES n WHERE n.NAME not in('MASTER') 
and f.DATABASE_ID=n.DATABASE_ID

SELECT @max = COUNT([ID]) FROM @FILE_LIST

WHILE @counter <= @max
BEGIN

 DECLARE @FILE_TYPE VARCHAR(100),
         @DBNAME VARCHAR(100),
         @LOGICAL_NAME VARCHAR(100),
         @PHYSICAL_NAME VARCHAR(500) ;
          
 SELECT @FILE_TYPE=TYPE_DESC,@DBNAME=[DBNAME],@LOGICAL_NAME=[LNAME],
 @PHYSICAL_NAME=SUBSTRING([PNAME],LEN([PNAME])-CHARINDEX('\',REVERSE([PNAME]))+2,LEN([PNAME])-1) 
 FROM @FILE_LIST WHERE [ID] = @counter
    
    IF(@FILE_TYPE='ROWS')
    BEGIN
    --ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );
        --PRINT @FILE_TYPE+','+@DBNAME+','+@LOGICAL_NAME+','+@PHYSICAL_NAME
        PRINT 'USE MASTER 
               GO 
               ALTER DATABASE '+@DBNAME+' MODIFY FILE ( NAME = '+@LOGICAL_NAME+', FILENAME = '''+@NEW_DATA_PATH+'\'+@PHYSICAL_NAME+''')
               GO'
  END
  ELSE
   PRINT 'USE MASTER 
          GO 
          ALTER DATABASE '+@DBNAME+' MODIFY FILE ( NAME = '+@LOGICAL_NAME+', FILENAME = '''+@NEW_LOG_PATH+'\'+@PHYSICAL_NAME+''')
          GO'
     SET @counter = @counter + 1
      
 END

Invalid object name 'master.dbo.spt_values'. (Microsoft SQL Server, Error: 208)

Solution : https://www.mssqltips.com/sqlservertip/3694/fix-invalid-object-name-masterdbosptvalues-when-viewing-sql-server-database-properties/


We need to import  spt_values from other server using import and export method.

Other Sources:

https://blogs.msdn.microsoft.com/suhde/2009/04/05/invalid-object-name-master-dbo-spt_values-microsoft-sql-server-error-208/

https://blog.sqlauthority.com/2017/07/21/sql-server-invalid-object-name-master-dbo-spt_values-management-studio/

Delete Maintenance Plans

We need to perform the below steps to delete the jobs or maintenance plans in such cases from sysmaintplan_log table or fromsysmaintplan_subplans table .

Step 1: 
Execute the below query to obtain the Maintenance plan name and Id SELECT NAME, ID FROM MSDB..SYSMAINTPLAN_PLANS 
Step 2: 
Replace the Id obtained from Step 1 into the below query and delete the entry from log tableDELETE FROM SYSMAINTPLAN_LOG WHERE PLAN_ID=' ' 
Step 3: 
Replace the Id obtained from Step 1 into the below query and delete the entry from subplans table as shown below,DELETE FROM SYSMAINTPLAN_SUBPLANS WHERE PLAN_ID = ' '
Step 4: 
Finally delete the maintenance plan using the below query where ID is obtained from Step1DELETE FROM SYSMAINTPLAN_PLANS WHERE ID = ' '
Step 5: 
Check and delete the jobs from SSMS if it exists.

Many thanks to kapil

https://social.msdn.microsoft.com/forums/sqlserver/en-US/ae6f809f-ef74-4534-8327-b7b120a2d700/unable-to-delete-a-sql-2005-maintenance-plan

Cannot detach a suspect or recovery pending database. It must be repaired or dropped.

Note: Below method of removing database is only when database shows RECOVERY_PENDING because of  MDF/LDF files missing.

Solution:

ALTER DATABASE DBNAME SET OFFLINE
GO
sp_detach_db 'DBNAME'
GO


In Other Cases Please Refer to (Please Note: Check with customer for dataloss)

https://dbamohsin.wordpress.com/2012/01 /23/cannot-detach-a-suspect-or-recovery-pending-database/

Prefer: Use latest backups available to restore the database.

SSRS Key Mangement Options

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn>RSKeyMgmt /?
Microsoft (R) Reporting Services Key Manager
Version 11.0.2100.60 x86

Performs key management operations on a local report server.

  -e  extract           Extracts a key from a report server instance
  -a  apply             Applies a key to a report server instance
  -s  reencrypt         Generates a new key and reencrypts all encrypted
                        content
  -d  delete content    Deletes all encrypted content from a report server
                        database
  -l  list              Lists the report servers announced in the report server
                        database
  -r  installation ID   Remove the key for the specified installation ID
  -j  join              Join a remote instance of report server to the
                        scale-out deployment of the local instance
  -i  instance          Server instance to which operation is applied;
                        default is MSSQLSERVER
  -f  file              Full path and file name to read/write key.
  -p  password          Password used to encrypt or decrypt key.
  -m  machine name      Name of the remote machine to join to the
                        scale-out deployment
  -n  instance name     Name of the remote machine instance to join to the
                        scale-out deployment
  -u  user name         User name of an administrator on the machine to join to
                        the scale-out deployment.  If not supplied, the current
                        user is used.
  -v  password          Password of an administrator on the machine to join to
                        the scale-out deployment
  -t  trace             Include trace information in error message

To create a back-up copy of the report server encryption key:
RSKeyMgmt -e [-i <instance name>] -f  -p 

To restore a back-up copy of the report server encryption key:
RSKeyMgmt -a [-i <instance name>] -f  -p 

To reencrypt secure information using a new key:
RSKeyMgmt -s [-i <instance name>]

To reset the report server encryption key and delete all encrypted content:
RSKeyMgmt -d [-i <instance name>]

To list the announced report servers in the report server database:
RSKeyMgmt -l [-i <instance name>]

To remove a specific installation from a scale-out deployment:
RSKeyMgmt -r <installation ID> [-i <instance name>]

To join a remote machine to the same scale-out deployment as the local machine:
RSKeyMgmt -j [-i <local instance name>] -m <remote machine name>
          [-n <remote instance name>] [-u <user name> -v ]

SQL Server Cluster Uninstall Failed- rule instance removal failed

We recently faced this issue, as the server was migrated to virtual and accidentally windows cluster was removed as part of decommission. Box is left out with SQL Server Installation while uninstalling we hit the below error, once the node name was removed it went successful.

Resolution:

1. Remove the entry from regedit.

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\Cluster:ClusterName

2. Re-run the uninstall from control panel.

The EXECUTE permission was denied on the object 'xp_regread', database 'mssqlsystemresource', schema 'sys'.

Receiving 'xp_regread' error as soon as connected to SQL Server Management Studio.

OR

Execution of SSIS packages from SQL jobs fails with error: The EXECUTE permission was denied on the object 'xp_regread', database 'mssqlsystemresource', schema 'sys'

Solution:

To connect and to view the SQL Server Agent:

USE [master]
GO
GRANT EXECUTE ON xp_regread TO [public]
GO


To Execute Job:


-->

USE [master]

GO

GRANT EXECUTE ON xp_sqlagent_enum_jobs TO [public]

GO