none
Shrink database to only up to its current initial size

    Question

  • In SQL server every database has a property Initial Size (MB) which can be seen in properties of database in SSMS. By default it will be 3 MB for mdf and 1 MB for ldf file.

    So now if create a new database then it will be set to default size(i.e 3 MB for mdf and 1 mb for ldf file). But after creating the database I change the initial size to some other value say For mdf 10 MB and ldf 5 MB.(In real-time some database administrator may want to change initial after creating database)

    But now if shrink the database it will shrink beyond the initial size I have set after creation(consider that no data is there in database otherwise it will shrink to its actual content size). It will shrink till the initial size it had during creation of database(i.e 3mb for mdf and 1 mb for ldf). I was expecting to shrink till the initial size which I have set(i.e 10 mb for mdf and 5mb for ldf). Can it be possible to do? If possible then how?

    In some article i saw like dbcc SHRINKFILE can be used to shrink beyond initial size and dbcc SHRINKDATABASE cannot shrink beyond initial size. But I want to shrink only till the initial size to which I have set.

    Note: I know that shrink is bad and should not be done.But I want to know how it can be done? If sql server considers only the initial size set during creation of database then what is the use of setting the initial size after creating database?

    Thursday, January 16, 2014 10:40 AM

Answers

All replies

  • Hello,

    even if in the UI of the database properties is named as "Initial size (MB)", the SQL Server don't stored the size value used to create the database; the column shows always the current size.

    If you shrink a database then it will shrink to the define or the minimum used size.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, January 16, 2014 10:53 AM
  • Ok i got it. But it is confusing as they have given name as Initial size for Current Size!! . Size is also shown in general tab of database properties also. 

    Also in this link they have given like "However, you can shrink the individual database files to a smaller size than their initial size by using the DBCC SHRINKFILE statement. You must shrink each file individually, instead of trying to shrink the whole database."

    This will make any new user confusing as which initial size they are talking about. One given in SSMS database properties or something else?

    Thursday, January 16, 2014 11:18 AM

  • Also in this link they have given like "However, you can shrink the individual database files to a smaller size than their initial size by using the DBCC SHRINKFILE statement. You must shrink each file individually, instead of trying to shrink the whole database."


    try this activity to clear your doubt
    --Create the database.  Notice the size of the files
    CREATE DATABASE MyDB
    ON 
    ( NAME = MyDB_dat,
        FILENAME = 'c:\MyDB.mdf',
        SIZE = 10MB,
        MAXSIZE = 50,
        FILEGROWTH = 5 )
    LOG ON
    ( NAME = MyDB_log,
        FILENAME = 'c:\MyDB.ldf',
        SIZE = 3MB,
        MAXSIZE = 25MB,
        FILEGROWTH = 5MB )
    go
    
    use MyDB
    go
    -- note down size
    sp_helpdb mydb
    
    --shrink database 
    DBCC shrinkdatabase(mydb)
    
    go
    --Compare the sizes, as you can see there is no change in sizes.
    sp_helpdb mydb
    
    --Srinking both log and data files
    dbcc shrinkfile('MyDB_Dat',1)
    go
    dbcc shrinkfile('MyDB_log',1)
    go
    
    --Compare the new size with the size after the use of dbcc shrinkfile
    sp_helpdb mydb
    go
    
    --Cleanup the messSmile
    use master
    go
    drop database MyDB

    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Thursday, January 16, 2014 12:42 PM
  • http://technet.microsoft.com/en-us/library/ms190488.aspx

    /*

    The database cannot be made smaller than the minimum size of the database. The minimum size is the size specified when the database is originally created, or the last size explicitly set by using a file size changing operation such as DBCC SHRINKFILE or ALTER DATABASE. For example, if a database is originally created with a size of 10 MB in size and grows to 100 MB, the smallest the database can be reduced to is 10 MB, even if all the data in the database has been deleted.

    */


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, January 16, 2014 1:31 PM
  • http://technet.microsoft.com/en-us/library/ms190488.aspx

    /*

    The database cannot be made smaller than the minimum size of the database. The minimum size is the size specified when the database is originally created, or the last size explicitly set by using a file size changing operation such as DBCC SHRINKFILE or ALTER DATABASE. For example, if a database is originally created with a size of 10 MB in size and grows to 100 MB, the smallest the database can be reduced to is 10 MB, even if all the data in the database has been deleted.

    */


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    I understood what you have mentioned above. As you said minimum size is the size specified during database creation. But is there any way to change the initial size of database so that during shrink it will come only up to that value. i know that during shrinkfile i can specify size. But i want to know whether we can change that initial size.
    Thursday, January 16, 2014 2:27 PM

  • Also in this link they have given like "However, you can shrink the individual database files to a smaller size than their initial size by using the DBCC SHRINKFILE statement. You must shrink each file individually, instead of trying to shrink the whole database."


    try this activity to clear your doubt
    --Create the database.  Notice the size of the files
    CREATE DATABASE MyDB
    ON 
    ( NAME = MyDB_dat,
        FILENAME = 'c:\MyDB.mdf',
        SIZE = 10MB,
        MAXSIZE = 50,
        FILEGROWTH = 5 )
    LOG ON
    ( NAME = MyDB_log,
        FILENAME = 'c:\MyDB.ldf',
        SIZE = 3MB,
        MAXSIZE = 25MB,
        FILEGROWTH = 5MB )
    go
    
    use MyDB
    go
    -- note down size
    sp_helpdb mydb
    
    --shrink database 
    DBCC shrinkdatabase(mydb)
    
    go
    --Compare the sizes, as you can see there is no change in sizes.
    sp_helpdb mydb
    
    --Srinking both log and data files
    dbcc shrinkfile('MyDB_Dat',1)
    go
    dbcc shrinkfile('MyDB_log',1)
    go
    
    --Compare the new size with the size after the use of dbcc shrinkfile
    sp_helpdb mydb
    go
    
    --Cleanup the messSmile
    use master
    go
    drop database MyDB

    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    I am not looking for this. I want to know whether i can change the initial size of database so that it will shrink only upto this size and not the size given during database creation.Please answer on this
    Thursday, January 16, 2014 2:29 PM
  • Initial size is the current size 

    select name, size * 8 AS [Initial Size in KB], size * 8 / 1024.00 AS [Initial Size in MB] from sys.master_files where database_id = DB_ID()

    You see in ssms uses for initial size.

    You can turn on Profiler and open properties on the database


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, January 16, 2014 3:37 PM
  • in my above example i have created database mydb with 10 MB data & 3 MB log file total size around 13 MB.
    then i shrink both data & log file to 1 MB and total database size around 2 MB.

    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Friday, January 17, 2014 4:37 AM
  • change the initial size of model database and there after all the databases will be created with same size of model DB.

    Ramesh Babu Vavilla MCTS,MSBI

    Friday, January 17, 2014 5:20 AM
  • in my above example i have created database mydb with 10 MB data & 3 MB log file total size around 13 MB.
    then i shrink both data & log file to 1 MB and total database size around 2 MB.

    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    You have shrinked the data & log by specifying the size in shrinkfile command other wise it will shrink only upto 10 mb for data and 3mb for log as it is initial size while creating database.

    So now instead of specifying size to be reduced in shrink file , can I change the initial size so that shrinkdatabase  command will shrink it to 1 mb without specifying size in shrink command? I mean can we change the initial size of database which shrinkdatabase command considers wile shrinking?

    Friday, January 17, 2014 5:50 AM
  • using DBCC SHRINKDATABASE you can not shrink database below its initial size, only shrinkfile with specifying size to be reduced can shrink it below its initial size.



    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Friday, January 17, 2014 5:56 AM
  • using DBCC SHRINKDATABASE you can not shrink database below its initial size, only shrinkfile with specifying size to be reduced can shrink it below its initial size.



    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Correct. But can I change the initial size of a database you are reffering about?
    Friday, January 17, 2014 6:09 AM
  • as clearly mentioned in this article, through DBCC SHRINKFILE with specifying size you can change initial size.

    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Friday, January 17, 2014 6:17 AM
  • as clearly mentioned in this article, through DBCC SHRINKFILE with specifying size you can change initial size.

    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    With DBCC SHRINKFILE with specifying size we can change the size to which db to be shrinked. It will not change this property for future dbcc shrinkdatabse commands.


    Friday, January 17, 2014 6:32 AM
  • With DBCC SHRINKFILE with specifying size we can change the size to which db to be shrinked. It will not change this property for future dbcc shrinkdatabse commands.


    YES you can not change Initial size, only alternative is to create new database with smaller initial size and copy data to this new database.

    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Friday, January 17, 2014 7:00 AM

  • YES you can not change Initial size, only alternative is to create new database with smaller initial size and copy data to this new database.
    But in my case all the database where created with default size of 3 mb for data and 1 mb for log. Now all the databases are in the range of 100-1000 mb. I wont shrink the database normally as it is not good practice. But some database grow more than 10 GB. That time I will shift unimportant contents from this database to another server. So that database size reduces from 10 GB to 1 GB. Now the remaining 9 Gb left unused. So Now i  want to specify database size to be reduce as 2 Gb so that i will get 1 gb free space. Which can be used to avoid autogrowth till 1gb of data is filled. So instead of all this if it was possible to change initial size (which shrink considers) then it would have been better. During this database creation there was no plan about size ,autogroth etc. As it was created about 3 years back. I cant again create new database with new initial size and shift data etc etc. 
    Friday, January 17, 2014 7:58 AM
  • I got the proper answer here 
    • Marked as answer by IT researcher Saturday, January 18, 2014 4:46 AM
    Saturday, January 18, 2014 4:46 AM
  • You are correct in your observations. The word "Initial" should be changed to "Current" or removed. Consider reporting this to connect.microsoft.com (if it isn't already reported) so MS are aware of this bug.

    There is a history why this is called Initial, related to tempdb, but that isn't really relevant. Fact is that the column name is wrong and misleading and should be changed.


    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, January 20, 2014 1:26 PM