Get-winEvent -ComputerName 'listner' -filterHashTable @{logname ='Microsoft-Windows-FailoverClustering/Operational'; id=1641}| ft -AutoSize -Wrap
Monthly Archives: July 2020
Database FileSize
This script shows how much space is used and how mutch allocated dispace is still avalible before it runs out of resources.
-- Show avalible space on data disk SELECT DISTINCT vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)], CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS [Available Size (GB)], CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %] FROM sys.master_files AS f WITH (NOLOCK) CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs OPTION (RECOMPILE) GO DROP TABLE IF EXISTS ##DBSIZE; GO CREATE TABLE ##dbsize ( [ComputerName] [sql_variant] NULL, [InstanceName] [sql_variant] NOT NULL, [SqlInstance] [sql_variant] NULL, [DBName] [nvarchar](128) NULL, [FileName] [sysname] NOT NULL, [Filegroup] [sysname] NULL, [PhysicalName] [nvarchar](260) NULL, [FileType] [nvarchar](60) NULL, [UsedSpaceMB] [float] NULL, [FreeSpaceMB] [float] NULL, [FileSizeMB] [float] NULL, [PercentUsed] [float] NULL, [GrowthMB] [float] NULL, [GrowthType] [varchar](7) NOT NULL, [MaxSizeMB] [float] NULL, [SpaceBeforeAutoGrow] [float] NULL, [SpaceBeforeMax] [float] NULL, [PossibleAutoGrowthMB] [float] NULL, [UnusableSpaceMB] [float] NULL ) ON [PRIMARY] GO declare @db varchar(128) -- database name declare @sql nvarchar(max) DECLARE db_cursor CURSOR FOR SELECT name FROM MASTER.dbo.sysdatabases OPEN db_cursor FETCH NEXT FROM db_cursor INTO @db WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = N'USE ['+@db+']; INSERT INTO ##dbsize SELECT SERVERPROPERTY(''MachineName'') AS ComputerName ,ISNULL(SERVERPROPERTY(''InstanceName''),''MSSQLSERVER'') AS InstanceName ,SERVERPROPERTY(''ServerName'') AS SqlInstance ,DB_NAME() AS DBName ,f.name AS [FileName] ,fg.name AS [Filegroup] ,f.physical_name AS [PhysicalName] ,f.type_desc AS [FileType] ,CAST(CAST(FILEPROPERTY(f.name,''SpaceUsed'') AS INT)/128.0 AS FLOAT) AS [UsedSpaceMB] ,CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS INT) / 128.0 AS FLOAT) AS [FreeSpaceMB] ,CAST((f.size/128.0) AS FLOAT) AS [FileSizeMB] ,CAST((FILEPROPERTY(f.name, ''SpaceUsed'')/(f.size / 1.0)) * 100 AS FLOAT) AS [PercentUsed] ,CAST((f.growth/128.0) AS FLOAT) AS [GrowthMB] ,CASE is_percent_growth WHEN 1 THEN ''pct'' WHEN 0 THEN ''MB'' ELSE ''Unknown'' END AS [GrowthType] ,CASE f.max_size WHEN - 1 THEN 2147483648. ELSE CAST((f.max_size / 128.0) AS FLOAT) END AS [MaxSizeMB] ,CAST((f.size / 128.0) AS FLOAT)-CAST(CAST(FILEPROPERTY(f.name,''SpaceUsed'')AS INT)/128.0AS FLOAT) AS [SpaceBeforeAutoGrow] ,CASE f.max_size WHEN (- 1) THEN CAST(((2147483648.) - CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS INT)) / 128.0 AS FLOAT) ELSE CAST((f.max_size - CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS INT)) / 128.0 AS FLOAT) END AS [SpaceBeforeMax] ,CASE f.growth WHEN 0 THEN 0.00 ELSE CASE f.is_percent_growth WHEN 0 THEN CASE f.max_size WHEN (- 1) THEN CAST(((((2147483648.)-f.Size)/f.Growth)*f.Growth)/128.0 AS FLOAT) ELSE CAST((((f.max_size-f.Size)/f.Growth)*f.Growth)/128.0 AS FLOAT) END WHEN 1 THEN CASE f.max_size WHEN (- 1) THEN CAST(CONVERT([int],f.Size*power((1)+CONVERT([float],f.Growth)/(100),CONVERT([int],log10(CONVERT([float],(2147483648.))/CONVERT([float],f.Size))/log10((1)+CONVERT([float],f.Growth)/(100)))))/128.0AS FLOAT) ELSE CAST(CONVERT([int],f.Size*power((1)+CONVERT([float],f.Growth)/(100),CONVERT([int],log10(CONVERT([float],f.Max_Size)/CONVERT([float],f.Size))/log10((1)+CONVERT([float],f.Growth)/(100)))))/128.0AS FLOAT) END ELSE (0) END END AS [PossibleAutoGrowthMB] ,CASE f.max_size WHEN - 1 THEN 0 ELSE CASE f.growth WHEN 0 THEN (f.max_size-f.size)/128 ELSE CASE f.is_percent_growth WHEN 0 THEN CAST((f.max_size-f.size-(CONVERT(FLOAT,FLOOR((f.max_size-f.Size)/f.Growth)*f.Growth)))/128.0 AS FLOAT) ELSE CAST((f.max_size-f.size-(CONVERT([int],f.Size*power((1)+CONVERT([float],f.Growth)/(100),CONVERT([int],log10(CONVERT([float],f.Max_Size)/CONVERT([float],f.Size))/log10((1)+CONVERT([float],f.Growth)/(100)))))))/128.0 AS FLOAT) END END END AS [UnusableSpaceMB] FROM sys.database_files AS f WITH (NOLOCK) LEFT JOIN sys.filegroups AS fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id' EXEC (@SQL) FETCH NEXT FROM db_cursor INTO @db END CLOSE db_cursor DEALLOCATE db_cursor SELECT * FROM ##dbsize DROP TABLE ##dbsize GO