SQL Server disk space emergency (disk expansion) reprint

First, background

In the online system, what should we do if we find that there is not enough disk space to store the database files? Can I buy a new hard drive to mount? (Linux can be mounted directly on the drive to expand), but our SQL Server is running under Windows, what can be done to solve this urgent problem?

There are two ways to solve the problem: the first is to convert the database disk to " dynamic disk ", the new disk can be solved; the second is what I'm going to tell you today, using SQL Server to add new files to other disks (or logical partitions), SQL Server will be able to get new data in a minute.

The difference between the above two methods is that the second method will respond faster and more in line with the general scenario, because we generally partition the disk in a few logical partitions, often there will be some logical partition remaining space will be relatively large, just good for placing new files.

Second, Knowledge Preparation

Before I explain how to expand the disk, let me tell you something about table partitioning, which is helpful for understanding disk expansion.

(Figure1: partition logic diagram)

Figure1 partition logical Diagram parsing:

1) Filegroups are only a logical existence;

2) A filegroup can contain more than one file; (filegroup FG1 contains file F1 and file F2)

3) The partitioning scheme includes a partition function and one or more filegroups, (partition scheme SCH1 uses the partition function fn and filegroups FG1 and n other filegroups)

4) A partition scheme can be referenced by multiple tables;

5) Two partitioning schemes can use the same partition function (partition scheme SCH1 and partition scheme SCH2 use the same partition function fn)

When using SSMS to generate SQL statements that create tables, we often see the words "PRIMARY" in SQL, which means that the tables we create are created in the primary filegroup (without a partition scheme specified), and by default, only one MDF is included in the primary filegroup. Therefore, when the data is growing, it is found that MDF files will continue to grow (consider the value of automatic file growth settings, not every time the data will grow MDF file).

In summary: In the case of insufficient database disk space, we add a NDF file in the primary filegroup, which is equivalent to our table partition (data dispersion), the difference is that we do not use partition function rules which data should exist in which filegroups. To learn more about the partitioning of the table, you can refer to: SQL Server Table Partitioning Combat Series (article index)

Third, Testing Process

(i) The situation where the simulation disk is not sufficient:

1) Create a database, set the initial size of the LDF file during the creation process, as far as possible to occupy the disk space, the initial size of MDF files as small as possible, convenient testing;

2) Insert a certain amount of data to occupy the remaining disk, plug into a certain amount of time will occur enough to error;

3) Next we create a NDF file in the main filegroup primary in the other logical partition;

4) Continue to insert data to view the data insertion;

(ii) test scripts and:

1) Use the following SQL to query the task database for file and filegroup information:

--File and filegroup select DF. [NAME],DF.PHYSICAL_NAME,DF. [SIZE],DF. [Growth],f.[name] as [filegroup],f.[is_default]from sys.database_files dfleft JOIN sys.filegroups FON df.data_space_id = f.data_space_id

2) Use the following SQL to query the task database for information about the remaining space of the file:

--current database, data file occupies with remaining space select Db_name () as dbname,name as filename,size/128.0 as Currentsizemb,size/128.0-cast ( Fileproperty (name, ' spaceused ') as INT)/128.0 as Freespacembfrom sys.database_files;

(Figure3: File remaining space)

3) Check the use of the F drive:

(Figure4: Disk information)

4) Execute the following SQL statement (Cartesian value) in the task database:

--Generate test Data select top 2000000 identity (int, up) as ID, 0 as useredinto tempafrom syscolumns a,syscolumns b

Executing the above SQL will produce the following error message:

MSG 1101, Level 17, State 12, line 2nd

The database ' Task ' could not be assigned a new page due to insufficient disk space in filegroup ' PRIMARY. Delete the objects in the filegroup, add additional files to the filegroup, or enable autogrow for the existing files in the filegroup to increase the necessary space.

5) above we have successfully simulated the environment where the disk cannot allocate space, now we are going to create a NDF file that is stored in the primary master file:

--Create the NDF file alter DATABASE [Task]add file (NAME = n ' primary_data ', FILENAME = N ' E:\DataBase\Primary_data.ndf ', SIZE = 20MB, FileGrowth = 10MB) to FILEGROUP [Primary];

6) Repeat step 4 of the SQL, the data can already be inserted, and now we look at the effect:

(Figure5: Files and Filegroups)

(Figure6: File remaining space)

Figure5 can see that a new file has been added to the E drive: PRIMARY_DATA.NDF, the data has been successfully inserted, and the free space for the PRIMARY_DATA.NDF file has been reduced (FIGURE6), indicating that we have successfully expanded the disk.

It is important to note that if you suspect that you may also need to create a new non-partitioned table or index in this database, you need to set the default option for the filegroup.

Four, Knowledge points

1. The file type of SQL Server can be divided into MDF, NDF, LDF, we usually create the database only when the MDF and LDF files are generally created.

2. The primary data file contains the startup information for the database and points to other files in the database. User data and objects can be stored in this file, or they can be stored in a secondary data file. Each database has a primary data file. The recommended file name extension for primary data files is. mdf.

3. Secondary data files are optional and user data is defined and stored by users. By placing each file on a different disk drive, the secondary file can be used to spread the data across multiple disks. Also, if the database exceeds the maximum size of a single Windows file, you can use a secondary data file so that the database can continue to grow. The recommended file name extension for secondary data files is. ndf.

4. The transaction log file holds the log information used to recover the database. Each database must have at least one log file. The recommended file name extension for the transaction log is. ldf.

5. Each database has a primary filegroup. This filegroup contains primary data files and all minor files that are not placed in other filegroups. You can create user-defined filegroups that are used to assemble data files for ease of administration, data allocation, and placement.

6. If you create an object in the database without specifying the filegroup to which the object belongs, the object is assigned to the default filegroup. You can designate only one filegroup as the default filegroup at any time. The files in the default filegroup must be large enough to accommodate all new objects that are not assigned to other filegroups.

7. The PRIMARY filegroup is the default filegroup unless changes are made using the ALTER DATABASE statement. However, system objects and tables are still assigned to the PRIMARY filegroup instead of the new default filegroup.

8. For Figure1 described in: Partition scheme Sch1 (a partition function, one or more filegroups), here is the validation SQL for this statement:

--1. Creating a partition Function Create PARTITION function[fun_archive_id] (INT) asrange rightfor VALUES (200000000)--2. Creating a partition scheme create PARTITION scheme[sch_archive_id] aspartition [Fun_archive_id]to ([primary],[primary])

9. For Figure6, Task.mdf and PRIMARY_DATA.NDF both appear to have a space decrease? What kind of hints did I give us?

SQL Server automatically assigns newly added space based on the initial size and growth of each file set, assuming that file a in the same filegroup is set to twice times the size of file B, a new data occupies three pages (page), 2 pages are allocated proportionally to file A, 1 pages are assigned to file B?

11. After SQL SERVER 2008, is there a new file stream data file and a full-text index file?

12. If a table is present in multiple files physically, the table's data page is organized as n (n for a specific number of files) for a B-tree. Instead of an object for a B-tree?

Original: http://www.cnblogs.com/gaizai/archive/2012/11/27/2790251.html

SQL Server disk space emergency (disk expansion) reprint