T-SQL Tuesday is a monthly community-wide blogging event created by Adam Machanic (blog|@AdamMachanic), and everyone is invited to particpate. This month’s blog post party — I like that phrase, it has a nice ring to it — is being hosted by Bradley Ball (blog|@SQLBalls), and the holiday-themed topic this month is T-SQL Tuesday #73: Naughty or Nice?. For my part, I want to relate a cautionary tale about how I crashed a production server at Microsoft at 3 o’clock in the wee hours of the morning, and I how I have avoided repeating that mistake.
The year was 2008, and we were experiencing some issues during the over-night bulk-loading processes. We needed more detail, so we decided to set up a SQL Trace to capture the workload. I set up a SQL Trace with SQL Profiler on the server capturing the activity to a file right before the end of my work day. I assured there was no noticeable performance degradation on the server, and left it running with the intent to stop it when it completed. I was aware of the overhead of running a trace, bit I kept the trace vents to a minimum and did not think we would have a problem.
Performance-wise, we had no problems with the trace that was running. It was this even though that I learned that Profiler itself does some things that are naughty. When you are running a client-side trace (via Profiler) it buffers the output to the user’s temporary space in their User Profile. You know, that subfolder at c:WindowsUsers
The buffer file for the SQL trace filled up the C drive and caused the OS to crash. Fortunately, when it restarted, the evidence — oops, I mean the buffer file — was gone and the person who responded to the alert (we had night-time coverage so at least nobody had to be awoken for this) saw no reason for the crash. When I came in and investigated the event logs, I saw that the C drive had run out of space and caused a crash. I tested my theory, and sure enough, I could see the C drive being filled if I ran a client-side trace with Profiler on my desktop. I crashed the server. I had done something I knew I shouldn’t do. I had been lazy and the server crashed in the middle of the night as a result.
Testing verified that running a server-side trace did not buffer data to the user space (or anywhere on the C drive). So the solution was simple, I simply needed to do what I already knew was the right thing to do. Since then, the only time I have done a client-side trace is to check something on my own desktop or laptop or when demoing how bad a client-side trace can be. Everyone should be using Extended Events anyways these days, but if you are going to use SQL Trace, don’t be naughty, be nice and do a server-side trace. Do not do client-side traces with Profiler.