• TDP (1/19/2009)


    You would think that if you cannot shrink the file below the initial size they'd at least give us the possibility to be able to decrease the initial size then.... :hehe:

    Actually shrink file does let you shrink the files so they’ll be smaller then the initial size. Check the script bellow that shows it:

    --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

    --Get the file's size

    select name, size from MyDB.sys.database_files

    --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 before the use of dbcc shrinkfile

    select name, size from MyDB.sys.database_files

    go

    --Cleanup the mess:-)

    use master

    go

    drop database MyDB

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/