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 😉

 

 

From MS SQL 2008 DBAs can make use of SQL Server Audits. This feature allows you to audit the interactions against objects in your system. For example, a SQL audit could be used to monitor SELECTS, INSERTS, DELETES and UPDATES against a particular database.

Below is an example of the T-SQL required to perform an audit against a whole database

1) First create the audit against the MASTER database.

USE MASTER
CREATE SERVER AUDIT TEST_AUDIT
TO FILE ( FILEPATH = '<<PATH_TO_AUDIT_FILE>>' )
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, STATE = ON)

Check in the Security –> Audit you’ll see the audit.

2) Against the database you want to audit create the audit specification

USE <<database>>

CREATE DATABASE AUDIT SPECIFICATION TEST_AUDIT_SPECIFICATION
FOR SERVER AUDIT TEST_AUDIT
ADD (SELECT
ON DATABASE ::<<database>>
BY PUBLIC )
WITH (STATE = ON)

Check in the  <<database>> –> Security –> Database Audit Specification you’ll see the audit.

A file (with the extension .sqlaudit) will be created to at the location you specified with the audit data. T-SQL can be used to view the contents of this file.

SELECT session_server_principal_name, database_name, object_name, statement, event_time
FROM sys .fn_get_audit_file ('<<PATH_TO_AUDIT_FILE>>' ,default, default)

 

A quickie . . .

Did you know that sp_spaceused can tell you the number of rows in a database using a lot less effort than using count()?

Count() will go through the whole table counting every row, sp_spaceused however will use meta data about the table to ascertain how many rows there are.

Might be useful 😉

 

First set the database in question to single user to ensure no other connections are made to it

Then examine sys.database_files to get the file names of the database. Make a note of these names

USE <<database>>
select *
from sys .database_files

Detach the database using sp_detach_db.

EXEC sp_detach_db '<<database_name>>', 'true'

Refreshing the databases tab in SSMS will show the database disappear. It has not been deleted rather detached from management studio.

Now move the MDF and LDF files to your desired location.

Attach your database files using sp_attach_db.

EXEC sp_attach_db @dbname = N'<<database_name>>',
    @filename1 = N'M:\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Data\database_DB.mdf',
    @filename2 = N'M:\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Data\database_DB_log.ldf'

 

Refresh databases and you’ll see your newly moved databases appear in SSMS.