Shrinking A File In SQL Server

Check out the solution in the video TSQL: DBCC SHRINKFILE Part I. Depending on the environment and setup, we may be required to shrink a file in SQL Server (such as a log file). Lower environments (generally, development and QA) tend to have tighter restrictions on space that we don't have as much in pre-production or production environments. This isn't to say that these are ideal setups, just what we tend to find. I would use extreme caution in shrinking any file in production for a multitude of reasons.

A few points that we answer in the video:

• What we should know in part one of this discussion
• What we should consider when thinking about shrinking a data versus log file
• In the case of log files, what does it mean if we constantly have to shrink files

As a caution, I will rarely shrink a file in SQL Server because the impact of shrinking a file. Good architects will pre-plan the log size and not have a reactive setup, though you'll find the reactive setup a popular choice in many environments. There is a direct correlation between micro-management of logs and eventual database corruption: I have seen 0 instances of database corruption in environments where logs were pre-planned and the model was followed with no exceptions.

One regular point I like to remind my audience, especially as of recent: we can often solve the same problem with a variety of techniques. While we look at one or two ways to solve the problem, these aren't the only ways we can approach this problem. The most appropriate solution to a problem is one in which you can troubleshoot quickly in the future and one in which you understand. Be careful about applying solutions that solve a problem, but introduce new problems in the future.

A great re-occuring example of this is my discussion on alerting best practices. There are a variety of alerts everywhere for everything in today's development environments, but many of these alerts cause more issues than they solve (one of which is creating a distraction). Be careful about "easy" solutions which introduce bigger problems. Know your environment and where to apply the best solution. Of course, all these being written, it is helpful to learn new techniques to solve problems because they may be useful in the future.