Featured Article(s)
DTS Conversion to SSIS (Part 3 of 4)
Profiling and converting DTS packages, and a look at tools that can help.
Working with SharePoint?
Moving to a new version of SharePoint, site consolidation, new applications and even reorganizing your site collections can be a major task with a lot different variables and challenges. This is where the SharePoint Migration Suite from Idera comes in – providing the tools you need to get the job done. They have a free 14-day trial so you can use it with your own systems to see just how much better things can be. Check it out here.
Database Maintenance (and Shrinking)
Mike had some feedback on shrinking the database – in their experiences and in support of their systems: "We have to shrink the tempdb database on a particular application that still runs on a physical server with fixed amount of disk because the databases / logs compete for the same "overflow" disk.
By shrinking the tempdb database (which grows really really big overnight due to reporting) we can effectively "reset" its footprint in the morning before users get on by ridding then re-define its "overflow" extent again such that their is no initial "reserved" space on the overflow extent so that the on-line transactions hitting the application database and logs have the best chance at not running out of disk space during the day. Real-world.
Bad (but best) design given physical disk contraints at the time of conception. Application waiting to be re-hosted from what was then newest technology."
Jeremy wrote in with additional thoughts on shrinking the db (NOT related to Mike – just including it here to further the discussion). "Ack. Shrinking a database as part of a re-indexing / re-organizing strategy? I’d advise against it. My *guess* would be that they are referring to the log file.
Re-indexing is the way to go if you can get away with it. Re-organizing an index is REQUIRED for indexes on partitioned tables as rebuild is not an option with an active partition. It also makes sense to only re-organize the active or changed partitions rather than all of them when dealing with partitioned tables. With that said, one must keep in mind statistics on partitioned tables. (ref. https://connect.microsoft.com/SQLServer/feedback/details/468517/update-statistics-at-the-partition-level ). Please vote!! J
At any rate, my general rule of thumb for index maintenance varies depending on the availability requirement of the database, the SAN(s) involved, the size of the indexes etc.. some databases, tables and indexes require different strategies than others. I can think of one specific example, on one table where a few indexes are re-org’d nightly and others are re-built weekly while others are totally left alone. In this example, most of them have very fine tuned fill-factors as well.
Great topic and one that is often done in environments today via the db maintenance plan. While re-indexing is often better than not re-indexing, it is very, very valuable to take a much deeper look at your re-indexing strategy and build something that is fault-tolerant, less stressful on a highly used system (think maxdop) and overall improves the availability and overall health of the databases in your environment."
Webcast: Using Log Shipping for High Availability
Log Shipping has been around in one form or another since SQL Server 2000 and many “homegrown†and third-party solutions have recently become available. This session will cover all you need to know about log shipping to use it effectively for high availability. We will look at set up, maintenance, monitoring, and failover. In addition, we will look at other SQL Server objects that you need to account for when using Log Shipping for HA. This session is for beginning to advanced SQL Server DBAs with little or no Log Shipping experience.
Presented by: Eric Johnson
> Register Now
> Live date: 7/14/2010 at 12:00 Pacific