Change compatibility mode of a database automatically to match the version of the SQL Server Instance hosting the db

Do you have an environment where databases get restored onto your SQL Instances from different sources? If yes, you must have come across the fact that all the databases do not originate from the same version of SQL Server. If you have a SQL 2016 Instance, it is possible that databases get restored from different Instances on different versions such as SQL 2014, SQL 2012 etc. When such a move happens, the compatibility mode of the db needs to be changed to match the level of the Instance where it is being restored to (SQL 2016 in this case). If that not done, you are not letting the db make use of all the features of SQL 2016. 

What if that db creation process is constant? How do you keep up with automating the process of checking for any dbs with lower compatibility level and then change that to match the version of the SQL Instance? Below script does exactly that for you. You an automate the process by scheduling the below script as a SQL job that runs every day.

DECLARE @BUILD VARCHAR (MAX)=substring(CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')),1,2)+'0'

DECLARE @CMD VARCHAR(MAX)

--SET @CMD= (

SELECT @CMD = 'USE master;' + (

SELECT CHAR(10)+'ALTER DATABASE '+NAME+' set compatibility_level = '+@BUILD

from sys.databases

where database_id>4

AND compatibility_level <> @BUILD

              FOR XML PATH('')

              ) + CHAR(10)

--)

      

PRINT @CMD

EXEC (@CMD)

Detach all the user dbs on a SQL Instance

What do you do when you have to detach every single user database on a given SQL Instance? Isn’t it tedious to type the detach command for every single db? If you hate doing that, you came to the right place! Use the below script to generate the detach commands which can be executed to detach the dbs. When there are active spids in a database, the detach command is going to fail. One way around that is to kill all the connections in a database. I have got an even better option. You can set the database to offline mode which kills all the active connections forcefully, set it back to online immediately and then detach the db rightaway. That will kill the connections and detach the dbs in one shot.

set quoted_identifier off

declare @dbname varchar(100)

declare @string varchar(3000)

declare cursor1 cursor for

select name from sysdatabases where dbid>4

open cursor1

fetch next from cursor1 into @dbname

while (@@fetch_status = 0)

begin

select @string="use master

go

alter database "+@dbname +" set offline with rollback immediate

go

alter database "+@dbname +" set online

go

sp_detach_db "+@dbname +"

go"

print @string

fetch next from cursor1 into @dbname

end

close cursor1

deallocate cursor1

'No Transaction is active' error - MSDTC

When running a query using linked server connection, with the linked server target being in a different domain, you might encounter this issue

When the below query is run, the query would just complete successfully.

select top 10 * from [server\instance].master.sys.all_views with (nolock)

But when you embed the query in a distributed transaction, it throws an error message:

BEGIN DISTRIBUTED TRANSACTION;  

              select top 10 * from [server\instance].master.sys.all_views with (nolock) 

              COMMIT TRANSACTION;  

             

Below is the error message:

OLE DB provider "SQLNCLI11" for linked server "server\instance" returned message "No transaction is active.".

Msg 7391, Level 16, State 2, Line 2

The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "sf-rel-sql-03\h5rel04" was unable to begin a distributed transaction.

In my case, the source SQL Instance (where the query is being run) is a clustered Instance that runs on top of two physical nodes. The destination server is in a different domain. Because the Instance is clustered, the destination server is unable to resolve the MSDTC successfully. We have two options to resolve this issue:

1.     Cluster the MSDTC resource

2.     Add the physical nodes names to the hosts file in the below folder (on the server that is being referred to in the linked server connection)

c:\windows\system32\drivers\etc\hosts

Change compatibility level of all databases after an upgrade

You make an upgrade to SQL 2016 from SQL 2014, one thing you have to watch out for is the compatibility levels of the upgraded databases. By default, all the databases will be at the compatibility level of the older version. They need to be changed manually to make use of all the new features of the upgraded version. Below is a script that can take care of that part for you!


set quoted_identifier off
declare @procname varchar(100)
declare @string varchar(1000)
declare cursor1 cursor for
select name from sys.databases where compatibility_level=120
open cursor1
fetch next from cursor1 into @procname
while (@@fetch_status = 0)
begin
select @string="alter database "+@procname +" set compatibility_level = 130"
print @string
fetch next from cursor1 into @procname
end
close cursor1

deallocate cursor1

Database Shrink file Error - 'Could not locate file' error

When you try to shrink a database log file, you might encounter the below error message:

Could not locate file 'dbname_log' for database 'DBNAME' in sys.database_files. The file either does not exist, or was dropped. 

This happens if the logical file name does not match the file name in sys.master_files. To verify that there is a file name mismatch, you can run the below commands:

Command 1:

use dbname

go

sp_helpfile

go

Command 2:

select name from sys.master_files where database_id = db_id('dbname')

You will see that the log file name is different in each one. Get the name of the log file from the second command (that appears different) and we will use that as 'oldfilename' in the command below

To fix this, we just have to run the 'alter db' command by specifying the correct file name (referred to as 'newfilename', should be something like ‘dbname_log’) as shown below:

USE [DBANAME]

GO

ALTER DATABASE [DBNAME] MODIFY FILE (NAME=N'oldfilename',NEWNAME=N'newfilename')

GO

If that didn’t do the trick try running the below command using ‘new filename’ in both places:

USE [DBANAME]

GO

ALTER DATABASE [DBNAME] MODIFY FILE (NAME=N'newfilename',NEWNAME=N'newfilename')

GO

SQL Agent does not come online in Failover Cluster Manager after upgrade to SQL 2016

I recently upgraded SQL 2014 SQL Instance in a cluster to SQL 2016. After the upgrade, the SQL Agent fails to come online and the Event Viewer shows the below error messages:

SQLServerAgent could not be started (reason: Unable to connect to server 'ServerName\InstanceName'; SQLServerAgent cannot start).

The description for Event ID 17052 from source MSSQLSERVER cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event: 

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

The error message is related to the ODBC driver 13 that is not compatible with SQL 2016. I had to upgrade that to ODBC 13.1 from the url below. Once the upgrade is done, the SQL Agent service will come back online.

https://support.microsoft.com/en-nz/help/3185365/sql-server-2016-agent-fails-to-start-or-failed-to-retrieve-data-error-when-you-try-to-read-error-log-from-ssms-2016

'VIEW SERVER STATE permission was denied on object 'server''error message

We had a developer encounter this error when trying to perform a right-click on a table and selecting 'Select top 1000 rows'. SSMS would eventually display the results, but the pop-up error message is annoying.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

ADDITIONAL INFORMATION:

VIEW SERVER STATE permission was denied on object 'server', database 'master'. (Microsoft SQL Server, Error: 300)

I was able to identify the root cause as the version of SSMS being used. In this case, the developer was trying to access SQL 2014 Instance using a SQL 2012 SSMS. The issue has gone away when SQL 2014 SSMS was used.