Sidd's SQL Solution

Today I’ll share a script, which I believe most of us will find very useful for their environment just like me. Blocking is very common event on a database server and in-fact blocking occurs almost all the time and fortunately most of them are not harmful (enough). But as we all know this normal occurring event can sometime cause huge issues and we should have some mechanism to track these harmful ones. Generally without third party assistance it is bit difficult to actually isolate blocking, which can be potentially harmful right now or in future and the default alerting options can flood inbox with too many mail alerts suppressing the one's which are relevant. Fortunately I’m able to ease the things a bit and was able to find a T-SQL way to track and send report of potential problem causing blocking and block chain processes and that too without flooding inbox with non-relevant alerts. -- ============================================= USE [master] GO SET AN

After some back to back problem driven posts, I thought to post something different, which should be equally tricky but more to fun and what’s better than a good T-SQL script. Recently in one of my projects I got a requirement to come up with a T-SQL script which can automatically reseed all tables on any given database if need be. Below is the result of this requirement. I've created a SP which can automatically check for current identity value of all the tables within a given database and can reseed it to new value. The SP takes three parameter viz. Database Name "@db_name (nvarchar(255))" , Print Bit "@print_bit (bit)" , Increment value "@incr_val (int)" . This will reseed the value of identity column of all the tables in a given database with new incremented value provided. The "@incr_val"  defaults to 0, which will reseed the identity column to start with immediate next value in the identity series defined. The "@print-bit&qu

Ok now this is same soup with different recipe and ingredients, which here means same type of trouble which we discussed in our last post but a very different approach for resolution as the problem is new. Just like last one, nobody wants to be in this situation but unlike the previous situation which we discussed, this one is more severe as it can occur any fine day causing things to go hay wire with people panicking and chasing what has happened. Why their application has suddenly not able to reach to database or why SQL Server is not working. Scenario Now, let me quickly explain the situation here first. This is the situation I recently faced where in one of my projects people start getting “ timeout ” or “ Cannot connect to <Instance> ” error. I quickly reached to the server and found that instance was not up. As it was a clustered server I checked the cluster logs and found that instance failed over before crashing (usual behavior). I tried again to bring the s

This is one of the (nightmare) situations in which no DBA want to get into, especially if it is your PROD SQL Server. But if you've already got yourself into one of such situations then you've come to right place. In this article we will see how to overcome one such situation where SQL Server instance doesn't come back or crashes immediately after starting the services in case you've just changed the location of your “TempDB” database. Test Scenario To start with we picked a test SQL Server Cluster and added few more “Tempdb” files to it in its default location which was in C: drive here.  Why Cluster? Because clusters are bit more complicated to troubleshoot and also took a while to get to it, so, I thought it is good if I go this way. We will however also cover steps for stand-alone server as well.  Once all the files have been added we used the following T-SQL statements to move them to new location. All the commands executed successfully

A good thing about "time" is that it also helps in adding more to our knowledge and knowledge is a powerful tool which helps us in implementing new things or may be see old things in new way. I recently revisited one of my old script which was for DB index maintenance and I've realized that it had become obsolete as technology and usage of the databases has changed over the years like now we are using more of high availability options, so this script should evolve as well. Moreover on certain SQL boxes it was not behaving as I intend it to be, so one fine day I sat down and decided to update the same. As earlier this script can choose the type of index maintenance to be performed i.e. "Rebuilding" or "Reorganising" of the index after calculating the fragmentation level of an index. However this time around I've wrapped up the script in a stored procedure as on some boxes putting the script directly in the job was throwing "Syntax error&q