July 31, 2004 at 4:39 pm
... reindex, then shrink a database? Does it matter?
Jim
August 1, 2004 at 9:38 am
In general Indexing makes your log file to grew depends on your recovery model. If you want to keep your log file small re index first and then shrink the log file and data file. But when you talk in terms of perf it doest make any difference
Shas3
August 2, 2004 at 7:51 am
Reindexing will cause your database to have extra free space (the space freed by the reindexing). Shrinking gets rid of excess free space. So, having said that, which do you think you should do last?
I always shrink AFTER I reindex. That way I get rid of the free space created by the reindex.
-SQLBill
August 2, 2004 at 8:19 am
I agree with Bill. Shrink after reindex.
August 2, 2004 at 8:21 am
I don't believe in shrinking the database. Your just setting yourself up again to grow the datafiles. Why would you want to keep growing and shrinking a datafile?
Something else to keep in mind while reindexing is that extra space is needed in the datafile to rebuild the indexes. At least as much free space as your largest index (probably a clustered index) because SQL Server makes a 'copy' of the index before removing the old index.
"Keep Your Stick On the Ice" ..Red Green
August 2, 2004 at 9:00 am
ok, but does shrinking cause fragmentation of indexes?
I run the script in the BOL to defrag my indexes on a weekly basis. If I run the script to defrag the indexes and immediately run it again it has nothing to do on the second run.
If I shrink the database and then run the defrag script it seems like the indexes are fragmented again (although no pages are removed). This is why I asked the question.
I think Jeff's comments above about not shrinking the database are interesting - how many people shrink their databases and how many leave them alone?
Jim
August 2, 2004 at 9:32 am
I would bet that shrinking a database would move the pages around....causing fragmentation. Just a guess.
Why shrink if you know you are going to need the space soon? If it got that large once then in a 'normal' environment I am sure it is going to get there again 🙂
"Keep Your Stick On the Ice" ..Red Green
August 2, 2004 at 12:01 pm
Here is my 2cents,
There are two options to shrink file/Database. If you use "TRUNCATEONLY" option, since iti s not mvoing any data and just take off tailed unused space back to operating system, it shouldn't cause fragmentation. Since our testing environment doesn't have enough space, I use this often to save some space.
So, the problem is "NOTRUNCATE" option, I'd guess and there only concern about clustered index since non-clustered index is already fraged as it is. Anyway, when you use this option, it still has to follow the clustered index/table rule which is store data based clustered index key which is implemented as a B-tree index structore and what the shrink file with notrucate option is doing similary to the disk defragment to have contiguouse sectors as much as possible and moving files when it fits the rule under clustered index/table. So, it is not doing fragment but just push back the unused page to the end of the file so when you use "TRUNCATEONLY", it will actually release the file size to operating system.
So, by doing index defrag, it will arrange the data structore with right fill factor and by doing that, there will be fragment and shrink file after that makes more sense to me.
So, I agree with Bill
August 3, 2004 at 8:50 am
Iam very new with sql
Question: how and what is the syntax to reindex the log files?
August 4, 2004 at 7:09 am
Alvin,
Log files don't have indexes.
Check out the BOL for information on:
DBCC DBREINDEX
DBCC INDEXDEFRAG
-SQLBill
BOL: Books OnLine = Microsoft SQL Server's Help
Found at Start>Programs>Microsoft SQL Server>Books OnLine
Installed as part of the Client Tools
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply