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

• SQL Queries : iVend Enterprise /HO database Purge

  ****************INTERNAL TO SUPPORT TEAM ( Not for Customers) ************************* Note:-This article is created only for Support team and will be done along with product expert. Environment: iVend 6.6 integrated with SAPB1 Problem Statement: ...

• 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 ...

• 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 ...