SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
RandomStream
RandomStream
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2787 Visits: 648
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.
quinn.jay
quinn.jay
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3856 Visits: 813
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 .
RandomStream
RandomStream
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2787 Visits: 648
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?
quinn.jay
quinn.jay
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3856 Visits: 813
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
quinn.jay
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3856 Visits: 813
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

RandomStream
RandomStream
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2787 Visits: 648
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

If that folder is not taking up large amount of space (say, less than 5GB), I suggest you leave it alone for now and run the queries I provided and post some details. Based on the results, we could provide additional suggestions. Without seeing the system we can only guess what might be taking up space.
quinn.jay
quinn.jay
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3856 Visits: 813
RandomStream - Friday, January 12, 2018 3:29 PM
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

If that folder is not taking up large amount of space (say, less than 5GB), I suggest you leave it alone for now and run the queries I provided and post some details. Based on the results, we could provide additional suggestions. Without seeing the system we can only guess what might be taking up space.


I ran all that code and there is so much sensitive info I have to chop out that it wont leave much useful info is the problem. I was able to whack a number of event view logs, to open more space, so I got a little bit more breathing room
HappyGeek
HappyGeek
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13441 Visits: 6387
quinn.jay - Friday, January 12, 2018 9:02 AM
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.


No, it does not simply truncate the current error log, it closes the current error log and cycles the error logs in a similar fashion to a server restart, see these two articles for details:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-cycle-errorlog-transact-sql and
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-cycle-agent-errorlog-transact-sql.

...
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148069 Visits: 22352
Suggest you start by listing all the files on the C: drive by size and then address them in that order, here is a script that does the file listing
Cool

CREATE TABLE #DIRLIST_C (D_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,D_TXT NVARCHAR(4000) NULL);
INSERT INTO #DIRLIST_C (D_TXT)
EXEC xp_cmdshell N'dir C:\ /S';


SELECT * FROM (
SELECT
DC.D_ID
,SUBSTRING(DC.D_TXT,1,17) AS TSTR
,SUBSTRING(DC.D_TXT,18,18) AS SSTR
,ISNUMERIC(SUBSTRING(DC.D_TXT,18,18)) AS NMBFLG
,CASE
WHEN ISNUMERIC(SUBSTRING(DC.D_TXT,18,18)) = 1 THEN CONVERT(BIGINT,REPLACE(REPLACE(SUBSTRING(DC.D_TXT,18,18),',',''),'File(s)',''),1) / POWER(2,20)
END AS XIZE
,DC.D_TXT
FROM #DIRLIST_C DC
WHERE DC.D_TXT IS NOT NULL
AND ISNUMERIC(SUBSTRING(DC.D_TXT,18,18)) = 1
) AS X ORDER BY X.XIZE DESC;

DROP TABLE #DIRLIST_C;

HappyGeek
HappyGeek
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13441 Visits: 6387
Curious to know why if there are no database files on the OS partition i.e. C: drive, you say in your first post you are shrinking transaction logs? If they are not there what difference does it make?

...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search