4 Managing Database Files
(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)
All databases have a primary data file (.mdf) and one or more transaction log files (.ldf) A database may also have secondary data files (.ndf). Datafiles may be grouped into filegroups. High performance databases (only) would have a secondary filegroup created immediately after database creation.
Data is stored in 8-kilobytes blocks of contiguous disk space called pages. Tables and Indexes are stored in extents of 8 contiguous pages, or 64kb.
As in oracle, data is modified in the buffer cache, the modification recorded in the transaction log file, with the checkpoint process periodically writing all completed transactions to the disk files.
RAID-5 is often used with SQL Server, although like Oracle RAID-10 will perform better. This is especially true for the transaction log files. Difference in RAID usage like this when applied across an organisation as standard will act to widen the cost gap between Oracle and SQL Server.
High performance databases (only) would separate transaction log files and data files onto separate disk arrays. At the extreme this would be done for tempdb also.
Filegroups can be used within a database to manually place individual tables and indexes onto individual disk drives. However disk striping normally produces the same performance benefits as filegroups without all the extra work – the SAME (Stripe and Mirror Everything) approach.
When creating a database, it makes sense to accept the defaults of unlimited file growth in 10% increments. This is especially crucial to the transaction log, as changes cannot be made to the data of a database with a full transaction log. A maintenance plan can be set up (see below) to periodically shrink files. Transaction log files are initially created by default to be 25% of the size of the data files. This default should be accepted unless the database data will have an unusually low number of changes, in which case a smaller transaction log file would be appropriate.
After creating, dropping or modifying a user database, back up the master database.
Lab 4.1 – create and modify a database.
4.2 Database Options
Database options are set through the options tab within database properties (right click on the database name in SQL Server Management Studio).
The important options are listed below.
Recovery Model is particularly important – it should always be set to full for production user databases (although not system databases such as master).
Database option |
Description |
| Recovery model = Bulk-logged(like _disable_redo_logging) | Allows a database to accept non-logged operations – use during bulk copying of data or when using SELECT INTO to conserve transaction log space.Restore from backup operations will not restore a database with non logged operations to a consistent state. |
| Recovery model = Simple(like noarchivelog mode) | Causes the transaction log to be truncated (committed transactions are removed) every time that the checkpoint process occurs – use only during development if needed to conserve transaction log space. Do not use in a production database. |
| Restrict Access | Can switch to single user or restricted user mode for the database – use when performing maintenance. |
| Database Read-Only | Defines a database as read-only – use to set security for decision support databases. |
| Auto Shrink | Determines whether the database size shrinks automatically. Databases can also be manually shrunk using the database -> all tasks -> shrink database menu option, or through scheduled maintenance tasks. |
| Auto Create Statistics | Gathers optimizer statistics for new tables automatically |
| Auto Update Statistics | Gathers optimizer statistics for stale tables automatically (like Oracle’s gather_stats_job) |
You can see the options for all databases at one time using the T-SQL command sp_helpdb in a ‘new query’ window with SQL Server Management Studio.
Lab 4.2 – Set and unset some database options.
4.3 Database File Sizes
Database file sizes can be changed through the Files tab of Database Properties (right click on database name), which also shows the initial size of the files when they were created.
The current sizes of the database files is seen by right clicking on database name, then choosing Tasks > Reports > Standard Reports > Disk Usage.
Files can be shrunk by right clicking on database name and choosing Tasks > Shrink. Performance is impacted while the shrink operation is running.
(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)
Leave a Reply