Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the management method of sqlserver table and library". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1.1. How ql server 2008R2 stores data

The database is stored on disk in units of files, which is composed of data files and transaction log files. A database should contain at least one data file and one transaction log file.

The database is created on one or more files on the physical media (disk), and it pre-allocates the physical storage space to be applied by the data and transaction log. The file in which data is stored is called a data file, which contains data and objects, such as tables and indexes. The file that stores the transaction log is called the transaction log file (also known as the log file).

The sql server 2008 R2 database has the following types of files

1. Master data file

two。 Auxiliary data file

3. Transaction log file

4. File stream

Give examples to illustrate

-- create a database

Create database schoolDB

Go

Use schoolDB

Go

The database created in this way has only one data file and one transaction log file. For viewing, right-click "Properties"-"File" on the created schoolDB.

You can click "add" in the interface to add a new data file, schooldb2, and later create a new table in the schooldb database, and the data in the table will be stored in the schooldb and schoolDB2 data files.

Data files are marked when they are stored in the database, and we can use sp_helpdb schoolDB

Data file structure

The structure of data file 1 and data file 3 if shown:

The data file consists of 64kb-sized extents (extend), each consisting of 8 consecutive pages of 8KB.

The smallest storage unit that sqlserver can recognize is called a page, and the size of a page is 8KB, which is the unit in which sqlserver stores data.

In the database, the size of a page is 8KB, and in a computer, 1KB is 1024, so a page can store 1024818192 bytes.

How to estimate the disk space occupied according to the number of records in the table

1. First calculate how many bytes there are in a row in a table, such as 6 bytes in the student ID field, 8 bytes in the name field, and how many bytes in the date of birth field

two。 Calculate how many lines a page can save.

3. How many rows of records are added to the table in the database every day, you can calculate how much hard disk space you need to add every day, so that you can estimate how much space is planned based on the amount of data.

Create a Tstudent table in the database

Create TABLE TStudent (

StudentID varchar (10) NOT NULL

Sname varchar (10) DEFAULT NULL

Sex char (2) DEFAULT NULL

CardID varchar (20) DEFAULT NULL

Birthday datetime DEFAULT NULL

Email varchar (40) DEFAULT NULL

Class varchar (20) DEFAULT NULL

EnterTime datetime DEFAULT NULL

)

Go

10 bytes + 10 bytes + 2 bytes + 20 bytes + 8 bytes + 40 bytes + 20 bytes + 8 bytes = 118 bytes

A row of the Tstudent table has 118 bytes

If you add 10000 records per day, how many pages will 10000 records account for?

A page can store 10248192 bytes, throw out the header 96 bytes, the real number of bytes stored is 8192-968096 bytes.

One page can store 8096 bytes, and one page can store 8096 Universe 118 records.

How many pages are there in 10000 lines? 100001,68,148 (page)

One page is 8KB, 148KB, 8KB, 1184KB

Storing 10000 records in the Tstudent table requires more disk space for 1MB. In this way, the disk space can be reasonably planned according to the number of records added each day.

No records have been inserted into the Tstudent table yet, so it takes up 0 pages of data

Provides commands to view the number of pages in a data file

Select OBJECT_NAME (i.object_id) as table name, number of data_pages as data pages

From sys.indexes as i

Join sys.partitions as p ON p.object_id=i.object_id and p.index_id=i.index_id

Join sys.allocation_units as an ON a.container_id=p.partition_id

Where i.object_id=object_id ('dbo.TStudent')

Execute stored procedures in the teaching environment and add 10000 records

There is a gap between the number of pages and what we have just calculated, which is normal. 68 records may not be inserted on each page.

1.1. Log of things

A thing is a collection of one or more T-SQL statements, and it has one property: either execution succeeds or fails. Every sql server database has a transaction log, a SQL statement for recording everything. In the event of a data disaster, the database can be restored through the T-SQL statement of transaction logging.

If the system fails, sql server will use the transaction log to redo (roll forward) all confirmed things and undo (roll back) all unfinished things. 1.1. Create a database (expand / shrink)

Use the graphical interface to create the database, in object Explorer, right-click the "Database" node, select the "New Database" command from the pop-up shortcut menu, open the "New Database" window, set the database name to "class", and determine the owner of the database. The user who created the database by default will become the owner of the database. The initial value of the master data file is 3mb, and the automatic growth value is 1mb. When the data file or log file space is used up, the capacity of the file will be increased according to the set automatic growth value.

You can set the save path for data files and log files in the path column. After clicking OK, a new database is successfully established, and two files are generated, one is the .MDF containing the data file, and the other is the .ldf file containing the log information.

Before you create a database, you must determine the name of the database, the owner (the user who created the database), the size, and the location where the data file and transaction log file are stored.

If you want to set up the database at the database level

Select options in the database properties, and you can modify database-related properties on the right.

1.1.1. Extended database

When the data files and log files in the database are full, more space needs to be allocated for data files and log files. Sql server can automatically extend the database based on the growth parameters defined when you create the database, or you can manually extend the database by allocating more file space on an existing data file, or by allocating space on another new data file.

When you expand a database, you must increase the capacity of the database by at least 1mb, and you can specify the maximum to which files are allowed to grow, which prevents the unlimited growth of files, resulting in the exhaustion of entire disk space.

Examples are as follows:

Set the class data file size of the class database to 100MB, do not grow automatically, and then add a new data file named "class1". The initial file size is set to "50MB", and the maximum file size is set to "500MB".

In object Explorer, right-click the class database, select the Properties command from the pop-up shortcut menu, open the Database Properties-class window, select the File option in the selection Page of the Database Properties-class window, and open the File selection page.

1.1.1. Shrink the database

After using the database for a period of time, the increase of free space in the database is often caused by data deletion, so it is necessary to reduce the disk space allocated to database files and transaction log files to avoid wasting disk space. When there is no data in the database, you can modify the properties of the database file to directly change its occupied space, but when there is data in the database, this will destroy the data in the database, so it is necessary to use contraction to reduce the database space.

Each file in the database can be reduced by deleting unused space, and SQL server allows both data files and log files to shrink by shrinking the database and freeing up unused space. You can shrink the database manually and automatically.

The method of manually shrinking the database

You can see from the figure that you can choose either to shrink the database or to shrink a data file separately.

Shrink the database:

When you shrink a database, you cannot shrink the entire database to a smaller size than the initial size, and if the database was created as 10mb and then grew to 10mb, the database can only shrink to a minimum of 10mb, even if all data has been deleted. But when you shrink a file, you can shrink the database file to a smaller size than its original size

Shrink data file

Select-- "Task"-- "shrink"-- "File" command to open the shrink file dialog box.

Select the data option in the Files of Type drop-down list box, or you can choose to shrink the log file here. The currently allocated space option and the available space option show the space occupied, the space used, and the percentage of shrinkage of the file.

Free unused space: frees up all unused space in the file and shrinks the file to the last allocated size, which reduces the file size but does not move any data.

Reorganize the page before releasing unused space: all unused space in the file is released and an attempt is made to relocate to unallocated space. Here you need to specify the value of the shrink file to option.

Empty the file by migrating the data to another file in the same filegroup: migrate all data in the specified file to another file in the same filegroup, and then delete the empty file.

Determine the options as needed, and then click OK to perform the shrink operation.

Automatically shrink the database

You can achieve auto-shrink by setting the Auto-shrink option parameter in the options selection page in the Database Properties-- class window. When the autoshrink option is set to true, the database of available space is automatically shrunk.

This is the end of the content of "how to manage sqlserver tables and libraries". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.