1. sp_helpdb - it outputs all the related information on the database/s. This summarizes the status of all your databases.
2. Check the services - well if you want to make sure that your instance is up and running, you can try to consider to logon to the server and to the instance as well.
3. Using T-SQL - there are a bunch of queries available out there you can use. This query will help us identify the instance uptime as well as if the SQL Agent is running.
USE master;
SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT 'SQL Server is running but SQL Server Agent <<NOT>> running'
END
ELSE BEGIN
PRINT 'SQL Server and SQL Server Agent both are running'
END
4. Checking the transaction log space - when dealing with performance, its interesting to look at how huge our transaction log space. Doing so will help prevent from encountering connectivity issues with the database caused by the transaction logs.You can use a dbcc command to check. So that you can run your existing transaction log backups if necessary.
dbcc sqlperf(logspace)
5. Check disk space utilization
Those I mentioned are the top three things I usually consider in doing some checks in MSSQL. Remember, if there's let say a performance degradation we have to look a lot of things like the blocking processes, database space usage as well as in the server part like the disk I/O and stuffs. We'll talk about that some other time. Thanks for dropping by. See you later.
