SQL Server DBA routinely checks common SQL

Source: Internet
Author: User
Tags dba

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.