SELECT [s_tst].[session_id], [s_es].[login_name] AS [Login Name], DB_NAME (s_tdt.database_id) AS [Database], [s_tdt].[database_transaction_begin_time] AS [Begin Time], [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes], [s_tdt].[database_transaction_log_bytes_reserved] AS
Continue readingAuthor:
How to find When database was last accessed in Sql Server ?
SELECT d.name, last_user_seek = MAX(last_user_seek), last_user_scan = MAX(last_user_scan), last_user_lookup = MAX(last_user_lookup), last_user_update = MAX(last_user_update) FROM sys.dm_db_index_usage_stats AS i JOIN sys.databases
Continue readingHow to find connection count with login name in Sql Server ?
SELECT login_name, session_count, last_batch_time FROM( SELECT login_name ,COUNT(session_id) AS session_count, MAX(last_request_end_time) AS last_batch_time FROM sys.dm_exec_sessions GROUP BY login_name ) p
Continue readingHow to find Total connection in sql server ?
SELECT COUNT(dbid) as TotalConnections FROM sys.sysprocesses WHERE dbid > 0
Continue readingHow to find number of connection in SQL Server based on each databases
SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame
Continue readingHow to kill all connection of a databases in sql server ?
–Set your Database in single user mode ALTER DATABASE yourDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE —Again Set your database in
Continue readingHow to restore database from full & differential backups in SQL Server?
To restore the full & differential database backup please follow the below steps:- Restore Full backup Open the Restore Database window in Microsoft
Continue readingHow to Find indexes for a database in SQL SERVER
use AdventureWorks2012 go SELECT DB_NAME(ddius.[database_id]) AS database_name , OBJECT_NAME(ddius.[object_id], DB_ID(‘AdventureWorks2012’)) –replace db name AS [object_name] , asi.[name] AS
Continue readingHow to find who are directly connected to the SQL Server instance ?
SELECT dec.client_net_address , des.host_name , dest.text FROM sys.dm_exec_sessions des INNER JOIN sys.dm_exec_connections dec ON des.session_id = dec.session_id CROSS APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle)
Continue readinghow to unhide all user databases in SQL SERVER ?
unhide all databases USE MASTER GO GRANT VIEW ANY DATABASE TO PUBLIC; GO
Continue reading