Microsoft SQL How To

How to run SQL Server Management Studio as an NT AUTHORITY\System user

Connected to the console aka main display of the remote server, either physically or remotely:
mstsc /admin /v:servername
Use the windows scheduler to start a new command prompt
at 11:10 /interactive c:\windows\system32\cmd.exe
Once the new shell pops up run the SQL Server Management studio:
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe"
Or run the SQL Server Enterprise Manager:
C:\WINDOWS\system32\mmc.exe /s "C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC"

How to monitor distributed transactions

To monitor and resolve MS DTC transaction log in onto the MS SQL server. Open Administrative Tools -> Component Services. Then expand Component Services -> Computers -> My Computer -> Distributed Transactions Coordinator -> Local DTC and click on the Transaction List.

How to list all indexes in a database

Run the following.

SELECT OBJECT_SCHEMA_NAME(tab.object_id,DB_ID()) AS "Schema", tab.name AS table_name, 
       idx.name AS index_name, col.name AS column_name, idx.type_desc, idx.fill_factor,idxcol.is_descending_key
FROM sys.tables AS tab
  INNER JOIN sys.indexes idx ON tab.object_id = idx.object_id  
  INNER JOIN sys.index_columns idxcol ON idx.object_id = idxcol.object_id 
  INNER JOIN sys.all_columns col ON tab.object_id = col.object_id AND idxcol.column_id = col.column_id 
WHERE tab.is_ms_shipped = 0 AND idx.type_desc <> 'HEAP'
  and idx.is_primary_key = 0 and idx.is_unique = 0 and idx.is_unique_constraint = 0
ORDER BY tab.name, idx.index_id

Change to your liking.

How to check locks on a database

A generic lock query

sp_lock

To see what is a behind a specific process number

sp_who2

For a specific object number do

 select OBJECT_NAME(...)

For a specific db number do

 select DB_NAME(...)

To see what is in a query cache for a process do

 DBCC INPUTBUFFER(<process_id here>)

A query from the syslockinfo

 select distinct object_name(a.rsc_objid), a.req_spid, b.loginame from master.dbo.syslockinfo a (nolock) 
 join master.dbo.sysprocesses b (nolock) on a.req_spid=b.spid where object_name(a.rsc_objid) is not null

The same query except smart - decoding all the values on the fly:

SELECT CONVERT (SMALLINT, l.req_spid) as 'Process',COALESCE(SUBSTRING (s.loginame,  1,  128), '') as 'Login'
        , COALESCE(SUBSTRING (s.hostname,  1,  128), '') as 'Host', SUBSTRING (DB_NAME(l.rsc_dbid),  1,  128) as 'DB'
        , OBJECT_NAME(CONVERT(VARCHAR, l.rsc_objid)) as 'Table', idx.name as 'Index'
        , SUBSTRING (v.name,  1,  4) as 'Type', SUBSTRING (u.name,  1,  8) as 'Mode', SUBSTRING (x.name,  1,  5) as 'Status'
    FROM master.dbo.syslockinfo l
    JOIN master.dbo.spt_values v ON l.rsc_type = v.number
    JOIN master.dbo.spt_values x ON l.req_status = x.number
    JOIN master.dbo.spt_values u ON l.req_mode + 1 = u.number
    LEFT JOIN master.dbo.sysprocesses s ON l.req_spid = s.spid
    LEFT JOIN sys.indexes idx ON l.rsc_indid = idx.index_id AND l.rsc_objid = idx.object_id 
    WHERE v.type = 'LR' AND x.type = 'LS'  AND  u.type = 'L' 

Once you have the table and the index numbers from the previous entry you can find out what transaction caused the locks

select * from sys.dm_tran_database_transactions t 
join sys.syslockinfo s ON s.req_transactionid=t.transaction_id
 where s.rsc_objid=<table_id here> and s.rsc_indid=<index_id here>

Once you have the transaction number you could try to find it in the MS SQL transaction log. However it looks like what is used for the transaction id in the transaction log is not the same as the ''transaction_id' from sys.dm_tran_database_transactions. You could try getting the oldest transaction and then looking around that time to pick up the needed transaction id and using it to search as follows:

DBCC OPENTRAN(itimdb)
select top 100 * from master.dbo.fn_dblog('96190:8913:21',null)
select * from master.dbo.fn_dblog(null,null) where [transaction id]='0000:18c48cd6'

How to get size and number of rows in each table in a database

 create table #tmp (tablename varchar(128), rowcnt int, reserved varchar(10), data varchar(10), index_size varchar(10), unused varchar(10))
 exec sp_MSforeachtable 'insert into #tmp EXEC sp_spaceused ''?'' '
 select tablename,rowcnt as rows_count, CAST(REPLACE(reserved,' KB','') AS INT)/1024 size_in_mb from #tmp order by size_in_mb desc
 drop table #tmp

How to insert binary data manually

insert into table1(datacolumn) values (convert(binary,'data'));

How to save content of an sql table from a query

select * into enrole.SCHEDULED_MESSAGE_BACKUP from enrole.SCHEDULED_MESSAGE

This command also creates a backup table (i.e. it should not exist) to copy the content back use this:

delete from enrole.SCHEDULED_MESSAGE 
insert into enrole.SCHEDULED_MESSAGE select * from enrole.SCHEDULED_MESSAGE_BACKUP

How to update indexes on SQL

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

To check the index fragmentation run

EXEC sp_MSforeachtable @command1="print '?' DBCC SHOWCONTIG ('?')"

or use it on just one table

DBCC SHOWCONTIG ([enrole.PROCESS])

1

@HowTo @Microsoft