Hi Friends,

From last few days, I was thinking about writing a series on Queries\T-SQL commands that we use in our day to day operations or those queries which should be handy for any SQL DBA(Junior or senior) out there. In this series, I’ll try to share as many queries as I have or I use. There are 1000s features in SQL Server and each feature has its own set of infinite queries so any blog cannot cover all queries and all features but I will try to cover the queries that is needed in our day to day activities as a DBA.

Comments are most welcome by readers which can also enhance my database of queries. So here goes first part of this series.
**DISCLAIMER : I have personally used all these queries but these may or may not work in your environment depending on your server setting, server edition\version etc. Please test these queries in lower environment first before executing these in production directly**

Note :- I would like to thank countless sites and articles present there on google which helped me save all these queries to my personal QUERY DATABASE. This series is for knowledge sharing pupose only and you can share these queries again to your colleagues and friends.

QUERY 1 :-
Free & Used space of all files in a Database : Many times we need to check which all datafiles are using how much space. Instead of using GUI, you can use below query directly to get all the details :-

select
 name
 , filename
 , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
 , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
 , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
 from dbo.sysfiles a

QUERY 2 :-
All Database files in a Instance with location : This query lists all the database files present in a Instance. Output of this file contains Logical filename and its physical path in the server.

SELECT name, physical_name AS current_file_location
 FROM sys.master_files

QUERY 3 :-

Query to find all the processes running on a particular DB : In many cases, you may not want to see for running processes on all the databases from sp_who2 and want to look directly for a particular database :-

SELECT [Database]=DB_NAME(dbid), spid, last_batch, status, hostname, loginame
 FROM sys.sysprocesses
 WHERE dbid = DB_ID(‘TESTDB’); ----change the databse name here

QUERY 4 :-

All Databases last access time from last reboot :- You may need to find which database is not being used by users in long time. This query may come handy but it has limitations that it contains data from last service restart only as data from DMV dm_db_index_usage_stats gets flushed after every service recycle :-

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

QUERY 5 :-

Script to find fragmentation level for every table of a database :- The below script provides fragmentation percentage for every index of every table in a database :-

select TableName=object_name(dm.object_id)
 ,IndexName=i.name
 ,IndexType=dm.index_type_desc
 ,[%Fragmented]=avg_fragmentation_in_percent
 from sys.dm_db_index_physical_stats(db_id(),null,null,null,'sampled') dm
 join sys.indexes i on dm.object_id=i.object_id and dm.index_id=i.index_id
 order by avg_fragmentation_in_percent desc

I shall be back with more queries soon. Please comment your views. Thanks for reading!!