Reading/scanning SQL Server Logs is a required DBA work item in all my work environments. I know there are lots different ways, mostly are using undocumented xp_readerrorlog or sp_readerrorlog.   Even in PS way, this xp_readerrorlog is wrapped as a sql command in the PS script. Actually, with PS and Regular… Read more

SQL Server SMO is a great resource for DBAs, unfortunately, before PowerShell, it is a pretty deep learning curve to get familiar with SMO. The beautify of PS is its own discovery capacity. This is best described in the Keith Hill’s free ebook (http://rkeithhill.wordpress.com/2009/03/08/effective-windows-powershell-the-free-ebook/). So in short, we… Read more

One challenge in a super-multiple server (say a few hundred servers) environment is to know what sql services (SSRS/SSAS/SSIS/Engine/Agent etc) are installed and what their versions and the startup accounts are.

Case Scenario: (SQL Service Inventory)

We are managing hundreds of physical servers in our environment, most of the servers… Read more

One common surprise I have encountered in almost all my DBA environments is that a sql job that usually runs <x> minutes, sometimes can take <2x> minutes or even more time to finish.

Case Scenario: (Monitor / alert long running sql jobs)

We have hundreds of sql instances supporting many… Read more

As a DBA, sometimes we may need to demonstrate to some stakeholders when a sql server instance was last rebooted, i.e, just to prove the sql instance weekly/monthly availability . I know there are quite a few blogs / posts online talking about how to do this check, such as… Read more

As a DBA in a complex environment, we frequently need to backup various environment information. One of the backups is to script out the configurations / objects, for example, scripting out replication,  jobs, policies and policy conditions etc. Almost all of these scripting work can be done via SSMS,… Read more

I have been blogged non-technical topics for too long and it is time for me to return to my favourite technical topics now.

I plan to write seven real-world cases where I feel PowerShell solution is much more efficient than pure T-SQL one, hopefully these cases can stir some… Read more