Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in - 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 His hobbies includes Running and Travelling. You can follow him on Twitter @SqlServerZest

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, 389 reads

Posted in - 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, 301 reads

Posted in - 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, 513 reads

Posted in - 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

1 comments, 1,127 reads

Posted in - 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

4 comments, 464 reads

Posted in - 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

10 comments, 966 reads

Posted in - 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

10 comments, 752 reads

Posted in - 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

3 comments, 819 reads

Posted in - 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

2 comments, 634 reads

Posted in - 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

10 comments, 615 reads

Posted in - SQL in simple style on 28 June 2013

SQL Server – How to Insert Explicit Values Into An Identity Column

By default, SQL Server automatically assigns a value to the Identity Column for each new row inserted into the table. However, if desired, we can insert explicit values into the Identity column when IDENTITY_INSERT property is set to ON. Let us take an example from AdventureWorks2012 database. Table [HumanResources].[Department] is…

Read more

0 comments, 275 reads

Posted in - SQL in simple style on 2 June 2013

SQL Server – Get the Default Data File and Log File Location using T-SQL Query

In SQL Serve 2012, we can get the default data file and log file locations using a simple T-SQL query as below: SELECT SERVERPROPERTY('INSTANCEDEFAULTDATAPATH') AS [Default_Data_path] ,SERVERPROPERTY('INSTANCEDEFAULTLOGPATH') AS [Default_log_path] GO Sample Output: These parameters INSTANCEDEFAULTDATAPATH & INSTANCEDEFAULTLOGPATH are new in SQL Server 2012 and are not documented yet. Usually undocumented…

Read more

0 comments, 353 reads

Posted in - SQL in simple style on 23 May 2013

SQL Server – Does the SQL Agent Service Account Must be a SysAdmin?

As of today (5/14/2013) this MSDN article says “SQL Server Agent service startup account must be a member of the SQL Server sysadmin fixed server role” However, in one of our environments (SQL 2012) I have seen, just a public fixed server role is good enough to start the SQL…

Read more

0 comments, 361 reads

Posted in - SQL in simple style on 14 May 2013

SQL Server – How to Best Remove Extra TempDB Data Files and Log Files

One of the Microsoft’s recommendation for optimizing the tempDB performance is to make each tempdb data file the same size. Today, on one of our servers, I noticed there are 13 data files with different sizes as shown in the below screenshot: My target here is to configure tempdb with…

Read more

9 comments, 409 reads

Posted in - SQL in simple style on 6 May 2013

SQL Server – How to Search/Query the SQL Server Error Log

Often times I keep checking the error log to make sure if everything is running fine. Here is an undocumented stored procedure that really helps simplify our task of searching the error log. Exec XP_ReadErrorLog GO This proc has 4 parameters: Parameter 1 (int), is the number of the log…

Read more

0 comments, 183 reads

Posted in - SQL in simple style on 25 March 2013

SQL Server – Database Engine Error Severities in Brief

When an error is raised by the SQL Server Database Engine, we usually see the Severity Level as in the below example: Msg 5184, Level 16, State 2, Line 1 The severity level of the error indicates the type of problem encountered by SQL Server. Hence looking at the Level…

Read more

0 comments, 176 reads

Posted in - SQL in simple style on 2 November 2012

SQL Server – Transaction Log File is Full; How to Shrink the Transaction Log

Here is a typical scenario every SQL Server DBA faces:  Transaction Log grows unexpectedly or becomes full!! So, do I need to increase the Log size? Fortunately we have a better solution – SHRINK the log file! Typically log truncation frees space in the log file for reuse by the…

Read more

2 comments, 219 reads

Posted in - SQL in simple style on 12 October 2012

SQL Server – Msg 2555, Level 16, State 1, Line 1 – Error DBCC SHRINKFILE with EMPTYFILE option

When tried to empty one of the tempdb data file using the below T-SQL command: (My goal here is to REMOVE the file using ALTER DATABASE command followed by. For more information on removing data/log files, please refer to this post How to Best Remove Extra TempDB Data Files and…

Read more

3 comments, 449 reads

Posted in - SQL in simple style on 7 October 2012

SQL Server – Understanding ‘Agent XPs’ Server Configuration Option

Today we got an escalation where on one of the servers ‘Agent XPs’ was in a disabled state. And when looked at SQL Server Agent node, it is displayed in Red which basically indicates SQL Agent Service is not running, however when checked the Agent Services are actually running, which…

Read more

0 comments, 220 reads

Posted in - SQL in simple style on 19 September 2012

Newer posts