Best Practice No.25

Keep your TempDB Files & all other database files on separate disks.

SQL Tip-5 | Lightning Fast way to find errors in SQL Server Error Logs

In my first SQL Tip Don’t forget to recycle your SQLServer ErrorLog I listed some common problems you may face if you are not recycling your errorlogs timely. However, I do understand the criticality and importance of Error Log that’s the reason I also suggested you to increase the retention of your Error Logs in another tip Do you know you can increase number of archived Errorlogs.

But even after recycling your error logs frequently you may end up with such a large Error Log that it becomes tiresome finding errors or other text. Specially if you have many DBs and Frequency of Transaction Log backup is very high. In this tip I’ll talk about a method which can certainly help you find any error or text out of your errorlog without forcing you to read the complete errorlog.

let’s find the needle in a Haystack Winking smile

SQL Server provides a Stored Procedure to read Error Logs; sp_readerrorlog.

By executing just the stored procedure without any parameter, it will give you the current SQL Server ErrorLog as an output.

Let’s say you want to read the previous error log, all you need to do is provide the first parameter i.e., Index number of Error Log. So if you want to read the Previous errorlog you can provide 1 as an input parameter on first position. <we are passing 1 for the previous errorlog because we are providing index number which is supplied by SQL Engine in reverse order starting from 0>

sp_readerrorlog 1

The Second positional input parameter is about log type:
a. 1 = SQL Server ErrorLog (Default Value)
b. 2 = SQL Agent ErrorLog

so if you ever want to read SQL Agent ErrorLog all you need to do is provide the second input parameter as 2.

Now the third and fourth parameters are search strings. If you want to search a text let’s say you want to know what all errors have been encountered since last rollover of Error Log;

sp_readerrorlog 1,1,’error’

This query will find your errorlog for the word “error” and return all rows having this word. You can further filter the rows by giving fourth parameter. If the fourth parameter is also specified this stored procedure will search the rows having both the values and return the output.

sp_readerrorlog 3,1, ‘error’, ‘18745’

You can specify any type of value in third\fourth parameter it could be alphanumeric or just numeric also.

Happy Learning 🙂

Sarabpreet Singh Anand
Subscribe now to get latest Tips in your Inbox

SQL Tip-4 | Do you know you can increase number of archived Errorlogs

By default, SQL Server keeps only seven error logs; Errorlog and Errorlog.1 to Errorlog.6. The name of the current, most recent log is Errorlog with no extension. In my SQL Tip-1 I advised why you should recycle your errorlog on frequent basis and how. But as we know when you recycle errorlogs SQL Server makes an archive of current errorlog and push it down the stack one after another and keep only 7 total error logs (1 current and 6 archived) – in this process we’ll very soon reach a limit of 6 Archive errorlogs retention and will start loosing error logs.

In some cases (for instance Tier1 DB Servers), you may want to keep more than six archived error logs so that you can read the past entries. For all those servers you can very well increase this retention policy. You can configure the retention limit between 6 to 99. Here is a step by step How to blog for the same.

Since the retention limit is being maintained in a registry key, In Order to change the Archive error log Retention limit on your SQL Server you have two options:

  1. Using command
  2. Using SSMS GUI Window

Changing Retention limit in Registry using command: There are n number of ways to change a key value in Registry but since we are SQL Server Admins\Dev’s let’s utilize the inbuilt stored procedure to change registry entries from SQL itself:

USE [master]
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’NumErrorLogs’, REG_DWORD, 12

In the above code, we are changing the retention value to 12. That means from now onwards SQL Server will maintain 13 total errorlogs (1 current and 12 archived)

Changing Retention limit in Registry using SSMS GUI Window: You can also change this retention value using GUI Window very easily.  Let’s take a look at the below screenshot of one of the servers where the Retention was set to 6 (By Default Value)



Now Right click on SQL Server Logs & choose configure



This is the default screen you’ll get if you’ve never changed this value.



Before you can change the limit you need to select the checkbox & once it is selected you can now provide any number between the range of 6 to 99.



Screenshot of one of the servers where I changed the limit and cycled error logs to see whether it is capable of holding higher number of archived logs or not.


I hope this helped you, and now you can easily change the retention if required.
Happy Learning 🙂

Sarabpreet Singh Anand
Subscribe now to get latest Tips in your Inbox

Best Practice No.24

Disable “xp_cmdshell” where it is not required. If this is required for some admin tasks make sure you disable this post completion of the activity to reduce surface area.

SQL Tip-3 | Don’t Shrink your Database

You should avoid shrinking your database until and unless it becomes unmanageable. Shrinking is a Resource intensive task, Moreover it introduces high degree of fragmentation which affects performance.

Let’s say you shrink a Database, Now if the data is growing in your database the data\log file will reserve more space by auto-growth (if configured) to meet the new requirements. So it does the same work again, Moreover your data is now fragmented which needs to be fixed by doing maintenance activity.

So, The best way to keep free space manageable in your data\log files is to configure Auto-Growth setting with fixed numbers (size) rather than using Percentage (%) value, this will make the auto-growth predictable and ensure that the growth will not go beyond a certain limit.

Happy Learning 🙂

Sarabpreet Singh Anand
Subscribe now to get latest Tips in your Inbox

Best Practice No.23

set start-up mode of Standalone SQL Services to start automatically.

Virtual Tech Conference | 1st August 2013 | 4PM till 10PM

Here is another chance to learn from MVPs. Microsoft & MVPs present VTC – Virtual Tech Conference – 12 Hours of non stop learning.

Register Today & Block your Seat, here are the URLs and the complete Agenda.

To Register for
IT Pro:

VTC image




Dev Track Sessions

4:00PM IST/6:30AM EST

Karthikeyan Anbarasan

VTC – Create Cross Platform apps effectively with Portable Class Libraries
Session Agenda:

· Cross platform app development

· Portable Class Library

· MVVM pattern for WP

5:00PM IST/7:30AM EST

Niraj Bhatt

VTC – Introduction to Windows Azure Active Directory

Session Agenda:

· Windows Azure


· Mobile Services

· Active Directory

· Identity

6:00PM IST/8:30AM EST

Vishnu Tiwari

VTC – Integrating On-premise SQL Server with Salesforce using BizTalk Server 2013

Session Agenda:

· Introduction to BizTalk 2013

· Communication between On-premise and cloud

· New BizTalk 2013 adapters to support On-premise and cloud integration

· Context for Salesforce and On-premise SQL Integration

· Unearthing various integration components

7:00PM IST/9:30AM EST

Nauzad Kapadia

VTC – OAuth and the App security model in SharePoint 2013

Session Agenda:

· Introduction to new Security Model for Apps

· OAuth 2.0 support in SharePoint 2013

· S2S High Trust protocol

8:00PM IST/10:30AM EST

Ashutosh Singh

VTC – Enriching SharePoint Search using FAST

Session Agenda:

· Overview of Search in SharePoint

· Overview of FAST 4 SharePoint

· Pipeline Extensibility for FAST and its advantages

· SharePoint 2013 & Enrichment of search results

9:00PM IST/11:30AM EST

Dr Nitin Paranjape

VTC – What every developer should know about Office

Session Agenda:

· Code Compatibility

· Object model nuances

· Understanding Excel List Object

· Methods available for dumping raw data for user level usage

· How to prevent rewriting existing features





IT Pro Track Sessions

4:00PM IST/6:30AM EST

Shantanu Kaushik

VTC – Preparing and Deploying Windows 8

Session Agenda:

· Application Compatibility

· User state migration

· Pre-install Environment

· Volume activation

· Deployment Options

· Performance and assessment toolkit

5:00PM IST/7:30AM EST

Geetesh Bajaj

VTC – Working with Flowcharts in Microsoft Office

Session Agenda:

· Working with Flowcharts

· Why Word, Excel, and PowerPoint?

· Flowchart Symbols

· Working with Connectors

· Quick Flowcharts with SmartArt

6:00PM IST/8:30AM EST

Sundararajan Narasiman

VTC – SharePoint 2013 App Model – SharePoint Hosted Apps

Session Agenda:

· SharePoint 2013 App Model

· SharePoint Hosted Apps

· Auto Hosted Apps

7:00PM IST/9:30AM EST

Ravikanth C

VTC – Desired State Configuration in PowerShell 4.0

Session Agenda: This is a demo oriented session. In this session, we will look at an introduction to DSC and see how the system administrators and DevOps teams can benefit from this feature.

8:00PM IST/10:30AM EST

Ratish Nair

VTC – Exchange Server 2013 Load balancing and Outlook Client Connectivity

Session Agenda:

· Exchange server 2013 – an overview of features

· Relevance of load balancing with Exchange 2013

· Configuring Exchange 2013 for load balancing

· Differences between Layer 4 and Layer 7 load balancing

· Details on outlook client connectivity – the connection process

· Troubleshooting issues with load balancing

9:00PM IST/11:30AM EST

Prabhat Nigam

VTC – Exchange 2013 – Database Availability Group and Auto Reseed

Session Agenda:

· DAG and Auto Reseed overview

· Benefits of DAG and Auto Reseed

· Getting started with DAG and Auto reseed.

· Configure DAG

· Configure Auto Reseed

Happy Learning Smile

SQL Tip-2 | Make Sure the startup type of your SQL Service is Automatic*

I know this one is very basic but in the recent past I noticed a couple of environments where the startup type of SQL Server Agent was set to manual which resulted in lot of issues.

Long story cut short – if your SQL Server is not clustered make sure the startup type is set to Automatic so that services can come online after a system reboot. Always use SQL Server Configuration Manager to change any property (including startup type) for any SQL related service.

Incase your SQL Server is clustered its better to keep the startup mode to Manual so that cluster service can take a decision & start the relevant node’s service based on Quorum.


Sarabpreet Singh Anand
Subscribe now to get latest Tips in your Inbox