“Attempt to read or write protected memory” error from SSMS for System.Data

I just spent a couple of hours fruitlessly trying to solve a problem and thought I’d blog the solution in case anyone else hits the issue. A few months back I got a new laptop and installed SQL Server 2014 on it and everything worked fine. A few weeks ago I installed SQL Server 2008 […]

Limiting error log file size in SQL Server 2012

It’s quite well known that you can optimize error log file management using SSMS to change the maximum number of error log files to 99 and running sp_cycle_errorlog every day at midnight (see this post on my old SQL Server Magazine blog for graphics). This works in all current versions of SQL Server. One thing […]

Cool free tool to parse and analyze SQLIO results

During every one of our Immersion Events, we designate Thursday evening as 'open mic' night where anyone can do a 15-minute presentation on anything they want (to do with SQL Server) to the class. We usually have 4 or 5 people who entertain us with interesting talks, and our recent classes in Chicago were no […]

OT Survey: internet browsers and mail readers

This survey is off-topic and was suggested by Justin Dearing (blog|twitter) and I'm curious about the results too so I agreed to run it. We're interested to see what internet browsers are most commonly used my members of the database community, along with which email clients. People are often fanatic about one browser/client or another […]

Beware of advice from 3rd-party tools around dropping indexes

(In this post I'm not going to name-and-shame, as I'm sure the problems will be fixed in time.) I want to warn you about unthinkingly acting on advice from 3rd-party tools around dropping nonclustered indexes. One of my long-term clients recently bought and installed a new tool and has been asking me about recommendations from […]

Ever had poor performance saving files in SSMS over slow network?

I just about beat my fists against my laptop this afternoon while updating a couple of demo scripts for our Performance class in Dallas this week. Well, not quite because I really like my new laptop. But you get the idea. I'm working over a 3G USB wireless connection and every time I want to […]

Are your CPUs running slowly? Tool tip and survey

(Yes, I know I haven't editorialized the last survey on What's in a Job Title – I will in the New Year.) Over the last couple of weeks I've signed up a bunch of new customers for maintenance/ops audits and general perf work and I've had all of them check whether power saving mode is […]

Configuring SSMS for presenting

A few people have asked over the last few days how Kimberly and I have SSMS set up for presenting so I thought I'd do a quick blog post explaining what we do. The first thing is to go download ZoomIt so that you can zoom in the screen and people can see the details. […]

A DBA myth a day: (13/30) you cannot run DMVs when in the 80 compat mode (T-SQL Tuesday #005)

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) This blog post is part of two series – my […]

Using diskpart and wmic to check disk partition alignment

(Check out our Pluralsight online training course: SQL Server: Improving Storage Subsystem Performance.)  A while ago I blogged about disk partition alignment, and how the default alignment of 31.5Kb on Windows Server 2003 can lead to enormous I/O performance problems (see Are your disk partition offsets, RAID stripe sizes, and NTFS allocation units set correctly?). We’ve […]

Scripting schema AND data with SSMS in SQL 2008

It's very commonly known that you can use the Script Wizard in Management Studio to script out tables or even an entire database. It's NOT commonly known that in SQL Server 2008, the wizard was upgraded and you can now script out the data too. The feature isn't enabled by default, which is why not […]

Misconceptions about running DMVs on databases with lower compatibility levels

It's lunchtime for my class – time for a blog post! This is an interesting one that crops up every so often (just an hour ago on SQL Server Central) and is not known very widely at all. There's a misconception that you cannot run DMVs in databases that have compatibility levels of 80 or […]

Free SQL Server tools

Just found this excellent blog post (continually updated) that lists a ton of free SQL Server tools for all sorts of purposes. Check it out at http://weblogs.sqlteam.com/mladenp/archive/2007/11/20/Free-SQL-Server-tools-that-might-make-your-life-a.aspx.

How to get all the corruption messages from CHECKDB

Hopefully all of you reading my blog already know to use the WITH ALL_ERRORMSGS option whenever you run DBCC CHECKDB (which is now the default in 2005 SP3, haven't checked if it made it into 2008 SP1). If you're doing some quick analysis of the results to see, for example, whether it's just nonclustered indexes […]

Importance of having a manageable environment

Last week's survey was two-fold – what's the largest SQL Server database you manage, and how many SQL Server databases are you responsible for managing. Here are the results as of 5/10/2009.   As far as the database sizes are concerned, the distribution curve isn't surprising, but the average size of databases *is*. 70% of […]

What is FSAgent.exe and why does it have lots of waits?

Another quickie today. If you're using Activity Monitor in SQL Server 2008, or Performance Data Collection with the Server Activity system data collection set, then you may see a constant, and large number of 'Other' SQL Server Waits. When you drill into these, you see that the highest number of waits are for FSAgent. The FSAgent is […]

SQL 2005 SP2 maintenance plan bug masking corruption

Not much to do here in Tokyo while waiting for the Bangkok flight except read and blog, and I've already done enough reading for one day on the flight here – so that leaves blogging! Here's an interesting case that I got involved in on SQLServerCentral (here's the original thread, linked here with permission of […]

TechNet Magazine: feature article on Common SQL Server Security Issues and Solutions

The May 2009 TechNet Magazine is now available online, and it's the annual security issue. In there is an article I wrote highlighting 10 common security issues (and solutions) you should worry about if you're not a security-savvy DBA. It covers: Physical security Network security Attach surface minimzation Service accounts Restricting use of administrator privileges […]

How to scan a network looking for SQL instances

I came across a good post over at the Data Management section of Less Than Dot, describing how to scan through a network looking for unsecure SQL instances. It makes use of the free SQL Ping tool and describes automating the process using SSIS. This is pretty interesting to me as I’ve just finished writing […]

Easy monitoring of high-severity errors: create Agent alerts

In my previous post today I talked about error 825 and how you should have an Agent alert set up to catch it when it happens. There are two ways you can set up Agent alerts – using SSMS or using the stored-proc sp_add_alert. In a post from April 2008 I showed how to create an […]

Should Microsoft provide a transaction log reader tool?

Now this one’s sure to spark some controversy… I was checking my RSS feeds of the SQL blogs that I follow and noticed an interesting post on Kalen’s blog (the post is here). She’d been lambasted for suggesting in the weekly SQL Server Magazine newsletter that *Microsoft* shouldn’t provide a log reader tool and asked […]

Available for download: Our High-Availability hands-on labs and SQL Server 2008 JumpStart materials

A couple of weeks ago I blogged about the three tracks of the SQL Server 2008 JumpStart course that SQLskills.com taught internally for Microsoft and some MVPs – see here for details. Well, the content is now available to download! Note that this was based on CTP-5 (November 2007 CTP) and there have been *lots* […]

TechNet Radio interview with Paul and Kimberly on SQL Server 2008 – Part 2

The second part of our radio interview with TechNet has been released (see here for part 1). In this installment we discuss troubleshooting and manageability in SQL Server 2008. You can get to it by going to the March 11th 2008 show here. Enjoy!

Whitepaper on Predeployment I/O Best Practices, plus a few urban legends around SQL Server IO

There’s a new whitepaper on TechNet that I’ve just come across (even though it was published 6 months ago!) called Predeployment I/O Best Practices. It’s really good – discussing the following: Guidelines for determining I/O capacity Disk configuration best practices and common pitfalls Using SQLIO to determine capacity and interpreting its results Using System Monitor […]

Enabling VS2005 support for SQL Server 2008 CTP-5

Hit another issue last night while playing with the latest CTP – VS2005 can’t cope with some of the changes in SS2008. The problem arose while defining an ASP.NET connection string that was pointing to a 2008 instance (while playing with query notifications and cache invalidation, but that’s unrelated) – it failed with a slightly […]

Katmai bug: SSMS doesn’t drop database locks when leaving the context of a database

I came across an interesting bug in Management Studio in the latest Katmai CTP today – when a connection disconnects from a database, SSMS doesn’t release the shared database lock that it holds. This prevents any operations that need exclusive database access (like a RESTORE) and can be somewhat disconcerting if you don’t realize what’s going […]

Paul and Kimberly interview on RunAs Radio – What’s New in SQL Server 2008?

While we were in Barcelona we sat down with Richard Campbell and Greg Hughes from RunAs Radio to record a 1/2 hour interview on SQL Server 2008. We touch on a ton of different features (look at the number of Categories I’ve tagged this with!) and have a bunch of laughs along the way – […]

SQL Server 2008: Configuring Peer-to-Peer Replication

One of the cool features added in SQL Server 2005 for scaling-out a workload was peer-to-peer replication. The major drawback was that to change an existing peer-to-peer topology, the entire topology had to be quiesced. In SQL Servr 2008, the Configure Peer-To-Peer Topology Wizard in Management Studio has undergone a major face-lift and a peer-to-peer […]

Tool for estimating the size of a database

[Edit 8/25/2013: The tool referenced in this post is no longer available.] During SQL Server 2005 development I did a comprehensive rewrite of the Books Online entries concerned with estimating index, table, and database size (see http://msdn2.microsoft.com/en-us/library/ms187445.aspx). I was recently having a discussion with Øystein Sundsbø about a bug in one of the formulas I wrote and […]