Category: Administration

Samir Behara takes us through a few fallacies with distributed computing:

The network is reliable
Service calls made over the network might fail. There can be congestion in network or power failure impacting your systems. The request might reach the destination service but it might fail to send the response back to the primary service. The data might get corrupted or lost during transmission over the wire. While architecting distributed cloud applications, you should assume that these type of network failures will happen and design your applications for resiliency.

To handle this scenario, you should implement automatic retries in your code when such a network error occurs. Say one of your services is not able to establish a connection because of a network issue, you can implement retry logic to automatically re-establish the connection.

There are some very good points in here.

Comments closed

Pamela Mooney shows how you can find three-part or four-part naming on a SQL Server instance:

The script below searches the metadata for views, sprocs and functions for occurrences of 3 and 4 part names.  Three-part names consist of databasename.schemaname.objectname, and four-part names consist of servername.databasename.schemaname.objectname. Because the code searches metadata, it isn’t always perfect.  If your comments mention a servername followed by a period, for example, it will be caught.  Nevertheless, it’s a great place to begin looking, and a real help in getting rid of problems before they really bite you.

Click through for the script.

Comments closed

Randolph West has a proposal for default max server memory on a SQL Server instance:

As noted in the previous post in this series, memory in SQL Server is generally divided between query plans in the plan cache, and data in the buffer pool (other uses for memory in SQL Server are listed later in this post).

The official documentation tells us:
[T]he default setting for max server memory is 2,147,483,647 megabytes (MB).

Look carefully at that number. It’s 2 billion megabytes. In other words, we might think of it as either 2 million gigabytes, 2,048 terabytes, or 2 petabytes.

Randolph is writing this like we don’t all have multiple petabytes of RAM on each machine.

Comments closed

Max Vernon shows how you can automatically expand log files to optimize VLF counts:

SQL Server Database Log file expansion can be fairly tedious if you need to make the log bigger in many reasonably-sized growth increments. It can be tedious because you may need to write and execute a large number of ALTER DATABASE ... MODIFY FILE ... commands.

The following code automatically grows a SQL Server Database log file, using the size and growth increments you configure in the script. If you set the @DebugOnly flag to 1, the script will only print the commands required, instead of executing them. This allows you to see what exactly will be executed ahead of time. Alternately, you could copy-and-paste the commands into a query window and execute them one-by-one.

Click through for that code.

Comments closed

Erik Darling looks at one of my favorite sp_WhoIsActive features:

Using sp_WhoIsActive in a slightly different way, we can see what a query has been up to over a duration of our choosing.

The delta columns are what happened over the span of time we pick. The columns that come back normally are cumulative to where the query is at now.

Click through to see what it does and how you might benefit from it.

Comments closed

Jeff Mlakar talks about a topic I like—dropping lots and lots of stuff:

Let’s assume that you have lots of tables that need to be dropped according to some criteria. Trying to do them all at once isn’t a good idea. Even with a powerful server it will either take forever or simply never finish.

For example – you may have millions of tables in sys.tables or millions of indexes you need to drop. SQL Server won’t process them well if you try to run it as one big statement.

I’ve never had millions of tables or millions of indexes to drop and now I am jealous. Regardless, Jeff has two techniques for us when you have a lot of work to do. And if you do need to figure out key dependencies, I have a script for that.

Comments closed

John Morehouse takes us through a venerable file copying tool for Windows:

Robocopy has been around for years within the Microsoft eco-system and it is highly versatile.  However, until recently, it wasn’t a tool that I was versed at and frankly, hardly used it.  Over the past year or so, however, I have found myself using it more and more as a solution to for file movement when needed.

Essentially, robocopy will move files or even directories from one location to another.  It will copy permissions without any additional coding, and it will only copy the files that do not exist within the destination.  This is useful in that you do not have to explicitly account for files that might already exist at the destination.  It handles it for you.

Read on to see more, as well as a demo of RoboCopy in action.

Comments closed