Very little space of harddisk problem

Question

Post reply

Very little space of harddisk problem

Viewing 4 posts - 16 through 18 (of 18 total)

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

WhiteLotus SSChampion Points: 11963 More actions · Answer 1

Hi Jeff ,

hmm I just did defragmentation for all indexes in that particular database as discussed before .

It turns out the database file size is the same ( before and after defragmentation ) ....

I did REBUILD for indexes that has more than 50 % of fragmentation and did REORGANIZE for the rest ..

Moreover as the side effect from defragmentation , the transaction log increase and full up the poor harddisk that has liitle space already ...

I did the maintenance in temporary new hard drive ( 100 GB) and did the backup of transaction log in order to decrease the log file size but tomorrow i need to give back the new hard drive ....

Any idea about this case ?

Sergiy SSC Guru Points: 110192 More actions · Answer 2

Do BACKUP LOG - it will free the space in the log file.

And while you have 2 drives - keep mdf and ldf apart from each other.

Move mdf back to the original drive and continue on your path.

Do not forget to do BACKUP LOG after each rebuilding/reorganising step.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

WhiteLotus (3/2/2015)
Hi Jeff ,
hmm I just did defragmentation for all indexes in that particular database as discussed before .
It turns out the database file size is the same ( before and after defragmentation ) ....
I did REBUILD for indexes that has more than 50 % of fragmentation and did REORGANIZE for the rest ..
Moreover as the side effect from defragmentation , the transaction log increase and full up the poor harddisk that has liitle space already ...
I did the maintenance in temporary new hard drive ( 100 GB) and did the backup of transaction log in order to decrease the log file size but tomorrow i need to give back the new hard drive ....
Any idea about this case ?

Did you follow what I recommended in my previous post at http://qa.sqlservercentral.com/Forums/FindPost1659542.aspx? Did you do log file backups as you went like I recommended? Did you check the FILL FACTORs as I recommended, etc, etc?

And, yes. I have an "idea about this case". Tell the people in charge of providing hard disk space that jumping through these types of hoops is ridiculous. It takes disk space to do this type of maintenance and disk space is one of the cheaper commodities. Between your time and theirs, they've already spent more money than what a decent and proper amount of disk space takes. Tell them to permanently add more disk space instead of nickel/diming things like this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

sql-lover SSCoach Points: 18530 More actions · Answer 4

I don't have much to add but ...

Very nice thread.

Very useful and insight recommendations from Jeff and interesting thread by the OP.