Monitoring & Setting Alerts for SQL Server Critical Errors

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 20-01-2011


As a Proactive DBA, you must monitor All SQL Server errors having severity level between 17 to 25. Any errors from level 20 to 25 are serious in nature however for 17 to 19 a DBA involvement is required for resolution. Here is self explanatory MS description of error levels, steps to get automatic alert as soon as any of error occurs and list of error messages.

Automate SQL Server Profiler Trace Execution

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 12-01-2011


SQL profiler is an excellent tool for T-SQL query performance optimization. SQL Server Production DBA’s use Profiler to scan what T-SQL queries are getting executed inside SQL Server and what are the queries that can be filtered for further fine-tuning. SQL profiler has inbuilt scheduler for stopping a trace, but Trace Properties does not provide functionality to automatically schedule a trace and keep it running for a desired frequency.

What Lenovo IdeaPad Y560 Laptop Can’t do?

Posted by Deepak Kumar | Posted in SQL DBA, Uncategorized | Posted on 03-01-2011


I rarely find time to play computer games, however when I do, I certainly go for Need for Speed racing game from EA. Considering high system requirements for now a days demanding games and with prime reason to run multiple virtual machines for SQL testing, I decided to go for Lenovo laptop for the second time. It was hard to choose but I was somehow confident with what I have chosen. Lenovo IdeaPad Y560 06465BU is a pretty decent size hardware to do most of stuff, Under the hood It has:

This Year, I will..

Posted by Deepak Kumar | Posted in SQL DBA, Uncategorized | Posted on 02-01-2011


As the new year begins.. What is your New Year resolution for fresh start? quit smoking or drinking? Or hit gym more often, lose weight and get back in shape? Find a new job? Save more money or get out of debt?

In addition to these, If you have space left for something called “learn something new!” here is what I can offer for free that I enjoy following and practicing. These are set of rules that you can pursue for better results and overall improvements.


Survival the fittest – if you cannot adopt changing technology, work culture, requirements, life style or surroundings you will move out of race pretty soon. Its mantra of life; stay flexible and be ready to accept changes, mold yourself, swim with the flow, absorb what you get; if you can not get dissolved in progression, will be filtered out. This is the phrase that evolved life & diversity on planet earth.

No pain, no gain – it’s simple motivational theory, like 1+1; there are no free lunches in this world. If you need something you got to do something in return. Stand up; get ready to pay in advance for what you desire. There are no magical Ginny in this world. Always remember Pain is momentary, but Glory is for forever!

Never, ever, repeat mistakes - If you keep on making same mistakes you never going to move out of circular drive & better you forget designation. Remember it always, if you cannot; it’s the one you can have tattoo on. Making mistakes are not exactly bad, but at the end you should learn from your mistakes and making sure you never repeat & forget learning.

Go get it, no reservations needed! – Nobody come to your door with a free lottery ticket to win, come on. You want something? You better get going, if not now; then when? Do not just keep on waiting for favorable conditions; instead achieve your targets and move ahead for next one.

Everyone and everything can teach you something – Ask yourself, have your learned everything? No, never you can’t simply.. There is so much you still have to do & explore. In plain words, stay hungry & open to learn more.

How to Monitor SQL Server CPU Usage and get auto alerts

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 28-12-2010


There are some condition & times when SQL Server CPU utilization exceeds threshold value (ex 80% or more) and stay constant even till 100% for a undefined duration. A DBA do not get notified automatically about performance degrade until its reported by application team or service desk . This usually happens when all in sudden unpredicted application workload goes to SQL Server for processing; but at good number of times, its because of some T-SQL Queries are taking longer than expected or because of bad execution plan or blocking. There may be more reason to add, but ultimately high CPU utilization on production systems is going to hurt performance.

SQL Server Query for Capacity Planning – SQL 2005 & 2008

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 26-12-2010


As a proactive DBA its one of responsibility to keep an eye on database’s growth and other file system changes for the databases hosted on SQL instance. However being busy on daily DBA tasks, its not always possible to prepare scripts or manually extract data from system for documentations & trend reporting purposes. I faced the similar challenge while working with large DB systems and came up with a T-SQL script that can do the needful without worries..

Wish you a Merry Christmas

Posted by Deepak Kumar | Posted in SQL DBA | Posted on 25-12-2010


From SQLknowledge and Team, We wish you and your family a Merry Christmas! It was big fun on Christmas eve, we went to see a 90ft Christmas tree and gifts shopping.


Happy to join SQLKnowledge

Posted by Shashikant | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 25-12-2010


I have joined sqlknowledge and in addition to Deepak and other members, I will be writing about SQL troubleshooting, tips and tricks keep checking for more..

Merry Christmas

SQL 2011 Denali Basic Setup and Configurations

Posted by Deepak Kumar | Posted in SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 22-12-2010


In continuation of my earlier post for Denali CTP1 launch, here are SQL Server 2011 setup screen prints. To begin with you can download installation media from Microsoft Link. Before you start installation, you must check system requirement and are as follows:

System Requirements: Details

  • Supported Operating Systems:Windows 7;Windows Server 2008 R2;Windows Server 2008 Service Pack 2;Windows Vista Service Pack 2
  • 32-bit systems
  • Computer with Intel or compatible 1GHz or faster processor (2 GHz or faster is recommended.)
  • 64-bit systems
  • 1.4 GHz or faster processor
  • Minimum of 1 GB of RAM (2 GB or more is recommended.)
  • 2.2 GB of available hard disk space

The very first screen you will see is typical SQL Server installation, For this installation I used new SQL Server stand alone installation.






















SQL Server Consolidation & Virtualization Practice..

Posted by Deepak Kumar | Posted in SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 22-12-2010



    image Consolidation by definition is the process of combining multiple SQL Server Databases & Servers running on different machines (could be geographically separated) onto a smaller number of more powerful machines in a central location. However in my opinion regarding SQL, its a process of getting organized, spending for what you need or use, and off-course saving at the same time. Lets see how consolidation and virtualization works together?

    Microsoft SQL Server since beginning is overall self maintaining, secure by default and self tuning DBMS that requires a very little or basic configuration during installation or later during usage, In a large Enterprise size organization its easier adaptability and usage can lead into uncoordinated installations, wasted hardware and licenses, lack of standards and security holes.

Think Again!  You should consider adopting Virtualization and Consolidation technique if you have similar questions:

  • Looking by your SQL Server inventory list, you realized that number of SQL servers are going up every month?

  • Looking by server utilization reports, you realized that your team or vendor over estimated database server hardware requirements and workload; leading into buying excessive hardware that your application is never going to use?

  • Every time it take months to plan & implement SQL patching, upgrades, installation etc, Applying SQL Server best practices and security updates/settings are complex and far of reach.

Better together Approach: You can consider three best possible scenario to implement, but are having its own benefits and concerns.

  • One OS/One SQLinstance- multiple databases
    Its the best that you can get, in simple words evaluate your application that uses a few database(s); move your databases to a shared database server. Concerns: shared cache, SA permission, similar name databases or objects and logins, maintenance window, remote desktop connection (rdp) etc

  • One OS/Multiple SQL Instance
    Build a server having multiple SQL Server instances for applications that require its own unique collation, SQL version & build, own dedicated memory/cache, tempdb etc. Concerns: SA permissions, remote desktop etc.

  • One OS/One SQL Instance (virtualization)
    For applications having extreme performance needs and unique set of configurations. build a server with multiple virtual Operating systems and each OS running SQL Instance.
    Software dependency: Microsoft Hyper-V, VMware, hp polyserve etc


      Consolidation & Virtualization benefits

      • Reduced Software & Operating System Licensing cost
      • Reduced Server Hardwar cost, fewer number of servers required
      • Datacenter space, Power consumption, Cooling Cost cut down (GreenIT)
      • Monitoring & Support cost; fewer resources needed to monitor/control/patch servers etc
      • Easier Server move-ability with scale up & scale out solutions
      • High availability option (depends on setup)


        • Single point of failure (But, you can implementing a good high availability solution to deal with this issue)
        • Takes time & efforts to consolidate (But, once setup year by year return in terms of savings)
        • Complex Service Charge Model. If your organization do the billing of services provided to various business units, then you may need to do complex calculation based on usage before billing to individual units.

          Milestones to Destination

          • Inventory: Prepare inventory of SQL Server hosted in your environment. You may consider using Microsoft Assessment and Planning tool (MAP)
          • Hardware Sizing: Document database server resource available on the server like CPU, Memory, Storage, DISKIO, etc
          • Hardware Usage: Identify database server usage/utilization over a period of time, prepare histogram of Peak, Low & average usage. You may use perfmon or 3rd party tools like VMware capacity planner.
          • Savings: Calculate server operational cost in current setup and compare with new consolidated & virtualized model.
          • Going ahead: You may want to ask some specific database related questions to application owners
            • Is it a vendor supported/provided SQL Instance with limitations or internal home grown application? and what is workload or capacity planning guidelines for future.
            • Any significant reason a physical server is required? or why physical to virtual (P2V) should not be done?
            • Can Databases’ from SQL Instance be clubbed/consolidated with other SQL Instance? what is frequency of database changes/deployment or downtime requirements.
            • Can SQL instance be upgraded to latest SQL Server version & build? as per virtualization standard in your environment?
            • What are high availability options implemented for the databases & SQL Service?
          • Best practices:

            • Its better to divide entire SQL Server inventory into multiple smaller sections. Example- Should look at creating 5 subset of 100 servers rather than going in for virtualization of all 500 servers in a single attempt. Apply learning, experiences and best practices in later subsets.
            • Use single machine with individual SQL Named Instance or VM for Production/Test/Development/Staging db requirements. (depends on application/environment)
            • Never ever, oversubscribe resources for your server on virtual platform
            • Calculate total server workload in virtualization model with real-time application and database workload in different scenario or timings.
            • Carefully choose virtualization technique and server hardware to implement

            Resources: Microsoft, VMware, MS PDF

            © 2010 Increase your website traffic with