Sql 2000 mdf and ndf file grows after dbcc dbreindex

Question

Post reply

Sql 2000 mdf and ndf file grows after dbcc dbreindex

arnold-491793

Say Hey Kid

Points: 709
More actions
January 6, 2010 at 11:13 am

#218089

Hi,
I have Sql 2000 mdf and ndf file partitioned in my Drive E. It always grows approximately 43% after running dbcc dbreindex.
Now i don't have enough space for my primary filegroup (these are my 3 mdf and 1 ndf located in Drive E).
Kindly suggest the best practice how to administer my indexes and how to save any spaces in my Drive E coz it is getting closer to 10mb free space.
Any suggestion is highly appreciated.
Thanks,
Arnet..

Viewing 12 posts - 1 through 11 (of 11 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

That's fairly normal. SQL needs space to put those new indexes.

I'd suggest you look at getting some more disk space.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arnold-491793 Say Hey Kid Points: 709 More actions · Answer 2

GilaMonster (1/6/2010)
That's fairly normal. SQL needs space to put those new indexes.
I'd suggest you look at getting some more disk space.

How about doing some DBCC Shrinkfile? Is this will free up more space in my production database mostly in my drive E?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If you're going to shrink after rebuilding, you may as well save the time and not bother rebuilding in the first place.

Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 4

And fragment all of those nicely defragmented indexes - so, basically a waste of time. To save some space in your data file, you can try using the SORT_IN_TEMPDB option - which will use space in tempdb for sort operations. However, this may not save you any space at all.

I would have to question why you have multiple partitions on a single drive. Are you using mount points for each partition so the partitions are actually on different spindles? If not, then there is really no reason to have them.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

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

First, get more disk space. Don't talk to your manager about how much free space you have. You need a decent amount of free space in the data files, so if you're down to 10MB after reindexing, get more space.

Second, don't shrink.

Third, see #1

arnold-491793 Say Hey Kid Points: 709 More actions · Answer 6

Steve Jones - Editor (1/6/2010)
First, get more disk space. Don't talk to your manager about how much free space you have. You need a decent amount of free space in the data files, so if you're down to 10MB after reindexing, get more space.
Second, don't shrink.
Third, see #1

Thanks. It so happened that I don't have enough space in my partition Drive E in my array disk. I need to free up space so i used dbcc shrinkdatabase in order to free up more space while waiting for additional disk. I need the production database to work coz it's running 24x7.

But then, this will make some impact in the performance? Right?

arnold-491793 Say Hey Kid Points: 709 More actions · Answer 7

I have another partition in my disk array that has enough space. I'm planning to move my data file into Drive F:

Here is the data file in my Drive E:

Data File/ File group Name

crp1Data.mdf/ crp1Data

crp2Data.mdf/ crp2Data

crp3Data.mdf/ crp3Data

crp4Data.mdf/ crp4Data

Is there someone who can help me transfer my "crp1Data.mdf" or more than one data file and filegroup to my Drive F: without losing any data? I need downtime to do this right?

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

ALTER DATABASE with the MOVE clause

Take the database offline

Move the files

Bring the database online.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arnold-491793 Say Hey Kid Points: 709 More actions · Answer 9

GilaMonster (1/7/2010)
ALTER DATABASE with the MOVE clause
Take the database offline
Move the files
Bring the database online.

Do you have sample on how to use Move Clause to move a current filegroup mdf/ndf file to different drive?

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Books Online does. Look for an example of moving TempDB. Same script works for user database. You just have to take offline and move files afterwards

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Shivrudra SSCrazy Points: 2835 More actions · Answer 11

See this:---http://www.mssqltips.com/tip.asp?tip=1688

Regards,
Shivrudra W