SQL Script To Get All Company Database Sizes

Microsoft Dynamics GPFollowing on from my recent script to return the functional currency for all companies I hve revisited an old script which gets the size of database and updated it to work in the same way.

This script needs to be run against your system database (called DYNAMICS by default) and it will return the size of the data and log files alongside the total size of the database for all companies.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @SQL NVARCHAR(MAX)

CREATE TABLE #DatabaseSizes(
	INTERID VARCHAR(5)
	,CMPNYNAM VARCHAR(65)
	,DataSize VARCHAR(20)
	,LogSize VARCHAR(20)
	,TotalSize VARCHAR(20)
)

SELECT
	@SQL = STUFF((
			SELECT 
				CHAR(13) + '
				SELECT
					''' + INTERID + '''
					,''' + CMPNYNAM + '''
					,LTRIM(STR(CONVERT(DEC (15,2),SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 = 0 THEN SIZE ELSE 0 END))) * 8192 / 1048576,15,2) + '' MB'')
					,LTRIM(STR(CONVERT(DEC (15,2),SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 <> 0 THEN SIZE ELSE 0 END))) * 8192 / 1048576,15,2) + '' MB'')
					,LTRIM(STR(CONVERT(DEC (15,2),SUM(CONVERT(BIGINT,SIZE))) * 8192 / 1048576,15,2) + '' MB'')
				FROM
					' + INTERID + '.dbo.sysfiles'
			FROM
				SY01500
			ORDER BY
				CMPNYNAM
			FOR
				XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

INSERT INTO #DatabaseSizes
	EXEC sys.sp_executesql @SQL
GO

SELECT
	*
FROM
	#DatabaseSizes
GO

DROP TABLE #DatabaseSizes
GO

Can ISC Software Solutions help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you'd like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.





Your Name (required):
Your Email (required):
Subject:
Enquiry message:

What should we write about next?





Your Name (required) -
Your Email (required) -

Leave a Reply

Your email address will not be published. Required fields are marked *