Kill Old Connections

You probably have users that like to connect the database, run a massive query, and then go home for the evening locking your nightly maintenance jobs and all other users out. I put together a handy little script to kill those offending users by looking at the last activity and killing those spids that are sitting idle.

USE [DB_UTILS]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

/*----------------------------------------------------------------
Description:
 Kills connections greater than xx days old in specific database and/or from specific program.
----------------------------------------------------------------
Called By:
 DB_UTILS - sp_KillOldConnections
----------------------------------------------------------------
Modifications: 7/2/14 Todd Palecek Initial Build
        8/1/14 Todd Palecek Changed to .04167 days (1 hour) old to kill
----------------------------------------------------------------*/

CREATE PROCEDURE [dbo].[sp_KillOldConnections] AS

BEGIN

CREATE TABLE #TEMP_WHO2
(SPID int,
Status char(20),
Login char(30),
HostName char(30),
BlkBy char(5),
DBName char(20),
Command char(75),
CPUTime int,
DiskIO int,
LastBatch char(20),
ProgramName char(75),
SPID2 int,
REQUESTID int) --not present in SQL 2000 or 7

INSERT INTO #TEMP_WHO2 EXEC sp_who2

--calculate year to add to month/day/time from LastBatch
declare @date varchar(10)
set @date = convert(varchar,datepart(yy,getdate()))

DECLARE @SPID varchar(50)

DECLARE cursorName CURSOR FOR
Select Spid 
FROM   #TEMP_WHO2
WHERE DBName='XXXXX' 
--and ProgramName like 'XXXXX%' and
and convert (datetime ,@date + '/' + LastBatch) < getdate() -.04167 and spid > 50
ORDER BY SPID

OPEN cursorName

FETCH NEXT FROM cursorName into @SPID

WHILE @@FETCH_STATUS = 0

BEGIN

print @SPID

declare @cmd varchar(20)

set @cmd = 'kill ' + @SPID

print @cmd

exec (@cmd)

FETCH NEXT FROM cursorName

INTO @SPID

END

CLOSE cursorName

DEALLOCATE cursorName

DROP TABLE #TEMP_WHO2

END

GO

Audit SQL Passwords for Easily Guessed

In case you are in scenario where you do not have complete control over the install of SQL there are a number of problems that can go wrong by letting non-DBAs perform the work. One of the biggest is setting the sa password to an easily guessed one. You are then on the hook for any problems that one of your well meaning developers creates by making changes with the sa user.

I like to perform annual audits of the sa user to ensure that none of servers are using some standard passwords for this account. I check for blank password, password = password, and password = sa utilizing a powershell script which records the results of all servers for documentation purposes. This is also a great tool to satisfy your Audit department!

When running comment out the passwords that you do not want to check and change the user id and password as needed.

Thanks to K. Brian Kelley for the original script.


# $serverList contains the list of SQL Server instances to audit
# $auditReport is the path to the file where the results are written
$serverList = "c:\Documents\servers.txt";
#$auditReport = "c:\Documents\sa_password_password_audit.txt";
$auditReport = "c:\Documents\sa_password_blank_audit.txt";
#$auditReport = "c:\Documents\sa_password_sa_audit.txt";
# cycle through each SQL Server instance and report the findings to the screen
ForEach ($SQLServer in Get-Content $serverList)
{
 
    #$connStr = "Server=$SQLServer; User Id=sa; Pwd=password"
    $connStr = "Server=$SQLServer; User Id=sa; Pwd="
    #$connStr = "Server=$SQLServer; User Id=sa; Pwd=sa"
       
    $conn = new-object System.Data.SqlClient.SqlConnection($connStr)
    try
    {
        $conn.Open()
        " !! Problem: Connected with password SA password to $SQLServer !!" | Out-File -filepath $auditReport -append;
    }
    catch [System.Data.SqlClient.SqlException]
    {
 "$SQLServer - OK." | Out-File -filepath $auditReport -append;       
    }
    $conn.Close();
}

SQL will not start; databases won't open

We had someone rebuild the drives that SQL database and log files live on.  No big deal, but SQL was not stopped cleanly before performing this action. Now it is time to call on the DBA to fix the issue.

First problem I ran into was that SQL would not start.  A quick review of the event log showed:

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'T:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\tempdb.mdf'.

Sure enough the T drive was one that was rebuilt.

The fix for this error is to create the path that SQL is looking for for tempdb.  I added all the folders for T:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ and SQL is off and running.

But my user databses are in Recovery Pending now. 

Start with:

alter database DB_UTILS set online with rollback immediate;

 

Folders don’t exist on the rebuilt drive again.  Add all the folders where the ldf files lived and databases are running.

But wait, not all databases are coming online.  Looks like a few that did not shut down cleanly:

File activation failure. The physical file name "F:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ReportServer_log.ldf" may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Msg 5181, Level 16, State 5, Line 1

Could not restart database "ReportServer". Reverting to the previous status.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

 

Let’s set our database into Emergency mode and lock everyone else out:

ALTER DATABASE ReportServer SET EMERGENCY

GO

 

ALTER DATABASE ReportServer SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

 

Now we can rebuild the log file:

 

DBCC CHECKDB('ReportServer',REPAIR_ALLOW_DATA_LOSS)

 

And let the users back in:

 

ALTER DATABASE ReportServer SET MULTI_USER

 

You do need to resize the log file and take a new full backup because the LSN chain has been broken.

 

 

 

 

 

Create Default Security Roles

We have struggled with security added to the databases at the individual level.  Not only does this create a lot of tedious work to add the users but we are left with a number of orphans that have left the organization.  To clean this up I have built  role level security and add the AD groups to the specific role.  Here are the specific roles and rights:

 /*
Built by Todd Palecek to simplify security management
Create default roles for database access
1. Developer
2. Admin
3. Reader
4. Business Analyst
5. Other as needed
AD groups are used to assign to the roles and users are added to the AD groups
thereby always keeping security up-to-date based on employment status
*/

--DEVELOPER role DEVELOPMENT server, AD Group 000-AppName-Devel
use msdb  --allow to see and modify jobs
go
EXEC sp_addrole 'Developer'
GO
EXEC sp_addrolemember 'db_datareader', 'Developer'
GO
EXECUTE sp_addrolemember 'SQLAgentOperatorRole', 'Developer' --allow to create agent Jobs
GO
--add users to role

use XXXX  --database where development will take place
go
EXEC sp_addrole 'Developer'
GO
EXEC sp_addrolemember 'db_ddladmin', 'Developer'  --create, drop, modify objects; primarily stored procs
GO
EXEC sp_addrolemember 'db_datareader', 'Developer'
GO
EXEC sp_addrolemember 'db_datawriter', 'Developer'
GO
GRANT EXECUTE TO [Developer]  --allow to execute any stored procs
go
GRANT VIEW DEFINITION to [Developer]  --view stored proc tsql
go
GRANT VIEW DATABASE STATE TO [Developer]  --query dmvs
go
--add users to role

use master
go
create server role [Developer]
GO
GRANT VIEW SERVER STATE TO [Developer]  --query dmvs
go

--DEVELOPER role PRODUCTION server, AD Group 000-AppName-Devel
use msdb
go
EXEC sp_addrole 'DeveloperProd'
GO
EXEC sp_addrolemember 'db_datareader', 'DeveloperProd'
GO
EXECUTE sp_addrolemember 'SQLAgentReaderRole', 'DeveloperProd'
GO
--add users to role

use XXXX
go
EXEC sp_addrole 'DeveloperProd'
GO
EXEC sp_addrolemember 'db_datareader', 'DeveloperProd'
GO
GRANT VIEW DEFINITION to [DeveloperProd]
go
--add users to role


--BUSINESS ANALYST role DEVELOPMENT server, AD Group 000-AppName-BusAnlyst
use msdb  --allow to see and modify jobs
go
EXEC sp_addrole 'BusinessAnalyst'
GO
EXEC sp_addrolemember 'db_datareader', 'BusinessAnalyst'
GO
EXECUTE sp_addrolemember 'SQLAgentReaderRole', 'BusinessAnalyst' --allow to view agent Jobs
GO
--add users to role

use XXXX--database where development will take place
go
EXEC sp_addrole 'BusinessAnalyst'
GO
EXEC sp_addrolemember 'db_datareader', 'BusinessAnalyst'
GO
EXEC sp_addrolemember 'db_datawriter', 'BusinessAnalyst'
GO
GRANT EXECUTE TO [BusinessAnalyst]  --allow to execute any stored procs
go
GRANT VIEW DEFINITION to [BusinessAnalyst]  --view stored proc tsql
go
--add users to role

--BUSINESS ANALYST role PRODUCTION server, AD Group 000-AppName-BusAnlyst
use msdb
go
EXEC sp_addrole 'BusinessAnalystProd'
GO
EXEC sp_addrolemember 'db_datareader', 'BusinessAnalystProd'
GO
EXECUTE sp_addrolemember 'SQLAgentReaderRole', 'BusinessAnalystProd'
GO
--add users to role
use DemandPlanningStage
go
EXEC sp_addrole 'BusinessAnalystProd'
GO
EXEC sp_addrolemember 'db_datareader', 'BusinessAnalystProd'
GO
GRANT VIEW DEFINITION to [BusinessAnalystProd]
go
--add users to role

--READER role DEVELOPMENT server, AD Group 000-AppName-R
use XXXX  --database where development will take place
go
EXEC sp_addrole 'Reader'
GO
EXEC sp_addrolemember 'db_datareader', 'Reader'
GO
--add users to role

--READER role PRODUCTION server, AD Group 000-AppName-R
use DemandPlanningStage  --database where development will take place
go
EXEC sp_addrole 'Reader'
GO
EXEC sp_addrolemember 'db_datareader', 'Reader'
GO
--add users to role

--BUS_SYS_ADMIN role DEVELOPMENT server, AD Group 000-AppName-BusSysAdmin
use msdb  --allow to see and modify jobs
go
EXEC sp_addrole 'Bus_Sys_Admin'
GO
EXEC sp_addrolemember 'db_datareader', 'Bus_Sys_Admin'
GO
EXECUTE sp_addrolemember 'SQLAgentOperatorRole', 'Bus_Sys_Admin' --allow to create agent Jobs
GO
--add users to role

use XXXX  --database where development will take place
go
EXEC sp_addrole 'Bus_Sys_Admin'
GO
EXEC sp_addrolemember 'db_ddladmin', 'Bus_Sys_Admin'  --create, drop, modify objects; primarily stored procs
GO
EXEC sp_addrolemember 'db_datareader', 'Bus_Sys_Admin'
GO
EXEC sp_addrolemember 'db_datawriter', 'Bus_Sys_Admin'
GO
GRANT EXECUTE TO [Bus_Sys_Admin]  --allow to execute any stored procs
go
GRANT VIEW DEFINITION to [Bus_Sys_Admin]  --view stored proc tsql
go
GRANT VIEW DATABASE STATE TO [Bus_Sys_Admin]  --query dmvs
go
--add users to role
use master
go
create server role [Bus_Sys_Admin]
GO
GRANT VIEW SERVER STATE TO [Bus_Sys_Admin]  --query dmvs
go

--BUS_SYS_ADMIN role PRODUCTION server, , AD Group 000-AppName-BusSysAdmin
use msdb
go
EXEC sp_addrole 'Bus_Sys_AdminProd'
GO
EXEC sp_addrolemember 'db_datareader', 'Bus_Sys_AdminProd'
GO
EXECUTE sp_addrolemember 'SQLAgentReaderRole', 'Bus_Sys_AdminProd'
GO
--add users to role
use DemandPlanningStage
go
EXEC sp_addrole 'Bus_Sys_AdminProd'
GO
EXEC sp_addrolemember 'db_datareader', 'Bus_Sys_AdminProd'
GO
GRANT VIEW DEFINITION to [Bus_Sys_AdminProd]
go
--add users to role

--OTHER role DEVELOPMENT server
--OTHER role PRODUCTION server

/*add users to role
CREATE USER [XXXX\AD Group] FOR LOGIN [XXXX\AD Group] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember 'Developer', '[XXXX\AD Group]'
GO
--change schema to dbo
ALTER USER [XXXX\AD Group] WITH DEFAULT_SCHEMA=[dbo];
*/ 

Rename SQL on a cloned server

Cloning the server to make an exact copy of Prod for development and QA is a great time saver and ensures configuration is done correctly.  Unfortunately SQL still thinks it is the original server.  Luckily this is an easy fix.

Check what server SQL thinks it is:

select * from sys.servers

--or
--select * from sysservers
--if you are still running SQL 2000

Now rename the server:

USE master
GO
sp_dropserver [old_name]
GO
sp_addserver [new_name], local
GO

And finally reconfigure any local monitoring, setup backups, etc.

Remove Multiple Log Files

For a table compression project we added an additional log to the database to handle the increased utilization.  Now we need to drop that log.

--Drop the log
ALTER DATABASE ED1 REMOVE FILE ED1LOG2

But I still see the extra log file in database properties.  What is going on?  The file does not exist in

select * from sys.sysfiles

To fix the display of the extra log file that has been removed:

 Run the log backup for the database.

This removes the extra log from database properties and we are back to normal operations.

Cycle Error Log before enabling Alerts

Today I learned to always cycle the error log before enabling alerts after receiving 15,000 text and email messages.  When you enable alerts SQL will read through the current error log and alert you to all the problems you have had.  Since we already fixed those issues we are only concerned about future problems.  To give yourself a clean log run:

EXEC sp_Cycle_ErrorLog

If you forgot to do clear the log and the other DBAs you work with are throwing things at you:

Stop SQL Agent

--number of unsent emails
select count(*) from msdb.dbo.sysmail_unsentitems  --remove all the unsent emails
delete from msdb.dbo.sysmail_unsentitems

Remember to turn SQL Agent back on when done.


   




  

Today we begin the journey of sharing what I have learned that makes people's eye's gloss over with a group that will be more interested.  I hope to give back to the community that has helped me so much and hopefully make your lives a bit easier.