• rightontarget (7/21/2015)


    Ran DBCC SHRINKFILE (1, TRUNCATEONLY); with not much success. I am trying to shrink this d/b because I am taking backup of it and restoring it to another server daily.

    Do you realize that you've already been told that your backup size IS NOT GOING TO SHRINK because you shrink the database file? The only thing that's going to affect the size of your backup is how much data you have and how much additional data is needed for indexes and metadata, and then whether you use compression on the backup or not. As others have already indicated, shrinking the database file isn't going to get you anywhere, and is generally a bad idea, as any pre-allocated space that may have been providing optimal disk space allocation now goes away, and has to be re-allocated in the amount the database is configured to grow by. As your total volume of data and indexes and metadata grows, so will your backup size. If the data volume in a backup has become too large for the process you've established, then you may need to consider some alternatives that SQL Server is designed to deal with, such as replication or log shipping.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)