Three properties of a given task that compel me to use PowerShell or another managed programming language like C# or SSIS over using T-SQL to accomplish the same task:

1. The need to interact with a file system, e.g. deleting old files, renaming existing files, executing a program, etc. If you're considering using xp_cmdshell or one of the undocumented extended procedures like xp_dirtree, don't, explore using PowerShell or another managed programming language like C# or SSIS.

2. The need to interact with multiple database instances, e.g. gathering information about an environment where I want to run the same query against 1-n instances and compare or do something with the results. If you're thinking of using Linked Servers, don't, explore using PowerShell or another managed programming language like C# or SSIS.

3. The need to interact with a service outside the SQL database engine, e.g. Active Directory, a Web Service, SharePoint, etc. If you're thinking of using CLR or the OLE Automation procs, don't, explore using PowerShell or another managed programming language like C# or SSIS.