Find out the backup lcoations/paths for multiple SQL Instances

If you are trying to consolidate backup dump zones/locations, we first need to analyze where the databases are being backed up to. Below query will give you the location of where the databases were backed up to. Run this in Central Management server and you can get a report of all the SQL Instances in one shot

SET NOCOUNT ON
SELECT CONVERT(CHAR(30), SERVERPROPERTY('Servername')) AS instance_name,
CONVERT(CHAR(30),bs.database_name) AS database_name,
CONVERT(CHAR(125),bf.physical_device_name) backup_path
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bf
ON bs.media_set_id = bf.media_set_id
JOIN master.dbo.sysdatabases db
ON bs.database_name = db.name
WHERE bs.backup_finish_date = (
      SELECT MAX(backup_finish_date)
      FROM msdb.dbo.backupset
      WHERE database_name = bs.database_name
            AND type = 'D'
      )
      AND (DATABASEPROPERTYEX(bs.database_name,'Status') = 'ONLINE'
      AND DATABASEPROPERTYEX(bs.database_name,'IsInStandby') = 0)

Unable to open dts packages from SQL 2008 Management Studio

I had to open up a dts package from SQL 2008 Management Studio and some additional installs are required to open it up. Microsoft documentation mentioned that ‘SQL Server 2000 dts designer components’ and ‘2005 backward compatibility tools’. I installed both of them today morning, but still was unable to open the package.

That’s when one of the DBA’s from our team suggested another fix that needed to be done.

There is something called ‘Environment Variables’ in System Settings that lets programs look for the program files of that package, in a certain order. Part of the environment variables looks like the below:

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\; C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;

DTS designer installs the files in folder 80 (the second one in order). When initiated, dts designer looks in order from left to right, one path after the other, as mentioned in the environment variables. In this case, it was looking for the files in order left to right, but when it could not find anything, it just kept on giving the error message saying ‘2000 DTS designer tools need to be installed’. Though it was installed, it was not able to invoke it because of the order in which the environment variables were setup. I had to change the order to as shown below.

C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\; C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;

That’s when it worked, and I am able to view the dts package now.

The order in which the system variables are setup initially was because we installed a 2000 tools after 2008. It just took the order of the installs and placed them one after the other as they were installed. Once I re-arranged them, it worked.

Kill all SPID's in a database

One way to kill the the connections in a database is by setting it to 'offline' mode with rollback immediate and then setting it back to 'online' mode. The rollback immediate clause kill all the connections by force.

use master

go

alter database dbname set offline with rollback immediate

go

alter database dbname set online

go

Another way of killing all the connections to a database is by identifying all the SPID's in that database and killing all of them. It can be done using a simple script below. Just use the appropriate database name in place of 'dbname' below.

use master

go

set quoted_identifier off

declare @cmd nvarchar(200)

declare @spid varchar(100)

declare varcursor cursor for

select spid from master..sysprocesses where db_name(dbid)='dbname'

open varcursor

fetch next from varcursor into @spid

while (@@fetch_status=0)

begin

select @cmd= 

"USE [master]

kill "+@spid+"

"

print @cmd

exec sp_executesql @cmd

fetch next from varcursor into @spid

end

close varcursor

deallocate varcursor

Compare SQL Server 'Max Server Memory' with the Physical Server Memory of the Windows Server

Ideally, the SQL Server's max server memory settings should be configured at 80% of the total phyical memory of the Windows Server. That will ensure the OS has enough memory to run all the other processes besides the SQL. If the SQL Server's max server memory is assigned to a value higher than 80% of the total server memory, SQL captures all the memory within its limit when needed and the OS starts feeling the pressuere. If you have any monitoring tools to page you when the available server memory falls below 5%, we will get these annoying alerts.

All that is needed in this case is properly configure the SQL's max server memory to not more than 80% of the server memory. What if you needed to run this check across all the Instances in your company? The below query will give you a quick snapshot of those details in terms of percentage. This query captures the SQL settings from the system table called 'sys.configurations' and physical server memory details from the dmv called 'sys.odm_os_sys_info'. Run this query in Central Management Server and it will give you some valuable information.

Use the below query for SQL 2005, 2008, 2008 R2:

declare @mem1 int

declare @mem2 int

select @mem1 = (select (physical_memory_in_bytes/1048576) as server_memory_in_MB from sys.dm_os_sys_info)

select @mem2 =(select convert(decimal,value) from sys.configurations where name='max server memory (MB)')

select @mem2 as SQL_Max_Memory, @mem1 as Physical_Server_Memory,

cast((cast(@mem2 as float)/ cast(@mem1 as float)) as decimal(4,2))*100 as SQL_Physical_memory_ratio

For SQL 2012 and above use the below query:

declare @mem1 int
declare @mem2 int
select @mem1 = (select (physical_memory_kb/1024) as server_memory_in_MB from sys.dm_os_sys_info)
select @mem2 =(select convert(decimal,value) from sys.configurations where name='max server memory (MB)')
select @mem2 as SQL_Max_Memory, @mem1 as Physical_Server_Memory,
cast((cast(@mem2 as float)/ cast(@mem1 as float)) as decimal(4,2))*100 as SQL_Physical_memory_ratio

Identify the versions & editions of all SQL Instances in a company

What if you are assigned to a task of identifying the versions & editions of each and every SQL Instance in the company? Your job has just been made easier with the script below! Run the below script on a CMS (Central Management Server) and you should have a neatly formatted result set.

set nocount on
declare @version varchar (10)
declare @servername varchar(250)
declare @ver varchar(10)
set @servername=(select @@servername)
set @version = substring(cast(SERVERPROPERTY('productversion') as varchar(100)),0,CHARINDEX('.',(cast(SERVERPROPERTY('productversion') as varchar(100))))+2)
set @ver=case @version
       when '8.0' then '2000'
       when '9.0' then '2005'
       when '10.0' then '2008'
       when '10.5' then '2008 R2'
       when '11.0' then '2012'
       when '12.0' then '2014'
       when '13.0' then '2016'
       end
select SERVERPROPERTY('MachineName') as Physical_Server_Name, @ver as Version, SERVERPROPERTY('productversion') as Build_Number,
SERVERPROPERTY('Edition') as Edition
set nocount off

Windows batch file to parse thru server names in a text file and execute scripts against them

DBA's often need to execute the same set of scripts against multiple instances. The job is made a lot easier by using a windows batch file that executes a sqlcmd against all the instances one after the other. All the server names can be placed in a text file and the below code parses thru the text file one after the other and executes sqlcmd against them. The output of each sqlcmd command is logged to the file 'output.txt'. '>>' is the operator that appends the output to the same text file each time sqlcmd runs rather than creating a new file and over-writing it every time.


@echo off
for /F "tokens=*" %%a in (servernames.txt) do sqlcmd -S%%a -E -i "C:\check\script.sql" >> "C:\check\output.txt" -b

Dropping user from all databases and then the login

There may be situations where DBA's get requests to drop a login. Right-click, drop login, done! But it leaves a lot of orphan users in the databases it had access to before dropping the login. In order to have a clean way of dropping the login, we need to drop the user from all the databases it exists in and then drop the login. Below is a simple script to do that.

EXEC master..sp_MSForeachdb 'USE [?] if exists (select 1 from sysusers where name=''domain\username'') drop user [domain\username]';

if exists(select 1 from master..syslogins where name='domain\username') drop login [domain\username];

It uses sp_msforeachdb to check and drop the user if it exists, finally drops the login if it exists. The same script can be used for dropping windows as well as sql logins..