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

#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 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

Find Memory Usage (RAM) by Sql query

Tip #2 Memory Usage  (RAM) by Sql processes.

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 determine Memory usage in percentage by sql processes. Run the following query in your sql server query window:

SELECT (((m.total_physical_memory_kb - m.available_physical_memory_kb) / convert(FLOAT, m.total_physical_memory_kb)) * 100) AS memoryusage
FROM sys.dm_os_sys_memory m
GO

Check CPU usage by Sql query

Tip #1 Check CPU usage by Sql query:

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 determine CPU consumption in percentage by sql processes. Run the following query in your sql server query window:

SELECT TOP 1 100 - x.System_Idle AS CPU_USAGE
FROM (
 SELECT y.record.value('(./Record/@id)[1]', 'int') AS record_id, y.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS System_Idle
 FROM (
  SELECT CONVERT(XML, record) AS record
  FROM sys.dm_os_ring_buffers
  WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE '%<SystemHealth>%'
  ) AS y
 ) AS x
ORDER BY x.record_id DESC
GO