Altering and Dropping Databases And Tables In Microsoft SQL Server 2000, Part 3 - The General Syntax

(Page 2 of 3 )

ALTER DATABASE <database name>   ADD FILE([Name 
= ‘logical file name’ >,]         Filename = < ‘File Name’ >           [, Size = 
<Size in Megabytes or kilobytes>]           [, Maxsize = <Size in Megabytes or 
kilobytes>]           [, Filegrowth  = <No of Megabytes or 
kilobytes/percentage>][,…n]               [ TO FILEGROUP filegroup_name][ ADD 
Log FILE     ([Name = ‘logical file name’ >,]         Filename = < ‘File Name’ > 
          [, Size = <Size in Megabytes or kilobytes>]           [, Maxsize = 
<Size in Megabytes or kilobytes>]           [, Filegrowth  = <No of Megabytes or 
kilobytes/percentage>])]          | REMOVE FILE <logical_file_name> [WITH 
DELETE]| ADD FILEGROUP <filegroup_name>| REMOVE FILEGROUP <filegroup_name >| 
MODIFY FILE < filespec >| MODIFY NAME = <new_dbname >| MODIFY FILEGROUP 
<filegroup_name> {filegroup_property | NAME = <new_filegroup_name >}| SET < 
optionspec > [ ,...n ] [ WITH < termination > ]| COLLATE < collation_name >}

The reality in this case is that we will rarely be using all the above stuff, but since it was syntax definition did not want to cut it short.

So after looking at all the boring stuff, now come to the exciting part, we need to expand our database to 100MB more. We do this by using a simple syntax given below:

ALTER DATABASE Customers     MODIFY FILE       
 (NAME = Customerdata,             SIZE =100MB)

Now unlike when we created our database, SQL Server does not give us back the above two table of information, instead we get a non-verbose:

The command(s) completed 
successfully

So how do you make sure if the changes we made have been updated to the database? We have to do it ourselves using the command below:

EXCEC sp_helpdb Customers

Name	db_size	Owner	dbid	Created	Status	Compatibility
Customers	120MB	sa	10	May 24, 2002	Status=online, Updateability=Read_write, UserAccess=MULTI_USER, Recovery =FULL Version=538, Collation=SQL_LATIN1_ General_CP1_C1_AS, SQLSortOrder =52 IsTornPageDetectionEnable, IsAutoCreateStatistics, IsAutoUpdateStatistics	80

And the following table: (Please note just giving you approximate file sizes, not exact figure).

Home FieldField

	Field	Filename	Filegroup	Size	MaxSize	Growth	Usage
Customers	1	‘C:\Program Files\Microsoft SQL Server \MSSQL \data\Customerdata.mdf’,	PRIMARY	10240 KB	102400KB	5120 KB	Data Only.
Customer Log	2	‘C:\Program Files\Microsoft SQL Server \MSSQL \data\Customelog.ldf’,	NULL	5120KB	25600 KB	5210KB	LOG ONLY.

One thing worth noting is that even though we exceed the pervious maximum size, we did not get an error. This is because we have explicitly increased the size. Another thing to note is that our MaxSize was only increased to our new explicitly specified value, leaving no space for any file growth. If only we had done things our original way, by just allowing SQL Server auto expand itself, then our import would have blown up in the middle due to size restriction. Things pretty much work the same way for any common database-level modification you’ll make. The permutations are endless. The more complex filegroup modification is outside the scope of this article. But just giving a short example

ALTER DATABASE Customers      ADD FILE       
 (NAME =  ‘Emp_File’,           FILENAME = ‘C:Program FilesMicrosoft SQL 
ServerMSSQLdataEMP_File.mdf’,             SIZE =4MB,               MAXSIZE = 
100MB,                 FILEGROWTH =2            )

Adds a file Emp_File of size 4MB to the database Customers. Similarly we can add a file group to the existing database. Below we add filegroup fgrp1 to the existing Customer:

ALTER DATABASE Customers      ADD FILEGROUP fgrp1

So we have seen how to alter an existing database by increasing its size, adding a file and even a filegroup. Now we will move into altering a table.

Next: ALTER TABLE >>
 

More MS SQL Server Articles
More By Gayathri Gokul

Comments On: Altering and Dropping Databases And Tables In Microsoft SQL Server 2000, Part 3

>>> Be the FIRST to comment on this article!