SQL Server 7.0 features many improvements in space allocation and space management within files. For example, the data structures that track page-to-object relationships have been redesigned. Instead of linked lists of pages, bitmaps are used because they are cleaner, simpler, and facilitate parallel scans. Now each file is more autonomous; it has more data about itself, within itself. This works well for copying or mailing database files.
SQL Server 7.0 also has a more efficient system for tracking table space. The changes allow:
In earlier versions of SQL Server, space allocations can cause blocking when large amounts of data were added. The SQL Server 7.0 space allocation algorithms and data structures are simple and efficient and will not cause blocking because SQL Server 7.0 tracks free space on a page. As rows are deleted from a table that does not have a clustered index, that space can be reused for new inserts. This is a more efficient use of disk space and speeds table scans by packing data more densely.
SQL Server 7.0 allocates pages to objects quickly and reuses space freed by deleted rows. These operations are internal to the system and use data structures not visible to users, yet the processes and structures are referenced occasionally in SQL Server messages. This information about space-allocation algorithms and data structures will help users and administrators understand the messages generated by SQL Server.
SQL Server 7.0 introduces some significant changes in the internal data structures that manage the allocation and reuse of pages. These data structures are not visible to end users, so the changes do not affect users other than by improving speed.
Laptop and desktop systems may have restricted disk space, so you can shrink database files automatically if the option is enabled. The server checks the space usage in each database periodically. If a database is found with a lot of empty space, the size of the files in the database is reduced. Both data and log files can be shrunk. This activity occurs in the background and does not affect any user activity within the database. You also can use SQL Server Enterprise Manager or DBCC to shrink files individually or as a group.
File shrink works by moving rows from pages at the end of a file to pages allocated earlier in the file. In an index, nodes are moved from the end of a file to pages at the beginning of a file. In both cases, pages are freed at the end of files and returned to the file system. Databases can shrink only to the point that no free space is remaining; there is no data compression.
Automated file growth reduces the need for database management and eliminates many problems that occur when logs or databases run out of space. When creating a database, an initial size for the file must be given. SQL Server creates the data files based on the size provided by the database creator, and data is added to the database that these files fill. By default, data files are allowed to grow as much as necessary until disk space is exhausted. Alternatively, data files can be configured to grow automatically when they fill with data, but only to a predefined limit. This prevents disk drives from running out of space.
This feature is useful when SQL Server is used as a database embedded in an application, in which the user does not have ready access to a system administrator. The user can let the files grow automatically as needed to lessen the administrative burden of monitoring the amount of free space in the database and allocating additional space manually.
When creating a database, data files should be as large as possible, based on the maximum amount of data expected in the database. You can permit the data files to grow automatically, but place a limit on the growth. If the initial data file size is exceeded and the file starts to grow automatically, reevaluate the expected maximum database size and plan accordingly by adding more disk space or by creating and adding more files or filegroups to the database.
Databases can be prevented from growing beyond their initial size. If the files fill, no more data can be added unless more data files are added. Allowing files to grow automatically can cause fragmentation if many files share the same disk. Therefore, it is recommended that files or filegroups be created on as many different local physical disks as are available. Objects that compete for space should be placed in different filegroups.