SQL Queries to find the database size
Environment:
Not Environment Specific
Problem Statement:
SQL Queries to find the database size
Solution:
There are below SQL Queries , used to analyze the iVend database :
SQL Query for database size
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) = 'Put the database name'
SQL Query for database size-Available Space and Used Space
SELECT [name], fileid, filename, [size]/128 AS 'Total Size in MB',
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB',
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB',
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128)/([size]/128))*100)) AS 'percentage Used'
FROM sysfiles
SQL Query for database size-Free Space available
SELECT [FREEPERCENTSPACE] = CONVERT(DECIMAL(10, 2), ((A.SIZE / 128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT) / 128.0) / (A.SIZE / 128.0)) * 100)
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id where fg.name = 'PRIMARY'
Optimize Database
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
EXEC sp_updatestats
Shrink the database
DBCC SHRINKDATABASE(@DBName, 4 )
Note : Database Optimization & Shrinking should be done in customer downtime with taking backup of existing one