Module 3: Creating and Managing DatabasesOverviewCreating DatabasesDefining DatabasesPowerPoint PresentationRetrieving Database InformationCreating FilegroupsManaging DatabasesManaging Data and Log File GrowthMonitoring and Expanding a Transaction LogShrinking a Database or FileDropping a DatabaseIntroduction to Data StructuresHow Data Is StoredReviewModule 3: Creating and ManagingDatabasesOverviewCreating DatabasesCreating FilegroupsManaging DatabasesIntroduction to Data Structures Creating DatabasesDefining DatabasesHow the Transaction Log WorksSetting Database OptionsRetrieving Database InformationDefining DatabasesCREATE 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_ASCREATE 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Creating a Database Defines: The name of the databaseThe size of the databaseThe files where the database will resideHow the Transaction Log Works Data modification is sent by applicationData modification is sent by application1111DiskModification is recorded in transaction log on diskModification is recorded in transaction log on disk3333Data pages are located in, or read into, buffer cache and modifiedData pages are located in, or read into, buffer cache and modified2222Buffer CacheDiskCheckpoint writes committed transactionsto databaseCheckpoint writes committed transactionsto database4444Retrieving 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 FilegroupsNorthwind DatabaseDefault Filegroup OrderHistoryGroupsys...sys...sys...sys... sys...sys...sys...sys... sysuserssysuserssysuserssysusers sysobjectssysobjectssysobjectssysobjects ............ OrdersOrdersOrdersOrders CustomersCustomersCustomersCustomers ProductsProductsProductsProducts OrdHistYear2OrdHistYear2OrdHistYear2OrdHistYear2 OrdHistYear1OrdHistYear1OrdHistYear1OrdHistYear1 Northwind.mdfNorthwind.mdfC:\ D:\OrdHist1.ndfOrdHist1.ndfOrdHist2.ndfOrdHist2.ndfNorthwind.IdfNorthwind.IdfE:\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 DatabaseManaging Data and Log File GrowthALTER DATABASE Sample MODIFY FILE ( NAME = 'SampleLog', SIZE = 15MB)GOALTER DATABASE SampleADD FILE (NAME = SampleData2, FILENAME='c:\Program Files\..\..\ Data\Sample2.ndf', SIZE=15MB, MAXSIZE=20MB)GOALTER DATABASE Sample MODIFY FILE ( NAME = 'SampleLog', SIZE = 15MB)GOALTER DATABASE SampleADD FILE (NAME = SampleData2, FILENAME='c:\Program Files\..\..\ Data\Sample2.ndf', SIZE=15MB, MAXSIZE=20MB)GOUsing Automatic File GrowthExpanding Database FilesAdding Secondary Database FilesMonitoring 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 NecessaryShrinking a Database or FileShrinking an Entire DatabaseShrinking a Data File in the DatabaseShrinking a Database AutomaticallySet autoshrink database option to trueDBCC SHRINKDATABASE (Sample, 25)DBCC SHRINKDATABASE (Sample, 25)DBCC SHRINKFILE (Sample_Data, 10)DBCC SHRINKFILE (Sample_Data, 10)Dropping a DatabaseDROP DATABASE Northwind, pubsDROP DATABASE Northwind, pubs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 Introduction to Data StructuresHow Data Is StoredTypes of Pages and ExtentsPages That Manage File SpacePages That Track Tables and IndexesDatabaseDatabaseHow Data Is StoredExtent (8 contiguous 8-KB pages)Page (8 KB)Tables,IndexesDataMax row size = 8060 bytesData (file).mdf or .ndfLog (file).IdfReviewCreating DatabasesCreating FilegroupsManaging DatabasesIntroduction to Data