Changing the Past

The National Archives

A few years ago my accountant caught a mistake in one of our tax returns. This was during the next year’s filing and so we had to amend to previous return, filing against almost two years later to correct an issue. Fortunately the error was in our favor and we ended up receiving a refund.

In most businesses you typically do not change data that was completed in the past unless there is a severe error. For some types of data, such as audit data, you never want to change it. And in many cases, even if there were some change, because of the way that you have operated the business based on those past values, you might decide not to change things.

However as more and more data is accumulated, and used in business decisions, legal proceedings, etc, it is likely that more data professionals will be faced with the questions about when it is appropriate to alter something. With that in mind, this Friday’s poll is:

When would you change archived data?

Are there circumstances that you are aware of where it is valid to change data that might be archived? Is it only to make corrections that were recorded incorrectly in the past? Is it to normalize data, so events like companies merging are able to run reports with pre-merge data?

This is a thorny subject, but it seems like one that we should be thinking about as data professionals, looking to give guidance to our clients.

Steve Jones

(Originally published at

The Voice of the DBA Podcasts

Shrinking Databases

Every time you shrink a database, an angel gets its wings torn off. Or Paul Randal feels a disturbance in The Force. In other words, don’t do it.

But people continually do shrink databases. Despite the constant advice and guidance from Microsoft, MVPs, and more, the fact that shrink is available easily in maintenance plans and with a DBCC command, people do it. I understand it since it’s natural to not waste space and so many other tools, like Access and Outlook, have files that only use the spaced needed.  Why not SQL Server?

It’s Friday, and I thought I might see if there might be a oslution tht makes sense. For this poll, I want to ask you about shrink:

Should shrink be removed or fixed?

By removed, I don’t mean completely take it out of SQL Server, but make it harder. Maybe require a trace flag, maybe something else that might reduce the regularity with which it’s run. I’d certainly recommend it be completely removed from maintenance plans, both the wizard and the designer.

Is there a better solution, however? Should perhaps shrink be fixed to be a more intelligent operation that doesn’t cause lots of fragmentation? Even if it’s slower, or maybe an operation that requires more resources to complete, would it be better to actually “fix” shrink?

Put your answers in the discussion and let us know what you think

Steve Jones

Coding With Music

Lots of developers use headphones and music at work, though I think corporate life in cubes encourages this, just to block out the distractions. When the walls of your office stop at the 5 foot level, you might need all the privacy you can get from a large set of speakers attached to your head.

I heard a few programmers debating this recently. One of them mentioned that he often uses music, a soothing melody that helps to block out distractions. Another said that white noise is a help. A few others actually mentioned that a private office and silence was more important for them. I know that in the past I’ve often tried to pick more mellow tunes, often jazz or classical music without a vocal component, when I needed to concentrate.

Another person mentioned that there has been work that shows music is distracting to people, and it can perhaps occupy a portion of your brain and make you less efficient. You might notice less patterns, or consider things differently with music playing. I found that plausible, and decided to ask the question this Friday.

Would you prefer silence, music, or some other background noise when you are working?

I often go to a Starbucks or a bookstore and write on my laptop, just to have the white noise of people in the background. I think I get some level of energy from having other people around, but not interacting with me. I might listen to some music, but often I just sit there and focus on my work.

When I used to work in a corporate environment, I found that I used music, but it was really to block out the distractions of other people nearby. Being in my own world often helped me get into the zone, and having large blocks of time were essential.

So let us know what works for you, why, and if you’ve tried anything else. If you have good arguments to present to management for achieving your ideal work environment, let us know as well.

Steve Jones

Little Devices

It seems that I get more and more done with my smartphone all the time. I find myself responding to lots of emails, having text or Twitter conversations, taking pictures, reading, even making notes on my iPhone. I can’t imagine every going back to a non-smartphone device. Whether it’s an iPhone, Android, or WinMo 7 device, I have found that they make me quite productive throughout the day.

I was just starting to get away from plain phones when I last worked for a large corporation. These days, I see many of my friends sporting smart phones, and getting work done on them. I even have one Windows administrator friend that carries an iPad around work, RDP’ing into servers to handle small tasks.

So I wonder how many database people can get things done. Since it’s Friday, I thought that it would make an interesting poll:

How much of your work could you get done from a mobile device.

I’m wondering if there is a chance that you can see a good portion of your administration, monitoring, or even daily work done from some device that isn’t a full size laptop? It could be a smartphone (Android, iOS, WinMo) or even a tablet (iPad/Galaxy) that allows you to actually knock things off your to-do list when you are away from your desk or don’t have a laptop.

If you’ve never used a mobile device for doing work, please don’t complain about them. They are great devices and they’ve made me very productive when I can’t sit at a desk or carry a laptop. I think many others agree, and I’m interested in seeing how use mobile devices.

Steve Jones

If you’re a DBA, you might want to check out SQL Monitor, from my employer, Red Gate Software. Maybe your boss will let you get a copy and an iPad to manage your servers.

More Triggers

In the old days of T-SQL, back when we wrote “CREATE TRIGGER …. FOR INSERT” we could only have one insert/update/delete trigger for each table. Eventually SQL Server allowed us to have multiple triggers, and even have some control over in what order the triggers fired.

Triggers are often hidden objects that confound DBAs who aren’t aware they exist. It’s not easy to tell when a table has a trigger on it, and since we don’t often use triggers, it’s not the first place people look when something strange happens.

However triggers are useful, and it seems that there are many people using them. For this Friday, I wanted to ask how people implement triggers in their applications.

Do you prefer one trigger for each table action or multiple triggers?

I’m curious what’s the 80 in your 0/20 rule for triggers. Should all update actions be handled in one trigger? Or should there be one trigger for business logic  and a separate one for auditing? I’m not sure it matters a lot for performance, but I can see that it might be easier to manage and track fewer triggers. The flip side is that something like auditing can be handled with one trigger, and business logic with another: a clean separation.

Triggers aren’t usually my first solution to a problem, but I do think there is value in using them. However I don’t see a lot of guidance about how to best implement them, so I’m hoping your answers today will help.

Steve Jones

Enterprise Management

One of the things that I’ve had to do in my career is manage large numbers of servers. There was a time when two of us managed several hundred instances of SQL Server, and managed to do it very well with a lot of automation. Recently I saw someone post a note about having 400+ servers to manage, and they were wondering how to perform enterprise management. In Oracle environments, they mentioned using OEM, RMAN and other tools to manage backups centrally and wanted to know what SQL Server solutions were out there.

I know things have changed quite a bit from the SQL Server 2000 days when we had to build all our own tools, so I wanted to ask the question as a poll this Friday:

What do you do for central management of lots of instances?

If you have more than 50 instances, what tools do you use to try and ensure you have a well managed environment. With the addition of the Central Management Server and Policy Based Management  to SQL Server, it definitely is easier to handle a larger load of instances

This Friday I’m wondering what tips and techniques you think work well for managing lots of instances. Any tricks you’ve used? Any third party products that are very handy? Share you knowledge this Friday.

Steve Jones