Expanding Database

[stoolpigeon]

If I want to expand a database and the data device on which it currently resides is full can I expand the data device or do I need to make a new device and expand the database onto the new device?

This is for SQL Server 6.5


thanks,
Ron

 

[essa2000]

Dear ;

I think you can use ALTER DATABASE to add a new file for your data. It if from BOL of SQL 2000 , but I hope it will also in 6.5 but you can check it out.


Permissions
ALTER DATABASE permissions default to members of the sysadmin and dbcreator fixed server roles, and to members of the db_owner fixed database roles. These permissions are not transferable.


A. Add a file to a database

This example creates a database and alters it to add a new 5-MB data file.

USE master
GO
CREATE DATABASE Test1 ON
(
NAME = Test1dat1,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
GO
ALTER DATABASE Test1
ADD FILE
(
NAME = Test1dat2,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
GO

B. Add a filegroup with two files to a database

This example creates a filegroup in the Test 1 database created in Example A and adds two 5-MB files to the filegroup. It then makes Test1FG1 the default filegroup.

USE master
GO
ALTER DATABASE Test1
ADD FILEGROUP Test1FG1
GO

ALTER DATABASE Test1
ADD FILE
( NAME = test1dat3,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB),
( NAME = test1dat4,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat4.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP Test1FG1

ALTER DATABASE Test1
MODIFY FILEGROUP Test1FG1 DEFAULT
GO

C. Add two log files to a database

This example adds two 5-MB log files to a database.

USE master
GO
ALTER DATABASE Test1
ADD LOG FILE
( NAME = test1log2,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\test2log.ldf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB),
( NAME = test1log3,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\test3log.ldf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
GO

D. Remove a file from a database

This example removes one of the files added to the Test1 database in Example B.

USE master
GO
ALTER DATABASE Test1
REMOVE FILE test1dat4
GO



Regards,
Muhammad Essa Mughal