Anuroop Challa

Data-file size check and get information for all databases on the server :- ============================================== SELECT      STATS.Volume_Mount_Point AS 'Drive',             DB_NAME(STATS.Database_ID) AS 'Database Name',             FILES.[Name] AS 'File Name',             FILES.[Size] / 128 AS 'File Size',             ((STATS.Total_Bytes / 1024) / 1024)             AS 'Drive Capacity (MB)',             ((STATS.Available_Bytes / 1024) / 1024)             AS 'Drive Free Space (MB)' FROM  sys.master_files FILES CROSS APPLY             sys.dm_os_volume_stats             (FILES.database_id, FILES.file_id) STATS

MS-SQL server Uptime or last restart check script ======================================== DECLARE @server_start_time DATETIME, @seconds_diff INT, @years_online INT, @days_online INT, @hours_online INT, @minutes_online INT, @seconds_online INT ; SELECT @server_start_time = login_time FROM master.sys.sysprocesses WHERE spid = 1 ; SELECT @seconds_diff = DATEDIFF(SECOND, @server_start_time, GETDATE()), @years_online = @seconds_diff / 31536000, @seconds_diff = @seconds_diff % 31536000, @days_online = @seconds_diff / 86400, @seconds_diff = @seconds_diff % 86400, @hours_online = @seconds_diff / 3600, @seconds_diff = @seconds_diff % 3600, @minutes_online = @seconds_diff / 60, @seconds_online = @seconds_diff % 60 ; SELECT @server_start_time AS server_start_time, @years_online AS years_online, @days_online AS days_online, @hours_online AS hours_online, @minutes_online AS minutes_online, @seconds_online AS seconds_online ;

How to check session deatils with blocked in MS_SQL ======================================= ====================================================== Full session deatils with blocked ========================================================= USE Master GO EXEC sp_who2 GO ----------------------------------or-------------------------- SELECT spid, kpid, blocked, d.name, open_tran, status, hostname, cmd, login_time, loginame, net_library FROM sys.sysprocesses p INNER JOIN sys.databases d  on p.dbid=d.database_id

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 retur...

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 [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')