Anuroop Challa

SQL to check active users connect MS_SQL database ======================================= SELECT  @@Servername AS Server ,         DB_NAME(database_id) AS DatabaseName ,         COUNT(database_id) AS Connections ,         Login_name AS LoginName ,         MIN(Login_Time) AS Login_Time ,         MIN(COALESCE(last_request_end_time, last_request_start_time))                                                          AS Last_Batch FROM    sys.dm_exec_sessions WHERE   database_id > 0         AND DB_NAME(database_id) NOT IN ( 'master', 'msdb' ) GROUP BY database_id ,          login_name ORDER BY DatabaseName;

How to check Authentication mode of SQL server ( SQL Server /Windows Authentication (or) both) ========================================= Use Master GO SELECT             CASE SERVERPROPERTY('IsIntegratedSecurityOnly')             WHEN 0 THEN 'Mixed Mode - Allows Both SQL Server and Windows Authentication Mode'             WHEN 1 THEN 'Allows Only Windows Authentication Mode' END AS [Currently Used SQL Server Authentication Mode] GO

how to check trans log backup details in ms-sql ===================================== SELECT   d.name,          MAX(b.backup_finish_date) AS backup_finish_date FROM     master.sys.sysdatabases d          LEFT OUTER JOIN msdb..backupset b          ON       b.database_name = d.name          AND      b.type          = 'L' GROUP BY d.name ORDER BY backup_finish_date DESC

=========================================== BACKUP START TIME AND FINISH TIME =========================================== DECLARE @dbname sysname SET @dbname = NULL --set this to be whatever dbname you want SELECT bup.user_name AS [User],  bup.database_name AS [Database],  bup.server_name AS [Server],  bup.backup_start_date AS [Backup Started],  bup.backup_finish_date AS [Backup Finished]  ,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'  AS [Total Time] FROM msdb.dbo.backupset bup WHERE bup.backup_set_id IN   (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset   WHERE database_name = ISNULL(@dbname, database_name) --if no dbname, then return all   AND type = '

MS-sql database back up check. ========================== -------------------------------------------------------------- Most Recent Database Backup for Each Database --------------------------------------------------------------- SELECT    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,    msdb.dbo.backupset.database_name,    MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date FROM   msdb.dbo.backupmediafamily    INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE  msdb..backupset.type = 'D' GROUP BY    msdb.dbo.backupset.database_name ORDER BY    msdb.dbo.backupset.database_name   ---------------------------------------------------------------------------------- Most Recent Database Backup for Each Database  in detailed view ----------------------------------------------------------------------------------- SELECT    A.[Server],    A.last_db_backup_date,

select [name], DATABASEPROPERTYEX([name],'recovery') from sysdatabases where name not in ('master','model','tempdb','msdb')

Ms-sql db sizes check script =============================== SELECT     D.name,     F.Name AS FileType,     F.physical_name AS PhysicalFile,     F.state_desc AS OnlineStatus,     CAST((F.size*8)/1024 AS VARCHAR(26)) + ' MB' AS FileSize,     CAST(F.size*8 AS VARCHAR(32)) + ' Bytes' as SizeInBytes FROM     sys.master_files F     INNER JOIN sys.databases D ON D.database_id = F.database_id ORDER BY     D.name

How to find SQL server error log location check =========================================== USE MASTER GO EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file' GO

How to find MS-SQL Database File Location :- ============================== EXEC sp_Helpfile; (or) --  run under particular db for file location. SELECT  @@Servername AS Server ,         DB_NAME() AS DB_Name ,         File_id ,         Type_desc ,         Name ,         LEFT(Physical_Name, 1) AS Drive ,         Physical_Name ,         RIGHT(physical_name, 3) AS Ext ,         Size ,         Growth FROM    sys.database_files ORDER BY File_id; GO

how to check  Edition. ================= select serverproperty('Edition')

 All locks and their definition in database ======================================== by using below ssql you get same out put. ------------------------------------------------------------------------- SELECT TYPE, name, description FROM v$lock_type ORDER BY TYPE; ------------------------------------------------------- output:- TYPE   NAME DESCRIPTION AB Auto BMR A general class of locks used by auto BMR for various purposes AC Application Continuity Synchronizes new service activation AD ASM Disk AU Lock Synchronizes accesses to a specific ASM disk AU AE Edition Lock Prevent Dropping an edition in use AF Advisor Framework This enqueue is used to serialize access to an advisor task AG Analytic Workspace Generation Synchronizes generation use of a particular workspace AH ASM Relocation Lock High Protects locked extent pointers during ASM file relocation

Grant READ vs SELECT in oracle 12C ======================================== From oracle 12c has introduced new privilege  "READ" it will be little different than select. The main difference b/w select and read are. select privilege will allow user can lock table rows, Read privilege does not allow to lock. example:- SQL> GRANT SELECT ANY TABLE TO <schema_name>; Grant succeeded. SQL> GRANT READ ANY TABLE TO <schema_name>; Grant succeeded. grant for particular object. SQL> GRANT SELECT ON <schema_name.Object_name> TO <schema_name>; Grant succeeded. SQL> GRANT READ ON  <schema_name.Object_name> TO <schema_name>; Grant succeeded. Thanks for your visit! your comment and suggestions are more helpful in my future posts more effective.