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 

• Related Articles

• Open Stock Transfers- SQL Queries

  Environment: iVend 6.6 with SAPB1 10. Problem Statement : There is need to have SQL Queried to find Open Stock Transfers in iVend Enterprise database SQL Queries : Below Queries are used : a) select ...

• Cannot open database 'CXSIntegration' login failed

  Environment: iVend 6.6 Problem Statement: Cannot open database 'CXSIntegration' login failed Symptoms: Environment Specific Resolution/Work Around: It seems wrong SQL password is entered for user "sa". So need to check correct password for SQL login ...

• Microsoft SQL Server Database Maintenance activity

  Database health check & Optimization steps Steps and walkthrough to do a health check and optimization of iVend database. The entire process broadly consists of 8 steps: Step 1: Take Backup of DB. Open SQL Server Management Studio Express and connect ...

• Steps to check the issue : 'Database size increase

  Scenario : If iVend database size is high in size, suggest to check the following information : Steps : 1) Where is the user getting this abnormal data in size (Enterprise or Store) 2) What is the SQL server version. (Express edition has a limit and ...

• iVend POS - Purge Data to Reduce iVend Store database size

  Introduction The Data Purging feature in iVend helps the Administrator to control the size of the iVend database at each store. Data purging at stores is controlled centrally and is managed from the iVend Enterprise at Head Office. The Administrator ...