Category: Tools and Scripts

Tools and Scripts

How to Update @@SERVERNAME to Actual Machine Name?

If, for whatever reason, the Windows Computer Name is changed after SQL Server is already installed, then @@SERVERNAME and the information in sys.servers would not automatically reflect the change (sysservers in older SQL versions).

This means that @@SERVERNAME contains the incorrect value for the machine name.

Sometimes, and especially in production environments, the value in that global variable is important and is used as part of business processes.

And if @@SERVERNAME doesn’t reflect the actual server name, it could cause problems.

Alternatively, it’s possible (and maybe even best) to use the SERVERPROPERTY function instead to get the actual server name, or machine name, or instance name. The information available through this function should be up-to-date even after you rename the Windows Computer Name. Continue reading How to Update @@SERVERNAME to Actual Machine Name?

Eitan BluminLeave a comment
Tools and Scripts

Automatically Enable or Disable Jobs Based on HADR Role

Imagine the following: You have a couple or more SQL Servers with some sort of High Availability solution between them (AlwaysOn Availability Groups, Database Mirroring, etc.). You also have a bunch of scheduled jobs which you need to be run on the Primary server.

But wait…. How would you make these jobs run only on the Primary server? After all, if they try to run on the Secondary, they would fail (whether because the database is inaccessible or because it’s read-only). Additionally, you would need to consider the possibility of a failover where the Primary and Secondary servers would switch roles.

This means that you would have to, first, create these jobs on both servers, and implement some sort of mechanism that would detect, for each job, whether the instance it’s being executed at is currently the Primary.

There are a few ways to go about doing this.

[For the sake of this article, let’s ignore the fact that most people don’t even think about it, and leave all of their important jobs on the Primary server only, while ignoring the risks of what would happen when their Primary server crashes and fails over to the Secondary] Continue reading Automatically Enable or Disable Jobs Based on HADR Role

Eitan BluminLeave a comment
Time to re-align!
Tools and Scripts

Re-align Identity Last Value to Actual Max Value

Sometimes, when you have a table with an IDENTITY column, there could be scenarios in which weird “gaps” are created between different IDs.

There can be several possible causes for this:

1. The most obvious cause is when rows are deleted from the table. If many rows are deleted from a table with an IDENTITY column, it’s obviously expected that nothing would “fill” up the “gaps” that these rows have left. IDENTITY values only go one way, they don’t automatically re-fill deleted values retroactively.

2. When a ROLLBACK is performed on a transaction after inserting into a table with an IDENTITY column, the increase in the IDENTITY value is NOT rolled back. So even if the row wasn’t actually inserted, the IDENTITY value is still increased. This can happen both with single-row INSERT commands, as well as BULK insertions. So if, for whatever reason, a lot of insertions are rolled-back in your database, you may see a lot of these “gaps”.

3. There’s a special mechanism, specifically in SQL Server 2012, which “pre-allocates” IDENTITY values for a table, and it does this in memory. So when the SQL service is restarted, next time you insert a value into the table, the IDENTITY value would “jump” by 1000 or 10000 (depending on the column data type). This happens in SQL 2012 only, and was reportedly fixed in later versions. More info about it in this blog post by Ahasan Habib. Continue reading Re-align Identity Last Value to Actual Max Value

Eitan Blumin2 Comments
Sir, please step aside and check your constraints
Tools and Scripts

Find and fix untrusted Foreign Keys in all databases

Not Trusted Foreign Key Constraints are such for which integrity of the underlying data is not guaranteed. This most commonly happens when you perform Bulk Insert into a table that has a FK constraint, or when you disable a FK constraint.

Having not trusted foreign keys can be dangerous for data integrity, because it’s possible to have invalid data in your table (despite the existence of the foreign key). Continue reading Find and fix untrusted Foreign Keys in all databases

Eitan BluminLeave a comment
Can't have a post about triggers without a gun image
Tools and Scripts

Advanced Service Broker Sample: Asynchronous Triggers

In my previous post, Advanced Service Broker Sample: Multi-Threading, I showed a rather advanced scenario where we can implement a multi-threading solution inside the SQL Server database engine.

In this post, I hope to show a simpler scenario of using Service Broker in SQL Server.
This time, I’ll start by handing out the API script itself and give brief explanation on how to use it.

Then, if you’re interested, you may continue reading for further explanations. Continue reading Advanced Service Broker Sample: Asynchronous Triggers

Eitan BluminLeave a comment
Multi-Threading
Tools and Scripts

Advanced Service Broker Sample: Multi-Threading

I managed to encounter several times a scenario in which an application needed to execute several queries independent of each other, and then group them together somehow. However, each of these queries takes a while to execute.

One might wonder: If only we could use some kind of multi-threading mechanism to execute them in parallel, and then the total running time will be that of the slowest query only and not the total of them.

Well, it just so happens that our first sample will show how you can implement “multi-threading” in SQL Server using Service Broker queues!

Continue reading Advanced Service Broker Sample: Multi-Threading

Eitan BluminLeave a comment
Obligatory Orphan-Related Imagery
Tools and Scripts

T-SQL Script to Fix Orphaned DB Users Easily

“Orphaned Users” is a common issue in SQL Server where a Database User is no longer associated with its relevant Server Login.

This often happens when the Server Login is deleted (even if it’s recreated later), or when the database is moved or restored to a different SQL Server.

The symptoms that you can expect from such a scenario is inability to use such database users. Specifically, using the login to connect to the database that it’s supposed to be mapped to. It just doesn’t work. Continue reading T-SQL Script to Fix Orphaned DB Users Easily

Eitan Blumin1 Comment