TSQL has a bunch of statistical system functions that can be used to return information about the system. This includes details about the number of connection attempts, cpu time, and total reads and writes and more. Many of these functions will be useful for performance monitoring. All of these functions return values that indicate cumulative [...]

The NTILE is used to assigned records into the desired number of groups. NTILE assigns a number to each record indicating the group it belongs to. The number of records in each group will be the same if possible, otherwise some groups will have less than the others. This function may be useful for assigning [...]

I wanted to find a way of programatically identifying the active node of a SQL Server Cluster. I found this post that demonstrated how to do it with TSQL. As I love Powershell so much here’s another method to do it; ?View Code POWERSHELL# Set cluster name $cluster_name = "ClusterName"; # Load SMO extension [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") [...]

The GROUPING_ID function computes the level of grouping in a resultset. It can be used in the SELECT, HAVING or ORDER BY clauses when used along with GROUP BY. The expression used in this function must match what has been used in the GROUP BY clause. This function can be usefully deployed to order a [...]

Here’s a simple example showing how to manage the failover process with Powershell, making sure all resources are running on one node. First, execute the below command to show which node owns the resources. ?View Code POWERSHELLGet-ClusterGroup -Cluster ClusterName | Format-Table -AutoSize; Name OwnerNode State —- ——— —– SoStagSQL20Dtc Node1 Online SQL Server Node1 Online [...]

You can use the GROUPING function to indicate if a column in a resultset has been aggregated or not. A value of 1 will be returned if the result is aggregated, otherwise 0 is returned. It is best used to identify the additional rows returned when a query uses the ROLLUP, CUBE or GROUPING_SETS clauses. [...]

You may encounter this error, about your storage networks, when setting up your Windows 2008 Failover Cluster. The following errors, Event ID 1129, will show up in Cluster Events… Cluster network ‘SAN1′ is partitioned. Some attached failover cluster nodes cannot communicate with each other over the network. The failover cluster was not able to determine [...]

The APP_NAME function returns the name of the application for the current database connection if the application has set it. Run the following in SSMS; ?View Code TSQLSELECT APP_NAME(); This will return; Microsoft SQL Server Management Studio – Query There’s no method of setting this value within TSQL. To set this value you need to [...]

I’m moving the backup jobs we run onto specific users and need to assign the db_backupoperator role to the user for each database. Very tedious to do in SSMS so here’s a quick script I knocked up. The script will assign a specific database role to all databases on a SQL Server. The user will [...]