Dynamics Community Forum Thread Details

This can be common following an upgrade especially as other poster mentioned inflated log file. You can use this script to get a breakdown of files per database and size of each file along with actual space used. If you see a lot of empty space in log files, you can shrink them to bring them back down in size (SQL will automatically grow them to the size it needs). You may also want to check whether databases are in full recovery model without actually performing log backups. If this is the situation then you'll see large transaction log files with all space used.

DECLARE @ServerVersion varchar(100)
SET @ServerVersion = CONVERT(varchar,SERVERPROPERTY('productversion'))
SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.',@ServerVersion, 4)-1)
--PRINT @ServerVersion
DECLARE @command nvarchar(2000)

IF OBJECT_ID('tempdb..#FileData','U') IS NOT NULL
BEGIN
PRINT 'Dropping #FileData'
DROP TABLE tempdb..#FileData
END

CREATE TABLE tempdb..#FileData
(
[CurrentHost] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[ClusterNodes] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[DB] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[FileType] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[Name] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[VolumeOrDrive] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[FileName] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[File Size (MB)] decimal(15,2) NULL,
[Space Used In File (MB)] decimal(15,2) NULL,
[Available Space In File (MB)] decimal(15,2) NULL,
[Drive Free Space (MB)] decimal(15,2) NULL
)
IF CONVERT(float, @ServerVersion) < 10.5 BEGIN --–2000, 2005, 2008

IF OBJECT_ID('tempdb..#xp_fixeddrives','U') IS NOT NULL
BEGIN
PRINT 'Dropping table #xp_fixeddrives'
DROP TABLE #xp_fixeddrives;
END

CREATE TABLE #xp_fixeddrives
(
Drive varchar(250),
MBFree int
)

INSERT INTO #xp_fixeddrives
(
Drive,
MBFree
)
EXEC master..xp_fixeddrives

SET @command = '
USE [?]
INSERT INTO #FileData
(
[CurrentHost],
[ClusterNodes],
[DB],
[FileType],
[Name],
[VolumeOrDrive],
[FileName],
[File Size (MB)],
[Space Used In File (MB)],
[Available Space In File (MB)],
[Drive Free Space (MB)]
)
SELECT CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS AS [CurrentHost],
CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, '''' ), '''')) COLLATE Latin1_General_CI_AS AS [CluserNodes],
CONVERT(varchar(250), DB_NAME()) COLLATE Latin1_General_CI_AS [DB],
CONVERT(varchar(250), df.type_desc) COLLATE Latin1_General_CI_AS [FileType],
CONVERT(varchar(250), f.Name) COLLATE Latin1_General_CI_AS [Name],
CONVERT(varchar(250), LEFT(f.FileName, 3)) COLLATE Latin1_General_CI_AS [VolumeOrDrive],
CONVERT(varchar(250), f.FileName) COLLATE Latin1_General_CI_AS [FileName],
CONVERT(Decimal(15,2), ROUND(f.Size/128.000, 2)) [File Size (MB)],
CONVERT(Decimal(15,2), ROUND(FILEPROPERTY(f.Name,''SpaceUsed'')/128.000,2)) [Space Used In File (MB)],
CONVERT(Decimal(15,2), ROUND((f.Size-FILEPROPERTY(f.Name,''SpaceUsed''))/128.000,2)) [Available Space In File (MB)],
CONVERT(Decimal(15,2), d.MBFree) [Drive Free Space (MB)]
FROM dbo.sysfiles f WITH (NOLOCK)
INNER JOIN sys.database_files df ON df.file_id = f.fileid
LEFT JOIN tempdb..#xp_fixeddrives d
ON LEFT(f.FileName, 1) COLLATE Latin1_General_CI_AS = d.Drive COLLATE Latin1_General_CI_AS;'
END
ELSE -- SQL 2008R2+ (function sys.dm_os_volume_stats is available)
BEGIN
SET @command = 'USE [?]
INSERT INTO #FileData
(
[CurrentHost],
[ClusterNodes],
[DB],
[FileType],
[Name],
[VolumeOrDrive],
[FileName],
[File Size (MB)],
[Space Used In File (MB)],
[Available Space In File (MB)],
[Drive Free Space (MB)]
)
SELECT CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS AS [CurrentHost],
CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, '''' ), '''')) COLLATE Latin1_General_CI_AS AS [CluserNodes],
CONVERT(varchar(250), DB_NAME(v.database_id)) COLLATE Latin1_General_CI_AS [DB],
CONVERT(varchar(250), df.type_desc) COLLATE Latin1_General_CI_AS [FileType],
CONVERT(varchar(250), f.name) COLLATE Latin1_General_CI_AS [Name],
CONVERT(varchar(250), v.volume_mount_point) COLLATE Latin1_General_CI_AS [VolumeOrDrive],
CONVERT(varchar(250), f.[Filename]) COLLATE Latin1_General_CI_AS [Filename],
CONVERT(Decimal(15,2), ROUND(f.Size/128.000,2)) [File Size (MB)],
CONVERT(Decimal(15,2), ROUND(FILEPROPERTY(f.Name,''SpaceUsed'')/128.000,2)) [Space Used In File (MB)],
CONVERT(Decimal(15,2), ROUND((f.Size-FILEPROPERTY(f.Name,''SpaceUsed''))/128.000,2)) [Available Space In File (MB)],
CONVERT(Decimal(15,2), v.available_bytes/1048576.0) [Drive Free Space (MB)]
FROM sys.sysfiles f WITH (NOLOCK)
INNER JOIN sys.database_files df ON df.file_id = f.fileid
CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.fileid) v;'
END -- END IF

EXEC sp_MSforeachdb @command

SELECT --CurrentHost,ClusterNodes,
   DB,
   CASE WHEN B.CMPNYNAM IS NULL AND A.DB='DYNAMICS' THEN 'DYNAMICS'
       WHEN B.CMPNYNAM IS NULL THEN ''
       ELSE RTRIM(B.CMPNYNAM) END GPCompany,
   Name,
   FileType,
   --VolumeOrDrive,
   FileName,
   [File Size (MB)],
   [Space Used In File (MB)],
   --,[Available Space In File (MB)],
   [Drive Free Space (MB)]
FROM #FileData A LEFT JOIN DYNAMICS.dbo.SY01500 B ON A.DB=B.INTERID COLLATE Latin1_General_CI_AS

Let me know if any questions.

Thanks,

Lance Brigham

Velosio

After Upgrade - Company db is now 2x orig size and Dynamics is 10x orig size

Helpful resources

News and Announcements

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Verified Answers

Leaderboard

Featured topics

Product updates