Module 3 Creating and Managing Databases Overview Creating Databases Creating Filegroups Managing Databases Introduction to Data Structures Creating Databases Defining Databases How the Transaction Log Works Setting Database Options Retrieving Database Information Defining Databases Creating a Database Defines The name of the database The size of the database The files where the database will reside CREATE CREATE DATABASE DATABASE Sample Sample ON ON PRIMARY PRIMARY NAME SampleData NAME SampleData FILENAME c Program FILENAME c Program Files Data Sample mdf Files Data Sample mdf SIZE 10MB SIZE 10MB MAXSIZE 15MB MAXSIZE 15MB FILEGROWTH 20 FILEGROWTH 20 LOG LOG ON ON NAME SampleLog NAME SampleLog FILENAME FILENAME c Program c Program Files Data Sample ldf Files Data Sample ldf SIZE 3MB SIZE 3MB MAXSIZE 5MB MAXSIZE 5MB FILEGROWTH 1MB FILEGROWTH 1MB COLLATE COLLATE SQL Latin1 General Cp1 CI AS SQL Latin1 General Cp1 CI AS How the Transaction Log Works 11 Data Data modification modification is is sent sent by by application application Buffer Cache Data pages pages are are located located in in 22 Data or or read read into into buffer buffer cache cache and and modified modified 33 Modification Modification is is recorded recorded in in transaction transaction log log on on disk disk Disk Disk 44 Checkpoint Checkpoint writes writes committed committed transactions transactions to to database database Retrieving Database Information Determine Database Properties by Using the DATABASEPROPERTYEX Function Use System Stored Procedures to Display Information About Databases and Database Parameters sp helpdb sp helpdb database name sp spaceused objname Creating Filegroups sys sys sys sys sysusers sysusers sysobjects sysobjects Northwind Database Orders Orders Customers Customers Products Products OrdHistYear2 OrdHistYear2 OrdHistYear1 OrdHistYear1 C D E Northwind mdf OrdHist1 ndf OrdHist2 ndf Northwind Idf Default Filegroup OrderHistoryGroup ndf is data file ldf is log file Managing Databases Managing Data and Log File Growth Monitoring and Expanding a Transaction Log Shrinking a Database or File Dropping a Database Managing Data and Log File Growth Using Automatic File Growth Expanding Database Files Adding Secondary Database Files ALTER ALTER DATABASE DATABASE Sample Sample MODIFY MODIFY FILE FILE NAME NAME SampleLog SampleLog SIZE SIZE 15MB 15MB GO GO ALTER ALTER DATABASE DATABASE Sample Sample ADD ADD FILE FILE NAME NAME SampleData2 SampleData2 FILENAME c Program FILENAME c Program Files Files Data Sample2 ndf Data Sample2 ndf SIZE 15MB SIZE 15MB MAXSIZE 20MB MAXSIZE 20MB GO GO Monitoring and Expanding a Transaction Log Monitoring the Log Monitoring Situations That Produce Extensive Log Activity Mass loading of data into indexed table Large transactions Performing logged text or image operations Expanding the Log When Necessary Shrinking a Database or File Shrinking an Entire Database DBCC DBCC SHRINKDATABASE SHRINKDATABASE Sample Sample 25 25 Shrinking a Data File in the Database DBCC DBCC SHRINKFILE SHRINKFILE Sample Data Sample Data 10 10 Shrinking a Database Automatically Set autoshrink database option to true Dropping a Database Methods of Dropping a Database SQL Server Enterprise Manager DROP DATABASE statement DROP DROP DATABASE DATABASE Northwind Northwind pubs pubs Restrictions on Dropping a Database While it is being restored When a user is connected to it When publishing as part of replication If it is a system database Introduction to Data Structures How Data Is Stored Types of Pages and Extents Pages That Manage File Space 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 Creating Databases Creating Filegroups Managing Databases Introduction to Data Structures
View Full Document
Unlocking...