Using XP_CMDSHELL we can run commands that could otherwise be run using Windows Scheduled Tasks. It is kick ass, what makes this even more kick ass is being able to capture the errors running these commands produce.

Below is an example of a simple script which runs a XP_CMDSHELL command and sends an email in the event of any failure. Dare I say this is even quick and dirty . . .

use msdb

declare @cmd_to_run varchar (1500)
declare @return_code int

--The command you want to run
select @cmd_to_run = '<<INSERT YOUR OWN COMMAND>>'

-- Run the command, capture what int code is returned
exec @return_code = master.dbo .xp_cmdshell @cmd_to_run

-- Returning a 0 means there is a problem, send an email if 0 is returned 
if @return_code <> 0
begin
       print 'FAILED'
       exec sp_send_dbmail
            @profile_name = '<<PROFILE NAME>>' ,
            @recipients = '<<RECIPIENTS>>' ,
            @importance = 'high' , --somethings failed dudes, its always high importance
            @subject = '<<SOMETHING MEANINGFUL TO YOU>>' ,
            @body = ''<<SOMETHING MEANINGFUL TO YOU>>'
end

 

 

The script below will generate an email if a database has had a full backup in the last 7 days, listing the database and the last backup in a table. This could be useful to flag up databases which failed to be backed up during a job or just for auditing/informational purposes. This script is attached to a job and is scheduled to run every after the full backup maintenance plan has run to inform the SQL team of any databases missing a bak.

-- any full backup failures?
declare @failureCount         int
set @failureCount =  1

if exists (

select null
from sys .databases as d
left join msdb.dbo.backupset as bs
on d. name = bs .database_name
where is_copy_only = 0 and type in ('D' )
group by database_name
having      datediff (D, max( BACKUP_FINISH_DATE), getdate()) > 7
)
                         begin
                               set @failureCount = 0

                         end

--if a full backup is missing, run the following 
if (@failureCount = 0)
begin

declare @bodyText              nvarchar(max )
declare @bodyLine              nvarchar(512 )

declare @databaseName         nvarchar( 128)
declare @@lastbak             nvarchar(128 )

-- populate mail body top
print 'populate table'
set @bodyText = N'<html><body>'
set @bodyText = @bodyText + N'<style> type="text/css" body {font-family:"Calibri";font-size:"10pt";} </style>'
set @bodyText = @bodyText + N'The following databases did not get a full backup on %s - verify is this is correct:'
set @bodyText = @bodyText + N'<br><br><table border="1">'
set @bodyText = @bodyText + N'<tr><th>Database Name</th><th>Last Backup</th></tr>'
set @bodyText = replace( @bodyText,N'%s' ,@@servername)

-- cursor for disabled jobs
print 'starting cursor'

declare csrDatabases cursor fast_forward
for

select
       cast(bs . database_name as nvarchar( 128)) ,
       cast(max ( bs. backup_finish_date ) as nvarchar (128))
from
       sys . databases as d
       left join msdb. dbo.backupset as bs
on
      d . name = bs .database_name
where
      is_copy_only = 0 and type = 'D'
group by
      database_name
having
       datediff(D , max (BACKUP_FINISH_DATE), getdate()) > 7
order by
      database_name

open csrDatabases 
fetch next from csrDatabases
into @databaseName, @@lastbak

-- populate mail html table with disabled jobs
while @@fetch_status <> -1
begin

       set @bodyLine = N'<tr><td>%1</td><td>%2</td></tr>'
       set @bodyLine = replace( @bodyLine,N'%1' ,@databaseName)
       set @bodyLine = replace( @bodyLine,N'%2' ,@lastbak)
       set @bodyText = @bodyText + @bodyLine

       fetch next from csrDatabases
       into @databaseName, @@lastbak

end

close csrDatabases 
deallocate csrDatabases 
print 'cursor ended'

-- populate mail body bottom
set @bodyText = @bodyText + N'</table>'
set @bodyText = @bodyText + N'</body></html>'

-- send mail
print 'start sending mail'
exec sp_send_dbmail
       @profile_name = 'profile_name',
      @recipients = 'email_address' ,
      @importance = 'high' ,
      @subject = 'BACKUP - FULL - MISSING ,
      @body  = @bodyText,
      @body_format = 'html'
print 'finish sending mail'
end

go

Note, with regard to backup ‘type’

  • ‘D’ denotes a full backup
  • ‘I’ denote a differential
  • ‘L’ denotes a log backup

This script could be easily adapted to check differential’s and log files.

Query plans possess vital information which can help a DBA to make optimization decisions.

The T-SQL below will retrieve the query plan for database of your choice. By extending the where clause in the sub query you can be more precise about the query plans you return based on particular commands for example. The sub query could even be replaced with your own SPID.

select query_plan
from sys .dm_exec_requests as er
cross apply sys. dm_exec_query_plan(er .plan_handle)
where session_id in ( select spid from sys. sysprocesses where dbid = DB_ID('database_name' ))

Happy viewing :)

 

 

 

I wrote a piece last week which described out mirroring could be disabled automatically on failure (http://www.electricwaffle.co.uk/2013/04/disabling-mirroring-automatically/). Part of the inspiration for this was to allow replication to continue in an environment where replication and mirroring were  implemented on the same 2005 instance. By default, when mirroring breaks, any unmirrored changes will not be grabbed by the Log Agent. Therefore a mirror failure stops replication :-(.

However  <<drum roll>> I found a trace flag which when enabled (with hotfixes) allow unmirrored changed to be grabbed for use in replication by the Log Agent. 1448

See the following KB for more information http://support.microsoft.com/kb/937041

Glorious 😀

 

***HELLO! IF YOUR LOOKING AT THIS TO FIND A WAY OF KEEPING REPLICATION GOING WHEN MIRRORING FAILS IN 2005 INSTANCE SEE http://www.electricwaffle.co.uk/2013/04/trace-flag-to-allow-log-agent-to-read-unmirrored-changes-in-ms-sql-2005/ ***

Mirroring and replication on the same instance can be an issue when mirroring fails. Lets say you notice your replication stops and you find in the Replication Monitor the error “Replicated transactions are waiting for next Log backup or for mirroring partner to catch up.”  If you’ve got mirroring in place check the status of the mirroring this might be the issue. You can use the T-SQL below to check the status

select db_name(database_id ), mirroring_state_desc
from sys .database_mirroring
where mirroring_guid is not null and mirroring_state_desc = 'SUSPENDED'

Drop the mirroring for any mirroring which are in a suspended state using

alter database <<database>> set partner off

Check replication and you’ll find it’ll most likely start up again.

This is all very well and good but if mirroring pokes at 2am and its an system used across many regions and people can’t use replication there’s an issue!

What can be useful is to have something in place to drop the mirror in the event that it suspends to save the replication. The script below could be added to a job running periodically to test the mirroring status and take action.

declare @commands nvarchar (4000)
declare @database_names varchar (1000)

--building command for turning mirroring off
select @commands = coalesce (@commands + '', '')+'ALTER DATABASE ' + db_name(database_id ) + ' SET PARTNER OFF ; '
from sys .database_mirroring
where mirroring_guid is not null and mirroring_state_desc = 'SUSPENDED'

--building list of servers which are in a suspended state
select @database_names = coalesce (@database_names + '', '')+ db_name(database_id )
from sys .database_mirroring
where mirroring_guid is not null and mirroring_state_desc = 'SUSPENDED'

--if the commands variable is not empty  . . . .
if(@commands <> '' )
begin

--send mail to inform people what is going to happen
exec msdb. dbo.sp_send_dbmail
    @profile_name = '<<NAME>>' ,
    @recipients = '<<EMAIL>>' ,
    @subject = 'MIRRORS IN SUSPENDED STATE' ,
    @query = 'select db_name(database_id) from sys.database_mirroring where mirroring_guid is not null and mirroring_state_desc = ''SUSPENDED''',
    @body = 'Databases were in a suspended state - mirroring to be removed',
    @importance = high,
    @attach_query_result_as_file = 1

       begin try

             --attempt to run the query to turn off mirroring
             exec sp_executesql @stmt = @commands

             --tell people the mirroring was turned off successfully
             exec msdb. dbo.sp_send_dbmail
             @profile_name = 'SQLServer' ,
            @recipients = '<<email>>' ,
            @subject = 'SUSPENDED MIRRORS PARTNER REMOVED SUCCESSFULLY' ,
            @importance = high,
            @body = @database_names

       end try

             begin catch

             -- tell people attempt to turn off mirroring failed
             exec msdb. dbo.sp_send_dbmail
             @profile_name = '<<NAME>>' ,
            @recipients = '<<EMAIL>>' ,
            @subject = 'SUSPENDED MIRRORS PARTNER REMOVAL FAILED' ,
            @importance = high,
            @body = @database_names

             end catch

end

Tailor the try/catch to meet your error to meet your needs :-)

 

 

Its important for security to monitor unused logins on SQL. Only logins which need to be there should be there. There are a number of ways to monitor the which logins are using used but these methods vary across the versions of SQL, it can be handy to have a basic standard method which a DBA is confidence possesses interoperability across all versions of SQL. Below i’ve outlined a really simple way the sys.sysprocesses table can be used to capture logon data which could be used to find logins which aren’t being used

1) Create a table to hold the audit data

create table record_login_user_behaviour (

loginame         varchar(50 ),
cmd              varchar(200 ),
database_name    varchar(50 ),
frequency        int,
date_captured    datetime

)

2) Create a stored procedure to capture login names of currently running processes. I included a count of the commands being used and the time, you may want to tweak for your needs.

USE <<database>>
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure mon_record_login_user_behaviour

as
set nocount on

insert into record_login_user_behaviour
select distinct loginame,cmd , DB_NAME (DBID) as database_name , COUNT(loginame ) as frequency, GETDATE() as date_captured
from sys .sysprocesses
where loginame <> 'sa'
group by loginame, cmd , dbid
GO

3) Attach capture procedure to job to regularly grab what logins are being used.

4) The script below will compare what logins have been captured as being used against the logins on SQL and produces a list of what what logins haven’t been used.

select distinct name
from sys .server_permissions as spe
inner join sys. server_principals as spr
on spe. grantee_principal_id  = spr .principal_id
where name not in (select distinct loginame from record_login_user_behaviour )

Any ways of improving this activity would be gratefully recieved :-)

 

 

Sometimes you want to make changes across all tables in a particular database.

For example, lets say you’ve got a database that was replicated and now its not and you want to strip the msrepl_tran_version column. During the replication process a column is added to all tables to ensure that there is uniqueness across all the rows. When replication is stopped there  is no longer a need to have this column, in fact its confusing being there  because it doesn’t do anything. The T-SQL below will generate script which when run in a query window will alter all the table in a particular database – it will drop the msrepl_tran_version to be precise,

USE <<DATABASE>>
SELECT
 'ALTER TABLE [' + o.[name] + '] DROP COLUMN [msrepl_tran_version];'
FROM
SYS.OBJECTS AS o
INNER JOIN SYS.COLUMN AS c
ON o.OBJECT_ID = c.OBJECT_ID
AND [name] = 'msrepl_tran_version'
WHERE
o.type = 'u'

 

 

Nice and quick this one . . . .

The T-SQL below brings together a number of meta tables in SQL to present all the permissions all users have against every schema and table/column in a database.

 select prin. name, prin .type_desc, per.class_desc , per. permission_name, per.state_desc, sch.name
from sys .database_principals as prin
inner join sys. database_permissions per
on prin. principal_id = per .grantee_principal_id
       left outer join sys.schemas sch
       on per. major_id = sch .schema_id and prin. name <> 'public'
where  class_desc = 'SCHEMA'
union
select prin. name, prin .type_desc, per.class_desc , per. permission_name, per.state_desc, isnull( obj.name , 'database_name' )
from sys .database_principals as prin
inner join sys. database_permissions per
on prin. principal_id = per .grantee_principal_id
       left outer join sys.objects obj
       on per. major_id = obj .object_id
where class_desc <> 'SCHEMA' and prin. name <> 'public'
order by prin.name

Moving Tempdb

You might want to move tempdb for a variety of reason, sizing being the most likely issue. Here’s how to do it using T-SQL

1) use tempdb. execute sp_helpfile to show the files associated to the database.

USE TempDB

EXEC sp_helpfile -- this will tell you want the names of the files to move are

2) Use the master database and alter the tempdb to change the location of the files

USE master

ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = '<<WHERE YOU WANT THE DATA FILE TO GO>>\tempdb.mdf')

ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = '<<WHERE YOU WANT THE LOG FILE TO GO>>\templog.ldf')

3) A message will be displayed informing us that the files have been modified in the catalogue but no changes will be made until the database is restarted.

4) Restart SQL and the changes will be made.

Obviously, make sure your restart isn’t going to affect production systems 😉