Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Suggested answer

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

(0) ShareShare
ReportReport
Posted on by
Hello, has anyone experienced this issue?
I upgraded an environment from GP 2015R2 to GP 18.1661.
The upgrade path taken: GP 2015R2 to 2016 then to 18.4.1361 and then to 18.5.1661.
When the upgrade moved from 2016 to 18.4 this is where the company database sizes doubled and dynamics moved to ten times its original size.
Does anyone know why this may have occurred?
Categories:
  • Suggested answer
    lancebrigham Profile Picture
    lancebrigham 119 on at
    After Upgrade - Company db is now 2x orig size and Dynamics is 10x orig size
    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
  • Suggested answer
    After Upgrade - Company db is now 2x orig size and Dynamics is 10x orig size
    Check the SQL database files, it is anticipated the log file to be a big portion of the total database size.  Check with your SQL dba for the database size may be reduced by shrinking the LDF log file down.  It is a perfect time to review the SQL database properties for database/file size growth configuration including the free disk space for the SQL database files and backups.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Announcing Category Subscriptions!

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,359 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,370 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans