MS-SQL

by

List of users in the instance

Needed a list of all users on the instance as per their domain name:

use master;
SELECT name FROM sys.database_principals
WHERE type_desc=’DATABASE_ROLE’ and name like ‘domainname%’
UNION ALL
SELECT name FROM sys.server_principals
WHERE type_desc=’SERVER_ROLE’ and name like ‘domainname%’
UNION ALL
SELECT name FROM sys.syslogins where name like ‘domainname%’

by

MS-Sql – table size

Script to find out the space reserved and used by sqlserver on a per table basis

DECLARE @ShowResultsInKB1_MB0 BIT
SET @ShowResultsInKB1_MB0 = 0;
WITH TableSizes AS (
    SELECT
        a3.name AS [schemaname],
        a2.name AS [tablename],
        a1.ROWS AS ROW_COUNT,
        (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
        a1.DATA * 8 AS DATA,
        (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.DATA THEN (a1.used + ISNULL(a4.used,0)) - a1.DATA ELSE 0 END) * 8 AS index_size,
        (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
    FROM
        (SELECT
            ps.object_id,
            SUM (
                CASE
                    WHEN (ps.index_id < 2) THEN ROW_COUNT
                    ELSE 0
                END
                ) AS [ROWS],
            SUM (ps.reserved_page_count) AS reserved,
            SUM (
                CASE
                    WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                    ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
                END
                ) AS DATA,
            SUM (ps.used_page_count) AS used
        FROM sys.dm_db_partition_stats ps
        GROUP BY ps.object_id) AS a1
    LEFT OUTER JOIN
        (SELECT
            it.parent_id,
            SUM(ps.reserved_page_count) AS reserved,
            SUM(ps.used_page_count) AS used
         FROM sys.dm_db_partition_stats ps
         INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
         WHERE it.internal_type IN (202,204)
         GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
    INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
    INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
    WHERE a2.TYPE <> N'S' AND a2.TYPE <> N'IT'
)
SELECT SchemaName, TableName, ROW_COUNT
    , CAST(ROUND(CASE @ShowResultsInKB1_MB0 WHEN 1 THEN reserved ELSE reserved / 1024.0 END, 2) AS DECIMAL(18,2))  AS reserved
    , CAST(ROUND(CASE @ShowResultsInKB1_MB0 WHEN 1 THEN DATA ELSE DATA / 1024.0 END, 2) AS DECIMAL(18,2))  AS DATA
    , CAST(ROUND(CASE @ShowResultsInKB1_MB0 WHEN 1 THEN index_size ELSE index_size / 1024.0 END, 2) AS DECIMAL(18,2))  AS index_size
    , CAST(ROUND(CASE @ShowResultsInKB1_MB0 WHEN 1 THEN unused ELSE unused / 1024.0 END, 2) AS DECIMAL(18,2))  AS unused
FROM TableSizes
ORDER BY ROW_COUNT DESC,SchemaName, TableName

example output
sql_edgesight_size
by

Locks in SqlServer

run the following to see the database locks – works for 2005+

SELECT  L.request_session_id AS SPID,
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName,
        P.object_id AS LockedObjectId,
        L.resource_type AS LockedResource,
        L.request_mode AS LockType,
        ST.text AS SqlStatementText,
        ES.login_name AS LoginName,
        ES.host_name AS HostName,
        TST.is_user_transaction AS IsUserTransaction,
        AT.name AS TransactionName,
        CN.auth_scheme AS AuthenticationMethod
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id
by

MS-SQL Backup History

run the following to see the backup history of your chosen database.

mssql_backup_history

 SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  
ORDER BY  
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_finish_date