November 19, 2008 at 8:23 am
Can some one provide me a script to shrink a database. We moved our data to a new server and before we go life I would lik to shrink database and I know that I need to rebuild indexes too. Is there a script for it?
November 19, 2008 at 8:25 am
straight from BOL
DBCC SHRINKDATABASE
( 'database_name' | database_id | 0
[ ,target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)
[ WITH NO_INFOMSGS ]
November 19, 2008 at 8:31 am
There is an option to do it mannually shrink database, is this the same thing?
November 19, 2008 at 8:38 am
yes this is something that you will have to do manualy, it is different from the AUTO-SHRINK option.
But be aware that unless you have had a lot of changes that won't be repeated, then the db will more than likely grow to the same size again , so shrinking databse is usually not recommended, SQL server is very good at handling disk usage.
November 19, 2008 at 8:44 am
If you do a shrink with reorg (the only way to be sure unused space is released), your database will become fragmented. By rebuilding your indexes this fragmentation is (almost) gone, but this results in some unused space in your database. If you do a shrink with reorg (the only way to be sure unused space is released), your database will become fragmented. By rebuilding your indexes this fragmentation is (almost) gone, but this results in some unused space in your database. If you do a shrink with reorg (the only way to be sure unused space is released), your database will become fragmented. By rebuilding your indexes this fragmentation is (almost) gone, but this results in some unused space in your database. If you do a shrink with reorg (the only way to be sure unused space is released), your database will become fragmented. By rebuilding your indexes this fragmentation is (almost) gone, but this results in some unused space in your database... :hehe:
Lesson: don't spent time in shrinking your database unless you'll save a lot of diskspace which is needed by other applications
Wilfred
The best things in life are the simple things
November 19, 2008 at 8:48 am
My data file is 184 KB and I wanted to shrink. What is the best way to do it?
November 19, 2008 at 8:53 am
yulichka (11/19/2008)
My data file is 184 KB and I wanted to shrink. What is the best way to do it?
why would you want to shrink a 184kb datafile? that is Tiny for a sql datafile.
November 19, 2008 at 8:56 am
Sorry that is 184,000 KB, I want to clean a free white space
November 19, 2008 at 9:01 am
how much free space is in your 184 MB datafile?
Wilfred
The best things in life are the simple things
November 19, 2008 at 9:07 am
47619.07mb(26%)
November 19, 2008 at 9:11 am
in management studio:
- right click on the database name
- select tasks->shrink->database
- mark the option "reorganize files before ...."
- click [ OK ]
Make sure you're doing this outside production hours in order to prevent locks and performance degradation
Wilfred
The best things in life are the simple things
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply