Deleting SSAS Cube Partitions Using PowerShell and AMO

I’ve really developed a jones for using PowerShell to make painful, repetitive, time consuming tasks much quicker. PowerShell’s great for automation, but it’s also great for running tasks on an adhoc basis.
Read the rest of this entry »

MSBuildSsis2012 Now Available (…or Rather SSIS 2012 Deployment Fail Revisited…Again)

Edit: this has proven to be a popular post, and the software has been downloaded a few times. I will post an update for SQL Server 2016 soon, though I think it is just references in the solution that needs updating.

Today I am pleased to announce the release of MSBuildSsis2012 on Codeplex. But before i get into what it does first, a bit of background:

Back in May, and subsequently a few more times since, I’ve posted about an error I get occasionally in one of our custom tasks that run in our builds. This custom task is a special case: As the SSIS 2012 project extension is dtproj, it cannot be compile using MSBuild. The typical solution would be to use DevEnv In MSBuild. And to automate deployment the solution (that would work for most people) is to use the SSIS Deployment Wizard in silent mode. However, we don’t use the SSIS Deployment Wizard in our automated builds as it does not work in Silent Mode when you have assembly references in the SSIS packages: it destroys those references and the dtsx packages fail whenever they run.

Read the rest of this entry »

Get Database Backup Progress


If you have backups running through jobs you may want to know the progress of the backup and where the backup media is. Script below returns this information for you.

Happy scripting!

	,B.TOTAL_ELAPSED_TIME / 60000 AS [Running Time]
		FROM sys.dm_exec_sql_text(B.SQL_HANDLE)
	,sys.dm_exec_requests B
ORDER BY percent_complete DESC


Altering SQL Job Status


As an environment grows bigger and you have many jobs across many server instances, it’s important to automate as much as you can. And starting with the small, tedious but time consuming jobs is never a bad idea! A good example is having to enable/disable SQL Agent jobs during maintenance, or when a snapshot for replication is being pushed out you may not want certain jobs to run to prevent failures. So here is a stored procedure to alter the status of a SQL Agent Job. You only need to pass in the name of the Agent job. If the job is enabled it will disable it, and as an added precaution it will stop the job if it is running. If the job is disabled it will enable and start it running. Though if you don’t want it to run then you can always add a bit switch to the sproc.

Happy scripting!

Read the rest of this entry »

Access Denied When Initializing Subscriber (OS 5)

Today I needed to initialize a pull subscription from a server that had been in the pool of subscribers for a published database, and had been out for sometime. In that time the distribution database had moved, so it was a case of re-creating the subscriptions all over. Once the pull subscription and distribution jobs created, I ran the snapshot agent, which uses a file share. When the snapshot agent had completed, I stared up the agent job on the subscriber. I had done this many times before, and was surprised to see an error not too dissimilar from the one below in the subscriber logs on Replication Monitor:

The process could not read file ‘\\Server1\SQLShareFiles\unc\publication\2050530095003\slmnc.pre’ due to OS error 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL21022)
Get help: http://help/MSSQL_REPL21022
Access is denied.
(Source: MSSQL_REPL, Error number: MSSQL_REPL5)
Get help: http://help/MSSQL_REPL5

Oh. Access is denied? Quickly Googling the error I found a useful post at DBAStackExchange. Although we do use SQL Agent Authentication, what was missing was the permissions to the file share. Browsing to the file share and giving the Agent account full control of the file share resolved the issue for me. And though using Agent accounts does not follow best practice for the Replication Agent Security Model, it got the snapshot pushing out.


Collecting Baselines for High Undistributed Commands

In my previous post I shared some SQL scripts which would give you the number of undistributed commands by publication, by publication and article, and by distribution database. So you can measure undistributed commands at a very high granularity (articles), or at a very low granularity (the entire distribution database.) How granular you go depends entirely on where your pain is: you could of course filter out to only a few articles, or a particular publication. Read the rest of this entry »

Queries to Help Baseline Undistributed Transactions In Distribution Database

When it comes to monitoring replication, using the Replication Monitor.exe gives you a good view of what is happening now. But unless you have someone watching Replication Monitor 24/7, it’s generally not a good idea to rely on it. In fact, its best to avoid running Replication Monitor too often. Instead, you can use the information stored in your distribution database to get information such as agents hitting latency thresholds and a raise in undistributed transactions. Using the information stored in the distribution database ensures that monitoring is centralised. (I’m aware that there is a built in check for replication exceeding a latency threshold, however I have seen a high number of undistributed commands where in fact there were none, and false positives can be frustrating.) Read the rest of this entry »

FizzBuzz In SQL

This has been done many times before by many other people, but I set this challenge to a friend of mine who was interested in programming, and then realised that I had no solution. So I spent the train journey home writing my effort below. Like a lot of things in computer programming, there’s more than one correct way, but many more incorrect ways! I like this way because it’s quite small and readable.


WHEN @COUNT % 3 = 0
AND @COUNT % 5 = 0
THEN 'FizzBuzz'
WHEN @COUNT % 3 = 0
THEN 'Fizz'
WHEN @COUNT % 5 = 0
THEN 'Buzz'

PRINT @FizzBuzz


“Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF. “

In yesterdays post I mused what surprises replication would have for me after resolving an issue around the error message “Could not execute sp_replcmds”. Today’s replication issue comes from an error message that greeted me this morning:

“Cannot insert explicit value for identity column in table [table] when IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544″ Read the rest of this entry »

“Could not execute sp_replcmds”

Replication always brings up new surprises, and the most recent surprise I encountered was this error message on every single publication from one database:

“could not execute sp_replcmds on server [servername]” Read the rest of this entry »


Get every new post delivered to your Inbox.

Join 166 other followers