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