SQL Server DBA routinely checks common SQL

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