February 21, 2011 at 8:06 am
Hey guys,
we have an archiving-software for Exchange and I want to build some regulary maintenance plans on the database.
The database's size is 17 GB and 12 GB is only index - so I want to rebuild all indices. But there is only 7 GB left on disk...so what could I do? Our database is running in FULL mode and I think the LOG will overflow.
Any suggestions what to do? Is it common that a index has a size double of the database?
Thank you in advance,
Norbert
February 21, 2011 at 11:00 am
You're cross over a few items there. The recovery model of the database and index rebuilds shouldn't be cross-concerns. Space on the drive is because you'll need room for the tempdb to be able to have some space for rearranging the indexes.
You say the databases are in full recovery, are you backing up the logs?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 21, 2011 at 11:27 am
Grant Fritchey (2/21/2011)
You're cross over a few items there. The recovery model of the database and index rebuilds shouldn't be cross-concerns. Space on the drive is because you'll need room for the tempdb to be able to have some space for rearranging the indexes.You say the databases are in full recovery, are you backing up the logs?
I think his concern is that when he re-indexes the 12 GB index, the 7gb left are going to fill. However, to that question, how large is your current log file, and as Grant mentioned, you are backing them up, right? If so, just make sure you do a backup directly before you start the process so it can re-use the space, and backup again immediately after.
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
February 22, 2011 at 12:21 am
Hello,
thank you for your replies.
Lokk at this link from msdn. The LOG file will be used for rebuilding and reorganizing indices.
Yes, I'm backing up the LOG periodicaly so the LOG file size doesn't matter at all. The 12 GB database size is only the size of the mdf-file - the LOG is between 500 MB and 1,5 GB.
My problem is, when I change the recovery model our backup chain is broken. And the problem is that the backups are organized by software so I can't do it manually.
It would be helpful to get some tipps how to reorganize/rebuild in this scenario. Or is it better to leave the database as it is now... (I don't think so)
regards
Norbert
February 22, 2011 at 5:34 am
Is any single index bigger than 7gb? I don't think you'll need to worry about uncommitted transactions exceeding that value. But, if you have to, break down the process, do a few indexes, stop, do a few more. It'll be complicated to set up and really hard to maintain, but if you can't add more space then that's what you may need to do. I suspect it'll be just fine. If you use the right approach to reindexing (I'd suggest getting Michelle Ufford's scripts) you'll only rebuild the ones that need it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 22, 2011 at 9:02 am
Hello,
thank you for your very helpful tipp.
I'm gonna break down to single indexes and then running this really awesome script.
Maybe you can explain to me how the rebuild concerns the LOG file. Why does the LOG grow when you rebuild/reorganize the index?
And congratulations to your books...I'm gonna buy one 😉
regards
Norbert
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply