January 11, 2018 at 3:58 pm
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
January 12, 2018 at 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.
...
January 12, 2018 at 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.
"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
January 12, 2018 at 8:51 am
Grant Fritchey - Friday, January 12, 2018 4:45 AMSounds 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
January 12, 2018 at 8:56 am
I've often moved the pagefile for Windows if I need space. Other than that, the suggestions above work.
January 12, 2018 at 9:02 am
HappyGeek - Friday, January 12, 2018 12:38 AMIt 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.
January 12, 2018 at 9:06 am
quinn.jay - Friday, January 12, 2018 8:51 AMGrant Fritchey - Friday, January 12, 2018 4:45 AMSounds 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
Change is inevitable... Change for the better is not.
January 12, 2018 at 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 12, 2018 at 9:19 am
Jeff Moden - Friday, January 12, 2018 9:08 AMp.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.
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 12, 2018 at 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
January 12, 2018 at 10:17 am
RandomStream - Friday, January 12, 2018 10:15 AMThe 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.
January 12, 2018 at 2:30 pm
RandomStream - Friday, January 12, 2018 10:15 AMThe 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 .
January 12, 2018 at 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?
January 12, 2018 at 3:25 pm
RandomStream - Friday, January 12, 2018 2:38 PMThat'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
January 12, 2018 at 3:27 pm
quinn.jay - Friday, January 12, 2018 3:25 PMRandomStream - Friday, January 12, 2018 2:38 PMThat'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