Sql database space details query

Find Sql database recovery model, Space used and space freed by MDF and LDF files on all databases.

------------------------------Data file size----------------------------
IF EXISTS (
  SELECT *
  FROM tempdb.sys.all_objects
  WHERE NAME LIKE '%#dbsize%'
  )
 DROP TABLE #dbsize

CREATE TABLE #dbsize (
 Dbname VARCHAR(30)
 ,dbstatus VARCHAR(20)
 ,Recovery_Model VARCHAR(10) DEFAULT('NA')
 ,file_Size_MB DECIMAL(20, 2) DEFAULT(0)
 ,Space_Used_MB DECIMAL(20, 2) DEFAULT(0)
 ,Free_Space_MB DECIMAL(20, 2) DEFAULT(0)
 )
GO

INSERT INTO #dbsize (
 Dbname
 ,dbstatus
 ,Recovery_Model
 ,file_Size_MB
 ,Space_Used_MB
 ,Free_Space_MB
 )
EXEC sp_msforeachdb 'use [?];
  select DB_NAME() AS DbName,
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) , 
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')), 
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB 
from sys.database_files  where type=0 group by type'
GO

-------------------log size--------------------------------------
IF EXISTS (
  SELECT *
  FROM tempdb.sys.all_objects
  WHERE NAME LIKE '#logsize%'
  )
 DROP TABLE #logsize

CREATE TABLE #logsize (
 Dbname VARCHAR(30)
 ,Log_File_Size_MB DECIMAL(20, 2) DEFAULT(0)
 ,log_Space_Used_MB DECIMAL(20, 2) DEFAULT(0)
 ,log_Free_Space_MB DECIMAL(20, 2) DEFAULT(0)
 )
GO

INSERT INTO #logsize (
 Dbname
 ,Log_File_Size_MB
 ,log_Space_Used_MB
 ,log_Free_Space_MB
 )
EXEC sp_msforeachdb 'use [?];
  select DB_NAME() AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB 
from sys.database_files  where type=1 group by type'
GO

--------------------------------database free size
IF EXISTS (
  SELECT *
  FROM tempdb.sys.all_objects
  WHERE NAME LIKE '%#dbfreesize%'
  )
 DROP TABLE #dbfreesize

CREATE TABLE #dbfreesize (
 NAME VARCHAR(50)
 ,database_size VARCHAR(50)
 ,Freespace VARCHAR(50) DEFAULT(0.00)
 )

INSERT INTO #dbfreesize (
 NAME
 ,database_size
 ,Freespace
 )
EXEC sp_msforeachdb 
 'use ?;SELECT database_name = db_name()
    ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')
    ,''unallocated space'' = ltrim(str((
                CASE 
                    WHEN dbsize >= reservedpages
                        THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
                    ELSE 0
                    END
                ), 15, 2))
FROM (
    SELECT dbsize = sum(convert(BIGINT, CASE 
                    WHEN type = 0
                        THEN size
                    ELSE 0
                    END))
        ,logsize = sum(convert(BIGINT, CASE 
                    WHEN type <> 0
                        THEN size
                    ELSE 0
                    END))
    FROM sys.database_files
) AS files
,(
    SELECT reservedpages = sum(a.total_pages)
        ,usedpages = sum(a.used_pages)
        ,pages = sum(CASE 
                WHEN it.internal_type IN (
                        202
                        ,204
                        ,211
                        ,212
                        ,213
                        ,214
                        ,215
                        ,216
                        )
                    THEN 0
                WHEN a.type <> 1
                    THEN a.used_pages
                WHEN p.index_id < 2
                    THEN a.data_pages
                ELSE 0
                END)
    FROM sys.partitions p
    INNER JOIN sys.allocation_units a
        ON p.partition_id = a.container_id
    LEFT JOIN sys.internal_tables it
        ON p.object_id = it.object_id
) AS partitions'

-----------------------------------
IF EXISTS (
  SELECT *
  FROM tempdb.sys.all_objects
  WHERE NAME LIKE '%#alldbstate%'
  )
 DROP TABLE #alldbstate

CREATE TABLE #alldbstate (
 dbname VARCHAR(25)
 ,DBstatus VARCHAR(25)
 ,R_model VARCHAR(20)
 )

--select * from sys.master_files
INSERT INTO #alldbstate (
 dbname
 ,DBstatus
 ,R_model
 )
SELECT NAME
 ,CONVERT(VARCHAR(20), DATABASEPROPERTYEX(NAME, 'status'))
 ,recovery_model_desc
FROM sys.databases

--select * from #dbsize
INSERT INTO #dbsize (
 Dbname
 ,dbstatus
 ,Recovery_Model
 )
SELECT dbname
 ,dbstatus
 ,R_model
FROM #alldbstate
WHERE DBstatus <> 'online'

INSERT INTO #logsize (Dbname)
SELECT dbname
FROM #alldbstate
WHERE DBstatus <> 'online'

INSERT INTO #dbfreesize (NAME)
SELECT dbname
FROM #alldbstate
WHERE DBstatus <> 'online'

SELECT d.Dbname
 ,d.dbstatus
 ,d.Recovery_Model
 ,(file_size_mb + log_file_size_mb) AS DBsize
 ,d.file_Size_MB
 ,d.Space_Used_MB
 ,d.Free_Space_MB
 ,l.Log_File_Size_MB
 ,log_Space_Used_MB
 ,l.log_Free_Space_MB
 ,fs.Freespace AS DB_Freespace_MB
FROM #dbsize d
INNER JOIN #logsize l ON d.Dbname = l.Dbname
INNER JOIN #dbfreesize fs ON d.Dbname = fs.NAME
ORDER BY Dbname

Finding recovery model type for all sql databases in a server.

Finding recovery model type for all sql databases in a server.

SELECT NAME AS [Database Name]
 ,recovery_model_desc AS [Recovery Model]
FROM sys.databases
GO

Fixing database is in use error while restoring sql database.

Following is the one of the most useful query while restoring database. It is used when we get error: Database is in use while restoring db from the backup.
Simply, run below query and then again try to restore database. This time, you will able to restore it. Here is the magical query:

------------------------------------------------------------
--<Config>
------------------------------------------------------------ 
DECLARE @DatabaseName VARCHAR(max)

SET @DatabaseName = 'yourdbname'

------------------------------------------------------------
--</Config>
------------------------------------------------------------
USE master

DECLARE @processes TABLE (ID INT)
DECLARE @databaseID INT

SELECT @databaseID = dbid
FROM sys.sysdatabases
WHERE NAME = @DatabaseName

INSERT INTO @processes
SELECT spid
FROM sys.sysprocesses
WHERE dbid = @databaseid

WHILE EXISTS (
  SELECT TOP (1) *
  FROM @processes
  )
BEGIN
 DECLARE @Process INT
 DECLARE @statement VARCHAR(max)

 SELECT TOP 1 @process = id
 FROM @processes

 SET @statement = 'kill ' + cast(@process AS VARCHAR(20))

 EXEC (@statement)

 DELETE TOP (1)
 FROM @processes
END

#TIP: Query to find the last sql server restart using DMVs

Following script will help us to find the last sql server restart datetime.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Find the dependent sql objects on a defined object

Find the dependent sql objects on a defined object:

Below query shows the dependent sql objects like tables, views etc. of defined object (here. YourObject).


SELECT referencing_schema_name, referencing_entity_name,referencing_id, referencing_class_desc, is_caller_dependentFROM sys.dm_sql_referencing_entities ('YourObject', 'OBJECT');GO

determining index fragmentation and business rules.

Index Fragmentation: It plays an important role in improving the performance of the sql server. The indexes get fragments as more and more records are inserted and updated to the respective table. so in order to check the fragmentation, use the following query to get all information: 

PS: Business Rules defined by Microsoft to determine the fragmentation of the indexes. 
1. If the index fragmentation average is greater than and equal to 30 percent, then rebuild/ recreate the index to reduce fragmentation.
2. If the index fragmentation average is between 5 to 30 percent then reorganize a respective index.
3. If the index fragmentation average is below 5 percent, then no action is required.

Also, in general for page_count, we use to practice page_count=>2000.

So, for example, if certain index at avg_Fragmentation_percent>30 but page_count= 100 (assuming), then we can ignore that index.

Query to determine fragmentation is defined below: 

SELECT ps.database_id
 ,ps.OBJECT_ID
 ,ps.index_id
 ,b.NAME
 ,ps.avg_fragmentation_in_percent
 ,ps.avg_fragment_size_in_pages
 ,ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
 AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
 AND ps.avg_fragmentation_in_percent >= 5
 AND page_count >= 2000
 AND b.NAME IS NOT NULL
ORDER BY ps.avg_fragmentation_in_percent DESC
GO

When was last tempdb cleared

Following query will help us to find when was tempdb cleared? Means when was last usage stats cleared? This will show how many days are passed when usage stats are cleared.

SELECT  DATEDIFF(DAY, sd.crdate, GETDATE()) AS days_historyFROM    sys.sysdatabases sdWHERE   sd.[name] = 'tempdb' ;

Finding the Last Sql Server Restarted.

Following query will help in finding the last Sql Server restarted date.

This is very useful query when you are looking for unused indexes. Before dropping unused indexes,  always check last sql server restarted by using following query.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

#Optimization TIP: Finding the Missing Indexes in your Sql Database.

Following Query will help you find the missing indexes in your database along with Total Costing of that Index. 

PS: Adding new Indexes is a tricky job. It may vary from scenario to scenario. You can add Indexes having highest Total costing and having least or none included columns. Complex Composite Indexes should be avoided.

SELECT *
FROM (
 SELECT OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) AS [TableName]
  ,ISNULL(dm_mid.equality_columns, '') + CASE 
   WHEN dm_mid.equality_columns IS NOT NULL
    AND dm_mid.inequality_columns IS NOT NULL
    THEN ','
   ELSE ''
   END + ISNULL(dm_mid.inequality_columns, '') [Column]
  ,dm_mid.included_columns
  ,ROUND(dm_migs.avg_total_user_cost * dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans), 0) AS [Total Cost]
  ,'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') + CASE 
   WHEN dm_mid.equality_columns IS NOT NULL
    AND dm_mid.inequality_columns IS NOT NULL
    THEN '_'
   ELSE ''
   END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + ']' + ' ON ' + dm_mid.statement + ' (' + ISNULL(dm_mid.equality_columns, '') + CASE 
   WHEN dm_mid.equality_columns IS NOT NULL
    AND dm_mid.inequality_columns IS NOT NULL
    THEN ','
   ELSE ''
   END + ISNULL(dm_mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
 FROM sys.dm_db_missing_index_groups dm_mig
 INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle
 INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle
 WHERE dm_mid.database_ID = DB_ID()
 ) x
ORDER BY x.[Total Cost] DESC
GO

Optimization Techniques- Finding Most expensive Queries in Sql Server

Following query will help you finding the most expensive queries in your sql database. This query will tell you about Total Logical Reads, Total Logical Writes, Last Logical Reads, Last Logical Writes etc.

Run the following query in your sql server query window:

SELECT TOP 10 qs.execution_count

        ,qs.total_logical_reads

        ,qs.last_logical_reads

        ,qs.total_logical_writes

        ,qs.last_logical_writes

        ,qs.total_worker_time

        ,qs.last_worker_time

        ,qs.total_elapsed_time / 1000000 total_elapsed_time_in_S

        ,qs.last_elapsed_time / 1000000 last_elapsed_time_in_S

        ,qs.last_execution_time

        ,qp.query_plan

        ,SUBSTRING(qt.TEXT, (qs.statement_start_offset / 2) + 1, (

                       (

                               CASE qs.statement_end_offset

                                      WHEN - 1

                                              THEN DATALENGTH(qt.TEXT)

                                      ELSE qs.statement_end_offset

                                      END - qs.statement_start_offset

                               ) / 2

                       ) + 1)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_worker_time DESC -- CPU time

SQL Optimization Techniques- Finding Heavy Stored Procedures in Sql Server

Following query will help you finding the most heavy stored procedures in your sql database. This query will tell you Number of Execution Count, AVG CPU time to execute the respective SP and Total CPU Time of the respective SP. 

Run the following query in your sql server query window:

SELECT DB_NAME(dest.dbid) AS [Database name]

        ,OBJECT_NAME(dest.objectid, dest.dbid) AS [Stored procedure name]

        ,[Execution Count]

        ,[Avg CPU time (ms)]

        ,[Total CPU time (ms)] [Avg Elapsed time (ms)]

        ,[Creation time]

        ,[Last Execution time] [Total Elapsed time (ms)]

        ,[Plan Count]

        ,getdate() AS [Recorded date]

        ,sql_handle

        ,dest.TEXT AS 'Text'

FROM (

        SELECT TOP 100 sql_handle

               ,COUNT(DISTINCT plan_handle) AS [Plan Count]

               ,MAX(execution_count) AS [Execution Count]

               ,SUM(total_worker_time / execution_count) / 1000 AS [Avg CPU time (ms)]

               ,SUM(total_elapsed_time / execution_count) / 1000 AS [Avg Elapsed time (ms)]

               ,MAX(creation_time) AS [Creation time]

               ,MAX(last_execution_time) AS [Last Execution time]

               ,SUM(total_worker_time / 1000) AS [Total CPU time (ms)]

               ,SUM(total_elapsed_time / 1000) AS [Total Elapsed time (ms)]

        FROM sys.dm_exec_query_stats

        GROUP BY sql_handle

        ORDER BY SUM(total_worker_time / execution_count) / 1000 DESC

        ) deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

WHERE dest.dbid = DB_ID();

Useful System functions in Sql server

Following are the some of the most useful system functions   which helps us to get useful information.

·         Command: sp_helpdb ‘database_name’

Example: EXEC sp_helpdb N'AdventureWorks2012';

Details: informs about a specified database.

·         Command: sp_helpindex ‘table_name’

Example: EXEC sp_helpindex N'tblEmployee';

Details: gives information about all the indexes created on defined table.

·         Command: sp_helpstats ‘table_name’

Example: EXEC sp_helpstats N'tblEmployee';

Details: gives information about columns and indexes on the specified table.

·         Command: sp_ helptext ‘table_name’

Example: EXEC sp_helptext N'sp_getAllEmployees';

Details: gives the definition of Stored Procedures, User defined Functions, User defined Rule, Trigger, view, Computed Column.

·         Command: sp_help  ‘Object_name’

Example: EXEC sp_help N'sp_getAllEmployees';

Details: gives information about database object, like in this case, for SP, it gets sp details and parameters information like name, type, length etc .

Find the list of indexes in sql database.

Tip #5 Find List of Indexes in Sql Database.

I am posting some of the most useful queries which help a dba/ developer to optimize database performance. Please reference to different Tips in other blogs as well.

Following query will help in getting the list of all indexes in your database. Run the following query in your sql server query window:

SELECT *
INTO #tmp
FROM (
 SELECT DISTINCT T.[name] AS [table_name]
  ,I.[name] AS [index_name]
  ,AC.[name] AS [column_name]
  ,I.[type_desc]
  ,I.[is_unique]
  ,I.[data_space_id]
  ,I.[ignore_dup_key]
  ,I.[is_primary_key]
  ,I.[is_unique_constraint]
  ,I.[fill_factor]
  ,I.[is_padded]
  ,I.[is_disabled]
  ,I.[is_hypothetical]
  ,I.[allow_row_locks]
  ,I.[allow_page_locks]
  ,IC.[is_descending_key]
  ,IC.[is_included_column]
 FROM sys.[tables] AS T
 INNER JOIN sys.[indexes] I
  ON T.[object_id] = I.[object_id]
 INNER JOIN sys.[index_columns] IC
  ON I.[object_id] = IC.[object_id]
 INNER JOIN sys.[all_columns] AC
  ON T.[object_id] = AC.[object_id]
   AND IC.[column_id] = AC.[column_id]
 WHERE T.[is_ms_shipped] = 0
  AND I.[type_desc] <> 'HEAP'
 ) m
ORDER BY m.table_name
 ,m.index_name

SELECT * FROM #tmp

Find Heap Tables in your sql database

Tip #4 Find Heap Tables in your sql database.

I am posting some of the most useful queries which help a dba/ developer to optimize database performance. Please reference to different Tips in other blogs as well.




Following query will help in determining the heap Tables in Sql database. Some people may not be aware of: What are Heap Tables?


Heaps (Tables without Clustered Indexes): A heap is a table without a clustered index. One or more non clustered indexes can be created on tables which can be stored as a heap. Data which is stored in heap,  is without specifying any order. 




Run the following query in your sql server query window:

SELECT T.NAME 'Heaptable'

FROM sys.indexes I
INNER JOIN sys.tables T
 ON I.object_id = T.object_id
WHERE I.type = 0
 AND T.type = 'U'

Calculate lines of code in Sql Stored Procedure

Tip #3 Find the lines of code in Sql Stored Procedure.

I am posting some of the most useful queries which help a dba/ developer to optimize database performance. Please reference to different Tips in other blogs as well.

Following query will help in determining the lines of code present in the stored procedure. This query will exclude spaces and comments while calculating the lines of codes.. Run the following query in your sql server query window:

SELECT t.sp_name
 ,sum(t.lines_of_code) - 1 AS lines_ofcode
 ,t.type_desc
FROM (
 SELECT o.NAME AS sp_name
  ,(len(c.TEXT) - len(replace(c.TEXT, CHAR(10), ''))) AS lines_of_code
  ,CASE 
   WHEN o.xtype = 'P'
    THEN 'Stored Procedure'
   WHEN o.xtype IN (
     'FN'
     ,'IF'
     ,'TF'
     )
    THEN 'Function'
   END AS type_desc
 FROM sysobjects o
 INNER JOIN syscomments c
  ON c.id = o.id
 WHERE o.xtype IN (
   'P'
   ,'FN'
   ,'IF'
   ,'TF'
   )
  AND o.category = 0
  AND o.NAME NOT IN (
   'fn_diagramobjects'
   ,'sp_alterdiagram'
   ,'sp_creatediagram'
   ,'sp_dropdiagram'
   ,'sp_helpdiagramdefinition'
   ,'sp_helpdiagrams'
   ,'sp_renamediagram'
   ,'sp_upgraddiagrams'
   ,'sysdiagrams'
   )
 ) t
GROUP BY t.sp_name
 ,t.type_desc
ORDER BY 1