How to recreate the msdb database in SQL Server 2005?

I’ve just spent a bunch time researching an answer to this question on the new disaster recovery forum because I couldn’t find any definitive info on how to do this in SQL Server 2005. I pieced together a method to do this on previous releases of SQL Server and tried it on one of the SQL Server 2005 instances… Read more

DROP IF EXISTS – new thing in SQL Server 2016

In SQL Server 2016 CTP3 objects can DIE (DROP IF EXISTS) Do you like to write following conditional DROP statements: IF OBJECT_ID(‘dbo.Product, ‘U’) IS NOT NULL DROP TABLE dbo.Product; IF EXISTS (SELECT * FROM sys.triggers WHERE name = ‘trProductInsert’) DROP TRIGGER trProductInsert I don’t like these, and if you also don’t like them, then you… Read more

CREATE OR ALTER – another great language enhancement in SQL Server 2016 SP1

We are happy to announce that SQL Server 2016 SP1 and SQL Server v.Next have new T-SQL language statement – CREATE [OR ALTER]. This statement combines CREATE and ALTER statements and creates object if it does not exist, or alter it if it is already there. CREATE OR ALTER can be applied on the following… Read more

Managing TempDB in SQL Server: TempDB Configuration

In my previous blogs, I described the types of objects in TempDB and how they are managed. I hope that it provided you with a good working knowledge of TempDB. Now the next question is how do I configure the TempDB for my production workload? In this context, there are three common questions as follows:… Read more

Fragmentation (part 1): What are records?

This blogging thing sucks you in, doesn’t it? Not content with having an ongoing series on disaster recovery and CHECKDB (with another 6 and 25 more posts planned respectively), I’m starting a new series on fragmentation. This will begin from first principles and work up, in approximately 18 posts over the next few months. The… Read more

Query Optimizer Additions in SQL Server 2016

In SQL Server 2016 we have introduced a number of new Query Optimizer improvements. This article summarizes some them and explains you can leverage the benefits of the new enhancements. Expect deep dive follow up articles for some of the enhancements. Here is the short list: Compatibility Level Guarantees Query Optimizer Improvements under Trace Flag… Read more

Lock Escalation in SQL2005

Lock Escalation:   If you consider the hierarchy of the objects in a SQL Server instance, at the top level you have the database, followed by schema, tables, table partitions, pages and then finally the individual rows. If you acquire a lock at higher level, it can cover more resources there by you consume fewer… Read more

SQLIOSim available for download

  There has already been a great deal of excitement over the release of SQLIOSim, and I know everyone is hungry for more details.  First the final cut of SQLIOSim is available for download at the Microsoft Download center.   http://download.microsoft.com/download/3/8/0/3804cb1c-a911-4d12-8525-e5780197e0b5/SQLIOSimX86.exe http://download.microsoft.com/download/6/5/2/65286f65-bff2-42b8-b0c9-87f117855069/sqliosimX64.exe http://download.microsoft.com/download/2/c/f/2cf8fb0a-a943-456b-9cf5-68ce426180a1/SQLIOSimIA64.exe    Inside the package you’ll find 2 executable files, SQLIOSim.exe and SQLIOSim.com.  The… Read more

Common bad advice around disaster recovery

I’ve just been itching to post this one. I’m doing a chalk-talk (AKA cabana session, this year AKA Technical Learning Centre session) at TechEd next Wednesday (8.30am 6/14) called ‘DBCC CHECKDB: Magic, Monsters, and Myths’. A lot of the material in this will be included in the mega-whitepaper on DBCC I’ll be writing over the summer, but… Read more

SQL Server in Windows Containers

Windows Containers are an isolated, resource controlled, and portable operating environment. An application inside a container can run without affecting the rest of the system and vice versa. This isolation makes SQL Server in a Windows Container ideal for Rapid Test Deployment scenarios as well as Continuous Integration Processes. This blog provides a step by… Read more