Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wardsan
Starting Member
48 Posts |
 Posted - 2010-09-15 : 04:59:54
|
| Folks,is there any benefit to performing a backup and restore on a sql database with regard to freeing up space/reducing the size of the database. Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-15 : 05:34:55
|
| No, restore will put it back exactly how it was before, including any free/unused space - so filesize will remain the same.You should do regular Index Rebuild, which will defragment the indexes etc., and Update Statistics which will make sure that the Statistical data about distribution of data in the indexes etc. which the Query Planner uses is up to date.If you have a one-off delete of stale data, say, then you can do a one-time SHRINK to reduce the overall size of the database, but you should not shrink the database below its normal operating size - that will fragment the indexes, and repeated Shrink and then the database Re-growing will fragment the physical file too.If you use Full Recovery Model (rather than Simple) then make sure the TLog backups are frequent (e.g. every 15 minutes) which will stop the LDF / Log file growing excessively.That's about it! |
 |
|
|
wardsan
Starting Member
48 Posts |
Posted - 2010-09-15 : 05:51:58
|
| The database is hundreds of GB. It is in simple recovery mode. There is a reorg of indexes scheduled for every sunday. I am not clear what the difference is between rebuilding and reorging indexes. there is a huge amount of free space in the datafile. What would be the best option for the current time? Would it be rebuilding the indexes or just shrinking the database.Thanks again |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-15 : 13:12:22
|
| If there is a lot of slack space you could shrink the database. Do you need the disk space? If not I would (within reason) leave well alone to allow for future growth. Backups only include "used" pages, so the only real downside is a) disk space used and b) a Restore will create a database that big too [which may be undesirable if you want to record onto a machine that has limited space)REbuilding index = copy the index to a new part of the file, neatly optimised. Generally access is not possible during this operation (or the index is not available), so is fine for a database when all the users have gone home, or for small tables, but not so good for database that have connected users 24/7. The Enterprises Version allows ONLINE Rebuild of indexes (i.e. whilst users are still connected).Reorganise index = equivalent of defragmentation. Index blocks are moved around, rather than a neat, fresh, copy made. Suitable of databases with connected users and, TBH, probably good enough in all situations.The exact nitty-gritty depends on what version of SQL you are using, and whether you have Enterprise or something less well endowed.So long as you are doing one or the other that's going to be fine, no need to do Reindex if you are currently doing Reorg."there is a huge amount of free space in the datafile"More than 20% of the database size? If not I doubt its worth it. Not sure I would bother unless it was at least 40% slack (or I was desperate for the disk space) |
|
|
|
|
|
|
|
|
sql backup and restore