CODEWITHSUNDEEP
sql-serverdatabaset-sql
Silly Rabbit
Silly Rabbit

Reputation: 328

How do I grow a database file?

I am trying to grow a database using the following the code below. However I get the following error. How do I check for size (3600MB) and grow it if necessary?

USING MyDatabase

ALTER DATABASE MyDatabase
MODIFY FILE (NAME =  MyDatabase_data, SIZE = 3600MB)

Error: MODIFY FILE failed. Specified size is less than or equal to current size.

UPDATE: I am not using Auto Grow due to the heavy traffic. Due to the setup I have here (long unrelated story) I need to make the change using code. However, if this code runs more than once I get the error described above. I need to check the size first before attempting the change again.

Upvotes: 1

Views: 19161

Answers (4)

Kristen
Kristen

Reputation: 4281

The size of the DB will be shown by

USE MyDatabase
EXEC sp_spaceused

looking at the code for sp_spaceused (I happen to be looking at a SQL 2000 server, but same/similar would be true for SQL2005 / SQL2008)

USE master
EXEC sp_helptext 'sp_spaceused'

and the relevant code is:

declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage   dec(15,0)
declare @pagesperMB     dec(15,0)

    select @dbsize = sum(convert(dec(15),size))
        from dbo.sysfiles
        where (status & 64 = 0)

    select @logsize = sum(convert(dec(15),size))
        from dbo.sysfiles
        where (status & 64 <> 0)

    select @bytesperpage = low
        from master.dbo.spt_values
        where number = 1
            and type = 'E'
    select @pagesperMB = 1048576 / @bytesperpage

    select  database_name = db_name(),
        database_size =
            ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB')

so from that you could save the size to a variable and compare again the size you were trying to set:

DECLARE @database_size bigint
SELECT  @database_size = (@dbsize + @logsize) / @pagesperMB
IF @database_size < 3600
BEGIN
    PRINT 'Expanding MyDatabase ...'
    ALTER DATABASE MyDatabase
    MODIFY FILE (NAME =  MyDatabase_data, SIZE = 3600MB)
    PRINT 'Expanding MyDatabase DONE'
END
ELSE
BEGIN
    PRINT 'No expansion of MyDatabase required'
END

Upvotes: 5

Brian R. Bondy
Brian R. Bondy

Reputation: 347216

MS SQL allocates more space than it needs. So your actual used database size is often much smaller than the allocated size.

MS SQL allocates more space than it needs because the allocation of more data is pretty expensive and it's more efficient to do it periodically instead of on each write to the DB.

If you want to alter how much space is allocated at a time, you can use the FILEGROWTH parameter to set up the type of file growth you'd like on your database.

A value of:

  • 0 means there is no growth allowed
  • [X]MB or [X]GB means to grow by that much when your DB current size is reached
  • [X]% means to grow by that much percent when your DB current size is reached

.

ALTER DATABASE [databasename] MODIFY FILE ( NAME = N'databasename', FILEGROWTH = 1GB )

To free up the extra allocated space you can perform a DBCC SHRINKDATABASE

To obtain the actual size of your database you can execute this:

SELECT size FROM sys.database_files WHERE name = 'databasename'

To obtain the actual size of your database log you can execute this:

SELECT size FROM sys.database_files WHERE name = 'databasename_log'

Upvotes: 1

Richard
Richard

Reputation: 108975

To get the size (and other information) of all database files on the server (obviously you can filter this):

select * from sys.database_files

Upvotes: 1

Simon Hughes
Simon Hughes

Reputation: 3574

The database will automatically grow. If you (MS SQL Server anyway) right click on the database, properties. You can set it grow by a percentage, say 10%, or by a fixed size, say 100Mb.

You should not need to maintain the database size yourself.

Upvotes: 2

Related Questions