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/
6.. List of the client / user connections to a database can be found using -
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;
GO6.. 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
