Microsoft SQL Server Tips & Tricks

How do I map a differential/transaction log backup to its full backup without using MSDB?

HarshDeep_Singh — Wed, 22 May 2013 15:38:26 GMT

If you have multiple full, differential and transaction log backups, there are chances you may end up facing the error below during the restore process. “ This differential backup cannot be restored because the database has not been restored to the correct...(read more)

Change Data Capture gets disabled when you break mirroring and bring the database online

HarshDeep_Singh — Wed, 15 May 2013 07:55:16 GMT

Issue ================ Recently we encountered an issue where customer performed a planned failover for a mirrored database and after the failover the new principal went into suspect mode because of some reason (which is outside the scope...(read more)

Do NOT delete files from the Windows Installer folder

HarshDeep_Singh — Tue, 30 Apr 2013 10:49:02 GMT

C:\windows\Installer is not a temporary folder and files in it should not be deleted. If you do it on machines on which you have SQL Server installed you may have to rebuild the operating system and reinstall SQL Server. The Windows Installer Cache...(read more)

How to monitor deadlock using extended events in SQL Server 2008 and later

HarshDeep_Singh — Sat, 27 Apr 2013 05:32:00 GMT

Before I talk about how to monitor the deadlocks let us have a small brief what is deadlock and how we were handling them in the previous versions of SQL Server. Deadlock : A deadlock occurs when there is a cyclic dependency between two or more...(read more)

Ventured into BULK INSERTS

HarshDeep_Singh — Wed, 13 Mar 2013 20:01:57 GMT

Recently, whilst working with bulk insert queries I came across an unexpected result set which led to some more research. Therefore, following facts/tips/tricks/workarounds might help you: If you execute the following code for example: NOTE...(read more)

How to Login to SQL Server with NT AUTHORITY\SYSTEM using PSExec Tool

HarshDeep_Singh — Tue, 18 Dec 2012 10:35:23 GMT

PSExec tool gives you an option to run a remote process or an application using System account, if SYSTEM account has permission on that application. Although there are many more features that are For example, if SQL Server has NT AUTHORITY\SYSTEM...(read more)

How to limit connections to SQL Server until all databases are ONLINE!

HarshDeep_Singh — Tue, 18 Dec 2012 10:32:00 GMT

Strange requirement, right? Recently, I got a peculiar case wherein the demand made by the customer was to make sure no application should be able to connect to SQL Server unless and until all the databases are ONLINE. The business requirement of this...(read more)

Why to look at "Wait Statistics" to troubleshoot SQL Performance

Harsh Chawla — Mon, 18 Jun 2012 13:36:17 GMT

Here we will discuss on Waits in the context of SQL Server: Highlights of this video blog :- 1. What is wait and why we need to focus on wait? 2. Lifecycle of a session 3. Analogy to explain waits 4. Types of Waits. 5. Where to see waits...(read more)

SQL server 2008 Filestream

Harsh Chawla — Mon, 18 Jun 2012 13:31:00 GMT

Filestream is a new feature in SQL server 2008, that allows storage and efficient access to BLOB data, using combination of SQL Server 2008 and NTFS file system. It provides an easy way of handling BLOB data with database transactional consistency...(read more)

Troubleshooting SQL server High CPU : Part 2

Harsh Chawla — Mon, 11 Jun 2012 13:55:00 GMT

In this blog we have explained the various causes for SQL to drive the processors i.e. high CPU and the steps to troubleshoot it. Possible causes that can lead to high CPU usage by SQL server:- 1.Inefficient Query Plans 2. Intra- query Parallelism...(read more)