Alter database file size - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Alter database file size

Author

Topic

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2012-10-24 : 13:04:24

Hi,

I ran "sp_spaceused" on the "TEST" database and I get the following:

http://screencast.com/t/DIMxz6AVy5

As you can see the file size is "10320 KB", So I used the following alter database command to reduce the database file size to "16000KB"


USE [master]
GO
ALTER DATABASE TEST MODIFY FILE
    ( NAME= N'Test', SIZE = 16000KB )
GO

But it errors out with this error:

Msg 5039, Level 16, State 1, Line 1
MODIFY FILE failed. Specified size is less than or equal to current size.

How could this be. The reserved file is less than the database file size I am assigning for the data file. Why should it error out?

Thanks,
Dolphin

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-10-24 : 15:35:11

You cannot shrink the file size using ALTER DATABASE. You have to use DBCC SHRINKFILE to reduce the size of the file.

With that said, make sure you rebuild all indexes after shrinking the file.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources