Create or Organize Your Script Library

Do you have your own script library?  If not, you should absolutely create yourself one as they can be invaluable and can save you from a world of pain.   Jeremiah Peschka blogged about versioning your scripts and it shows a great way for you to go through this process using a few different free options.   If you are starting from ground zero, start here or at the very least create yourself a working folder on your computer and save off anything that you come across.

Now comes the second group of people, we will call them people like me.   You have a script library (in my case a very large one) and you can find your scripts without any problem but the scripts are not well organized.   Now is the time, get them organized and maybe put them into a GitHub or BitBucket.   Start versioning them appropriately as you make changes to them or as software changes versions and they need to be modified.   This is the situation I am in today, and my plan is to get them neatly organized and put up in a very easy to access, easy to reference repository within the next couple of months.

For those of you who are already on top of this, tell the rest of us the way that you did it.   I am sure there are dozens or hundreds of ways to save off your script library and any tips you have for those of us without or who are less organized would be greatly appreciated.

And two months from now, I will put a new post up saying where I have made it.

SQL Agent – Powershell Task Appears To Do Nothing?

Have you ever come across a situation where you have some Powershell task within a SQL Agent job and it appears to be taking WAY longer than you feel like it should?   If you have, you are not alone and if your situation is like mine, this is by design!

Now the question becomes, what can you do?   The answer is pretty simple, you just have to tell SQL to allow Powershell to use more than 2 threads at a time.    The number 2 comes from the msdb.dbo.syssubsystems table, which you can view all of the settings by running the following:

SELECT * FROM msdb.dbo.syssubsystems

And as it turns out, you can very easily change this by running SQL similar to this (pick your own number…pick it wisely):

Update msdb.dbo.syssubsystems
set max_worker_threads = 10
where subsystem = 'Powershell'

 

And after restarting the SQL Agent, you can have up to that many jobs with Powershell steps all running simultaneously.

I have not personally come across any repercussions of changing this setting, but if you do please fill me in.  I would love to hear those stories  before I have to live through them myself,