SQL Server database space management (1)

Frequently encountered problems with databases:

1), database file space exhausted 2), log file growth 3), database file cannot shrink 4), autogrow and auto shrink

This series is based on the above 4 questions to analyze and summarize the database space management methods.

1. How to allocate files and how to check file space

First, you run sp_spaceusedin your database, and then you see the usage space information for the current database; This command will show you the approximate database space information, and cannot see the usage of each data file and log file. Where temporary data objects are stored in the database tempdb, this command is not counted. We know that the database is divided into main files (. mdf) and auxiliary files (. ndf); The data is stored and managed by page and area, there is a command to view the specific page content:DBCC page (<db ID >,<file ID; <page Id>,<format id>) before we run this command, turn on trace flag:

using a command with a parameter of 3, we set the parameter to 2: look at the output information, the information inside the combination SQL Server topic One: The contents of the index (above) can be understood, the following is actually the content of 222 of the data page to show out; supplementary (not related to the following): SQL Server will store a total of more than 8K of data pages, separate row overflow ( Row-overflow) page.

PAGE: (1:222)

BUFFER: BUF @0x00000001f07a2f40bpage= 0x00000001e1068000 Bhash = 0x0000000000000000 Bpageno = (1:222) Bdbid=5Breferences =0Bcputicks =0Bsamplecount=0BUse1 =7102Bstat =0x9blog= 0x2121215a bnext =0x0000000000000000PAGE HEADER:P Age @0x00000001e1068000m_pageid= (1:222) M_headerversion =1M_type =2m_typeflagbits= 0x0 M_level =0M_flagbits =0x200m_objid (allocunitid.idobj)= theM_indexid (Allocunitid.idind) =2Metadata:allocunitid =562949955649536Metadata:partitionid=562949955649536Metadata:indexid =2Metadata:objectid= theM_prevpage = (1:221) M_nextpage = (1:223) Pminlen=Tenm_slotcnt =137m_freecnt = -M_freedata=7880m_reservedcnt =0M_LSN = ( -: -:6) m_xactreserved=0M_xdesid = (0:0) m_ghostreccnt =0m_tornbits= -639233702DB FragID=1Allocation Statusgam (1:2) = Allocated SGAM (1:3) = notAllocated PFS (1:1) =0x40 Allocated 0_pct_fulldiff (1:6) = CHANGED ML (1:7) = notmin_loggedDATA: Memory Dump @0x000000000f89a000000000000f89a000:01020000 00020200dd000000 01000a00 df000000 .............  000000000F89A014:01008900 220000002600C81E de00000001000000The omission of .....  000000000F89BFF4:90015C01 1001be0086006000.. \.......`. OFFSETTABLE: Row-Offset136(0x88)-7826(0x1e92)135(0x87)-7792(0X1E70)134(0x86)-7720(0X1E28)133(0x85)-7636(0X1DD4) the(0x7d)-7174(0X1C06)118(0x76)-6802(0x1a92) omit ... ...5(0x5)- -(0x190)4(0x4)-348(0x15c)3(0x3)-272(0x110)2(0x2)- the(0xBE)1(0x1)-134(0x86)0(0x0)- the(0x60) DBCC execution is complete. 

another way to view the database is to select the data in Mangement Studio, right---and on the report--and view in the standard report: The two methods are different; Here is the statistic by district; the sp_spaceused above using the page statistics method, this method internally calls the DBCC showfilestats command, which directly calculates the allocation information of the read area on the page from the system of GAM and SGAM, directly calculating how many areas of the database file are allocated Because most SQL Server allocates new space according to the area, and the information on the System allocation page is updated in real time, this statistic method is reliable and can be executed when the server load is very high, so it is not recommended to sp_spaceused method. This method is not as good as viewing system attempts (sys.dm_db_partition_stats);

In fact, there is a way to view the data space:DBCC showcontig (or sys.dm_db_index_physical_stats) This command can count your table or the number of pages cited, how many extents, Even the average amount of data on the page. From these values you can figure out how much space a table occupies. The price of this command hates that SQL Server scans the database and the scanning process is locked, for example sys.dm_db_index_physical_stats has three scanning methods. The more accurate the scan, the longer. 1:limited 2:sampled 3:detailed The 3-way scan time is increased, and the following is the statistics for DBCC showcontig . This information is good for the use of the statistical Area and the degree of fragmentation.

DBCC Showcontig is scanning'Dimaccount'table ... Table:'Dimaccount'(5575058); indexID:1DatabaseID:8has been executedTABLElevel of scanning. -Number of pages scanned ....................:2-Number of scan zones ...................:1-Zone switching times ....................:0-The average number of pages in each district ............:2.0-Scan density [best count: Actual Count] ....:100.00% [1:1]-Logical scan fragments ...........:50.00%-area scan fragments ...........:0.00%-The average number of bytes available per page ...........:2695.0-Avg. page density (full) ...........:66.70%DBCC Showcontig is scanning'dimcurrency'table ... Table:'dimcurrency'(21575115); indexID:1DatabaseID:8has been executedTABLElevel of scanning. -Number of pages scanned ....................:1-Number of scan zones ...................:1-Zone switching times ....................:0-The average number of pages in each district ............:1.0-Scan density [best count: Actual Count] ....:100.00% [1:1]-Logical scan fragments ...........:0.00%-area scan fragments ...........:0.00%-The average number of bytes available per page ...........:3147.0-Avg. page density (full) ...........:61.12%DBCC Showcontig is scanning'DimCustomer'table ... Table:'DimCustomer'(37575172); indexID:1DatabaseID:8has been executedTABLElevel of scanning. -Number of pages scanned ....................:1569-Number of scan zones ...................:197-Zone switching times ....................:1238-The average number of pages in each district ............:8.0-Scan density [best count: Actual Count] ....:15.90% [197:1239]-Logical scan fragments ...........:75.27%-area scan fragments ...........:3.05%-The average number of bytes available per page ...........:3037.7-Avg. page density (full) ...........:62.47% omitted ... DBCC execution is complete. If DBCC outputs an error message, contact your system administrator. 

Additional: Log Files

We know that log files are not allocated and managed by page, area. The SQL Server database engine internally divides each physical log file into multiple virtual log units. The virtual log unit does not have a fixed size, and a physical log file contains a virtual log unit dozens of fixed. Administrators cannot configure or set the size or number of virtual log units. However, SQL Server attempts to control the number of virtual log units, limiting it to a reasonable range. The exception is that the log file grows one time per field and adds at least one virtual log unit. Therefore, if a log file undergoes multiple autogrow, the number of virtual log units inside is much larger than the normal log file, which can affect the efficiency of log file management and affect the startup of SQL Server. The transaction log is a wrapped file. In addition, there is a blog that specifically describes SQL Server logs. The way the transaction log works;

2. Space Monitoring Example

Cond...... Monitoring tempdb space through scripting

SQL Server database space management (1)