x

increasing a Data Base Size in Sql 2000

Hello i i have a data base that is used for an application which state that there is no space available. I would like to increase the size limit for this data base. how can I accomplish this, and what should I check before I do so?

more ▼

asked Mar 28 '10 at 11:20 PM in Default

dpierce gravatar image

dpierce
21 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Start with by checking the logical names and attributes of the datafile and log file associated with your database, use sp_helpfile stored procedure like these:

USE [master]
GO
Exec sp_helpfile
Go

You should be able to see the size of your data and log files.

If you dont have space on the disk make sure you free up some space or move your files to a bigger disk space. But if the autogrowth is not turned on and the data is full you can go to Studio Management and right click on the database in question, select properties then on the left side choose Files. Then you can change the database size accordingly.

Or you can use the following ALTER DATABASE statement by changing the Database name, your data and log file along with your new size.

USE [master]
GO
ALTER DATABASE [Your Database Name] MODIFY FILE ( NAME = N'YOur Data File', SIZE = 51200KB )
GO
ALTER DATABASE [Your Database Name] MODIFY FILE ( NAME = N'Yourlogfile_log', SIZE = 1024KB )
GO

Dont depend on autogrow, make sure you plan the size of your databases ahead. Also if your databases are in FULL Recovery mode, make sure to properly maintain the transaction log.

Good Luck!

more ▼

answered Mar 29 '10 at 12:57 AM

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

(comments are locked)
10|1200 characters needed characters left

You can do this either using Enterprise Manager or T-SQL. Both are explained in MSDN.

Enterprise Manager:
http://msdn.microsoft.com/en-us/library/aa197143%28SQL.80%29.aspx

T-SQL (use ALTER DATABASE):
http://msdn.microsoft.com/en-us/library/aa275464%28SQL.80%29.aspx
Here's an example from the article for increasing the size of a database file:

USE master GO ALTER DATABASE Test1 MODIFY FILE  (NAME = test1dat3,  SIZE = 20MB) GO 
more ▼

answered Mar 29 '10 at 12:57 AM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x472
x402

asked: Mar 28 '10 at 11:20 PM

Seen: 1064 times

Last Updated: Mar 29 '10 at 12:53 AM