February 10, 2015 at 3:36 pm
Hi All,
My current disk drive has 4 GB free only . Total : 60 GB
It has 1 DB that has 40 GB mdf file and 15 GB ldf file . I suggested to add the harddisk capacity but unfortunately we don't have it
The index fragmentation is very high on that DB. and log space used is only 2%
What should I do ?
Please kindly help
Thanks !
February 10, 2015 at 4:20 pm
I'd shrink the log file:
DBCC SHRINKFILE ( 2, 4096 )
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 10, 2015 at 5:08 pm
WhiteLotus (2/10/2015)
Hi All,My current disk drive has 4 GB free only . Total : 60 GB
It has 1 DB that has 40 GB mdf file and 15 GB ldf file . I suggested to add the harddisk capacity but unfortunately we don't have it
The index fragmentation is very high on that DB. and log space used is only 2%
What should I do ?
Please kindly help
Thanks !
What's the fragmentation level on the clustered indexes and how big is the largest index, clustered or otherwise?
--Jeff Moden
February 10, 2015 at 5:30 pm
Hi Jeff
Thanks for you reply
The largest index is PK_Index1 , size : 15 GB , Pages : 2.051.264
it is clustered Index
The second largest is PK_Index2 , size : 2.6 GB ,Pages : 102.471
it is clustered index too
Anything that you would like to know ?
Any suggestion about what I can do ?
Thankss
February 10, 2015 at 5:48 pm
WhiteLotus (2/10/2015)
Hi JeffThanks for you reply
The largest index is PK_Index1 , size : 15 GB , Pages : 2.051.264
it is clustered Index
The second largest is PK_Index2 , size : 2.6 GB ,Pages : 102.471
it is clustered index too
Anything that you would like to know ?
Any suggestion about what I can do ?
Thankss
Thanks. You missed an important part of my question. What's the fragmentation level of these two indexes?
--Jeff Moden
February 10, 2015 at 5:52 pm
Ahhh sorry
the fragmentation level for the first index is very high : 98.77 %
and the second one is : 3.93 %
the fragmentation level will impact on the index size ?
Cheers
February 10, 2015 at 6:42 pm
WhiteLotus (2/10/2015)
Ahhh sorrythe fragmentation level for the first index is very high : 98.77 %
and the second one is : 3.93 %
the fragmentation level will impact on the index size ?
Cheers
Possibly. "It Depends". If you used the "Sampled" or "Detailed" method for sys.dm_db_index_physical_stats, then you'll also see a column for "avg_page_space_used_in_percent". That would be a good indication of how much space is being wasted by each index whether it's actually fragmented or not. For example, a perfectly defragmented index will typically only have an "80" in this column if the FILL FACTOR is only "80".
For the indexes that you've listed, the first is 98.77% fragmented. That's a pretty good indication that the wrong keys may have been used for that clustered index. In most cases, the clustered index should be narrow, unique, static, and ever-increasing. There are exceptions, of course, but that's the general guideline. Defragmentation of large clustered indexes is quite expensive especially if you don't have the Enterprise Edition.
The first thing I'd say is that reindexing to save space on a critically limited harddrive is, at best, a temporary patch to get you out of the woods. Someone needs to buy some more harddisk space and they need to do it ASAP.
In the meantime, we might be able to recover some space to, as I said, get you out of the woods.
I did, however, forget to ask a couple of questions.
1. Do you have a maintenance window for this or do we have to keep everything online for the duration?
2. Which Edition of SQL Server do you have? Standard Edition or Enterprise Edition?
3. Is the log file on the same drive as the datafile or not?
--Jeff Moden
February 10, 2015 at 11:57 pm
Thank you for the reply !! much appreciate it !
1. No maintenance so far
2. we use standard edition
3. Yes the log file is the same drive with data file . Log file size is 19 GB for that DB but when i check the log file used is only 4%
My concern is if I rebuild that largest index which has a lot of pages then it will increase the log file ( as the side effect of REBUILD ) and fill up the space in D :\ ...
I am thinking about removing the unused indexes but I am afraid I will delete the important indexes
What do you think ?
February 11, 2015 at 7:54 am
It would be nice to remove unused indexes but it does take a bit to prove that they're truly unused. Unless you have a monster index that we can easily prove is unused, let's not worry about removing indexes yet.
Ok... Here are the steps that I'd likely use if faced with such a problem. Considering the size of the logfile, we can do this all with the utmost of safety of the data in mind. Because you have the Standard Edition, some of this will need to be done offline.
If an index is over 128 extents (that's only 8MB), an index REBUILD will make a new index before it drops the old one and that includes rebuilding clustered indexes and creating clustered indexes (the HEAP will be preserved until the new clustered index is committed). That big table of 15GB just isn't going to withstand such a REBUILD unless you find more space.
On the other hand, REORGANIZE will do it in the space already allotted. The problem with REORGANIZE is that it's fully logged. Fortunately, it would appear that you currently have enough space in the log file to handle that if it's empty.
With all that in mind...
The first thing I'd do is check all of the indexes for FILL FACTOR. If any have other than "0" or "100", they will end up being larger after a defrag than they are now and you'll have to determine if they really need the special FILL FACTOR or not. Write down which ones you want to change to "100".
The next thing I'd do is to take a backup of the database and the log file, just to be safe and to make sure the log file is as empty as possible (assuming that you don't have replication or other log file dependent thing going on).
For all the following, make sure that you use the WITH (SORT_IN_TEMPDB = ON) option. For those indexes that you want to change to "100" FILL FACTOR, don't forget to include the FILL FACTOR in the WITH just for those indexes. It goes without saying that you should also check the default FILL FACTOR for the database. If it's not "0" or "100", consider changing it now.
The next thing that I'd do is do a REORGANIZE of just the CLUSTERED index on that first table that you identified as being the biggest clustered index and then do another logfile backup to empty the logfile. If we end up with enough room after everything else is done, we might return to this table and do a REBUILD but a REORGANIZE on this large highly fragmented table should free up some headroom for the other stuff we need to do.
Once the clustered index on that first large table is done, then do a REORGANIZE on the remaining clustered indexes keeping an eye on the freespace of the log file. If the free space gets too small, then do a logfile backup to clear it out. Keep doing this until all the clustered indexes that need to be defragged are. The reason why we're doing REORGANIZE instead of REBUILD is two fold... it's done in the space already allocated and it's done online even though you have the Standard Edition.
So that we don't have to mess with reactivating any foreign keys, do the same kind of REORGANIZE on any non-clustered indexes that are also UNIQUE. Do it the same way as previous keeping an eye out for freespace on the logfile and backing it up when necessary.
For all the remaining non-clustered indexes and since they can't actually be REBUILDt online, you can either do a REORGANIZE on them keeping a watchful eye on the log file OR you can put the database into the BULK LOGGED Recovery Model and disable each index before you rebuild it to allow the REBUILD to be minimally logged. Don't forget to change back to the FULL Recovery Model when you're done.
I wouldn't shrink the log file unless you increase the log file backups to once every 15 minutes or so to help keep it small.
Like I said, this is a "get out of the woods" patch. It's NOT the final solution. The final solution will be to get some extra disk space ASAP. You should account for two years worth of growth + at least 4 times the total data/index space of the largest table, IMHO. That's more of a "gut" feeling recommendation than anything else but it's served me well in the past.
--Jeff Moden
February 11, 2015 at 11:28 pm
Thanks so much for your explanation ! Thats kind of you ..
I just got approval to add 1 new drive which is 100 GB so I will move the Data file and log file of that big database there .. Doing the Reindex for a few days .. After done I need to give back that drive due do several reason .. so i need to move back the files to the original location ..... happy and sad...
February 12, 2015 at 1:30 am
WhiteLotus (2/11/2015)
Thanks so much for your explanation ! Thats kind of you ..I just got approval to add 1 new drive which is 100 GB so I will move the Data file and log file of that big database there .. Doing the Reindex for a few days .. After done I need to give back that drive due do several reason .. so i need to move back the files to the original location ..... happy and sad...
Seriously? They're going to make you give back a 100GB drive? You can buy a bloody 1TB hard DAS harddrive for $50-60 USD. For a san, it'll cost more like $500 but you're only using 1/10th of it. Stingy buggers. They know the cost of everything and the value of nothing. You've already spent more than $50 of your time messing around with this. If they're that stingy with hardware, you might want to consider looking for a better place to work. Seriously. What they're doing to you is ridiculous.
--Jeff Moden
February 16, 2015 at 3:38 pm
I am serious 🙁 .. Sad really .. hey I got good news .. my Team just did something on that drive and got 17 GB free now .... 🙂
February 16, 2015 at 4:04 pm
WhiteLotus (2/16/2015)
I am serious 🙁 .. Sad really .. hey I got good news .. my Team just did something on that drive and got 17 GB free now .... 🙂
Let's hope they didn't simply shrink the database(s). Check your databases for size and then check them for 99%+ fragmentation.
--Jeff Moden
February 16, 2015 at 9:18 pm
Hi Jeff,
I re-read your explanation . I am still confused with your statement about REORGANIZE.
" On the other hand, REORGANIZE will do it in the space already allotted. The problem with REORGANIZE is that it's fully logged. Fortunately, it would appear that you currently have enough space in the log file to handle that if it's empty."
What do you mean by Fully logged ? ..what is the issue with it ?
At the moment I am doing REORGANIZE on that biggest Index ...
Cheers
February 16, 2015 at 10:37 pm
Any changes the REORGANIZE makes to an index are fully logged in the log file rather than minimally logged if you do an index REBUILD in the BULK LOGGED mode. REORGANIZE also does it within the space already allocated in the MDF file rather than using the extra space to create a new index before dropping the old one like REBUILD does. There's a trick to getting REBUILD to only use the currently allocated space for NCIs but its 100% offline whereas REORGANIZE is always ONLINE even in the Standard Edition.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply