Module 3 Creating and Managing Databases Overview n

Module 3: Creating and Managing Databases

Overview n Creating Databases n Creating Filegroups n Managing Databases n Introduction to Data Structures

u Creating Databases n Defining Databases n How the Transaction Log Works n Setting Database Options n Retrieving Database Information

Defining Databases n Creating a Database Defines: l The name of the database l The size of the database l The files where the database will reside CREATE DATABASE Sample ON PRIMARY ( NAME=Sample. Data, FILENAME='c: Program Files. . DataSample. mdf', SIZE=10 MB, MAXSIZE=15 MB, FILEGROWTH=20%) LOG ON ( NAME=Sample. Log, FILENAME= 'c: Program Files. . DataSample. ldf', SIZE=3 MB, MAXSIZE=5 MB, FILEGROWTH=1 MB) COLLATE SQL_Latin 1_General_Cp 1_CI_AS

How the Transaction Log Works 1 Data modification is sent by application Buffer Cache 2 Data pages are located in, or read into, buffer cache and modified 3 Modification is recorded in transaction log on disk Disk 4 Checkpoint writes committed transactions to database

Retrieving Database Information n Determine Database Properties by Using the DATABASEPROPERTYEX Function n Use System Stored Procedures to Display Information About Databases and Database Parameters l sp_helpdb database_name l sp_spaceused [objname]

Creating Filegroups sys. . . sysusers sysobjects Northwind Database. . . Orders Customers Products Ord. Hist. Year 2 Ord. Hist. Year 1 C: D: E: Northwind. mdf Ord. Hist 1. ndf Ord. Hist 2. ndf Northwind. Idf ndf is data file; Default Filegroup Order. History. Group ldf is log file

u Managing Databases n Managing Data and Log File Growth n Monitoring and Expanding a Transaction Log n Shrinking a Database or File n Dropping a Database

Managing Data and Log File Growth n Using Automatic File Growth n Expanding Database Files n Adding Secondary Database Files ALTER DATABASE Sample MODIFY FILE ( NAME = 'Sample. Log', SIZE = 15 MB) GO ALTER DATABASE Sample ADD FILE (NAME = Sample. Data 2, FILENAME='c: Program Files. . DataSample 2. ndf', SIZE=15 MB, MAXSIZE=20 MB) GO

Monitoring and Expanding a Transaction Log n Monitoring the Log n Monitoring Situations That Produce Extensive Log Activity n l Mass loading of data into indexed table l Large transactions l Performing logged text or image operations Expanding the Log When Necessary

Shrinking a Database or File n Shrinking an Entire Database DBCC SHRINKDATABASE (Sample, 25) n Shrinking a Data File in the Database DBCC SHRINKFILE (Sample_Data, 10) n Shrinking a Database Automatically Set autoshrinkdatabase option to true

Dropping a Database n Methods of Dropping a Database l SQL Server Enterprise Manager l DROP DATABASE statement DROP DATABASE Northwind, pubs n Restrictions on Dropping a Database l While it is being restored l When a user is connected to it l When publishing as part of replication l If it is a system database

u Introduction to Data Structures n How Data Is Stored n Types of Pages and Extents n Pages That Manage File Space n Pages That Track Tables and Indexes

How Data Is Stored Database Data (file). mdf or. ndf Log (file). Idf Tables, Indexes Extent (8 contiguous 8 -KB pages) Data Page (8 KB) Max row size = 8060 bytes

Review n Creating Databases n Creating Filegroups n Managing Databases n Introduction to Data Structures