Get Average Backup Growth and Time Taken

SELECT [database_name] AS [Database] ,[backup_start_date] AS [Date] ,AVG([backup_size] / 1024 / 1024) AS [BackupSize_MB] ,AVG([backup_size] / 1024 / 1024) OVER ( PARTITION BY DATEPART(mm, [backup_start_date]) ORDER BY DATEPART(mm, [backup_start_date]) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS [movingAvg] ,AVG([backup_size] / 1024 / 1024) OVER ( PARTITION BY DATEPART(mm, [backup_start_date]) ORDER BY DATEPART(mm, [backup_start_date]) RANGE UNBOUNDED PRECEDING ) AS [Avg] ,CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, backup_start_date, backup_finish_date), 0), 108) AS TimeTaken FROM msdb.

Average Backups and Their Growth

Hello! (There is an updated version of this script herethat includes duration of the backup) Departing from the scripts I have been sharing all week about the SQL Agent job, the script below gives you the day average of your backup and both the average and the rolling average of the size of the database backups throughout the month. This script will only work on 2012 and later. If you try it on 2008 R2 and earlier you get a syntax error I’ve not seen before: “The Parallel Data Warehouse (PDW) features are not enabled.

Cumulative Updates - SQL Server 2012 SP2

UPDATE: You should probably hold off installing this until the first CU is available (see linked post)

SQL Server 2012 Service Pack 2 is available - but there’s a catch!

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 2!

Cumulative Updates - SQL Server 2012 SP1 CU10

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #10.

Cumulative Updates - SQL Server 2012 SP1 CU9

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #9.

20% Discount on Microsoft SQL Exams? Oh Go On Then...

As my more regular readers will know, I am working towards my MCSA SQL Server 2012. So I was pretty pleased to see this tweet the other day. https://twitter.com/MSLearning/status/435877633067593729 I signed up and booked my exam for March 28th. I’ve booked the “Administering Microsoft SQL Server 2012 Databases”. This is my day job after all! A few caveats with the voucher:

Cumulative Updates - SQL Server 2012 SP1 CU8

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #8.

Moving Tables that use Partitioning and Compression Across File Groups

SSIS 2012 Deployment Fail Revisited

( Part 2 focuses on clearing out the SSISDB by creating new stored proc based on the one used by the maintenance job. If your Maintenance Job is taking hours to run, you need to check this out.)

When deploying our Ispacs through my builds, I’ve still been getting the intermittent “timeout” issues I first wrote about back in May, and this has been exacerbated by extra SSIS Projects added to the SSISDB catalog.

Cumulative Updates - SQL Server 2012 SP1 CU7

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #7.

Cumulative Updates - SQL Server 2012 SP1 CU6

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #6.To apply this cumulative update package, you must be running SQL Server 2012 SP1. You may have to restart the computer after you apply this cumulative update package. This CU contains 23 fixes and is pertinent for versions 11.0.3000 – 11.0.3373.0 but is not for SQL Server 2012 RTM (11.0.2100 – 11.0.2999). There seem to be quite a few SSAS updates in this release.

Unknown Unknowns: Analysis Services Failing to Start

Cumulative Updates - SQL Server 2012 RTM CU9

A 9th Cumulative Update is available for those users still on the RTM branch of SQL Server 2012. This is in keeping with Microsoft’s Incremental Service Modelling. http://support.microsoft.com/kb/2867319You may have to restart your machine before the update is applied. Please check that the hotfixes which are included in this cumulative update package are relevant to you, though some of these seem fairly important, such as poor performance when you run a query that uses the LIKE operator in the WHERE clause.

Cumulative Updates - SQL Server 2012 SP1 CU5

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #5.To apply this cumulative update package, you must be running SQL Server 2012 SP1. You may have to restart the computer after you apply this cumulative update package. This CU contains 30 fixes and is pertinent for versions 11.0.3000 – 11.0.3368 but is not for SQL Server 2012 RTM (11.0.2100 – 11.0.2999)

Cumulative Updates - SQL Server 2012 SP1 CU4

As part of theirIncremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #4.http://support.microsoft.com/kb/2833645 To apply this cumulative update package, you must be running SQL Server 2012 SP1. You may have to restart the computer after you apply this cumulative update package. This CU contains 39 fixes and is pertinent for versions 11.0.3000 – 11.0.3367 but is not for SQL Server 2012 RTM (11.0.2100 – 11.0.2999)The build number of the cumulative update package is 11.

SSIS 2012 Deployment Fail Part Two: SSIS Server Maintenance Job

Recently I post about a timeout issue I was getting when deploying SSIS projects. It was mainly to do with too much log and previous versions being kept in the SSISDB on our test environments. My solution was to run the SSIS Server Maintenance Job prior to every deployment to mitigate the timeout. By and large this has been super effective. However that job can take a considerable amount of time to run.

SSIS 2012 Deployment Fail

(Update: Part 2 focuses on clearing out the SSISDB by creating new stored proc based on the one used by the maintenance job. If your Maintenance Job is taking hours to run, you need to check this out.)

No WiX Wednesday this week, owing to commitments in real life. Instead, here is something regarding SSIS 2012 Deployment. Enjoy!

As part of our CI and Test Builds we have automated the deployment of two SSIS Projects. One is fairly large and the other one contains only two dtsx packages. Recently we have been getting timeout issues with the deployment of the solutions.

Keeping Microsoft Products Up-to-Date

I posed a question to one of our new starters the other day: Would you rather have 6 months experience in SQL Server 2012 or 8 years experience in SQL Server 2005? I asked him this in response to his surprise that we seem to use the latest versions of all the Microsoft products: Windows 8, TFS 2012, Visual Studio 2012 (including the new BIDS version), SQL Server, Windows Server 2012… the list goes on. Certainly it benefits us employees as we get exposure to the latest software. But the policy to upgrade to the latest SKU’s of Microsoft products isn’t for the benefit of the employees,it’s just a bonus. And, though it may look like the company is on the bleeding edge of IT, that ’s also not really the case either. It may be up to date, but certainly not bleeding edge. Other than access to the latest features, there are two main reasons why it makes sense to upgrade the latest version:

Cumulative Updates - SQL Server 2012 RTM CU7

A 7th Cumulative Update is available for those users still on the RTM branch of SQL Server 2012. This is in keeping with Microsoft’s Incremental Service Modelling. http://support.microsoft.com/kb/2823247You may have to restart your machine before the update is applied. Please check that the hotfixes which are included in this cumulative update package are relevant to you, though some of these seem fairly important. This CU has 17 fixes, and the version number will now be 11.

Cumulative Updates - SQL Server 2012 SP1 CU3

As part of theirIncremental Service Modelling , Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #3.http://support.microsoft.com/kb/2812412 This CU contains 38 fixes and is pertinent for versions 11.0.3000 – 11.0.3348 but is not for SQL Server 2012 RTM (11.0.2100 – 11.0.2999)Please check that the hotfixes which are included in this cumulative update package are relevant to you, unlike the last CU2,which fixed a major bug , it is generally recommended that you only install a CU if one of the fixes is pertinent to you.