1. Set memory options -- Configure min Server Memory
Exec Sp_configure n ' Min server memory (MB) ' , 0
-- Set max server memory configuration items
Exec Sp_configure n ' Max server memory (MB) ' , 256
-- Make updates take effect
Reconfigure With Override
2. Use Files and file groups/ *-- Function Description
The following code creates a database named mydb on SQL Server 2000
The database includes one primary data file, three user-defined file groups, and one log file.
The alter database statement specifies the User-Defined file group as the default file group.
Then, create a table by referring to the default file group and place the image data and indexes in the specified file group.
Finally, delete the specified data file in the file group.
--*/
-- Switch to master database
Use Master
Go
-- A. Create a database named mydb.
Create Database Mydb
On Primary -- Main file group and main data file
(Name = ' Mydb_primary ' ,
Filename = ' C: \ mydb_prm.mdf ' ),
Filegroup mydb_fg1 -- User-Defined File Group 1
(Name = ' Mydb_fg1_dat1 ' ,
Filename = ' C: \ mydb_fg1_1.ndf ' ), -- Secondary data file 1
(Name = ' Mydb_fg1_dat2 ' ,
Filename = ' D: \ mydb_fg1_2.ndf ' ), -- Secondary data file 2
Filegroup mydb_fg2 -- User-Defined File Group 2
(Name = ' Mydb_fg1_dat ' ,
Filename = ' E: \ mydb_fg2.ndf ' ) -- Secondary data file
Log On -- Log Files
(Name = ' Mydb_log ' ,
Filename = ' D: \ mydb. LDF ' )
Go
-- B. Modify the default data file group
Alter Database Mydb modify filegroup mydb_fg1 Default
Go
-- Switch to the new database mydb
Use Mydb
-- C. Create a table in the default file group mydb_fg1 and save the specified image data in the User-Defined file group mmydb_fg2.
Create Table Mytable
(Cola Int Primary Key ,
COLB Char ( 8 ),
Colc Image )
Textimage_on mydb_fg2
-- Create an index on mydb_fg2
Create Index Ix_mytable On Mytable (COLA) On Mydb_fg2
Go
-- D. Move the data from mydb_fg1_dat1 to another data file and clear the data file mydb_fg1_dat1.
DBCC Shrinkfile (mydb_fg1_dat1, emptyfile)
-- Delete the data file mydb_fg1_dat1
Alter Database Mydb remove File Mydb_fg1_dat1
3. Adjust the file attributes of the tempdb Database -- A. Set the size of the master data file of the tempdb database to 10 MB.
Alter Database Tempdb
Modify File (
Name = Tempdev,
Size = 100 MB)
Go
-- B. Move the master data file of the tempdb database to the specified disk partition and add a data file to it.
-- Move master data files
Alter Database Tempdb modify File
(Name = ' Tempdev ' ,
Filename = ' D: \ tempdb. MDF ' )
-- Add secondary data file
Alter Database Tempdb Add File
(Name = ' Tempdata_1 ' ,
Filename = ' D: \ tempdb_data_1.ndf ' )