April 29, 2011 at 2:12 am
Hi All,
Does rebuilding the index consumes disk space. I came to this situaltion coz i am building the indexes before building, the space availble on the drive was 30 GB and in rebuilding the index now its running out of space.If so how can I reclaim the space as we cant shrink the dB as it is on production also is there any way that so that rebuilding the index will not consume the current drive space.
Please guide.
April 29, 2011 at 2:39 am
What version and edition are you using ? Are you doing an online rebuild ? Indexes do consume disk space as the indexes unless you specify where they built they are by default in the primary file group.
Why are you doing a rebuild ? Is the fragmentation > 30 % you could do an index defrag possibly instead.
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
April 29, 2011 at 2:54 am
Hi Warwick ,
We have sql2k8 standard edition. The avg_fragmentation is more than 30%. All the objects are on the primary group only. How can we specify another group for building the index.
April 29, 2011 at 3:14 am
Vinod,
Unless you have another drive available on the server to create a new file group on you will not get any benefit.
If you are not able to have some more disk added either as anther drive or to expand your current drive you may have to drop your index, check how much free space you have internally in your data file , check how much space you have on disk. Compare this to your current index size. If you have enough you can then creat the index again. Depending on the size of the table and the columns in the index this may take some time to complete.
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
April 29, 2011 at 3:24 am
Can u share how to point to different filegroup in alter index...rebuild syntax coz i dont find it.
April 29, 2011 at 4:26 am
It is not an option for alter index. You will need to do an alter database. Either BOL or MSDN will give you the syntax to create the new file group. http://msdn.microsoft.com/en-us/library/ms174269.aspx
You can also move the index by following these steps at this link http://msdn.microsoft.com/en-us/library/ms174269.aspx
Once you have created the filegroup and moved the index from primary you will be able to rebuild.
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
April 29, 2011 at 4:52 am
Vinod,
During online indexing sql takes space from the current DB for temporary storage of data.there is one option "SORT_IN_TEMPDB" . keep it ON so that tempdb can be used for temporary storage
Read this article http://msdn.microsoft.com/en-us/library/ms188281.aspx
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 29, 2011 at 5:06 am
If your tempDB is on the same disk as your user db you will have the same issue of running out of disk space so take this into consideration
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
April 29, 2011 at 6:16 am
Production server!! and if you are using same disk for both user DBs and temmpdb , then start thinking to move tempdb on different disk.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 30, 2011 at 11:58 pm
Hey thanks Bhuvnesh,
I was planning to use tempdb but little confused whether rebuild will use tempdb or not. Now its clear.
Thanks alot:-)
May 1, 2011 at 12:53 am
first thing is to create script of index rebuilding from SSIS and execute 5 to 15 indexes at a time as your space and execute log backup and then log truncate after every execution of indexes
second thing is to take a USB and create a log file on it for temporary then rebuild indexes then truncate this file and then remove it from the database
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 1, 2011 at 2:25 pm
Syed Jahanzaib Bin hassan (5/1/2011)
first thing is to create script of index rebuilding from SSIS and execute 5 to 15 indexes at a time as your space and execute log backup and then log truncate after every execution of indexes
I'm probably one of the larger SSIS advocates here, but I have no idea why you'd want to do this in SSIS. T-SQL scripts are much easier to manipulate at this level and there are a few incredibly robust ones in existence out there for public consumption. Why do you feel SSIS is the proper vehicle for this, especially in dealing with loops of sets of them? SSIS does 1 by 1 iteration in its loops. You'd have to build a custom component for 5-15 of them, or use multiple control paths to force that kind of parallelism.
Second, you NEVER, EVER log truncate on a full/bulk-logged system. You break the log chain. I agree with the backups, but you should NEVER truncate unless you intend to full backup immediately following the process to rebuild the log chain, and immediately test your backups. Breaking the log chain like that is a recipe for disaster.
second thing is to take a USB and create a log file on it for temporary then rebuild indexes then truncate this file and then remove it from the database
Again, why? What's a USB got to do with this? Most USB's don't have tremendous volume nor do they have significant speed increases over a SAN. Also, depending on the method you've used, you can't easily truncate off an NDF file to remove it. There's a ton of background tasks that would need to be performed. Admittedly, it could go easily, but it's certainly not common nor a known best practice. Do you have any articles or performance testing figures that show this to be a wise decision, or does it just seem like a good idea to you?
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
Your alphabet soup proves nothing. Please do a little more research, in particular when involving restoration items, before someone follows your advice and hoses their system up. You can recover from most mistakes, but an error in backup/restore methods and recovery levels, or when trying to repair from corruption, is pretty much a one-shot deal.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 2, 2011 at 2:25 am
I'm probably one of the larger SSIS advocates here
I am shocked because you dont know about how to generate T-SQL script from SSIS for the maintenance
Again, why? What's a USB got to do with this? Most USB's don't have tremendous volume nor do they have significant speed increases over a SAN. Also
Because if you have no space on the server available then you can use
Your alphabet soup proves nothing. Please do a little more research, in particular when involving restoration items, before someone follows your advice and hoses their system up. You can recover from most mistakes, but an error in backup/restore methods and recovery levels, or when trying to repair from corruption, is pretty much a one-shot deal
I always said such type of people are mentally retarted nothing else,dont attack personally just provide solution here ok:-P , dont generate post to increase your points if you dont have solution ok
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 2, 2011 at 3:26 am
Syed Jahanzaib Bin hassan (5/2/2011)
I'm probably one of the larger SSIS advocates here
I am shocked because you dont know about how to generate T-SQL script from SSIS for the maintenance
Where did Craig state or imply that?
The question is why use SSIS for index maintenance? Sure, it can be used, but it has few advantages, especially considering the excellent T-SQL scripts that are available that do intelligent index maintenance by taking into account index size, fragmentation level and a large host of other factors, all of which you'd have to redo yourself if using SSIS, and which maintenance plans can't do.
It's a question of why reinvent the wheel when it's been done so well by others.
Because if you have no space on the server available then you can use
Frankly if a server is completely out of space then someone is not doing their job. That should not happen on a well-maintained server, not during regular maintenance.
I always said such type of people are mentally retarted
Absolutely uncalled for. Insults have no place on a professional forum. And before you claim Craig started it, he just asked you to do research, the first personal insult was from you.
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
May 2, 2011 at 12:02 pm
Syed Jahanzaib Bin hassan (5/2/2011)
I am shocked because you dont know about how to generate T-SQL script from SSIS for the maintenance
How to generate a T-SQL script from SSIS? Why in the world would I want to do that? Oh, I *can* do it, but why would I want to? I've already written the script itself. That's not only reinventing the wheel I'm making things harder on other administrators by putting the methodology into two separate components, the SSIS engine for the build logic and then the T-SQL engine for the results.
Because if you have no space on the server available then you can use
As Gail mentioned, if you have no space you've got other problems, however, let's avoid that particular issue. Are you saying that your physical servers do NOT have their USB ports blocked and locked? That anyone can go up and hook up their Ipod for a little while? The only place you will normally have that kind of access is your local PC. That's a whole different set of optimization techniques, because you're working off IDE drives, limited RAM, lighter processors, and (usually) more memory load.
So, if you're out of space for the usage of TempDB, you'll need to simply do less indexes at a time. Using selective index choices via the available choices, this is not a significant issue.
I always said such type of people are mentally retarted nothing else,dont attack personally just provide solution here ok:-P , dont generate post to increase your points if you dont have solution ok
Ah, you misunderstand. See, if I wanted to improve my post count I'd just go find myself a nice quiet little article and post away a word at a time. I'm afraid for you that most of my posts have purpose, this series in particular to make sure that you define and explain why you're avoiding what's considered standard best practices... like not breaking log chains. If you can support them with tests and examples, I'm all for it. I have no problem being wrong if you can explain where and how.
However, regarding the personal attack... you're right, it can be read that way. However, it's not particularly to you, but to the general method to show that you know better via a litany of letters instead of content and research. I'm more then happy to teach and discuss. I also learn a lot here when I'm wrong.
Though, you do bring up one good point. I didn't specifically address the OP. However, since we hadn't heard from Vinod since Bhuvnesh pointed him in the right direction, I didn't feel that portion was necessary. If he needs further help he'll post again. I wanted to make sure a couple of worrisome comments you made weren't left hanging.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply