When you create a database, it is important to accurately specify the amount of space to allocate for it. If you allocate too much space, you waste device space that could be used by other databases. If you allocate too little space, the database may run out of storage space. (However, note that if you don't allocate enough storage space for the database, or if the database later grows to use most or all of its allocated space, you will be able to easily expand the room allocated to the database, as long as there is space available on one or more devices.)
Following are some basic concepts to review before you begin to create databases and their associated transaction logs.
SQL Server uses three units of data storage:
Each fragment of storage for a database must be at least 1 allocation unit ¾ 0.5 MB, or 256 contiguous 2KB pages. The first of the 256 pages is the allocation page. It contains an array that shows how the other 255 pages are used; unlike all other pages, it does not contain database rows.
SQL Server allocates storage space for a database on one or more database devices. SQL Server can theoretically manage as many as 32,767 databases. You can specify which database device, or database devices, to put the database on, and the amount of space to allocate for the database. In addition, you can place the database's transaction log on a separate database device. The following illustration shows the relationship between databases and devices.
If you do not specify a database device, SQL Server places the database on one or more of the database devices specified as default devices in the sysdevices table of the master database. For more information about assigning a database device as default, see Chapter 5, Managing Devices. If you do not specify a size for the database, SQL Server allocates the size of the model database or the size specified by the Database Size configuration option, whichever is larger.
Unless you are creating very small, non-critical databases, you should always place the transaction log on a separate device from its database. This improves performance and allows you to keep backups of the log.
In general, you should allocate from 10 to 25 percent of your database size for the transaction log. This is a rough guideline, and the size will vary considerably according to database usage. If you frequently modify a database, the transaction log can grow quite large, so it's a good idea to monitor the size of your log to determine how much space you will need.
To check the space used by a database's transaction logdbcc checktable (syslogs)
You can also use another DBCC command, DBCC SQLPERF (LOGSPACE), to check the space used by a database's transaction log. For information on using DBCC CHECKTABLE (SYSLOGS) and DBCC SQLPERF (LOGSPACE), see the Microsoft SQL Server Database Developer's Companion.
Initially, only the system administrator has permission to create databases. However, the SA can grant permission to create databases to other SQL Server users, and the SA can change database ownership. For more information about permissions, see Chapter 8, Security Concepts.
The minimum size allowed for a new database is determined by the size of the model database. When SQL Server is installed, the model database is allocated 1 MB of storage space (unless you have upgraded from a previous version of SQL Server, in which case model will still be allocated 2 MB). For information on changing the size of the model database, see Expanding or Shrinking Databases, later in this chapter.
For more information about estimating database size, see Appendix B, Estimating Database Size. For information on reducing allocated storage space, see Expanding or Shrinking Databases, later in this chapter.