1. Database
Use Master; exec sp_helpdb
1.1 Querying database size
Results such as:
1.2 Querying Database status
Use msdb Select name, user_access_desc, - - user access mode state_desc, -- Database status Recovery_model_desc, - - recovery mode page_verify_option_desc, -- page detection options Log_reuse_wait_desc -- log reuse wait from sys.databases
Querying Database status
name user_access_desc state_desc recovery_model_desc page_verify_option_desc log_reuse _wait_descmaster multi_user ONLINE simple CHECKSUM nothingtempdb multi_user Online simple CHECKSUM nothingmodel multi_user online full CHECKSUM Nothingmsdb multi_user ONLINE simple CHECKSUM nothingreportserver multi_user ONLINE Full CHECKSUM nothingreportservertempdb multi_user ONLINE simple CHECKSUM Nothingshyanglao multi_user ONLINE full CHECKSUM Nothing
Results:
1.3 Querying database Usage size
UseShyanglaoGo --Calculate space by page, have performance impact, basic accuracyexecsp_spaceusedGo ----Can @updateusage = ' true ' and will run DBCC UPDATEUSAGEexecsp_spaceused@updateusage = 'true'Go ----Displays the number of page and row count errors in the catalog view for a database and correctsDBCCUpdateusage ('Shyanglao')
Query page already uses size
Results such as:
2. Data files
2.1 Data File Size
UseMaster--View all files and sizes in a databaseexecsp_helpfile--View all files in the database, path, status, sizeSelect db_name(database_id) dbname, Type_desc,--the data or the logName--logical name of the filePhysical_name,--the physical path of the fileState_desc,--File StatusSize* 8.0/1024x768 as 'file Size (MB)' fromsys.master_files--extent compute space by area, no performance impact, basic accuracy, put totalextents*64/1024 in megabytes--It also applies to calculating the file size of tempdb, but does not include log filesDBCCShowfilestats
querying data File size
Results such as:
3. log files
3.1 Query log file size
UseMaster--View Log file database, path, status, sizeSelect db_name(database_id) dbname, Type_desc,--the data or the logName--logical name of the filePhysical_name,--the physical path of the fileState_desc,--File StatusSize* 8.0/1024x768 as 'file Size (MB)' fromsys.master_fileswhereType_desc= 'LOG' --log size of all databases, space usageDBCCSqlperf (Logspace)
query log file size
Results such as:
SQL Server DBA routinely checks common SQL