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