Simple Recovery Mode And Transaction Logs

Kenneth Fisher discusses changing your database’s recovery model to Simple in order to clean out a transaction log:

Clearing out a full transaction log is a common problem. A quick search will find you dozens of forum entries and blog posts. Because of that I’m not going to talk about the correct methods of dealing with a transaction log full error. What I want to discuss is why you shouldn’t use the following method.

And Kenneth also hits the one legitimate use:  dire emergency.  If this is a normal part of some process (e.g., warehouse loading), bite the bullet and either live in Simple recovery mode (understanding the risks) or get the disk space to keep it in Full mode.  Switching back and forth—especially if you aren’t taking full backups immediately after switching back—is a good way to get yourself burned.

Drive Failure

SQL Wayne cautions you to think about drive failure:

Now is when the MTBF comes in. If all of the drives were from the same batch, then they have approximately the same MTBF. One drive failed. Thus, all of the drives are not far from failure. And what happens when the failed drive is replaced? The RAID controller rebuilds it. How does it rebuild the new drive? It reads the existing drives to recalculate the checksums and rebuild the data on the new drive. So you now have a VERY I/O intensive operation going on with heavy read activity on a bunch of drives that are probably pushing end of life.

This is where it’s important to keep spares and cycle out hardware.

Automating SQL Server Installations

Joey D’Antoni argues in favor of automating SQL Server installations:

Anyway, on to my original topic—I know most shops aren’t Comcast where we were deploying multiple servers a week. However, automating your installation process has a lot of other benefits—you don’t have to worry about misconfigurations, unless you put in into your script. It also forces you into adopting a standard drive letter approach to all of your servers. So when something breaks, you know exactly where to go. And more importantly you save 15 minutes of clicking next.

For any shop which deploys SQL Server more than a couple times every few years, automating your installation process is a smart move.  Even if you rarely deploy, the consistency benefits make it worthwhile.

Shrinking TempDB

Tara Kizer shows how to shrink a recalcitrant tempdb:

I came across this solution recently when I had to shrink tempdb. I tried shrinking each of the 8 data files plus CHECKPOINTs, repeatedly. It would not budge. I almost threw in the towel and emailed out that the space issue would be fixed during our next patching window, but then I found David Levy’s reply. DBCC FREEPROCCACHE worked like a charm.

Word of warning:  understand what FREEPROCCACHE does before running it.  In an emergency like the scenario Tara describes, the benefit outweighs the cost, but do be aware that there is a cost.

Max Server Memory Matrix

Randolph West has a new tool:

After many months of confusing myself with mental arithmetic, I decided to create a straightforward list based on Jonathan’s algorithm, in 4 GB increments, for my own reference. I would like to share this with you today.

You can bask in the glory that is my Max Server Memory Matrix athttps://bornsql.ca/memory/.

It’s a nice lookup table, so thanks to Randolph for putting that together.

Check Endpoint Security

Erik Darling ran into an issue with endpoint security while setting up mirroring:

This is the error text:

The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://ORACLEDB.darling.com:5022’. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

SUPER SLEUTH

Alright, that’s silly. I used the GUI. Instead of going to bed I’ll spend some time checking all my VM network settings. BRB.

I’m back. They were all correct. I could ping and telnet and set up linked servers and RDP. What in the name of Shub-Niggurath is going on with this thing?

These things always happen right before bed, right before the big meeting, right before lunch.  They never happen on a slow Tuesday afternoon, it seems…

Multi-Site Availability Groups

Derik Hammer discusses spanning an Availability Group across multiple sites:

In the architecture above, replica A and B are in the primary data center while replica C and D are in the disaster recovery (DR) site. Like the previous architecture, the disks are displayed as local but the most important part is that they are physically separate. SANs are wonderful systems with a lot of redundancy but they can also be a single point of failure. Keep your Availability Group disks separate.

This is a nice architectural overview.  Once the series is done, it looks like it’ll be a good resource to discuss high availability and disaster recovery with management and show the options and trade-offs.

Startup Stored Procedures

Kenneth Fisher describes startup stored procedures:

sp_procoption is a system stored procedure that lets us change the options on a stored procedure and in this case set it to run on startup. Note: The account that is running SQL Server needs to have permissions to start the Agent service. (Or do whatever your startup stored procedure does.) You can have as many stored procedures running on startup as you want but remember the more you have the longer it’s going to take for your instance to start.

There are a few uses cases in which startup stored procedures can be useful, but my reservation about them is similar to my reservation about triggers:  it’s not apparent to people that a startup stored procedure is in place, so if there is a problem with it, troubleshooting might be harder than normal without good documentation.

Widening Identity Columns

Aaron Bertrand looks at converting an identity integer into an identity bigint:

This is a very disruptive change to the structure of the table, obviously. (And an interesting side observation: the physical order of the columns, RowID and filler, have been flipped on the page.) Reserved space jumps from 136 KB to 264 KB, and average fragmentation bumps up modestly from 33.3% to 40%. This space does not get recovered by a rebuild, online or not, or a reorg, and – as we’ll see shortly – this is not because the table is too small to benefit.

Note: this is true even in the most recent builds of SQL Server 2016 – while more and more operations like this have been improved to become metadata-only operations in modern versions, this one hasn’t been fixed yet, though clearly it could be – again, especially in the case where the column is an IDENTITY column, which can’t be updated by definition.

Read the whole thing.  The clustered key scenario (which will be later in the series) is a bit more interesting to me, as that would be a more common use case for identity values.

System Views

Robert Sheldon has an introductory-level article on the various system views available within SQL Server:

System views are divided into categories that each serve a specific purpose. The most extensive category is the one that contains catalog views. Catalog views let you retrieve information about a wide range of system and database components—from table columns and data types to server-wide configurations.

Information schema views are similar to some of the catalog views in that they provide access to metadata that describes database objects such as tables, columns, domains, and check constraints. However, information schema views conform to the ANSI standard, whereas catalog views are specific to SQL Server.

In contrast to either of these types of views, dynamic management views return server state data that can be used to monitor and fine-tune a SQL Server instance and its databases. Like catalog views, dynamic management views are specific to SQL Server.

One of the best things the authors of SQL did was require that metadata management be in the same language:  you write SQL code to query metadata the same as if it were normal data.

Categories

July 2019
MTWTFSS
« Jun  
1234567
891011121314
15161718192021
22232425262728
293031