July 15, 2011 at 5:59 am
Jayanth_Kurup (7/15/2011)
Run checkpoint and then try doing a shrink file
Checkpoint does not make the log reusable in full recovery. As mentioned previously shrinking is probably a bad idea here.
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
July 15, 2011 at 9:46 am
Sorry, yes space is an issue, and I'm several months out on a new storage purchase in the best case scenario. Space in this datacenter is so incredibly expensive (think $$$$$.$$ for ###GB). So I'm trying to be as efficient as possible.
In the end though, this is just showing that I don't fully understand the reasons a transaction log will drop in size, or more specifically, not drop below it's initial size one day, but do so the next.
I should clarify the shrink job is for the data file not the log file, I misspoke in my earlier post.
July 15, 2011 at 10:07 am
mortalic (7/15/2011)
I should clarify the shrink job is for the data file not the log file, I misspoke in my earlier post.
That's even worse. A shrink of the log can be OK. A shrink of the data file fragments every index there.
If you're low on space look at archiving, removing unnecessary data, moving some tables/indexes to a slower (cheaper) portion of the SAN or similar along with getting additional storage.
Shrinking daily is ultimately self-destructive and isn't going to gain you anything. You shrink the DB, it grows, you shrink it again, it grows back, etc, etc.
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
July 15, 2011 at 10:33 am
So is it more productive to not shrink the db, and not rebuild the indexes? I understand the correlation between this and space usage due to the increase in transaction log backup size during this process. currently I reorg indexes at night and rebuild them on Sundays. I've noticed that the database tends to grow quite significantly without a routine shrink.
What would be a better maintenance procedure for indexes and keeping the vendor database in check?
July 15, 2011 at 10:40 am
Ditto to what Gail said. Do not shrink your data file.
You need to reevaluate your entire mirrored server set up. Why is it chewing up space when your primary server is not? The hardware, disk space at the very least, should have been as close to identical as possible.
If the total disk space is the same, but you're having space issues only on the mirror, then you need to see what other processes are creating files to chew up disk space. In this scenario, I guarantee you have another issue that is causing a crimp on your SQL Server resources. Resolve that first.
If nothing else, consider why you have a mirrored server in the first place. What are you using it for? Disaster Recovery? Reporting Server? If it's giving you that much of a headache, do you really need it? If the answer is yes, then you need to convince your boss to prioritize the appropriate fixes in the budget. Money may be tight now, but it's going to be spent anyway when the server stops due to lack of space. And fragmented indexes will just cause all sorts of performance issues that will cost more in manhour dollars than the needed GB of hard drive space.
July 15, 2011 at 10:42 am
mortalic (7/15/2011)
So is it more productive to not shrink the db, and not rebuild the indexes?
You're taking Gail's comment out of context. Indexes do require periodic rebuilding just as part of regular maintenance. You can't avoid it. But if you shrink the db, then rebuild the indexes, you're just inflating the database again and it might just inflate right back to the size it's at now.
EDIT: Answering the vendor question: It depends on what the database / application does, but there is no good way. Possibilities include: Check what processes are unnecessary and remove them from the stream. Give it a Max size. Play with your autogrowth setting so the db isn't over inflating, but don't set them so low that your db is constantly "thrashing" in an attempt to grow correctly. But none of this is guaranteed.
Tongue-in-check-answer: You want to keep a vendor DB in check? ... Tell the users to just stop using the application or stop new business from coming into your system. @=)
July 15, 2011 at 11:02 am
Brandie, my apologies, but I think I didn't explain well enough, it's not specifically the mirror server that's the problem. Both systems have identical storage (mirror has slightly more actually) and I've been referring to the principal for this thread. The mirror is for DR purposes only, though I've added a snapshot to it for troubleshooting purposes to keep the business team from running ad-hoc queries on the Principal. The mirror is required for our client obligations and it does work well.
Indexes do require periodic rebuilding just as part of regular maintenance. You can't avoid it. But if you shrink the db, then rebuild the indexes, you're just inflating the database again and it might just inflate right back to the size it's at now.
I just double checked my index jobs and my shrink jobs, the index jobs (reorg daily, rebuild Sunday) run after the shrink job by a few hours, so my question is which is better, to have the shrink before the index maintenance, or after? I set it before originally because of the point Gail mentioned, if you shrink it, wouldn't it fragment the index again, making the index maintenance useless?
If I shouldn't shrink the database daily would it be better to run it after the index rebuild on Sundays? Or is the takeaway here you guys suggesting that I stop shrinking it for a few days and see how it grows because shrinking the data file is completely unnecessary?
July 15, 2011 at 11:18 am
Gail and I are both recommending that you avoid shrinking the data file if at all possible. The database claimed that space because it uses it, even if only occasionally. It is far healthier for your db if you're not constantly shrinking, even after an index rebuild, because the db will just need to grow again and growth takes time out of your data processing cycles.
That doesn't mean people follow that advice. I know many folks who do shrink their data files regularly. But it's not a best practice, or even a recommended one.
July 15, 2011 at 12:18 pm
We're suggesting you don't shrink it at all. You're not gaining yourself anything. You shrink it, SQL grows it again (for index rebuilds, normal usage), you shrink it, SQL grows it. All that's doing is wasting resources and fragmenting the file system.
Get more space or archive and delete old data.
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
July 15, 2011 at 2:24 pm
Ok, I think I understand, one last question then, wouldn't the index rebuilds cause it to grow significantly though?
July 15, 2011 at 3:19 pm
mortalic (7/15/2011)
Ok, I think I understand, one last question then, wouldn't the index rebuilds cause it to grow significantly though?
If there's not enough free space in the DB file (like would be the situation after a shrink) yes. If there is enough free space in the data file, no.
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
July 15, 2011 at 3:21 pm
Ok that makes sense, thank you guys for the help understanding this.
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply