• ScottPletcher (3/4/2013)


    The ALTER command to try to change an existing file to a smaller size gives you an error on SQL 2005.

    That's when you're trying to reduce the initial size, so when the DB's size has been defined via alter database at 1.5 GB, then you cannot shrink it down to 1 GB. That requires DBCC ShrinkFile.

    The case in the original post of this question was when the DB has grown too large, via autogrow, the initial size is defined in the system catalogs as 8 MB and you want to make the file 1 GB in size. With that, you can definitely use Alter Database, with TempDB you might (and I repeat might) need to restart first and then specify the larger file size. Just one restart though, not multiple. The restart puts TempDB back to the default size, then you specify the ALTER with the larger file size.

    If you have a TempDB that someone has explicitly specified the size too large and you want to change the initial defined size that's in the system catalogs to a lower size (so someone mistakenly defined TempDB as 10GB not 1 GB), that's the time you need a DBCC ShrinkFile and for TempDB that should be done with the server in single user mode. That said, unless you have people often altering TempDB and specifying the wrong size, that certainly shouldn't be something done often, if it ever needs doing at all.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass