Problems with sp_rename

renamedTable smallUnsupported or undocumented functions are not surprising in IT software. SQL Server also has some, for example very useful sp_who2. If it suddenly stops working, do not blame Microsoft - they have never encouraged you to use it. Leaving undocumented commands behind, there are also stored procedures that are well documented and supported but not recommended to use. One of them is sp_rename which I am writing about.

Script to shrink tempdb

shrinkIn general shrinking a database is easy. This operation is done when an administrator wants to make database files smaller. It can be even done with SQL Server Management Studio. It becomes a little more complex when the database is tempdb. It is a system database that stores different types of temporary objects. As a consequence, it cannot be easily shrinked to reduce occupied disk space. This post contains a script that allows for that.

 

What?! SELECT query caused a deadlock?

deadlockVideoDeadlocks. We all know a lot about them, right? A sample scenario is simple - one process (A) deadlocks with another one (B) when they acquire locks in a different order. A waits on B and B waits on A. One of them has to be killed to resolve the situation. Is that all? Not really. If you work with SQL Server you might have experienced a deadlock when one of the processes was executing a SELECT query and nothing else. It is surprising at the first glance, but actually it makes sense. Watch my video explanation.

portThe answer to the question which port is used by SQL Server? seems easy. By default it is 1433 but there are exceptions from this rule. If more than one SQL Server instance is needed on the same machine, the easiest option is to use named instances. Each of them work on a different port so only one can use 1433 or even neither of them! In this article, I will present a few methods of finding out which port is used by a particular SQL Server instance. They require different level of access and various tools so choose one that works for you.

SqlAndLinuxI don't think it was expected by many but after several years of strong link between SQL Server and Windows, impossible happened on March 7th, 2016 - Microsoft announced bringing SQL Server to Linux. It was not only announced, but a private preview was made available for testing. The preview proves that Microsoft really made some big steps to make it happen. Actually, those are not only steps, but the project is very advanced. Core relational database capabilities are already in the preview, availability features are going to be released in 2017.