Database daily maintenance-checklist_03 about database data file size check

Capacity planning in daily data maintenance is one of the fundamental tasks of each DBA and is a very important task. In a production environment, some of the more important business databases will have insufficient pre-capacity planning, or unexpected bursts of data growth, until the entire disk space is filled, the system will be unusable, and ultimately the business is impacted. At this time, it would be worse if there were no timely disk warnings, resulting in economic losses. So, in order to avoid this situation, we can do early warning, proactively collect the relevant data file size, such as database, data files, backup file size, as well as the main business data table size, and so on, regularly tracked and reported form to the storage team, alerting capacity changes. The detailed code is as follows:

1. Check the database file size

SELECT CONVERT (varchar), GETDATE (), as ' Date ', d.name as ' Database name ', SUM (convert decimal (9,3), convert (float , l.size) * (8192.0/1024.0/1024.)  /1024.)) As ' Size (GB) '

From Sys.master_files l

Left JOIN sys.databases D on l.database_id=d.database_id

WHERE d.database_id>4--Exclude system databases

GROUP BY D.name

ORDER BY ' Size (GB) ' desc

--or ...
---mdf+ldf
SELECT DISTINCT
--A.DATABASE_ID,
A.name as DbName,
A.compatibility_level,
CONVERT (VARCHAR), A.create_date, 106) as Dbcreatedate,
CONVERT (VARCHAR), cast (CAST (SUM (B.size * 8.0/1024.0/1024.) As NUMERIC (10,2)) as money), 1) as ' Size (GB) ',
DATABASEPROPERTYEX (a.name, ' Recovery ') as Recoverymode,
DATABASEPROPERTYEX (a.name, ' Status ') as Databasestatus,
Case DATABASEPROPERTYEX (a.name, ' Isautocreatestatistics ') when 1 Then ' TRUE ' ELSE ' FALSE ' END as autocreatestatsenabled,
Case DATABASEPROPERTYEX (a.name, ' IsAutoUpdateStatistics ') when 1 Then ' TRUE ' ELSE ' FALSE ' END as autoupdatestatsenabled,
DATABASEPROPERTYEX (a.name, ' useraccess ') as UserAccess,
DATABASEPROPERTYEX (a.name, ' updateability ') as updateability,
Case DATABASEPROPERTYEX (a.name, ' istornpagedetectionenabled ') while 1 Then ' TRUE ' ELSE ' FALSE ' END as Tornpagedetectionena Bled
Case DATABASEPROPERTYEX (a.name, ' isrecursivetriggersenabled ') while 1 Then ' TRUE ' ELSE ' FALSE ' END as Recursivetriggersena Bled
Case DATABASEPROPERTYEX (a.name, ' Isinstandby ') while 1 Then ' TRUE ' ELSE ' FALSE ' END as Isdbstandby
From
Master.sys.databases a INNER JOIN master.sys.master_files b on a.database_id = b.database_id

where a.database_id>4
GROUP by
A.DATABASE_ID,
A.name,
A.compatibility_level,
CONVERT (VARCHAR), a.create_date, 106)
ORDER by 1

2. Check the database backup file size

SELECT getdate () as,

B.server_name, Round (SUM (CONVERT (float,b.backup_size)/1024.0/1024.0/1024.0), 2) as ' BACKUP_SIZE_GB ',

Round (SUM (CONVERT (float,b.compressed_backup_size)/1024.0/1024.0/1024.0), 2) as ' COMPRESSED_BACKUP_SIZE_GB ' from Msdb.. Backupset b

where B.database_name not in (' Model ', ' master ', ' msdb ', ')

--and b.type= ' D '

and Backup_start_date>getdate ()-1

GROUP by B.server_name

3. Check the table space size

SELECT object_name (ID) tablename,
Case when reserved * 8 > 1024x768 then RTRIM (8 * reserved/1024) + ' MB '
ELSE RTRIM (Reserved * 8) + ' KB '
END Datareserve,
Case when Dpages * 8 > 1024x768 then RTRIM (8 * dpages/1024) + ' MB '
ELSE RTRIM (Dpages * 8) + ' KB '
END used,
Case 8 * (reserved-dpages) > 1024
Then RTRIM (8 * (reserved-dpages)/1024x768) + ' MB '
ELSE RTRIM (8 * (reserved-dpages)) + ' KB '
END Unused,
Case when (8 * dpages/1024-rows/1024 * minlen/1024) > 1024
Then RTRIM ((8 * dpages/1024-rows/1024 * minlen/1024)
/1024x768) + ' MB '
ELSE RTRIM ((8 * dpages/1024-rows/1024 * minlen/1024))
+ ' KB '
END free,
Rows as Rows_count
From sys.sysindexes
WHERE indid = 1
and status = 2066--status= ' 18 '
ORDER by reserved DESC

4. Check the table index size

--Special reminder: This query is slow, if it is a production environment, select non-business time to execute

IF object_id (' tempdb.. #Indexdata ', ' U ') is not NULL
DROP TABLE #Indexdata
DECLARE
@SizeofIndex BIGINT, @IndexID INT,
@NameOfIndex nvarchar, @TypeOfIndex nvarchar (50),
@ObjectID int, @IsPrimaryKey int,
@FGroup VARCHAR (20)

CREATE table #Indexdata (name nvarchar (50),
IndexID int, IndexName nvarchar (200),
sizeofindex int, Indextype nvarchar (50),
IsPrimaryKey int,fgroup VARCHAR (20))
DECLARE Indexloop CURSOR for
SELECT idx.object_id, idx.index_id, Idx.name, Idx.type_desc
, Idx.is_primary_key,fg.name
From sys.indexes IDX
Join Sys.objects So
On idx.object_id = so.object_id JOIN sys.filegroups FG
On idx.data_space_id = fg.data_space_id
where Idx.type_desc! = ' Heap '
and So.type_desc not in (' internal_table ', ' system_table ')
and Idx.name in (

Select
I.name

From Sys.dm_db_index_usage_stats as ius
JOIN sys.indexes as I on i.index_id = ius.index_id
and i.object_id = ius.object_id
WHERE ius.database_id = db_id ()--and i.name like '%clusteredindex% '
--and object_name (i.object_id) like '%dailysales '
and i.is_disabled = 0
)

OPEN Indexloop
FETCH NEXT from Indexloop
Into @ObjectID, @IndexID, @NameOfIndex,
@TypeOfIndex, @IsPrimaryKey, @FGroup
while (@ @FETCH_STATUS = 0)
BEGIN
SELECT @SizeofIndex = SUM (avg_record_size_in_bytes * record_count)
From Sys.dm_db_index_physical_stats (db_id (), @ObjectID,
@IndexID, NULL, ' detailed ')
Insert into #Indexdata (name, IndexID, IndexName, Sizeofindex,
Indextype,isprimarykey,fgroup)
SELECT TableName = object_name (@ObjectID),
IndexID = @IndexID,
IndexName = @NameOfIndex,
Sizeofindex = CONVERT (DECIMAL (16,1), (@SizeofIndex/(1024.0 * 1024))),
Indextype = @TypeOfIndex,
IsPrimaryKey = @IsPrimaryKey,
Fgroup = @FGroup
FETCH NEXT from Indexloop
Into @ObjectID, @IndexID, @NameOfIndex,
@TypeOfIndex, @IsPrimaryKey, @FGroup
END
CLOSE Indexloop
Deallocate Indexloop
Select name as TableName, IndexName, Indextype,
Sizeofindex as [Size of Index (MB)],
case if IsPrimaryKey = 1 Then ' Yes ' Else ' No ' End as [IsPrimaryKey]
, Fgroup as [File Group]
From #Indexdata ORDER by Sizeofindex DESC

-----------------------------------------------------------------------------------------

Samezhao

Database daily maintenance-checklist_03 about database data file size check