Archive for 2013

List AD Groups Setup on SQL Server

Here’s a quick powershell snippet to display the Windows groups setup as logins on SQL Server. ?View Code POWERSHELLImport-Module SQLPS -DisableNameChecking -ErrorAction Ignore; Import-Module ActiveDirectory -DisableNameChecking -ErrorAction Ignore; $sql_server = "sql_instance"; $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $sql_server; $srv.Logins | Where-Object {$_.LoginType -eq "WindowsGroup";}; Output will be similar to below. Name Login Type Created —- ———- ——- […]

Native table ‘performance_schema’.’threads’ has the wrong structure

After upgrading a MySQL slave from 5.5 to 5.6.14 I attempted to execute the following query… ?View Code MYSQLSELECT * FROM performance_schema.threads; Despite the error log not reporting any issues I received the following error… Native table ‘performance_schema’.’threads’ has the wrong structure This was simply resolved by executing… ?View Code BASHmysql_upgrade -h localhost -u root […]

SQL Server Partitioning for Paupers

Last year I posted about a pauper’s partitioning technique I used with SQL Server to solve some data purging issues. In a similar vein I recently found SQL Server partitioning without Enterprise Edition that looks like the answer to an issue in our systems. We have large amounts of data but simply can’t justify the cost […]

Getting started with Query Notifications with SQL Server 2008 R2

I’ve been experimenting with Query Notifications in SQL Server 2008 R2. This looks like a really cool way of implementing real-time features into your applications without constantly battering your database with select statements. Instead we can request a notification for when the data has changed for a query. Here’s a quick demo of the feature. […]

Inspect those indexes

Here’s a few queries I often use to review the indexes in our SQL Server systems. Tables should usually have a primary key. Are all of these intentional in your system? ?View Code TSQL– Tables with no primary key SELECT OBJECT_SCHEMA_NAME(t.[object_id]) AS [schema_name], OBJECT_NAME(t.[object_id]) AS [table_name] FROM sys.tables t LEFT JOIN sys.indexes i ON i.[object_id] […]