SQL Queries to find the database size

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

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