Running out of space on Server OS drive, where SQL Server app located, what to clear out?

  • I'm running a MS Windows Server 2012 R2, and SQL Server 2016 SP2, the main HDD OS drive is split into two partitions, C:\ and D:\, with lots of space on D:\ and only 60GB total space on C:\ now with less than 5GB left, it's in the red.

    Besides using Minitool trade space around, a radical move, what all can I delete log file wise that keeps growing? I'm already shrinking the T log files each week, and I have found and deleted all the /tmp, /temp, cache, etc usual places.

    How can I delete those old archive logs I see in the log viewer? I have found info on deleting it so I think, but its not it, I want to delete a large swatch of them. I see stuff like go to the database, right click, props, file, slect the log and then remove. Thats not removing those old archive logs I have fro mte h day this thign was stood up.

    Any and all help is greatly appreciated.

    Thanks

  • It is not clear as to your actual setup to know what to suggest: In terms of the SQL server logs and SQL Agent logs, that are titled "Archive #1" etc. if your concern is the size that they are growing to then you can recycle them on say a weekly (or daily) basis with an SQL job that runs the following:

    Use [master];

    GO

    SP_CYCLE_ERRORLOG

    GO

    USE msdb ;

    GO

    EXEC dbo.sp_cycle_agent_errorlog ;

    GO


    If you have database files on the OS partition you may wish to consider moving these too.

    ...

  • Sounds like you may have data and transaction files on your system drive. Don't. Move those. You'll have to take the databases offline and do a detach/attach, but that will solve the problem. Shrinking files over and over is problematic.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey - Friday, January 12, 2018 4:45 AM

    Sounds like you may have data and transaction files on your system drive. Don't. Move those. You'll have to take the databases offline and do a detach/attach, but that will solve the problem. Shrinking files over and over is problematic.

    Thankfully no they are on other drives, its seems to be logs local to the SQL Server that is on the OS drive that slowly grow and I didn't have a lot of space t begin with. S/A's and DBA's prior to me, would install other things on the OS drive when they are not supposed to, only the SQL Server app goes there

  • I've often moved the pagefile for Windows if I need space. Other than that, the suggestions above work.

  • HappyGeek - Friday, January 12, 2018 12:38 AM

    It is not clear as to your actual setup to know what to suggest: In terms of the SQL server logs and SQL Agent logs, that are titled "Archive #1" etc. if your concern is the size that they are growing to then you can recycle them on say a weekly (or daily) basis with an SQL job that runs the following:

    Use [master];

    GO

    SP_CYCLE_ERRORLOG

    GO

    USE msdb ;

    GO

    EXEC dbo.sp_cycle_agent_errorlog ;

    GO


    If you have database files on the OS partition you may wish to consider moving these too.

    I dont have db files on the OS partition, thankfully. I did run the code you shared, thanks, it seemed to only truncate the current log file, but and didn;t do anything to the other Archive# files. And then there those Windows NT logs, and the SQL Server Agent logs. There are so many with lots of details, it seems to be this as the slow growing files on the OS that I'm needing to trim with the right tool. and not go in with File Explorer and delete and screw up things. I say that, as I've made that mistake before.

  • quinn.jay - Friday, January 12, 2018 8:51 AM

    Grant Fritchey - Friday, January 12, 2018 4:45 AM

    Sounds like you may have data and transaction files on your system drive. Don't. Move those. You'll have to take the databases offline and do a detach/attach, but that will solve the problem. Shrinking files over and over is problematic.

    Thankfully no they are on other drives, its seems to be logs local to the SQL Server that is on the OS drive that slowly grow and I didn't have a lot of space t begin with. S/A's and DBA's prior to me, would install other things on the OS drive when they are not supposed to, only the SQL Server app goes there

    Job logs, backup logs, and SQL Server Agent and System logs can (and should be) greatly reduced.  For the job and backup logs, once you purge those, you may need to shrink MSDB to recover the space and then rebuild the indexes to recover the performance in MSDB.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • p.s.  I don't remember the exact number but there's also a Trace Flag that will stop recording successful backups in the SQL Server logs.  If you do log file backups as often as I do, that can be a great little space saver.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Friday, January 12, 2018 9:08 AM

    p.s.  I don't remember the exact number but there's also a Trace Flag that will stop recording successful backups in the SQL Server logs.  If you do log file backups as often as I do, that can be a great little space saver.

    3226. 

    http://www.sqlservercentral.com/blogs/denniss-sql-blog-1/2016/01/08/trace-flag-3226-suppress-all-successful-backups-in-sql-server-error-log/

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • The more info you provide the more helpful suggestions you shall receive. Try the following and post as much result as you care to disclose. The first one was adopted from Glenn Berry's blog. Also, check the size of your 'C:\Windows\WinSxS' - the most useless space-wasting folder on C: drive. I'm not going to say what you should do with this folder so that I don't agitate fellow members 😉


    SELECT
      SERVERPROPERTY('ServerName') AS [ServerName], 
      SERVERPROPERTY('InstanceName') AS [Instance],
      SERVERPROPERTY('IsClustered') AS [IsClustered],
      SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [NetBIOS],
      SERVERPROPERTY('Edition') AS [Edition],
      SERVERPROPERTY('ProductLevel') AS [ProductLevel],         
      SERVERPROPERTY('ProductUpdateLevel') AS [UpdateLevel], 
      SERVERPROPERTY('ProductVersion') AS [Version],
      SERVERPROPERTY('ProductMajorVersion') AS [MajorVersion],
      SERVERPROPERTY('ProductMinorVersion') AS [MinorVersion],
      SERVERPROPERTY('ProductBuild') AS [Build],
      SERVERPROPERTY('ProductBuildType') AS [BuildType],         
      SERVERPROPERTY('ProductUpdateReference') AS [UpdateReference],
      SERVERPROPERTY('ProcessID') AS [ProcessID],
      SERVERPROPERTY('Collation') AS [Collation],
      SERVERPROPERTY('InstanceDefaultDataPath') AS [DataPath],
      SERVERPROPERTY('InstanceDefaultLogPath') AS [LogPath],
      SERVERPROPERTY('BuildClrVersion') AS [BuildCLRVersion]
    GO
    SELECT
      servicename,
      process_id,
      status_desc,
      last_startup_time,
      [filename]
    FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);
    GO
    SELECT DB_NAME(db.database_id) AS [Database],
      db.recovery_model_desc AS [RecoveryModel],
      db.compatibility_level AS [CompatibilityLevel],
      db.user_access_desc AS [AccessMode],
      db.state_desc AS [State],
      db.page_verify_option_desc AS [PageVerifyOption],
      db.log_reuse_wait_desc AS [ReuseWaitDesc]    
    FROM sys.databases AS db WITH (NOLOCK)
    GO
    EXECUTE sp_MSForEachDB
    'USE [?];
    SELECT
        ''[?]'' AS [Database],
      A.name AS [LogicalFileName],
      A.TYPE_DESC AS [Type],
        A.PHYSICAL_NAME AS [PhysicalFile],
        CONVERT(DECIMAL(10,2),A.SIZE/128.0) AS [FileSize(MB)],
        CONVERT(DECIMAL(10,2),CAST(FILEPROPERTY(A.NAME, ''SPACEUSED'') AS INT)/128.0) AS [UsedSpace(MB)],
        CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, ''SPACEUSED'') AS INT)/128.0) AS [Freespace(MB)],
        CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, ''SPACEUSED'') AS INT)/128.0)/(A.SIZE/128.0))*100) AS [Freespace(%)],
        [AutoGrowth] = ''By '' +
        CASE is_percent_growth
          WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + '' MB -''
          WHEN 1 THEN CAST(growth AS VARCHAR(10)) + ''% -'' ELSE '''' END
            +
        CASE max_size
          WHEN 0 THEN ''DISABLED''
          WHEN -1 THEN '' Unrestricted''
          ELSE '' Restricted to '' + CAST(max_size/(128*1024) AS VARCHAR(10)) + '' GB'' END
            +
        CASE is_percent_growth
          WHEN 1 THEN '' [autogrowth by percent!]''
          ELSE '''' END
    FROM sys.database_files A
    order by A.TYPE desc, A.NAME;'
    GO
    SELECT SUBSTRING(mf.[physical_name], 1, 3)
    FROM sys.master_files mf WITH (NOLOCK)
    WHERE mf.database_id = 2 and mf.type = 1
    GO
    SELECT [filename], creation_time, CONVERT(DECIMAL(12,2),size_in_bytes/1048576)
    FROM sys.dm_server_memory_dumps WITH (NOLOCK)
    ORDER BY creation_time DESC OPTION (RECOMPILE);
    GO

  • RandomStream - Friday, January 12, 2018 10:15 AM

    The more info you provide the more helpful suggestions you shall receive. Try the following and post as much result as you care to disclose. The first one was adopted from Glenn Berry's blog. Also, check the size of your 'C:\Windows\WinSxS' - the most useless space-wasting folder on C: drive. I'm not going to say what you should do with this folder so that I don't agitate fellow members 😉


    SELECT
      SERVERPROPERTY('ServerName') AS [ServerName], 
      SERVERPROPERTY('InstanceName') AS [Instance],
      SERVERPROPERTY('IsClustered') AS [IsClustered],
      SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [NetBIOS],
      SERVERPROPERTY('Edition') AS [Edition],
      SERVERPROPERTY('ProductLevel') AS [ProductLevel],         
      SERVERPROPERTY('ProductUpdateLevel') AS [UpdateLevel], 
      SERVERPROPERTY('ProductVersion') AS [Version],
      SERVERPROPERTY('ProductMajorVersion') AS [MajorVersion],
      SERVERPROPERTY('ProductMinorVersion') AS [MinorVersion],
      SERVERPROPERTY('ProductBuild') AS [Build],
      SERVERPROPERTY('ProductBuildType') AS [BuildType],         
      SERVERPROPERTY('ProductUpdateReference') AS [UpdateReference],
      SERVERPROPERTY('ProcessID') AS [ProcessID],
      SERVERPROPERTY('Collation') AS [Collation],
      SERVERPROPERTY('InstanceDefaultDataPath') AS [DataPath],
      SERVERPROPERTY('InstanceDefaultLogPath') AS [LogPath],
      SERVERPROPERTY('BuildClrVersion') AS [BuildCLRVersion]
    GO
    SELECT
      servicename,
      process_id,
      status_desc,
      last_startup_time,
      [filename]
    FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);
    GO
    SELECT DB_NAME(db.database_id) AS [Database],
      db.recovery_model_desc AS [RecoveryModel],
      db.compatibility_level AS [CompatibilityLevel],
      db.user_access_desc AS [AccessMode],
      db.state_desc AS [State],
      db.page_verify_option_desc AS [PageVerifyOption],
      db.log_reuse_wait_desc AS [ReuseWaitDesc]    
    FROM sys.databases AS db WITH (NOLOCK)
    GO
    EXECUTE sp_MSForEachDB
    'USE [?];
    SELECT
        ''[?]'' AS [Database],
      A.name AS [LogicalFileName],
      A.TYPE_DESC AS [Type],
        A.PHYSICAL_NAME AS [PhysicalFile],
        CONVERT(DECIMAL(10,2),A.SIZE/128.0) AS [FileSize(MB)],
        CONVERT(DECIMAL(10,2),CAST(FILEPROPERTY(A.NAME, ''SPACEUSED'') AS INT)/128.0) AS [UsedSpace(MB)],
        CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, ''SPACEUSED'') AS INT)/128.0) AS [Freespace(MB)],
        CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, ''SPACEUSED'') AS INT)/128.0)/(A.SIZE/128.0))*100) AS [Freespace(%)],
        [AutoGrowth] = ''By '' +
        CASE is_percent_growth
          WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + '' MB -''
          WHEN 1 THEN CAST(growth AS VARCHAR(10)) + ''% -'' ELSE '''' END
            +
        CASE max_size
          WHEN 0 THEN ''DISABLED''
          WHEN -1 THEN '' Unrestricted''
          ELSE '' Restricted to '' + CAST(max_size/(128*1024) AS VARCHAR(10)) + '' GB'' END
            +
        CASE is_percent_growth
          WHEN 1 THEN '' [autogrowth by percent!]''
          ELSE '''' END
    FROM sys.database_files A
    order by A.TYPE desc, A.NAME;'
    GO
    SELECT SUBSTRING(mf.[physical_name], 1, 3)
    FROM sys.master_files mf WITH (NOLOCK)
    WHERE mf.database_id = 2 and mf.type = 1
    GO
    SELECT [filename], creation_time, CONVERT(DECIMAL(12,2),size_in_bytes/1048576)
    FROM sys.dm_server_memory_dumps WITH (NOLOCK)
    ORDER BY creation_time DESC OPTION (RECOMPILE);
    GO

    The last bit was also from someone whose name I can no longer recall. I apologize for not giving credit where it is due.

  • RandomStream - Friday, January 12, 2018 10:15 AM

    The more info you provide the more helpful suggestions you shall receive. Try the following and post as much result as you care to disclose. The first one was adopted from Glenn Berry's blog. Also, check the size of your 'C:\Windows\WinSxS' - the most useless space-wasting folder on C: drive. I'm not going to say what you should do with this folder so that I don't agitate fellow members 😉


    SELECT
      SERVERPROPERTY('ServerName') AS [ServerName], 
      SERVERPROPERTY('InstanceName') AS [Instance],
      SERVERPROPERTY('IsClustered') AS [IsClustered],
      SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [NetBIOS],
      SERVERPROPERTY('Edition') AS [Edition],
      SERVERPROPERTY('ProductLevel') AS [ProductLevel],         
      SERVERPROPERTY('ProductUpdateLevel') AS [UpdateLevel], 
      SERVERPROPERTY('ProductVersion') AS [Version],
      SERVERPROPERTY('ProductMajorVersion') AS [MajorVersion],
      SERVERPROPERTY('ProductMinorVersion') AS [MinorVersion],
      SERVERPROPERTY('ProductBuild') AS [Build],
      SERVERPROPERTY('ProductBuildType') AS [BuildType],         
      SERVERPROPERTY('ProductUpdateReference') AS [UpdateReference],
      SERVERPROPERTY('ProcessID') AS [ProcessID],
      SERVERPROPERTY('Collation') AS [Collation],
      SERVERPROPERTY('InstanceDefaultDataPath') AS [DataPath],
      SERVERPROPERTY('InstanceDefaultLogPath') AS [LogPath],
      SERVERPROPERTY('BuildClrVersion') AS [BuildCLRVersion]
    GO
    SELECT
      servicename,
      process_id,
      status_desc,
      last_startup_time,
      [filename]
    FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);
    GO
    SELECT DB_NAME(db.database_id) AS [Database],
      db.recovery_model_desc AS [RecoveryModel],
      db.compatibility_level AS [CompatibilityLevel],
      db.user_access_desc AS [AccessMode],
      db.state_desc AS [State],
      db.page_verify_option_desc AS [PageVerifyOption],
      db.log_reuse_wait_desc AS [ReuseWaitDesc]    
    FROM sys.databases AS db WITH (NOLOCK)
    GO
    EXECUTE sp_MSForEachDB
    'USE [?];
    SELECT
        ''[?]'' AS [Database],
      A.name AS [LogicalFileName],
      A.TYPE_DESC AS [Type],
        A.PHYSICAL_NAME AS [PhysicalFile],
        CONVERT(DECIMAL(10,2),A.SIZE/128.0) AS [FileSize(MB)],
        CONVERT(DECIMAL(10,2),CAST(FILEPROPERTY(A.NAME, ''SPACEUSED'') AS INT)/128.0) AS [UsedSpace(MB)],
        CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, ''SPACEUSED'') AS INT)/128.0) AS [Freespace(MB)],
        CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, ''SPACEUSED'') AS INT)/128.0)/(A.SIZE/128.0))*100) AS [Freespace(%)],
        [AutoGrowth] = ''By '' +
        CASE is_percent_growth
          WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + '' MB -''
          WHEN 1 THEN CAST(growth AS VARCHAR(10)) + ''% -'' ELSE '''' END
            +
        CASE max_size
          WHEN 0 THEN ''DISABLED''
          WHEN -1 THEN '' Unrestricted''
          ELSE '' Restricted to '' + CAST(max_size/(128*1024) AS VARCHAR(10)) + '' GB'' END
            +
        CASE is_percent_growth
          WHEN 1 THEN '' [autogrowth by percent!]''
          ELSE '''' END
    FROM sys.database_files A
    order by A.TYPE desc, A.NAME;'
    GO
    SELECT SUBSTRING(mf.[physical_name], 1, 3)
    FROM sys.master_files mf WITH (NOLOCK)
    WHERE mf.database_id = 2 and mf.type = 1
    GO
    SELECT [filename], creation_time, CONVERT(DECIMAL(12,2),size_in_bytes/1048576)
    FROM sys.dm_server_memory_dumps WITH (NOLOCK)
    ORDER BY creation_time DESC OPTION (RECOMPILE);
    GO

    I ran this: dism /online /cleanup-image /startcomponentcleanup /resetbase  

    to see if it would cleanup that winsxs dir, and after about 30 min, I got this message:

    Error: 6824
    The operation cannot be performed because another transaction is depending on the fact that this property will not change.
    The DISM log file can be found at C:\Windows\Logs\DISM\dism.log

    I deleted the huge useless log it created .

  • That's a very courageous attempt on a Friday.  I hope you understood the implications of the cleanup and the server is not mission critical. How much space did you reclaim from that?

  • RandomStream - Friday, January 12, 2018 2:38 PM

    That's a very courageous attempt on a Friday.  I hope you understood the implications of the cleanup and the server is not mission critical. How much space did you reclaim from that?

    None, but now you're scaring me. It failed with that error, and I think it did nothing, certainly no space was reclaimed

  • quinn.jay - Friday, January 12, 2018 3:25 PM

    RandomStream - Friday, January 12, 2018 2:38 PM

    That's a very courageous attempt on a Friday.  I hope you understood the implications of the cleanup and the server is not mission critical. How much space did you reclaim from that?

    None, but now you're scaring me. It failed with that error, and I think it did nothing, certainly no space was reclaimed

    BTW it got to 20% after about 30 min runtime and then failed

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply