Coolthing Of Theday

  • Subscribe to our RSS feed.
  • Twitter
  • StumbleUpon
  • Reddit
  • Facebook
  • Digg

Friday, 8 February 2013

O'la! As in Ola Hallengren's SQL Server Backup, Integrity Check, and Index and Statistics Maintenance SQL scripts...

Posted on 17:11 by Unknown

The Bit Bucket (Greg Low): IDisposable - Update of Ola Hallengren's Maintenance Tools (Still Free Too)

SQL Server 2008 R2 SP2 and SQL Server 2012 SP1 introduced sys.dm_db_stats_properties. This DMV is a new way to check the number of rows that have been modified since the last time a set of statistics was updated. It is described here: (http://msdn.microsoft.com/en-us/library/jj553546.aspx)

In the latest version of Ola's tools, he's made use of this new DMV for customers that are on one of these SQL Server versions. For customers on earlier versions, he continues to use sys.sysindexes in the same way he did previously.

Another notable change is that the new version works better when combined with log shipping on the same databases (avoids options that would break log shipping chains).

Ola's tools have been gaining a well-deserved reputation, particularly given the price (ie free).

..."

SQL Server Maintenance Solution - SQL Server Backup, Integrity Check, and Index and Statistics Maintenance

"The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. The solution is based on stored procedures, the sqlcmd utility, and SQL Server Agent jobs. I designed the solution for the most mission-critical enterprise environments, and it is used in many organizations around the world. Numerous SQL Server community experts recommend the SQL Server Maintenance Solution, which has been a Gold and Silver winner in the 2012, 2011, and 2010 SQL Server Magazine Awards. The SQL Server Maintenance Solution is free.

“Hands down, one of the best tools out there! If you’re responsible for a database, you should be using Ola’s scripts. It’s very comprehensive, is efficient with resources, has numerous options, and is a brilliant piece of SQL coding!” - SQL Server Magazine

Getting Started

Download MaintenanceSolution.sql. This script creates all the objects and jobs that you need.

Learn more about using the SQL Server Maintenance Solution:

  • DatabaseBackup: SQL Server Backup
  • DatabaseIntegrityCheck: SQL Server Integrity Check
  • IndexOptimize: SQL Server Index and Statistics Maintenance

...

Intelligent Index Maintenance

The SQL Server Maintenance Solution lets you intelligently rebuild or reorganize only the indexes that are fragmented. In the IndexOptimize procedure, you can define a preferred index maintenance operation for each fragmentation group. Take a look at this code:

...

Update Statistics

The IndexOptimize procedure can also be used to update statistics. You can choose to update all statistics, statistics on indexes only, or statistics on columns only. If an index is rebuilt, the statistics is not being updated. You can also choose to update the statistics only if any rows have been modified since the most recent statistics update.

...

Solve “No Current Database” Issues

Most DBAs have experienced the error message “BACKUP LOG cannot be performed because there is no current database backup” or “Cannot perform a differential backup for database, because a current database backup does not exist”. These errors usually occur when you have created a new database or changed the database recovery model from Simple to Full. The answer is to determine, before you run the backup, whether a differential or transaction log backup can be performed. You can use the DatabaseBackup procedure’s @ChangeBackupType option to change the backup type dynamically if a differential or transaction log backup cannot be performed.

Here’s an example of how to use the @ChangeBackupType option:

...

Back up to Multiple Files

Databases are becoming larger and larger. The SQLCAT team posted an article about how to tune the performance of SQL Server backup compression, by using multiple backup files, and the BUFFERCOUNT and MAXTRANSFERSIZE options. The DatabaseBackup procedure supports these recommendations:

...

Run Integrity Checks of Very Large Databases

The SQL Server Maintenance Solution has been designed to do integrity checks of very large databases. In the DatabaseIntegrityCheck procedure you can choose do the checks on the database level, the filegroup level, or the table level. It also supports limiting the checks to the physical structures of the database:

..."

Question. Did any of those above items make you nod, smile or grimace in remembered pain? Then you've been in the DBA, officially or unofficially, seat. Best of all is the price on these scripts, free. Well even better, is reading the scripts and using them as a learning tool...

Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Posted in DBA, SQLServer | No comments
Newer Post Older Post Home

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Popular Posts

  • Mr. 7,000! This is my 7,000th post...
    Before this post; After; 20 visits between taking these snaps? Oh wait, that's probably me searching for past related posts....
  • "Windows Server Essentials Media Pack" (DNLA Stream, HTML5 and Dashboard Media stuff)
    Microsoft Downloads - Windows Server Essentials Media Pack This pack enables the media streaming functionality for Windows Server 2012...
  • Rad Gate Post... Get your Red Gate Post here...
    simple talk - Melanie Townsend - Get a copy of the Red Gate Post We recently put together a newspaper of some of the best articles fr...
  • Windows Management Framework 4.0 (PowerShell 4, PowerShell ISE, Management OData, WMI, etc.) now available
    Keith Hill's Blog - PowerShell 4.0 Now Available You can get PowerShell 4.0 for down level operating systems now via the WMF 4.0 d...
  • Viasfora - Your new favorite Visual Studio Text/*ML Editing Extension?
    Winterdom - Introducing Viasfora A couple of days ago, I unveiled Viasfora , my latest attempt at building a decently packaged extensi...
  • "Windows Server [2012 R2]: The Best Infrastructure to Run Linux Workloads"
    In the Cloud - What’s New in 2012 R2: Enabling Open Source Software Part 4 of a 9-part series . ... There are a lot of great s...
  • [Hardware Review] Life with Haswell... Haswell/Harris Beach Intel SDS Ultrabook Review - Part 2
    "So Greg, how's life with Haswell been?" "Pretty Sweet! (Mostly)" I've been given an opportunity to review t...
  • Fuzzy Lookup Add-In for Excel (Insert lame "Fuzzy, wuzzy was an Excel..." snip here)
    Microsoft Downloads - Fuzzy Lookup Add-In for Excel The Fuzzy Lookup Add-In for Excel performs fuzzy matching of textual data in Exce...
  • Caliburn.Micro v1.5.0 released (CM gets Tasks, Async/Await and Share/Setting for RT... and bug fixes of course)
    Caliburn.Micro - Caliburn.Micro v1.5.0 "Release Notes This release fixes many bugs. It also adds support for Task and async/a...
  • Just about everything you ever wanted to know about SQL Server Date and Time Data Types...
    CodeProject - Date and Time Data Types and Functions - SQL Server (2000, 2005, 2008, 2008 R2, 2012) Introduction It would be bette...

Categories

  • .Net
  • 3DPrinting
  • AFeedYouShouldRead
  • Agile
  • ALM
  • Amazon
  • Amiga
  • Analytics
  • Android
  • ASP.NET
  • Azure
  • BigData
  • bing
  • Blogging
  • Book
  • BookReview
  • BUILD
  • C
  • C#
  • C++
  • Career
  • Cat
  • cheatsheet
  • ClickOnce
  • Cloud
  • ComputerHardware
  • css
  • Data
  • DBA
  • DependencyInjection
  • Deployment
  • Design
  • Development
  • devops
  • DVCS
  • ebook
  • EDD
  • Education
  • EnterpriseLibrary
  • EntityFramework
  • Exchange
  • Expression
  • gadget
  • Game
  • GIT
  • Google
  • Government
  • Hadoop
  • hardware
  • HardwareReview
  • HaswellReview
  • HTML5
  • Humor
  • IE
  • IEExtension
  • IfAllElseFails
  • IIS
  • ILMerge
  • Image
  • Infographic
  • interview
  • InversionOfControl
  • Java
  • Javascript
  • Kinect
  • LightSwitch
  • LINQ
  • Linux
  • LosAngeles
  • Lucene
  • Lync
  • MEF
  • Metro
  • MicrosoftOffice
  • MicrosoftOutlook
  • Mono
  • MVC
  • MVVM
  • NetMon
  • NLP
  • NoSQL
  • NuGet
  • OData
  • OneNote
  • OpenXML
  • Paint.Net
  • Personal
  • Photosynth
  • Physics
  • portable
  • Poster
  • PowerShell
  • Preparedness
  • Presentation
  • Prism
  • PrivateCloud
  • RegEx
  • RemoteDesktop
  • Reporting
  • RIAServices
  • Science
  • ScienceFiction
  • Scratch
  • Scrum
  • ServiceBus
  • SharePoint
  • Silverlight
  • SimiValley
  • SPA
  • Space
  • SQLServer
  • Storyboard
  • Surface
  • SVG
  • SystemAdministration
  • T4
  • TeamBuild
  • TeamFoundationServer
  • TechEd
  • Training
  • TypeScript
  • UnitTesting
  • UnityApplicationBlock
  • Utility
  • Veteran
  • VirtualMachine
  • Visio
  • VisualBasic
  • VisualStudio
  • WCF
  • Web X.X
  • Webcast
  • WebFeed
  • WebMatrix
  • Windows
  • Windows7
  • Windows8
  • Windows8.1
  • WindowsHomeServer
  • WindowsLiveWriter
  • WindowsPhone
  • WindowsServer
  • WinRT
  • WiX
  • WMI
  • WOPI
  • WPF
  • XAML
  • XBox360
  • XboxOne
  • zombie

Blog Archive

  • ▼  2013 (500)
    • ►  December (12)
    • ►  November (61)
    • ►  October (65)
    • ►  September (38)
    • ►  August (47)
    • ►  July (75)
    • ►  June (39)
    • ►  May (40)
    • ►  April (42)
    • ►  March (39)
    • ▼  February (42)
      • How Microsoft and others, empowers you with PowerS...
      • Awake Await, Async Asink... Think before you async...
      • Shaping up Office 2013 with these 300 Visio icon s...
      • Today's LINQPad fun, SelectExcept! (a tip on selec...
      • Helping a sick system get better... Five Windows A...
      • Helping you Hadoop with "Hadoop illuminated" the f...
      • Robert continues to guide us in the right directio...
      • IE10 RTW now available for Windows 7 and Windows S...
      • LINQToWiki (yes, even Wikipedia)
      • Sacha say IOC you! Sacha Barber presents his Barba...
      • Robert's "One Stop VS Update Shop" guide to all th...
      • Windows 8 + Kona + Steve = Itinerary Hunter
      • ClickOnce, Windows 8 and SmartScreen (If you're us...
      • Wak-A-PST with Microsoft Exchange PST Capture 2.0
      • Excel with Excel without Excel... Seven Excel/XLS ...
      • The All-In-One Framework goes Metro... errr... umm...
      • Modernize your WPF app's with the Modern UI for WP...
      • The Expression death march continues... Expression...
      • Kevin's Tips To Technology Presentation Perfection...
      • The HTML5 Quick Start For Greg's (err... Dummies.....
      • Find your inner App Hero with these two new Window...
      • It's a Rap! The Surface Pro Rap, that is...
      • Microsoft Message Analyzer better in Beta 2 (Gantt...
      • Note to Los Angeles Metro (aka subway) riders: Don...
      • Adobe Photoshop Source Now Available! (For the 199...
      • Microsoft SQL Server Licensing Question? There's a...
      • Wiki-wiki, here's some popular PowerShell add-ons...
      • Table Per Type(TPH) Inheritance in Entity Framewor...
      • Gaming up a story with the PowerPoint Game Design ...
      • Glima!... Gilma v3 is a .Net 4.0 update...
      • "Official" Surface Getting Started Guide (free 68 ...
      • You've heard of ASCII Art? How about DNS Art!?
      • Adding a 4th row of Windows 8 Start Screen tiles t...
      • O'la! As in Ola Hallengren's SQL Server Backup, In...
      • One download of wonderful [Samples] for Windows 8....
      • Composing Maintainable XAML Apps, the nearly two h...
      • Unit Test Stored Procedures? Here's one example...
      • Intro to MVVM in four easy episodes....
      • Learn how to learn, creatively with the free MIT L...
      • Microsoft TechEd North America 2013, New Orleans, ...
      • How portable is your assembly? Check it with the P...
      • Removing the SkyDrive Pro Explorer Context Menu it...
Powered by Blogger.

About Me

Unknown
View my complete profile