SQL Server Database Health checkup tips

  ----// To monitor pressure observing task by workload (CPU/Memory/IO) 

 SELECT 
 AVG(current_tasks_count) AS [avg_current_tasks_count],
 AVG(runnable_tasks_count) AS [avg_runnable_tasks_count],
 AVG(work_queue_count) AS [avg_work_queue_count],
 AVG(pending_disk_io_count) AS [avg_pending_disk_io_count]
 FROM sys.dm_os_schedulers
 WHERE scheduler_id < 255

 ----// To monitor TempDB space usage

 USE [tempdb]
 GO

 SELECT GETDATE() AS [Run Time], SUM(user_object_reserved_page_count) * 8 AS [user_obj_kb]  ,
 SUM(internal_object_reserved_page_count) * 8 AS [internal_obj_kb],
 SUM(version_store_reserved_page_count) * 8 AS [version_store_kb],
 SUM(unallocated_extent_page_count) * 8 AS [freespace_kb],
 SUM(mixed_extent_page_count) * 8 AS [mixedextent_kb]
 FROM sys.dm_db_file_space_usage

 USE [tempdb]
 GO

 SELECT name AS [logical_name], size/128.0 AS [Total size in MB],
 size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS [Available space in MB]
 FROM sys.database_files


-- To check the workload RUN this query to check internal_obj_kb and tempDB size
 USE [AdventureWorks2017]
 GO

 WHILE (1=1)
 BEGIN
DBCC DROPCLEANBUFFERS
SELECT TOP (100000)
a.*
FROM master..spt_values a, master..spt_values b, master..spt_values c
ORDER BY a.number DESC, b.number DESC, c.number DESC
 END

GO

--To check version_store_kb values

----// Enable READ COMMITED SNAPSHOT Isolation level ON database PWI_TEMPDBFULL 
USE [master]
GO
ALTER DATABASE [AdventureWorks2017] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO

----Open a new query window and execute below query

USE [AdventureWorks2017]
GO
CREATE TABLE OpenTran (NAME VARCHAR(50));
GO
INSERT INTO OpenTran VALUES('PWI')
GO


USE [AdventureWorks2017]
GO
SELECT * FROM OpenTran

BEGIN TRANSACTION
UPDATE [OpenTran] SET [NAME] = 'Test'


SELECT * FROM sys.dm_tran_version_store  ----// This DMV s used to check the current transaction detail

ROLLBACK TRANSACTION

GO

----// T-SQL Script to Check SQL Server Job History

SELECT jobs.name AS [Job_Name], 
msdb.dbo.agent_datetime(run_date, run_time) AS [Run DateTime], 
history.run_duration AS [Duration in Second]
FROM msdb.dbo.sysjobs jobs
INNER JOIN msdb.dbo.sysjobhistory history ON jobs.job_id = history.job_id
WHERE jobs.enabled = 1
GO

----// T-SQL Script to Check SQL Server Agent's properties from SSMS

EXEC msdb.dbo.sp_get_sqlagent_properties

----// T-SQL Script to Check Service account for SQL Server

SELECT servicename, service_account
FROM sys.dm_server_services
GO

----// Check the space occupied by the Data file and Log file on the drive by any database.

SELECT DB_NAME(database_id) AS DBName,Name AS Logical_Name, Physical_Name,(size*8)/1024 SizeMB
FROM sys.master_files

WHERE DB_NAME(database_id) = 'Test_CLR'
GO

----// Check how much space will it contain on the drive with the help of the command RESTORE FILELISTONLY.

1 2	RESTORE FILELISTONLY FROM DISK = N'E:\My Work\DBA\DB Backup\Test_Data.bak';

Once you run the command it will display the details of the MDF, LDF or any other file contained in the backup. Pay attention to the column Size. This column actually contains the size of the file in bytes once the backup is restored on the database.

In our example, if you notice there are two different files MDF and LDF. The respective sizes in the bytes are as following:

• MDF: 53477376
• LDF: 3538944

Now you can convert the same using the following formula:

• 1 byte = 8 bits
• 1 kilobyte (K / Kb) = 2^10 bytes = 1,024 bytes
• 1 megabyte (M / MB) = 2^20 bytes = 1,048,576 bytes
• 1 gigabyte (G / GB) = 2^30 bytes = 1,073,741,824 bytes
• 1 terabyte (T / TB) = 2^40 bytes = 1,099,511,627,776 bytes
• 1 petabyte (P / PB) = 2^50 bytes = 1,125,899,906,842,624 bytes
• 1 exabyte (E / EB) = 2^60 bytes = 1,152,921,504,606,846,976 bytes

Or just use Google Calculator (which I do most of the time), which also displays the formula divide the digital storage value by 1e+9.

• MDF: 53477376 = 51 MB
• LDF: 3538944= 3 MB

We need around 512 MB of free space if we want to restore my backup. now you can see, how to forecast the Size of Restore has a very simple solution.