Change the size of database

We have created a database with size 500MB and find that
the size of it should be much larger (2GB) according to
the developer.
We would like to know is there any way to extend the size
of the database ? OR We have to backup the existing one,
delete it, create a new one, load the data ?
Thanks
You can change the size of the database files in Enterprise Manager, or you
can use the ALTER DATABASE command:
ALTER DATABASE databasename MODIFY FILE (NAME=logical_file_name,
SIZE=2000MB)
[See BOL for more info]
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:5a7601c525d6$3b6bb270$a501280a@.phx.gbl...
> We have created a database with size 500MB and find that
> the size of it should be much larger (2GB) according to
> the developer.
> We would like to know is there any way to extend the size
> of the database ? OR We have to backup the existing one,
> delete it, create a new one, load the data ?
> Thanks
|||You can increate the size of database files using ALTER DATABASE ... MODIFY
FILE. You can find syntax details on the Books Online. Below is an example
copied from the Book Online <tsqlref.chm::/ts_aa-az_4e5h.htm>.
ALTER DATABASE Test1
MODIFY FILE
(NAME = test1dat3,
SIZE = 20MB)
You can also do this from Enterprise Manager. Consider also allowing file
growth as a safety net.
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:5a7601c525d6$3b6bb270$a501280a@.phx.gbl...
> We have created a database with size 500MB and find that
> the size of it should be much larger (2GB) according to
> the developer.
> We would like to know is there any way to extend the size
> of the database ? OR We have to backup the existing one,
> delete it, create a new one, load the data ?
> Thanks
|||Paul wrote:
> We have created a database with size 500MB and find that
> the size of it should be much larger (2GB) according to
> the developer.
> We would like to know is there any way to extend the size
> of the database ? OR We have to backup the existing one,
> delete it, create a new one, load the data ?
> Thanks
Of course you can increase database size. The size of database is made
up of a sum of the data files and log files used. So you need to
determine how many data files you have and what size you'd like to
increase them to. Same for the log files. In each case, you have the
option and should make use of the AutoGrow option which can increase
data and log file sizes by a percentage or a fixed amount when space
runs low. It's best to manange file sizes yourself and increase
off-hours, but using AutoGrow will prevent the database from crashing if
it runs out of disk space. Still keep an eye on the file sizes over
time. Increasing the file sizes now can limit external fragmentation in
the files. If you have time to take the SQL Server offline, I would
defrag the drives and then start up SQL Server and increase the file
sizes.
You can use the ALTER DATABASE command, but it's easier to use SQL
Enterprise Manager for this type of task.
David Gugick
Imceda Software
www.imceda.com
|||In this way, when I execute the ALTER TABLE command, does
it only change the size of data file or the size of the
database (Data File and Log File) ?
ALTER DATABASE databasename MODIFY FILE
(NAME=logical_file_name,
SIZE=2000MB)
[vbcol=seagreen]
>--Original Message--
>Paul wrote:
size[vbcol=seagreen]
one,
>Of course you can increase database size. The size of
database is made
>up of a sum of the data files and log files used. So you
need to
>determine how many data files you have and what size
you'd like to
>increase them to. Same for the log files. In each case,
you have the
>option and should make use of the AutoGrow option which
can increase
>data and log file sizes by a percentage or a fixed amount
when space
>runs low. It's best to manange file sizes yourself and
increase
>off-hours, but using AutoGrow will prevent the database
from crashing if
>it runs out of disk space. Still keep an eye on the file
sizes over
>time. Increasing the file sizes now can limit external
fragmentation in
>the files. If you have time to take the SQL Server
offline, I would
>defrag the drives and then start up SQL Server and
increase the file
>sizes.
>You can use the ALTER DATABASE command, but it's easier
to use SQL
>Enterprise Manager for this type of task.
>
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>
|||> In this way, when I execute the ALTER TABLE command, does
> it only change the size of data file or the size of the
> database (Data File and Log File) ?
It will change the size of the specified file. Each physical file has a
unique logical name within a database. Specify the logical name in the
ALTER DATABASE command. You can use sp_help_db to list the database file
names. Example below:
--alter data file
ALTER DATABASE MyDatabase
MODIFY FILE
(NAME='MyDatabase',
SIZE=2000MB)
--alter log file
ALTER DATABASE MyDatabase
MODIFY FILE
(NAME='MyDatabase_Log',
SIZE=200MB)
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:6f6401c525de$a967b280$a601280a@.phx.gbl...[vbcol=seagreen]
> In this way, when I execute the ALTER TABLE command, does
> it only change the size of data file or the size of the
> database (Data File and Log File) ?
> ALTER DATABASE databasename MODIFY FILE
> (NAME=logical_file_name,
> SIZE=2000MB)
> size
> one,
> database is made
> need to
> you'd like to
> you have the
> can increase
> when space
> increase
> from crashing if
> sizes over
> fragmentation in
> offline, I would
> increase the file
> to use SQL
|||> You can use sp_help_db to list the database file names
Oops. I meant sp_helpdb. And of course with the database parameter:
EXEC sp_helpdb 'MyDatabase'
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:es66IOeJFHA.732@.TK2MSFTNGP12.phx.gbl...
> It will change the size of the specified file. Each physical file has a
> unique logical name within a database. Specify the logical name in the
> ALTER DATABASE command. You can use sp_help_db to list the database file
> names. Example below:
> --alter data file
> ALTER DATABASE MyDatabase
> MODIFY FILE
> (NAME='MyDatabase',
> SIZE=2000MB)
> --alter log file
> ALTER DATABASE MyDatabase
> MODIFY FILE
> (NAME='MyDatabase_Log',
> SIZE=200MB)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul" <anonymous@.discussions.microsoft.com> wrote in message
> news:6f6401c525de$a967b280$a601280a@.phx.gbl...
>
|||I think someone else already pointed out that SQL Databases are composed of
2 or more files: at least one (or more) database file(s) and a log file.
You specify the file you want to change the size of in the statement below
by specifying its name in place of "logical_file_name". If you specify the
database file name it will change the size of the database file; if you
specify the log file name it will change the size of the log file.
There are some restrictions on changing database sizes, particularly on
shrinking them. See the ALTER DATABASE and BACKUP LOG (WITH TRUNCATE_ONLY)
commands in BOL for specifics.
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:6f6401c525de$a967b280$a601280a@.phx.gbl...[vbcol=seagreen]
> In this way, when I execute the ALTER TABLE command, does
> it only change the size of data file or the size of the
> database (Data File and Log File) ?
> ALTER DATABASE databasename MODIFY FILE
> (NAME=logical_file_name,
> SIZE=2000MB)
> size
> one,
> database is made
> need to
> you'd like to
> you have the
> can increase
> when space
> increase
> from crashing if
> sizes over
> fragmentation in
> offline, I would
> increase the file
> to use SQL

Change the size of database

We have created a database with size 500MB and find that
the size of it should be much larger (2GB) according to
the developer.
We would like to know is there any way to extend the size
of the database ? OR We have to backup the existing one,
delete it, create a new one, load the data ?
ThanksYou can change the size of the database files in Enterprise Manager, or you
can use the ALTER DATABASE command:
ALTER DATABASE databasename MODIFY FILE (NAME=logical_file_name,
SIZE=2000MB)
[See BOL for more info]
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:5a7601c525d6$3b6bb270$a501280a@.phx.gbl...
> We have created a database with size 500MB and find that
> the size of it should be much larger (2GB) according to
> the developer.
> We would like to know is there any way to extend the size
> of the database ? OR We have to backup the existing one,
> delete it, create a new one, load the data ?
> Thanks|||You can increate the size of database files using ALTER DATABASE ... MODIFY
FILE. You can find syntax details on the Books Online. Below is an example
copied from the Book Online <tsqlref.chm::/ts_aa-az_4e5h.htm>.
ALTER DATABASE Test1
MODIFY FILE
(NAME = test1dat3,
SIZE = 20MB)
You can also do this from Enterprise Manager. Consider also allowing file
growth as a safety net.
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:5a7601c525d6$3b6bb270$a501280a@.phx.gbl...
> We have created a database with size 500MB and find that
> the size of it should be much larger (2GB) according to
> the developer.
> We would like to know is there any way to extend the size
> of the database ? OR We have to backup the existing one,
> delete it, create a new one, load the data ?
> Thanks|||Paul wrote:
> We have created a database with size 500MB and find that
> the size of it should be much larger (2GB) according to
> the developer.
> We would like to know is there any way to extend the size
> of the database ? OR We have to backup the existing one,
> delete it, create a new one, load the data ?
> Thanks
Of course you can increase database size. The size of database is made
up of a sum of the data files and log files used. So you need to
determine how many data files you have and what size you'd like to
increase them to. Same for the log files. In each case, you have the
option and should make use of the AutoGrow option which can increase
data and log file sizes by a percentage or a fixed amount when space
runs low. It's best to manange file sizes yourself and increase
off-hours, but using AutoGrow will prevent the database from crashing if
it runs out of disk space. Still keep an eye on the file sizes over
time. Increasing the file sizes now can limit external fragmentation in
the files. If you have time to take the SQL Server offline, I would
defrag the drives and then start up SQL Server and increase the file
sizes.
You can use the ALTER DATABASE command, but it's easier to use SQL
Enterprise Manager for this type of task.
David Gugick
Imceda Software
www.imceda.com|||In this way, when I execute the ALTER TABLE command, does
it only change the size of data file or the size of the
database (Data File and Log File) ?
ALTER DATABASE databasename MODIFY FILE
(NAME=logical_file_name,
SIZE=2000MB)

>--Original Message--
>Paul wrote:
size[vbcol=seagreen]
one,[vbcol=seagreen]
>Of course you can increase database size. The size of
database is made
>up of a sum of the data files and log files used. So you
need to
>determine how many data files you have and what size
you'd like to
>increase them to. Same for the log files. In each case,
you have the
>option and should make use of the AutoGrow option which
can increase
>data and log file sizes by a percentage or a fixed amount
when space
>runs low. It's best to manange file sizes yourself and
increase
>off-hours, but using AutoGrow will prevent the database
from crashing if
>it runs out of disk space. Still keep an eye on the file
sizes over
>time. Increasing the file sizes now can limit external
fragmentation in
>the files. If you have time to take the SQL Server
offline, I would
>defrag the drives and then start up SQL Server and
increase the file
>sizes.
>You can use the ALTER DATABASE command, but it's easier
to use SQL
>Enterprise Manager for this type of task.
>
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||> In this way, when I execute the ALTER TABLE command, does
> it only change the size of data file or the size of the
> database (Data File and Log File) ?
It will change the size of the specified file. Each physical file has a
unique logical name within a database. Specify the logical name in the
ALTER DATABASE command. You can use sp_help_db to list the database file
names. Example below:
--alter data file
ALTER DATABASE MyDatabase
MODIFY FILE
(NAME='MyDatabase',
SIZE=2000MB)
--alter log file
ALTER DATABASE MyDatabase
MODIFY FILE
(NAME='MyDatabase_Log',
SIZE=200MB)
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:6f6401c525de$a967b280$a601280a@.phx.gbl...[vbcol=seagreen]
> In this way, when I execute the ALTER TABLE command, does
> it only change the size of data file or the size of the
> database (Data File and Log File) ?
> ALTER DATABASE databasename MODIFY FILE
> (NAME=logical_file_name,
> SIZE=2000MB)
>
> size
> one,
> database is made
> need to
> you'd like to
> you have the
> can increase
> when space
> increase
> from crashing if
> sizes over
> fragmentation in
> offline, I would
> increase the file
> to use SQL|||> You can use sp_help_db to list the database file names
Oops. I meant sp_helpdb. And of course with the database parameter:
EXEC sp_helpdb 'MyDatabase'
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:es66IOeJFHA.732@.TK2MSFTNGP12.phx.gbl...
> It will change the size of the specified file. Each physical file has a
> unique logical name within a database. Specify the logical name in the
> ALTER DATABASE command. You can use sp_help_db to list the database file
> names. Example below:
> --alter data file
> ALTER DATABASE MyDatabase
> MODIFY FILE
> (NAME='MyDatabase',
> SIZE=2000MB)
> --alter log file
> ALTER DATABASE MyDatabase
> MODIFY FILE
> (NAME='MyDatabase_Log',
> SIZE=200MB)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul" <anonymous@.discussions.microsoft.com> wrote in message
> news:6f6401c525de$a967b280$a601280a@.phx.gbl...
>|||I think someone else already pointed out that SQL Databases are composed of
2 or more files: at least one (or more) database file(s) and a log file.
You specify the file you want to change the size of in the statement below
by specifying its name in place of "logical_file_name". If you specify the
database file name it will change the size of the database file; if you
specify the log file name it will change the size of the log file.
There are some restrictions on changing database sizes, particularly on
shrinking them. See the ALTER DATABASE and BACKUP LOG (WITH TRUNCATE_ONLY)
commands in BOL for specifics.
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:6f6401c525de$a967b280$a601280a@.phx.gbl...[vbcol=seagreen]
> In this way, when I execute the ALTER TABLE command, does
> it only change the size of data file or the size of the
> database (Data File and Log File) ?
> ALTER DATABASE databasename MODIFY FILE
> (NAME=logical_file_name,
> SIZE=2000MB)
>
> size
> one,
> database is made
> need to
> you'd like to
> you have the
> can increase
> when space
> increase
> from crashing if
> sizes over
> fragmentation in
> offline, I would
> increase the file
> to use SQL