Author Archives: brentm

Failed data collector upload job

Posted on 2012/12/11 by brentm

When the MDW data collector fails the upload job,  the cached files start building up and the MDW database starts getting out of date. If you look at the SQL agent job history for the upload job you will likely … Continue reading →

Posted in Troubleshooting | Comments Off on Failed data collector upload job

Working with SQL Server data compression

Posted on 2012/12/04 by brentm

 Before compressing any database, a check to see whether ROW or PAGE compression will give the best result is necessary before carrying out any change. The script below will generate a set of scripts to run and the output you can use … Continue reading →

Posted in Compression | Comments Off on Working with SQL Server data compression

Creating a SQL Database

Posted on 2012/12/02 by brentm

Writing an article about how to create a database might seem a bit too simple to many but I was surprised when talking to people that they didn’t know why I would do it this way.  When you create a … Continue reading →

Posted in Tips | Comments Off on Creating a SQL Database

Scaling MDW

Posted on 2012/12/02 by brentm

SQL Server 2008 introduced the MDW – a Management Data Warehouse. The MDW is a data warehouse for storing performance data about your SQL servers. It is best configured using a centrally located database – in the environment I work … Continue reading →

Posted in Performance | Comments Off on Scaling MDW

Moving Cluster Storage Groups

Posted on 2012/10/16 by brentm

Moving cluster storage groups is as easy as a short powershell command. I keep forgetting the command for moving the available storage so that it is on the correct node for my SQL install. It is accomplished like this… Run up a … Continue reading →

Posted in Clustering | Comments Off on Moving Cluster Storage Groups

DBCC IND and DBCC PAGE

Posted on 2012/07/03 by brentm

DBCC IND – use to lookup what pages are allocated to an object Syntax: DBCC IND ( database_name, table_name, index_id ) NOTE: there must be records in the table before this will return a result set. NULL values in the … Continue reading →

Posted in Troubleshooting | Comments Off on DBCC IND and DBCC PAGE

snapshoting large tables

Posted on 2012/05/24 by brentm

When initialising a transactional replication subscription with a snapshot, and the source table is very large this can cause concurrency issues for the source database. This is because when performing a snapshot, an exclusive lock is taken on the table … Continue reading →

Posted in Replication | Comments Off on snapshoting large tables

generate permissions for a role

Posted on 2012/05/08 by brentm

This script will generate all the permissions that a role or all roles have… SELECT state_desc COLLATE Latin1_General_CI_AS + ‘ ‘ + permission_name COLLATE Latin1_General_CI_AS + ‘ ON ‘ + ‘[‘ + SCHEMA_NAME(schema_id) + ‘].’ + ‘[‘ + OBJECT_NAME(object_id) + … Continue reading →

Posted in Security | Comments Off on generate permissions for a role

role based security

Posted on 2012/05/07 by brentm

I’m a strong believer of role based security and today proved my point. In a server migration that we have underway, I migrated what I thought was all the permissions for this particular login. As it turned out, someone in … Continue reading →

Posted in Security | Comments Off on role based security

Startup stored procedures

Posted on 2012/05/06 by brentm

Stored procedures are enabled for execution with the proc sp_procoption. Create your stored procedure in the master database and then call sp_procoption like so… sp_procoption @ProcName = ‘my_stored_procedure’ , @OptionName = ‘startup’ , @OptionValue = ‘on’ By calling sp_procoption, you … Continue reading →

Posted in Tips | Comments Off on Startup stored procedures