quick and dirty shrinklogs for all databases

This is a real quick and dirty way to shink all the database logs in all the databases please makesure you want to shrink the master, msdb,tempdb and so on before running this.

EXEC SP_MSFOREACHDB 'dbcc shrinkfile(2,1)'

Script to check whats running in tempdb and want statements its running

This will help deturming what is running in tempdb this will give you the sql text.

use tempdb
SELECT tst.[session_id],
s.[login_name] AS [Login Name],
DB_NAME (tdt.database_id) AS [Database],
tdt.[database_transaction_begin_time] AS [Begin Time],
tdt.[database_transaction_log_record_count] AS [Log Records],
tdt.[database_transaction_log_bytes_used] AS [Log Bytes Used],
tdt.[database_transaction_log_bytes_reserved] AS [Log Bytes Rsvd],
SUBSTRING(st.text, (r.statement_start_offset/2)+1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE r.statement_end_offset
END - r.statement_start_offset)/2) + 1) AS statement_text,
st.[text] AS [Last T-SQL Text],
qp.[query_plan] AS [Last Plan]
FROM sys.dm_tran_database_transactions tdt
JOIN sys.dm_tran_session_transactions tst
ON tst.[transaction_id] = tdt.[transaction_id]
JOIN sys.[dm_exec_sessions] s
ON s.[session_id] = tst.[session_id]
JOIN sys.dm_exec_connections c
ON c.[session_id] = tst.[session_id]
LEFT OUTER JOIN sys.dm_exec_requests r
ON r.[session_id] = tst.[session_id]
CROSS APPLY sys.dm_exec_sql_text (c.[most_recent_sql_handle]) AS st
OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp
where DB_NAME (tdt.database_id) = 'tempdb'
ORDER BY [Log Bytes Used] DESC;

Check which tables have triggers and compare

This is useful if you have 2 or more databases in different sites.

Script to check which tables have triggers

SELECT s1.name as tablename, s2.name as triggername FROM sysobjects s1
JOIN sysobjects s2 ON
s1.id =s2.parent_obj
AND s2.xtype = 'TR'
WHERE s1.xtype = 'U'
ORDER BY s1.name


output

tablename triggername
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
AccruedDivis trAccrueddivisAuditQueueInsert
AccruedDivis trAccrueddivisAuditQueueUpdate
AccruedDivis trAccrueddivisAuditQueueDelete
AlertMessages trAlertMessagesInsert
Run this on both of the servers and in the databases required
copy the data to an excel worksheet
I have two databases one in London and one in Hong Kong in this sample



use a vlookup to compare like this and the copy the formula down

=VLOOKUP(B115,'sheet.name'!B:B,1,FALSE)

How to start service broker on a database

enable service broker
-- Alter the database to enable ServiceBroker functionality

DECLARE @dbname VARCHAR(30)

SELECT @dbname=db_name()

PRINT 'Setting up service broker on ' + @dbname



EXEC ('ALTER DATABASE ' + @dbname + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE')

EXEC ('ALTER DATABASE ' + @dbname + ' set NEW_BROKER')

EXEC ('ALTER DATABASE ' + @dbname + ' SET ENABLE_BROKER')

EXEC ('ALTER DATABASE ' + @dbname + ' SET MULTI_USER')

IF (SELECT is_broker_enabled FROM sys.databases WHERE name = @dbname) = 0

BEGIN

-- PRINT 'The Service broker wasn''t enabled !!'

RAISERROR ('The Service broker wasn''t enabled !!', 1, 1)

END