Services broker

-- Message types
SELECT * FROM sys.service_message_types

-- Contracts
SELECT * FROM sys.service_contracts

-- Queues
SELECT * FROM sys.service_queues

-- Services
SELECT * FROM sys.services

select * from sys.conversation_groups

select * from sys.conversation_endpoints

Database Master Key

Information about the database master key

select *
from sys.symmetric_keys


----------

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

USE AdventureWorks;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';
GO


----------

BACKUP MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD = 'password'

USE AdventureWorks;
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'sfj5300osdVdgwdfkli7';
BACKUP MASTER KEY TO FILE = 'c:\temp\exportedmasterkey'
ENCRYPTION BY PASSWORD = 'sd092735kjn$&adsg';
GO


-------
RESTORE MASTER KEY FROM FILE = 'path_to_file'
DECRYPTION BY PASSWORD = 'password'
ENCRYPTION BY PASSWORD = 'password'
[ FORCE ]

USE AdventureWorks;
RESTORE MASTER KEY
FROM FILE = 'c:\backups\keys\AdventureWorks_master_key'
DECRYPTION BY PASSWORD = '3dH85Hhk003#GHkf02597gheij04'
ENCRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD';
GO

SQL Server uptime

SQL 2005 Server uptime

select login_time
from sys.dm_exec_sessions
where session_id = 1


SQL 2008 Server uptime

SELECT sqlserver_start_time
FROM sys.dm_os_sys_info

SQL Server: The index "[IndexName]" on table "[TableName]" cannot be reorganized because page level locking is disabled.

http://www.interworks.com/blogs/bbickell/2010/05/10/sql-server-index-indexname-table-tablename-cannot-be-reorganized-because-p


SELECT *
FROM sys.indexes
WHERE ALLOW_PAGE_LOCKS = 0


SELECT 'ALTER INDEX [' + I.Name + '] ON [' + T.Name + '] SET (ALLOW_PAGE_LOCKS = ON)' As Command
FROM sys.indexes I
LEFT OUTER JOIN sys.tables T ON I.object_id = t.object_id
WHERE I.allow_page_locks = 0 AND T.Name IS NOT NULL

ex:ALTER INDEX [Refnum IDX] ON [Applicants] SET (ALLOW_PAGE_LOCKS = ON)