SQL Server Basic Useful Queries

Basic and very useful commands\Scripts:

1. Command to get SQL Server Version Details

  SELECT
  SERVERPROPERTY    ('productversion') As ProductionVersion,
  SERVERPROPERTY    ('productlevel') As ProductionLevel,
  SERVERPROPERTY    ('edition') As Edition,
  @@version As Version;
  GO

2. Command to check database files full status by percent:
SELECT 
            name as DB_FileName,
     convert(varchar(100), FileName) as FileName, 
            cast(Size as decimal(15,2))*8/1024 as 'SizeMB', 
            FILEPROPERTY (name, 'spaceused')*8/1024 as 'UsedMB',
            Convert(decimal(10,2),convert(decimal(15,2), (FILEPROPERTY (name, 'spaceused')))/ 
            Size*100) as PercentFull,
            filegroup_name(groupid) as 'FileGroup',
            FILEPROPERTY (name, 'IsLogFile') as 'IsLogFile' 
      FROM dbo.sysfiles order by IsLogFile
      GO 

---

3. Steps to shrink database log file:

i. Check the log files status using above command.
ii. If log file is full means above 90%, then try to take the backup of the database.
and then run checkpoint using below command:

Use 
GO
CHECKPOINT;

iii. Once checkpoint finished, try to shrink the file using below command:

Use 
GO
DBCC SHRINKFILE(,1)
GO

That's it.

-- Command to get the list of shrink file command for 
--all the databases where log file is full more than 50%

  DECLARE @cSQl varchar(1000) 
  Set @cSQl= 'use [?];select ''use '' + DB_Name() + '' dbcc shrinkfile('' + name +'',1)'' from dbo.sysfiles
  Where FILEPROPERTY (name, ''IsLogFile'')=1 

  and Convert(decimal(10,2),

  convert(decimal(15,2),(FILEPROPERTY (name, ''spaceused'')))/Size*100)<50 

---

4. Command to get DB growth trend on backup basis.

        DECLARE @cmd varchar(4000)

        DECLARE @recid int
 DECLARE @busize decimal(10,2)
 DECLARE @prevbusize decimal(10,2)
 DECLARE @pctchange decimal(10,2)
 DECLARE @dbname varchar(128)

 SET @dbname = 'Master' -- SET THE DATABASE NAME HERE

 CREATE TABLE #dummybackupsizes
 (
 RecID int IDENTITY,
 DatabaseName varchar(128),
 StartDate datetime,
 FinishDate datetime,
 BackupSizeMB decimal(10,2),
 PctChangeFromPrev decimal(10,2)
 )

 INSERT INTO #dummybackupsizes
 SELECT CONVERT(varchar(128),S.database_name) AS DatabaseName, S.backup_start_date AS StartDate, 

        S.backup_finish_date AS FinishDate, CONVERT(decimal(10,2),S.backup_size/1024.0/1024.0) 

        AS BackupSizeMB, CONVERT(decimal(10,2),0.0) AS PctChangeFromPrev
 FROM msdb..backupset S 
 JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
 WHERE S.database_name = @dbname AND S.type = 'D' AND (DAY(S.backup_start_date) = 1 

        OR DAY(S.backup_start_date) = 8 OR DAY(S.backup_start_date) = 15 

        OR DAY(S.backup_start_date) = 28)
 ORDER by backup_start_date DESC

 DECLARE myCursorVariable CURSOR FOR 
 SELECT RecID, BackupSizeMB FROM #dummybackupsizes

---

5. Script to find User Details:

 CREATE TABLE #DB (ServerName VARCHAR(100),
          DBName VARCHAR(100),
          USER_NAME VARCHAR(100),
          ROLES VARCHAR(100),
          LOGIN_NAME VARCHAR(100),
          Def_DB VARCHAR(100),
          Def_SCHEMA VARCHAR(100),
          USERID VARCHAR(100),
          SID VARCHAR(100)
       )

 Exec sp_MSforeachdb 'Use ? ;
 INSERT INTO #DB (User_Name, Roles,Login_Name,Def_db,Def_Schema,UserID,SID) EXEC SP_HELPUSER
 --SELECT @@servername as SERVER_NAME,DB_NAME() AS DATABASE_NAME,

LOGIN_NAME,USER_NAME,ROLES FROM #DB 
 Update #DB SET ServerName = @@SERVERNAME where ServerName IS NULL
 Update #DB SET DBNAME = DB_NAME() where DBNAME IS NULL'
 Select * from #DB
 DROP TABLE  #DB 

---

6. Script to Backup DB Roles & permission

--Script to backup DB roles & Users Permission—run as SQLCMD mode 

 :setvar dbname "test1"

 DECLARE @DBName varchar(100)
 SELECT @DBName=name from sys.databases where name='$(dbname)'
 IF @DBName IS NULL
 BEGIN
     SELECT '$(dbname)' + ' database does not exists' Message
     union
     SELECT 'No permisison generated'
 END
 ELSE IF @DBName IS NOT NULL
 BEGIN

 SET NOCOUNT ON
 --USE '$(dbname)'
 DECLARE @SQLStatement VARCHAR(4000)
 DECLARE @T_DBuser TABLE (DBName SYSNAME, UserName SYSNAME, AssociatedDBRole NVARCHAR(256),

[type] nvarchar(1) NULL)
 DECLARE @UserPermisison TABLE (ID INT IDENTITY(1,1),UserPermissonScript nvarchar(MAX))
 DECLARE @OutPermission Table (ID INT IDENTITY(1,1),UserPermissonScript nvarchar(MAX))
 DECLARE @Databasepermission Table (UserPermissonScript nvarchar(MAX))
 DECLARE @outpt Table (UserPermissonScript nvarchar(MAX))

 DECLARE @Start_loop int
 DECLARE @End_loop int

 SET @SQLStatement='SELECT 
    CASE dp.state_desc 
   WHEN ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT'' 
   ELSE dp.state_desc  
    END  
   + '' '' + dp.permission_name + '' ON '' + 
    CASE dp.class 
   WHEN 0 THEN ''DATABASE::['' + DB_NAME() + '']'' 
   WHEN 1 THEN ''OBJECT::['' + SCHEMA_NAME(o.schema_id) + ''].['' + o.[name] + '']'' 
   WHEN 3 THEN ''SCHEMA::['' + SCHEMA_NAME(dp.major_id) + '']'' 
    END  
   + '' TO ['' + USER_NAME(grantee_principal_id) + '']'' + 
    CASE dp.state_desc 
   WHEN ''GRANT_WITH_GRANT_OPTION'' THEN '' WITH GRANT OPTION;'' 
   ELSE '';''  
    END  
    COLLATE DATABASE_DEFAULT 
 FROM '+@DBName+'.sys.database_permissions dp 
   LEFT JOIN '+@DBName+'.sys.all_objects o 
  ON dp.major_id = o.OBJECT_ID 
 WHERE dp.class < 4 
   AND major_id >= 0 
   AND grantee_principal_id <> 1;'   

   INSERT @Databasepermission
 EXEC (@SQLStatement)

 SET @SQLStatement=''

 SET @SQLStatement='
 SELECT db_name() AS DBName ,dp.name AS UserName,
        USER_NAME(drm.role_principal_id) AS AssociatedDBRole,dp.type
 FROM '+@DBName+'.sys.database_principals dp
 LEFT OUTER JOIN '+@DBName+'.sys.database_role_members drm
 ON dp.principal_id=drm.member_principal_id
 WHERE dp.sid NOT IN (0x01) AND dp.sid IS NOT NULL AND dp.type NOT IN (''C'') AND dp.is_fixed_role <> 1
 AND dp.name NOT LIKE ''##%'' AND dp.name not in (''public'',''dbo'',''guest'') ORDER BY DBName'
 INSERT @T_DBuser
 EXEC (@SQLStatement)
 insert into @UserPermisison(UserPermissonScript)
 SELECT 'EXEC '+@DBName+'.dbo.sp_changedbowner ''sa'''
 UNION
 SELECT 'EXEC '+@DBName+'.dbo.sp_addrolemember ''' + AssociatedDBRole +''''+','''+UserName+'''' 

        FROM @T_DBuser where AssociatedDBRole IS NOT NULL
 UNION
 SELECT DISTINCT 'USE ' + @DBName + '  CREATE USER [' +   UserName +'] FOR LOGIN ['+UserName+']' 

         FROM @T_DBuser WHERE type='S'
 UNION
 SELECT DISTINCT 'USE ' + @DBName + '  CREATE USER [' +   UserName +'] FOR LOGIN ['+UserName+']' 

        FROM @T_DBuser WHERE type='U'
 UNION
 SELECT DISTINCT 'USE ' + @DBName + '  CREATE ROLE [' +   UserName +']' FROM @T_DBuser WHERE type='R'
 UNION
 SELECT * FROM @Databasepermission
 --write code

   SET @Start_loop=0
   SELECT @End_loop=count(*) FROM @UserPermisison 

   while @Start_loop<=@End_loop
   BEGIN
   insert into @OutPermission(UserPermissonScript)
   select UserPermissonScript from @UserPermisison where ID=@Start_loop
   insert into @OutPermission(UserPermissonScript) values ('GO')
              SET @Start_loop=@Start_loop + 1 
   END
   update @OutPermission SET UserPermissonScript=  REPLACE(UserPermissonScript, 'master', @DBName)
   select UserPermissonScript as 'SELECT @@serverName' from @OutPermission order by ID desc
   --select * from @T_DBuser
 SET NOCOUNT OFF
 END

---

7. Powershell command to get the Server Disk space details remotely:
/*Enter COMPUTERNAME */
Get-WmiObject Win32_volume -ComputerName COMPUTERNAME|Format-Table Name, Label, 
@{Name="Size(GB)";Expression={[decimal]("{0:N0}" -f($_.capacity/1gb))}}, 
@{Name="Free Space(GB)";Expression={[decimal]("{0:N0}" -f($_.freespace/1gb))}}, 
@{Name="Free (%)";Expression={"{0,6:P0}" -f(($_.freespace/1gb) / ($_.capacity/1gb))}} –AutoSize

---

 

8.Script to find out CPU Usage, I/O Usage and Memory Usage of database
--Database level / Database wise CPU, memory and I/O usage
--As part of DBA’s daily checklist, we need to monitor few parameters of a database throughout the day. 
--It includes CPU utilization, 

--Memory utilization and I/O utilization. Here are the T-SQL scripts to monitor sql server instances database wise.

 WITH DB_CPU_StatsAS(SELECT DatabaseID,DB_Name(DatabaseID)AS [DatabaseName],
 SUM(total_worker_time)AS [CPU_Time(Ms)]
 FROM sys.dm_exec_query_stats AS qsCROSS APPLY
 (SELECT CONVERT(int, value)AS [DatabaseID] 
 FROM sys.dm_exec_plan_attributes(qs.plan_handle) 
 WHERE attribute =N'dbid')AS epaGROUP BY DatabaseID)
 SELECT ROW_NUMBER()OVER(ORDER BY [CPU_Time(Ms)] DESC)
 AS [row_num],DatabaseName,[CPU_Time(Ms)],
 CAST([CPU_Time(Ms)] * 1.0 /SUM([CPU_Time(Ms)])OVER()* 100.0 AS DECIMAL(5, 2))AS [CPUPercent]
 FROM DB_CPU_StatsWHERE DatabaseID >4 --system databases
 AND DatabaseID <> 32767 -- Resource DB
 ORDER BY row_numOPTION(RECOMPILE);

---

9. Command to get Database last accessed date:

 SELECT DatabaseName, MAX(LastAccessDate) LastAccessDate
 FROM
 (SELECT
  DB_NAME(database_id) DatabaseName
 , last_user_seek
 , last_user_scan
  , last_user_lookup
 , last_user_update
 FROM sys.dm_db_index_usage_stats) AS PivotTable
 UNPIVOT 
 (LastAccessDate FOR last_user_access IN
 (last_user_seek
  , last_user_scan
 , last_user_lookup
 , last_user_update)
  ) AS UnpivotTable
 GROUP BY DatabaseName
 HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')
  ORDER BY 2 

---

10. Command to check Blocking

 Sp_who2
-- To get the details of all the sessions
  SELECT s.session_id, r.blocking_session_id, db_name(r.database_id) as [Database], 

  r.wait_time, s.login_name, s.cpu_time/60000 as [CPU_Time(mins)], 
  s.memory_usage, r.[status], r.percent_complete, r.nest_level, [Text] 
  FROM sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r on s.session_id = r.session_id 
  cross apply sys.dm_exec_sql_text (sql_handle) WHERE s.status = 'running' 
  order by s.cpu_time desc 
-- To get the details of Active sessions
   Sp_who2 ‘active’
-- Get the info in details
  SELECT text,program_name,hostname,* FROM master..sysprocesses 
  cross apply sys.dm_exec_sql_text(sql_handle) 
  WHERE spid='enterSpid'
--To Kill Process
  KILL Spid;
  Example:
  KILL 56 WITH STATUSONLY;
  GO

---

11.Command to get TOP 15 Cached plans Script
--Run the following query to get the TOP 15 cached plans that consumed the most cumulative CPU, 

--All times are in microseconds 
SELECT TOP 15 t.dbid, t.[text], qp.query_plan, qs.total_worker_time as total_cpu_time, qs.max_worker_time as max_cpu_time, qs.creation_time, qs.execution_count, qs.total_elapsed_time, qs.max_elapsed_time, qs.total_logical_reads, qs.max_logical_reads, qs.total_physical_reads, qs.max_physical_reads, t.objectid, t.encrypted, qs.plan_handle, qs.plan_generation_num FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp ORDER BY qs.total_worker_time DESC

---

12. Few useful queries to get cluster details:
Find name of the Node on which SQL Server Instance is Currently running
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName] 
If the server is not cluster, then the above query returns the Host Name of the Server.
Find SQL Server Cluster Nodes
a. Using Function
SELECT * FROM fn_virtualservernodes() 

b. Using DMV 
SELECT * FROM sys.dm_os_cluster_nodes 

Find SQL Server Cluster Shared Drives
a. Using Function
SELECT * FROM fn_servershareddrives() 

b. Using DMV
SELECT * FROM sys.dm_io_cluster_shared_drives

---

13. Command to check 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 desc

---

14.Query to check the running process status by percent:

select
@@servername
, session_id, start_time, status
, percent_complete
, total_elapsed_time/1000/60 as 'elapsed min'
, command, db_name(database_id) as dbname, blocking_session_id
, total_elapsed_time/1000/60/60 as 'elapsed Hrs'
,estimated_completion_time
from sys.dm_exec_requests
where session_id > 50
 

15. Identify the blocking query

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address =wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id =tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id =tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id =wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO
 
Script to view all current processes / sessions on the server

SELECT * from master.dbo.sysprocesses


16.  Query to identify Login details and created date

USE                               
GO                              
SELECT name, createDate                                 
FROM sysusers      
Where name = 'Domain\ID'
GO





17
--Server level Logins and roles
SELECT sp.name AS LoginName,sp.type_desc AS LoginType, 
sp.default_database_name AS DefaultDBName,slog.sysadmin AS SysAdmin,
slog.securityadmin AS SecurityAdmin,slog.serveradmin AS ServerAdmin, 
slog.setupadmin AS SetupAdmin, slog.processadmin AS ProcessAdmin, 
slog.diskadmin AS DiskAdmin, slog.dbcreator AS DBCreator,
slog.bulkadmin AS BulkAdmin
FROM sys.server_principals sp  JOIN master..syslogins slog
ON sp.sid=slog.sid 
WHERE sp.type  <> 'R' AND sp.name NOT LIKE '##%'






18
--Databases users and roles
DECLARE @SQLStatement VARCHAR(4000) 
DECLARE @T_DBuser TABLE (DBName SYSNAME, UserName SYSNAME, AssociatedDBRole NVARCHAR(256)) 
SET @SQLStatement='
SELECT ''?'' AS DBName,dp.name AS UserName,USER_NAME(drm.role_principal_id) AS AssociatedDBRole 
FROM ?.sys.database_principals dp
LEFT OUTER JOIN ?.sys.database_role_members drm
ON dp.principal_id=drm.member_principal_id 
WHERE dp.sid NOT IN (0x01) AND dp.sid IS NOT NULL AND dp.type NOT IN (''C'') 
AND dp.is_fixed_role <> 1 
AND dp.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY DBName'
INSERT @T_DBuser
EXEC sp_MSforeachdb @SQLStatement
SELECT * FROM @T_DBuser ORDER BY DBName





19
--Get objects permission of specified user database 
USE 
GO
DECLARE @Obj VARCHAR(4000)
DECLARE @T_Obj TABLE (UserName SYSNAME, ObjectName SYSNAME, Permission NVARCHAR(128))
SET @Obj='
SELECT Us.name AS username, Obj.name AS object,  dp.permission_name AS permission 
FROM sys.database_permissions dp
JOIN sys.sysusers Us 
ON dp.grantee_principal_id = Us.uid 
JOIN sys.sysobjects Obj
ON dp.major_id = Obj.id '
INSERT @T_Obj 
EXEC sp_MSforeachdb @Obj
SELECT * FROM @T_Obj 


20.
Command to get the list of Stored Procedures
USE 
GO
SELECT name, create_date, modify_date 
FROM sys.objects
WHERE type = 'P' 


21. Script to get the UserDetails
-- This will give you userid, username, LoginType, DBROles, CreatedDate, is_disbaled status.

DECLARE @User_Tbl TABLE
(ServerName sysname, DBName sysname, UserID int, UserName sysname, LoginType sysname, 
AssociatedRole varchar(max), UserStatus varchar(10), create_date datetime,modify_date datetime)
 
INSERT @User_Tbl
EXEC sp_MSforeachdb
'USE [?]
SELECT 
@@ServerName As ServerName,
''?'' AS DB_Name,
su.uid,
case dp.name when ''dbo'' then dp.name + '' (''+ 
(select SUSER_SNAME(owner_sid) 
from master.sys.databases where name =''?'') + '')'' else dp.name end AS UserName,
dp.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,
CASE sp.is_disabled 
WHEN 0 THEN ''Enabled''
WHEN 1 THEN ''Disabled''
ELSE ''NA''
END As [UserStatus],
dp.create_date,
dp.modify_date
FROM sys.database_principals dp
LEFT JOIN sysusers su ON dp.sid=su.sid
LEFT OUTER JOIN sys.database_role_members mem ON dp.principal_id=mem.member_principal_id
LEFT OUTER JOIN sys.Server_principals sp ON sp.sid=dp.sid
WHERE dp.sid IS NOT NULL and dp.sid NOT IN (0x00) and
dp.is_fixed_role <> 1 AND dp.name NOT LIKE ''##%'''
 
SELECT ServerName, DBname,UserID, UserName ,LoginType, 
STUFF((SELECT ',' + CONVERT(VARCHAR(200),associatedrole)
FROM @User_Tbl usertbl2
WHERE usertbl1.DBName=usertbl2.DBName AND usertbl1.UserName=usertbl2.UserName
FOR XML PATH('')),1,1,'') AS UserPermissions, UserStatus, Create_Date 
FROM @User_Tbl usertbl1  
GROUP BY ServerName, DBname ,UserID, UserName ,LoginType, UserStatus, Create_Date
ORDER BY DBName, UserName