none
how to reduce initial data file size in sql server 2005

    Question

  • I have created a database with data file size 80GB and now I want to reduce the file size to 30GB in sql server 2005.

    need help to reduce the initial file size. when I do

    alter

    database test modify file (name = test, size = xx mb) getting following error

    MODIFY FILE failed. Specified size is less than current size.

    Monday, May 04, 2009 2:13 PM

Answers

  • To shrink a file after it is allocated, you need to use the SHRINKFILE command:

    USE [DatabaseName]
    GO
    DBCC SHRINKFILE (N'FileName' , size in KB)
    GO
    


    You can get further information on this in the BOL:

    DBCC SHRINKFILE (Transact-SQL)

    You can't shrink a file beyond the size of the existing data in the file, and you should only shrink a file in the instance where the database won't require the additional space again in the future.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Monday, May 04, 2009 2:31 PM
    Moderator
  • Hi,

    The ALTER DATABASE statement can increase the value of the size property of a data file. But it doesn’t support decrease the size of a date file. As the Jonathan mentioned above, DBCC SHRINKFILE allows you to shrink files in the current database.

    Please note that when you specify target_size, DBCC SHRINKFILE attempts to shrink the specified file to the specified size in megabytes. However, DBCC SHRINKFILE doesn't shrink a file past the size needed to store the data. For example, if 70 percent of the pages in a 10-MB data file are used, a DBCC SHRINKFILE statement with a target_size of 5 shrinks the file to only 7 MB, not 5 MB.

    You could view the Disk Usage report to check the data files space usage. You can access the Disk Usage report by using SQL Server Managerment Sutdio.  To view the report, right-click the database name in Object Explorer, point to Reports and then click Dist Usage.


    If you have any more questions, please let me know.

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Monday, May 11, 2009 3:46 AM
    Moderator

All replies

  • The error message is telling you that it has more data than the size you specified which is 30GB.

    Why are you trying to shrink the file size? Extra space is necessary when the data grows, autogrow is NOT used. When autogrow is used, the file may be fragmented at the OS level (not index fragmentation) which may hurt performance.


    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Monday, May 04, 2009 2:24 PM
    Moderator
  • To shrink a file after it is allocated, you need to use the SHRINKFILE command:

    USE [DatabaseName]
    GO
    DBCC SHRINKFILE (N'FileName' , size in KB)
    GO
    


    You can get further information on this in the BOL:

    DBCC SHRINKFILE (Transact-SQL)

    You can't shrink a file beyond the size of the existing data in the file, and you should only shrink a file in the instance where the database won't require the additional space again in the future.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Monday, May 04, 2009 2:31 PM
    Moderator
  • U cannot reduce the file size less than initial size, if your initial data size 80 giga byte than u cannot reduce the size to 30 GB. to ensure whether u have free space in your db, right klik your database name and then klik properties n see space available, that size shows u whether your DB have any free space to be shrink
    Monday, May 04, 2009 3:07 PM
  • Hi,

    The ALTER DATABASE statement can increase the value of the size property of a data file. But it doesn’t support decrease the size of a date file. As the Jonathan mentioned above, DBCC SHRINKFILE allows you to shrink files in the current database.

    Please note that when you specify target_size, DBCC SHRINKFILE attempts to shrink the specified file to the specified size in megabytes. However, DBCC SHRINKFILE doesn't shrink a file past the size needed to store the data. For example, if 70 percent of the pages in a 10-MB data file are used, a DBCC SHRINKFILE statement with a target_size of 5 shrinks the file to only 7 MB, not 5 MB.

    You could view the Disk Usage report to check the data files space usage. You can access the Disk Usage report by using SQL Server Managerment Sutdio.  To view the report, right-click the database name in Object Explorer, point to Reports and then click Dist Usage.


    If you have any more questions, please let me know.

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Monday, May 11, 2009 3:46 AM
    Moderator
  • Thanks to ALL for your help
    Monday, May 11, 2009 12:47 PM