easy way to view locks and blocked/blocking processes in SQL Server

I created a view to check on what objects are have active locks and which may or may not be blocking other session(s) because I wasn't fond of the Activity Monitor's Process listing:

if object_id( 'v_locks' ) is not null
drop view v_locks
go

create view v_locks as (

select    status = sessns.status + ' (BLOCKED)',
        locking = object_name( locks.resource_associated_entity_id)
        + ' (' +
        case  locks.request_mode    when  'S'        then 'Shared'
                                    when  'U'        then 'Update'
                                    when  'X'        then 'Exclusive'
                                    when  'IS'        then 'Intent Shared'
                                    when  'IU'        then 'Intent Update'
                                    when  'IX'        then 'Intent Exclusive'
                                    when  'SIU'        then 'Shared Intent Update'
                                    when  'SIX'     then 'Shared Intent Exclusive'
                                    when  'UIX'     then 'Update Intent Exclusive'
                                    when  'BU'      then 'Bulk Update'
                                    else                 'Other'
        end + ')',
        computer = sessns.host_name,
        login = sessns.nt_domain + '\' + sessns.nt_user_name,
        program = sessns.program_name,
        rqst.session_id,
        pending_command = rqst.command,
        blocked_by = convert(varchar,rqst.blocking_session_id),
        rqst.wait_type,
        isolation_level        =    case    rqst.transaction_isolation_level
                                        when 1 then 'Read Uncommitted'
                                        when 2 then 'Read Committed'
                                        when 3 then 'Repeatable Read'
                                        when 4 then 'Serializable'
                                        when 5 then 'Snapshot'
                                end,
        last_sql = ( select TEXT from sys.dm_exec_sql_text( sql_handle ) )
from    sys.dm_exec_requests        rqst,
        sys.dm_exec_sessions        sessns,
        sys.dm_tran_locks            locks
where    rqst.blocking_session_id <> 0
and        rqst.session_id                =        sessns.session_id
and        rqst.session_id                =        locks.request_session_id
and        locks.resource_type            in        ('OBJECT')

union



select    status = sessns.status + ' (BLOCKING)',
        locking = object_name( locks.resource_associated_entity_id)
        + ' (' +
        case  locks.request_mode    when  'S'        then 'Shared'
                                    when  'U'        then 'Update'
                                    when  'X'        then 'Exclusive'
                                    when  'IS'        then 'Intent Shared'
                                    when  'IU'        then 'Intent Update'
                                    when  'IX'        then 'Intent Exclusive'
                                    when  'SIU'        then 'Shared Intent Update'
                                    when  'SIX'     then 'Shared Intent Exclusive'
                                    when  'UIX'     then 'Update Intent Exclusive'
                                    when  'BU'      then 'Bulk Update'
                                    else                 'Other'
        end + ')',
        computer = sessns.host_name,
        login = sessns.nt_domain + '\' + sessns.nt_user_name,
        program = sessns.program_name,
        sessns.session_id,
        pending_command = '',
        blocked_by = '',
        '',
        isolation_level        =    case    sessns.transaction_isolation_level
                                        when 1 then 'Read Uncommitted'
                                        when 2 then 'Read Committed'
                                        when 3 then 'Repeatable Read'
                                        when 4 then 'Serializable'
                                        when 5 then 'Snapshot'
                                end,
        last_sql =  (select text from sys.dm_exec_sql_text( cons.most_recent_sql_handle ) )
from        sys.dm_tran_locks            locks
inner join    sys.dm_exec_sessions        sessns
on            (    locks.request_session_id                =        sessns.session_id  )

inner join  sys.dm_exec_connections        cons
on            (   locks.request_session_id                =        cons.session_id )

where    locks.resource_type            in        ('OBJECT')
and        locks.request_session_id    not in  ( select session_id from sys.dm_exec_requests )
and        locks.request_session_id    in      ( select blocking_session_id from sys.dm_exec_requests )



union

select    status = sessns.status + ' (NOT BLOCKING)',
        locking = object_name( locks.resource_associated_entity_id)
        + ' (' +
        case  locks.request_mode    when  'S'        then 'Shared'
                                    when  'U'        then 'Update'
                                    when  'X'        then 'Exclusive'
                                    when  'IS'        then 'Intent Shared'
                                    when  'IU'        then 'Intent Update'
                                    when  'IX'        then 'Intent Exclusive'
                                    when  'SIU'        then 'Shared Intent Update'
                                    when  'SIX'     then 'Shared Intent Exclusive'
                                    when  'UIX'     then 'Update Intent Exclusive'
                                    when  'BU'      then 'Bulk Update'
                                    else                 'Other'
        end + ')',
        computer = sessns.host_name,
        login = sessns.nt_domain + '\' + sessns.nt_user_name,
        program = sessns.program_name,
        sessns.session_id,
        pending_command = '',
        blocked_by = '',
        '',
        isolation_level        =    case    sessns.transaction_isolation_level
                                        when 1 then 'Read Uncommitted'
                                        when 2 then 'Read Committed'
                                        when 3 then 'Repeatable Read'
                                        when 4 then 'Serializable'
                                        when 5 then 'Snapshot'
                                end,
        last_sql =  (select text from sys.dm_exec_sql_text( cons.most_recent_sql_handle ) )
from        sys.dm_tran_locks            locks
inner join    sys.dm_exec_sessions        sessns
on            (    locks.request_session_id                =        sessns.session_id  )

inner join  sys.dm_exec_connections        cons
on            (   locks.request_session_id                =        cons.session_id )

where    locks.resource_type            in        ('OBJECT')
and        locks.request_session_id not in ( select blocking_session_id from sys.dm_exec_requests )
and        locks.request_session_id not in ( select session_id from sys.dm_exec_requests )


);

select * from v_locks;

recover empty space from an ISCSI target NTFS volume after defragmentation

If you have an NTFS volume on an ISCSI target with compression and/or deduplication, you will "lose" empty space every time you run a defragmentation.

Specifically I am talking here about Hyper-V Server 2012 /2012R2 with VHD's stored on an NTFS volume on a ISCSI target with compression enabled.

Degragmenting an NTFS volume COPIES the data from blocks to re-arrange it and defragment. It does not write 0's to the (now) un-used blocks, which will screw up your compression and deduplication. Zero filled blocks dedup/compress extremely well. As you VHD's fluctuate in size, and your guest VM's with NTFS volumes ALSO run  defragmentation (and they should) you will end up with "empty" blocks that are not zero filled. (both in the guest VM and on the hyper-v host)

How do you get back that space?

One way is to use a program called Erase to write 0's to all the empty blocks on your NTFS volumes hosted on ISCSI targets.

With the free version of Hyper-V, Hyper-V Server, you will need to manually install the 3.5 .NET framework and manually start the eraser.exe program from its installation folder, but otherwise you can use the

"British HMG Infosec Standard 5, Baseline Standard" which writes 0's to empty blocks when cleaning empty space.

To install 3.5 .NET framework on hyper-v, use the command prompt to copy a Server 2012 ISO image to the C: drive of your hyper-v server.

mount the ISO image with

powershell Mount-DiskImage

then give it the path to the ISO image you copied.

now

powershell Get-Volume

to see what drive letter the ISO image ended up at.
install the 3.5 framework:
dism /online /enable-feature /featurename:NetFX3 /all /Source:K:\sources\sxs

replace K: with the drive letter of the iso image, and you should then be able to install Eraser and run it from the command line (first switching to its install directory.)

make sure to configure the 0 byte fill and you can add a task to erase empty space on your NTFS ISCSI target volumes.

this will be more effective if you also run it inside your VM's that have NTFS volumes you defragment.

excel - retrieve data from a table in SQL Server into your worksheet, sort it, and display an "as of" date

If you need to select data from SQL Server (or other ODBC database), you can alter the code in the following to suit your needs. It connects to the database, executes a SQL statement (an insert, just as an example of how-to ) and then selects from dbo.employee, then sorts the results.
Note: I commented out the  ActiveSheet.Range("H25").CurrentRegion.Delete part because it will delete also
the headers you added into the excel template ( CopyFromRecordset does not set a column header, you have to do that yourself) Any questions let me know.

credit to code copied from Steven's question (who copied it from MSDN)
http://stackoverflow.com/questions/1120674/accessing-sql-database-in-excel-vba


Sub getdatabasedata()

    'Declare variables'
        Dim objMyConn As ADODB.Connection
        Dim objMyCmd As ADODB.Command
        Dim objMyRecordset As ADODB.Recordset

        Set objMyConn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset

    'Open Connection'
        objMyConn.ConnectionString = "Driver={SQL Server}; Server=brucemdev\ssisdev; Database=DEV;"
        objMyConn.Open

    'Insert a dummy row'
        On Error Resume Next

        objMyConn.Execute "insert into employee(name,title) values('bob','fannysuruncle')"

        If objMyConn.Errors.Count > 0 Then
            MsgBox "Error inserting into employee" & ": " & Err.Description
            Exit Sub
        End If

        On Error GoTo 0

    'Set and Excecute SQL Command'
        Set objMyCmd.ActiveConnection = objMyConn
        objMyCmd.CommandText = "select * from employee"
        objMyCmd.CommandType = adCmdText

    'Open Recordset'
        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open

    'Copy Data to Excel'
        'ActiveSheet.Range("H25").CurrentRegion.Delete'
        ActiveSheet.Range("H25", "K999").Delete


        ActiveSheet.Range("H25").CopyFromRecordset objMyRecordset

    'Sort the resultset on name'
       
       With ActiveSheet.Sort
           With .SortFields
               .Clear
               .Add Key:=ActiveSheet.Range("I24"), _
                       SortOn:=xlSortOnValues, _
                       Order:=xlAscending, _
                       DataOption:=xlSortTextAsNumbers
   
           End With
           .SetRange ActiveSheet.Range("H24").CurrentRegion
           .Header = xlYes
           .MatchCase = False
           .Orientation = xlTopToBottom
           .SortMethod = xlPinYin
           .Apply
       End With

        ActiveSheet.Range("H23").Value = "Date refreshed: " & Now

End Sub


you will end up with something looking like: