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 readingCategory: SQL Server -DBA
SQL Server -DBA, SQL Server Services, who dropped or altered tables,procedures or databases in SQL Server, What in SQL Server
How 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 readingHow to disable and Enable sa login in SQL Server
–make sure you have a login that is part of the sysadmin role before doing this!!!! USE [master] GO ALTER
Continue reading