Shrink DB ?

Question

Shrink DB ?

winan888

SSC Rookie

Points: 27
More actions
September 4, 2009 at 9:44 am

#138678

Hello,
Thanks for your info, I got confuse today!.. my app runs very very slow .. my app vendor asks me to shrink database and defrag db..
I am new to sqlserver 2005:
1. is it really necessary to shrink db? .. what should I do? ..
2. what different between shrink db and defrag db?
I need advise..
many thanks in advance
Regards
Winanjaya

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 1

winan888 (9/4/2009)
Hello,
Thanks for your info, I got confuse today!.. my app runs very very slow .. my app vendor asks me to shrink database and defrag db..
I am new to sqlserver 2005:
1. is it really necessary to shrink db? .. what should I do? ..
2. what different between shrink db and defrag db?
I need advise..
many thanks in advance
Regards
Winanjaya

If you are having performance issues you might want to look at updating Statistics and the Indexes on the database. Shrinking database will physical shrink the size of the file by removing all pages that are currently not allocated; usually shrinking is not recommended because if the database grew it needed that space. Defrag is referring to rebuilding or reorganizing your indexes to gain performance.

You can run following command to see the fragmentation level of your indexes:

SELECT *

FROM SYS.DM_db_Physical_Index_stats(0,NULL,NULL,NULL,'LIMITED')

This will give you list of all the indexes in the current database and you can determine if there is a fragmentation issue; if the fragmentation is high (> 30%) you will need to rebuild indexes and if it is less then that you can just reorganize.

Thanks.

Mohit.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

And just to add to that a bit, shrinking the database is not a method for optimizing performance. It's a little shocking that a vendor is recommending this as a way to make your database run faster.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

winan888 SSC Rookie Points: 27 More actions · Answer 3

Thanks,

How about shrinking log file? .. is it to improve performance or not?

and btw.. any method to rebuild / reindex safely and fastly?

thanks a lot in advance

Regards

Winanjaya

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 4

From your questions, it makes me think that you also have a disk space issue. Maybe the files are growing too fast, or you need to restore a database for testing purposes? Only less rows (deleting or old useless rows or archiving to another database for example) will increase performance. If you did that, a shrink db is a logical thing to do.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 5

You don't shrink the log for performance. You might want to read this article: http://www.sqlservercentral.com/articles/64582/

winan888 SSC Rookie Points: 27 More actions · Answer 6

Disk space is not an issue.. current diskspace status is 200GB free.. my DB size is only +/- 30GB..

I think I need to defrag it rather to shrink it (pls correct me if I'm wrong) .. then do rebuild index for all tables ..could any body please provide me what 's the command for rebuild index? .. and I thought I want to run this procedure (rebuild index) once a week at midnight ..

any suggestion?

thanks & regards

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 7

The ALTER INDEX command is used: http://msdn.microsoft.com/en-us/library/ms188388%28SQL.90%29.aspx

You can also use a maintenance plan to do this. Create a new one that does it on a weekly basis. Or check scripts on this site for some that will reindex only those indexes which are fragmented

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 8

I can imagine that the recommendation looks like this:

1) first shrink your db

2) then rebuild your indexes

but if your ISV advice to shrink AFTER rebuild - it isn't an advice it is a trick like "format c:" ;)))

what does the shrink do ?

let assume that this is our pretty database (with low fragmentation):

|index1:page1|index1:page2|index1:page3|index1:page4|index1:page5|FREEPAGE1|FREEPAGE2|
|index2:page1|index2:page2|index2:page3|FREEPAGE3|FREEPAGE4|
|index3:page1|index3:page2|index3:page3|index3:page4|index3:page5|index3:page6|index3:page6|

shrink is string from beginning of our database on looking for some free space, when he found it - it takes the last page of from a database and put it into this free space, and so one ... . after a shrink our database will looks like this:

|index1:page1|index1:page2|index1:page3|index1:page4|index1:page5|index3:page7|index3:page6|
|index2:page1|index2:page2|index2:page3|index3:page5|index3:page4|
|index3:page1|index3:page2|index3:page3|

here only one index get a fragmentation but real database with a thousands of pages and objects... it can be real scary (after shrinking a 70GB db I've got a >95% of fragmentation).

in our first situation the database is greater - but the performance is greater too. because index3 has small fragmentation. After shrink the fragmentation is ... greater;/

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 9

If you shrink after rebuilding, you can re-fragment the indexes. When the shrink moves pages, it puts them where it can, so you easily can undo your rebuilding work.

If you shrink first, the rebuild might grow things again for the reindex.

Keep free space in your files. Enough to handle some data growth AND reindexing.