SQL Database reference notes

1. The list of the user sessions or queries being run in the SQL Server 

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

2. to shutdown the DB forcefully without having to wait for the transactions - 

USE master
GO
ALTER DATABASE xenmobile
SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

3. Kill the session that we get from 1st instruction above 
kill < session id > 

4. SQL Database restore - source database went into "Restoring" state -  

root cause - 
This can happen when we do the restore of target database with options - 
Take tail-log backup restore ; leave source db in the restore state.  
resolution - 
RESTORE DATABASE [SOURCE DB] WITH RECOVERY; 

This brings the Source DB back to online state. 

5. SQL server database encryption - 

Query to check if the DB is in encrypted state or not -  

additional info - https://www.mssqltips.com/sqlservertip/2641/sql-server-transparent-data-encryption-tde-performance-comparison/ 

SELECT
    db.name,
    db.is_encrypted,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
FROM
    sys.databases db
    LEFT OUTER JOIN sys.dm_database_encryption_keys dm
        ON db.database_id = dm.database_id;
GO

6.. List of the client / user connections to a database can be found using - 

DECLARE @AllConnections TABLE(
    SPID INT,
    Status VARCHAR(MAX),
    LOGIN VARCHAR(MAX),
    HostName VARCHAR(MAX),
    BlkBy VARCHAR(MAX),
    DBName VARCHAR(MAX),
    Command VARCHAR(MAX),
    CPUTime INT,
    DiskIO INT,
    LastBatch VARCHAR(MAX),
    ProgramName VARCHAR(MAX),
    SPID_1 INT,
    REQUESTID INT
)

INSERT INTO @AllConnections EXEC sp_who2

SELECT * FROM @AllConnections WHERE DBName = 'YourDatabaseName'

7. Row count for a DB on SQL Server -   

SELECT T.name AS [TABLE NAME], 
       I.rows AS [ROWCOUNT] 
FROM   sys.tables AS T 
       INNER JOIN sys.sysindexes AS I 
               ON T.object_id = I.id 
                  AND I.indid < 2 
ORDER  BY I.rows DESC