Simple SQL Server

How do I use this thing?

Ok, so I know this looks big and scary with lots of parameters. The three of them that are required to run it are only @Subject, @Recipients, and @Query1. The six I consider required to make something great also include @OrderBy1 (i.e. make it easy to read the query results), @BodyIntro (i.e. explain why they’re getting the email and what the query results are before they see them), and @BodyEnding (i.e.

Leave Autogrowth On?

Yes, absolutely. This isn’t a question. This could be worded as “Do you want a slight pause or do you want your application to fail?” The answer is a little more obvious when you word it that way. The only time I’ve seen an appropriate answer of “No” is when you have multiple data files in a filegroup and only want one to grow. To answer how long this pause will be you have to ask these two questions.

Instant File Initialization – What is it?

Short answer, this makes growths exponentially quicker by opening an often-acceptable small security hole. Of course, I always have a long answer… By default, all space used by SQL Server is zeroed out before control is handed over. This makes sure that it’s clean space for two basic reasons. First, the database requires that space to be clean because of how it plans to use it.

Instant File Initialization – How do I use it?

This is an odd feature that you can’t just see in a table in SQL Server. The easiest way is to run an xp_cmdshell command and look for it, but you can optionally turn on trace flags, grow or create a database, turn off the trace flags, then look in your error log. Paul Randal gets into both methods in his post How to tell if you have instant file initialization enabled?.

Why is this common to do?

There is typically at least once in every large database’s life where the log needs to be shrunk for one of a couple reasons. Going off of the two reasons above, the first two are because a large process ran that won’t run again. The third reason I go over here is because of small VLFs. 1.