Shrink File

Question

Shrink File

HBhagat

Hall of Fame

Points: 3341
More actions
September 2, 2011 at 7:58 am

#99758

Hi,
Last night I had executed a maintainance job for Optimization in my live server. The job executed executed 6 hrs and was eating my disk space. The log space got 64GB.
My DB files have increased by size.
Will it be a great idea to use dbcc shrinkfile in my Live DB to shrink the mdf and ndf files?
I have shrinked the log file.
Thanks,
H

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

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

Steve Jones - SSC Editor SSC Guru Points: 734562 More actions · Answer 1

No.

Short answer is that you don't want to shrink data files (MDF/NDF) unless some unusual event caused them to grow and the data space isn't needed.

You've listed two things that aren't necessarily related. If you run a maintenance job, it's possible that the log file grows. However that's needed for the maintenance. If you shrink the log file, you aren't necessarily helping yourself since the log file has to grow the next time maintenance runs, and log file growths REQUIRE zeroing out the file, adding to the time.

Don't shrink files at all unless you know why they grew and are sure they won't need to grow again anytime soon.

For the data files, you should have some pad in there. These aren't like Excel/Word files where the data is the file size. They should have space for maintenance and future allocations. Look at your backup (full) growth. Extrapolate out the data growth and leave enough space for 3-4 months growth in the data files with a little pad. Check every month, and make the file growth yourself in an amount that will allow you to run for 3-4 months if space is low.

HBhagat Hall of Fame Points: 3341 More actions · Answer 2

Hi,

Thanks for the reply.

But I had to shrink the files as the original size of DB was 181Gb and after the maintainance job its became 233 GB.

After shrinking each file the db size was down to 165 GB .

But I have a problem related with this . Once everything was and trading started, my server got stuck for some reason, and my server became ir-responsive. It had to be restarted then. But after that, there were no problems in the server.

I want to know,

1. was I wrong in running a re-order index job for the database.

2. Every day in morning there is a job where update_statistics runs. Also my db setting is set to auto update statistics. So are my indexes also updated, as i found lot of tables where the tables are defragged.

Thanks,

H

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

SQL_DBA_New (9/13/2011)
Hi,
Thanks for the reply.
But I had to shrink the files as the original size of DB was 181Gb and after the maintainance job its became 233 GB.
After shrinking each file the db size was down to 165 GB .
But I have a problem related with this . Once everything was and trading started, my server got stuck for some reason, and my server became ir-responsive. It had to be restarted then. But after that, there were no problems in the server.
I want to know,
1. was I wrong in running a re-order index job for the database.
2. Every day in morning there is a job where update_statistics runs. Also my db setting is set to auto update statistics. So are my indexes also updated, as i found lot of tables where the tables are defragged.
Thanks,
H

1 - No, you were wrong shrinking or thinking 223 GB is bad. Sql server needs space to rebuild the indexes, that's why it grew.

2 - Now your indexes are still fragmented (99%) because of the shrink.

I use this to work only on the index that need it : http://sqlfool.com/2011/06/index-defrag-script-v4-1

HBhagat Hall of Fame Points: 3341 More actions · Answer 4

Hi,

I had to shrink the files as there was less free disk space, in my servers.

I had to cancel the process as it ran for more than 6 hrs but it did not completed.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

SQL_DBA_New (9/13/2011)
Hi,
I had to shrink the files as there was less free disk space, in my servers.
I had to cancel the process as it ran for more than 6 hrs but it did not completed.

The link I posted works with a stop time (tested before executing the next command).

If you run log backups regularly you should be fine and not run out of room (you're not putting the backups on the same drive(s) as the db, right?).

HBhagat Hall of Fame Points: 3341 More actions · Answer 6

Hi Ninja's_RGR'us,

Thanks for the code.

My question is whether I did the correct thing to run a job for re-organize the indexes as a mainatainenance plan.

My company is taking an action against me, as the server got stuck during the busy day of the week.

I need some valid points to make sure that what I did for re-organize the index was correct and justice to my job role.

Thanks,

H

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

SQL_DBA_New (9/13/2011)
Hi Ninja's_RGR'us,
Thanks for the code.
My question is whether I did the correct thing to run a job for re-organize the indexes as a mainatainenance plan.
My company is taking an action against me, as the server got stuck during the busy day of the week.
I need some valid points to make sure that what I did for re-organize the index was correct and justice to my job role.
Thanks,
H

Yes it's correct, but I personally wouldn't use the base maintenance plan for this (as this works too much for no reason). The script I sent is the way to go.

As for cie actions I can't do much. I don't know what contracts you have in place.

HBhagat Hall of Fame Points: 3341 More actions · Answer 8

HBhagat

Hall of Fame

Points: 3341

September 13, 2011 at 7:31 am

#1380607

Thanks Ninja's_RGR'us