1 / 17

Module 2: Creating and Managing Databases

Module 2: Creating and Managing Databases. Overview. Defining Databases Using Filegroups Managing Databases. Lesson: Defining Databases. How Are Databases Created? How the Transaction Log Works Database Options How Is Database Information Retrieved?. How Are Databases Created?.

nida
Download Presentation

Module 2: Creating and Managing Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 2: Creating and ManagingDatabases

  2. Overview • Defining Databases • Using Filegroups • Managing Databases

  3. Lesson: Defining Databases • How Are Databases Created? • How the Transaction Log Works • Database Options • How Is Database Information Retrieved?

  4. How Are Databases Created? • Creating a Database Defines: • The name of the database • The size of the database files • The location where the database will reside CREATE DATABASE SampleON PRIMARY ( NAME=SampleData, FILENAME='c:\Program Files\..\..\Data\Sample.mdf', SIZE=10MB, MAXSIZE=15MB, FILEGROWTH=20%) LOG ON ( NAME=SampleLog, FILENAME= 'c:\Program Files\..\..\Data\Sample.ldf', SIZE=3MB, MAXSIZE=5MB, FILEGROWTH=1MB)COLLATE SQL_Latin1_General_Cp1_CI_AS

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

  6. Database Options • Set Database Options By Using: • SQL Server Enterprise Manager • ALTER DATABASE statement • Database Option Categories • Auto options • Cursor options • Recovery options • SQL options • State options

  7. How is Database Information Retrieved? • Retrieve Database Properties by Using the DATABASEPROPERTYEX Function • Use System Stored Procedures to Display Information About Databases and Database Parameters • sp_helpdb • sp_helpdbdatabase_name • sp_spaceused [objname]

  8. Lesson: Using Filegroups • What Are Filegroups? • Filegroup Management

  9. What Are Filegroups? Northwind Database sys... ... sys... Orders OrdHistYear2 sysusers Customers OrdHistYear1 sysobjects Products E:\ C:\ D:\ OrdHist1.ndf Northwind.Idf Northwind.mdf OrdHist2.ndf Default Filegroup OrderHistoryGroup

  10. Filegroup Management • Creating Additional Filegroups • Designating the Default Filegroup ALTER DATABASE Northwind ADD FILEGROUP OrderHistoryGroup GO ALTER DATABASE Northwind MODIFY FILEGROUP OrderHistoryGroup DEFAULT GO • Sizing the Primary Default Filegroup • Viewing Filegroup Information

  11. Lesson: Managing Databases • Data and Log File Growth Management • Guidelines for Transaction Log Maintenance • How Databases or Files Are Shrunk • How Databases Are Dropped • Best Practices

  12. Guidelines for Managing File Growth • Use Automatic File Growth • Expand Database Files • Add Secondary Database Files ALTER DATABASE SampleMODIFY FILE ( NAME = 'SampleLog',SIZE = 15MB) GO ALTER DATABASE Sample ADD FILE (NAME = SampleData2, FILENAME='c:\Program Files\..\..\ Data\Sample2.ndf', SIZE=15MB, MAXSIZE=20MB) GO

  13. Guidelines for Transaction Log Maintenance • Monitor the Log • Monitor Situations That Produce Extensive Log Activity • Mass loading of data into indexed table • Large transactions • Performing logged text or image operations • Expand the Log When Necessary

  14. How Databases or Files Are Shrunk • Shrinking an Entire Database • Shrinking a Data File in the Database • Shrinking a Database Automatically • Set autoshrink database option to true DBCC SHRINKDATABASE (Sample, 25) DBCC SHRINKFILE (Sample_Data, 10)

  15. How Databases Are Dropped • Methods of Dropping a Database • SQL Server Enterprise Manager • DROP DATABASE statement • 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 DROP DATABASE Northwind, pubs

  16. Best Practices Back Up the Master Database ü Carefully Plan the Initial File Size ü Monitor File Sizes and Manually Increase Them During Low-Activity Time ü Specify a Maximum File Size ü Specify Large Autogrow Increments ü Change the Default Filegroup ü

  17. Lab A: Creating and Managing Databases • Exercise 1: Creating the ClassNorthwind Database • Exercise 2: Managing the Growth of the ClassNorthwind Transaction Log File • Exercise 3: Setting the Database Recovery Model

More Related