Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SqlServerZest.com - SQL in simple style

Suresh Raavi is a database professional, with a zest for SQL Server specializing in Administration and performance tuning. Presently, he is a Sr. SQL Server DBA at the Microsoft Products and Services IT SQL team, taking part in various challenging projects, focusing mainly on performance tuning, database development, high availability and database design. He is passionate about database technologies and "data" in general. He has earned the MCITP Database Administrator 2008, and holds a Master of Science degree in Engineering. He provides insights from the field on his blog at http://SqlServerZest.com. His hobbies includes Running and Travelling. You can follow him on Twitter @SqlServerZest

SQL Server – Aborted Online Index Operation & Observed Elevated Values for PWAIT_MD_RELATION_CACHE and MD_LAZYCACHE_RWLOCK Wait Types

Last week on one of our production servers, we’ve encountered a strange issue which is relatively new (only applicable for SQL Server 2012 and 2014 versions). Based on the symptoms observed and doing a quick Bing search found we encountered a product bug! I observed multiple SPIDs, around 300+ running…

Read more

0 comments, 92 reads

Posted in SqlServerZest.com - SQL in simple style on 12 August 2014

SQL Server – Script to Identify and Update Database Auto-Growth Setting

An auto-growth event is the process by which the SQL Server engine expands the size of a database file when it runs out of space. The amount by which a database file should grow is determined by the value that is set in the SQL configuration. While every organization has…

Read more

1 comments, 150 reads

Posted in SqlServerZest.com - SQL in simple style on 25 June 2014

SQL Server – Script to Configure TempDB Files per Number of Logical Processors

In order to reduce Tempdb contention one of  the best practices is to maintain multiple sized Tempdb data files, matching the number of processors and up to a maximum of 8. In this post I will show you T-SQL script to identify current Tempdb configuration and number of logical processors…

Read more

0 comments, 195 reads

Posted in SqlServerZest.com - SQL in simple style on 24 June 2014

SQL Server – How To Find SQL Virtual Name From Host Name in SQL Failover Clustering Using PowerShell

Often times it is required to find out the SQL name from the host names in a clustered environment so you can use it to connect through SSMS. There are many ways you can achieve this, however I am showing 2 methods here. Method 1 is the most common way…

Read more

0 comments, 195 reads

Posted in SqlServerZest.com - SQL in simple style on 14 April 2014

SQL Server – Using Tools to Analyze Query Performance

Here is the session I gave for the techies at Microsoft about in-built tools available in SQL Server to analyze a query performance. This is a bit lengthy, but covers ALL out of the box tools in SQL Server to get query metrics.

Read more

0 comments, 192 reads

Posted in SqlServerZest.com - SQL in simple style on 11 February 2014

SQL Server – Deploying Multiple SSIS Packages into SQL Server Using Command Prompt Utility – DTUTIL

Though there are several ways for importing SSIS packages into SQL Server either by creating a stored procedure or using a SSIS package itself, I find this simple code of line much easier and handy to work with.  It utilizes the “dtutil.exe” application which is installed by default with SQL…

Read more

1 comments, 289 reads

Posted in SqlServerZest.com - SQL in simple style on 4 November 2013

SQL Server – Understanding Allocation Units – In Row Data, LOB Data & Row Overflow Data

Before we get started on this topic, here is a quick fact..in SQL Server 2000, there used to be hard limit on the data that can be stored in a single row, which is 8,060 bytes. So, if the data exceeds this limit, the update or insert operation would fail!…

Read more

2 comments, 297 reads

Posted in SqlServerZest.com - SQL in simple style on 14 October 2013

SQL Server – Restoring a TDE Encrypted Database to a Different Server

In this article I will show you how to restore a database that is encrypted with Transparent Data Encryption (TDE) on to a different server. Let’s use the same database ‘TDE_Test’ that we have created in the previous post HERE Restoring a database to a different SQL Instance is usually…

Read more

0 comments, 298 reads

Posted in SqlServerZest.com - SQL in simple style on 3 October 2013

SQL Server –Encrypting and Securing Native Backups Using Transparent Data Encryption (TDE)

Being part of the database engineering team, we were given a task to setup disaster recovery program for some of our SQL Servers hosting critical applications. After a few discussions, we have planned to make use of log shipping to sync data between the two data centers. This requires us…

Read more

2 comments, 256 reads

Posted in SqlServerZest.com - SQL in simple style on 3 October 2013

SQL Server – How to Copy or Export an SSIS Package Using Command Prompt Utility – DTUTIL

The command prompt utility dtutil can be very handy when we want to quickly export an SSIS package from either file system to msdb or vice-versa. For a quick demo, I created a package called “ProductPrice” uder the file system C:\packages, as shown in the screenshot below Also, I created…

Read more

2 comments, 686 reads

Posted in SqlServerZest.com - SQL in simple style on 2 October 2013

SQL Server – How to Add More Than One Column as a Primary Key

Sometimes you might want to add more than one column as primary key. For example, if you have three columns named Last Name, First Name and Address and  there can be duplicate Last Names  or duplicate First Names but can never have duplicates in Last Name, First Name  and Address…

Read more

0 comments, 245 reads

Posted in SqlServerZest.com - SQL in simple style on 17 September 2013

SQL Server – How to Create Registered Servers to Easily Manage and Query Multiple Instances at a Time

Registered servers is a great way to manage, categorize and access the SQL servers through SSMS and also one of the overlooked aspects. Registered servers are handy when you want to have all your servers at one place, sorted and categorized in various ways. With registered servers all your instances…

Read more

1 comments, 196 reads

Posted in SqlServerZest.com - SQL in simple style on 16 September 2013

SQL Server – Find all the LOB Data Type Columns in a Database Using T-SQL Script

Today morning when I was working on a huge database containing lots of LOB data, I was required to know what tables have LOB data, and the list of LOB columns by table name along with the data type. I found that starting SQL Server 2005, we can easily retrieve…

Read more

0 comments, 336 reads

Posted in SqlServerZest.com - SQL in simple style on 12 September 2013

SQL Server – How to Perform Striped Database Backup & Restore Using T-SQL Command

Even though there are more pros than cons, Striping database backups are often overlooked by many DBAs. Based on my observations in our environment, striping can significantly benefit larger database backups (~500+ GB). As shown in the picture below, striping is nothing but splitting one backup file to multiple backup…

Read more

0 comments, 893 reads

Posted in SqlServerZest.com - SQL in simple style on 3 September 2013

SQL Server – Why is the ‘sa’ Login Account Disabled & How to Enable ‘sa’ Login Account

Ever noticed and wondered why the well-known SQL Server system administrator (sa) login is in a disabled state? The reason is simple, sa login account is disabled out of the box (by default) in Windows Authentication mode. You have to enable manually to use it. On the other hand, if…

Read more

0 comments, 287 reads

Posted in SqlServerZest.com - SQL in simple style on 20 August 2013

SQL Server – How to Move Database Files – Data File and Log File – From One Drive Location to the Other

Most of the DBAs at some point of time in their career should have faced at-least one of the below situation(s): 1. The disk/SAN where the database files are located is going to be replaced 2. Disk is full and no more free space available 3. Data files and/or log…

Read more

4 comments, 620 reads

Posted in SqlServerZest.com - SQL in simple style on 10 August 2013

SQL Server – Automating Backup Files Cleanup Task Using Maintenance Plans

Cleaning/deleting the old backup files is equally important as taking the backups. Doing so we can (i) Avoid overhead cost for the storage space, and (ii) Ensure and retain enough space on the disk for the next successful backup Using ‘Maintenance Plans’ this task will be a cake walk for…

Read more

4 comments, 583 reads

Posted in SqlServerZest.com - SQL in simple style on 7 August 2013

SQL Server – Get all Login Accounts Using T-SQL Query – SQL Logins, Windows Logins, Windows Groups

Earlier today I was required to pull the list of all SQL Login Accounts, Windows Login Accounts and Windows Group Login Accounts (basically all the Logins along with the Account Type of the Login) on one of the SQL Server instance where there are close to a hundred Login Accounts…

Read more

0 comments, 519 reads

Posted in SqlServerZest.com - SQL in simple style on 6 August 2013

SQL Server – How to Download and Install the ‘right’ AdventureWorks Sample Database

One of the first things I did when I began my journey with SQL Server was installing a sample database. Though it seems to be very simple now, but not really when I just started leaning database. So, this post will be helpful for friends wanting to start their career…

Read more

4 comments, 347 reads

Posted in SqlServerZest.com - SQL in simple style on 31 July 2013

SQL Server – Understanding ‘sp_spaceused’ Results for Database Size Information

From a recent conversation with a customer, I noticed even some of the experienced DBAs are not very clear about the terms “unallocated space” and “unused space” and by doing a quick Bing search, found there are many SQL friends out there trying to get a clear correlation between these…

Read more

3 comments, 345 reads

Posted in SqlServerZest.com - SQL in simple style on 28 June 2013

Older posts