Who is online?  215 guests and 0 members
home  »  blogs  »  Sarabpreet Anand

SQLServerGeeks.com Blogs

Blogs RSS Feed

Sarabpreet Anand : Most Recent postings

Sarabpreet Anand

Monitor SQL Server Services using Powershell

7/9/2013 11:25:12 PM by Sarabpreet Anand  -  Comments: 1  -  Views: [5263]

Last month on 26 th June I delivered a webcast How to Monitor SQL Servers using Powershell – Part 1 under Microsoft Lunch Time Banner. For those who don’t know about Lunch Time Series – it’s a new series started by Microsoft MVPs that happens every alternate Wednesday at lunch time generally for one hour. The webcast was very well received and I got multiple successive questions on the same. In this blog I’ll show you how easy it is to use the powershell inbuilt cmd...

Read More

Sarabpreet Anand

Resolving – Disk Missing Issue for TempDB with a Twist

4/4/2013 9:53:48 AM by Sarabpreet Anand  -  Comments: 9  -  Views: [4002]

A not so common but major issue could be missing Disk\Drive issue for any SQL Server, If there were user DB files on that disk chances are you’ll lose some data if the same Disk is not allocated again to you, Moreover you have to bear downtime. (Though there are some exceptions to it which we’ll discuss in another blog post.) But in case your TempDB was there on missing Drive\location – it will not even allow your SQL Server to start & gives the below error: CREATE FILE enc...

Read More

Sarabpreet Anand

Free SQL Event | Delhi NCR | 16-Feb-2013

2/12/2013 6:28:09 PM by Sarabpreet Anand  -  Comments: 0  -  Views: [1154]

Hi Friends, Are you facing performance problems in your environment? You find it difficult to create performance baseline or tracking different counters via Profiler? You have the data but don't really know how to use that data to create a baseline and analyse the delta in values? If your answer is yes then come and attend this Free Community session by SQLServerGeeks.com where I will explain different ways to capture the data and creating the SQL Server performance baseline. That’s not al...

Read More

Sarabpreet Anand

Honored with SQL Server MVP Award

1/2/2013 5:19:04 PM by Sarabpreet Anand  -  Comments: 7  -  Views: [1748]

Hi Friends, I am glad to announce that I’ve been honored with Most Valuable Professional (MVP) Award for SQL Server by Microsoft. I am very happy and excited to receive this award, the feeling of joining this Elite Technical club can’t be explained. I would like to take this opportunity to thanks Microsoft and SQL Server Community to make this happen. Here's the snippet of Long Awaited Email: A special thanks to Amit Bansal (SQL MVP) for providing such a great platform to us (SQLServ...

Read More

Sarabpreet Anand

Error while opening SQL Server Configuration Manager – Fixed

11/17/2012 5:28:04 AM by Sarabpreet Anand  -  Comments: 4  -  Views: [13718]

Hi Friends, Today I again experienced the same old frustrating error while opening SQL Server Configuration Manager on one of the SQL Servers. Refer the screenshot below. The error says: Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid namespace [0x8004100] Adding text so that you can find it through search engine when you face this issue. Now as ...

Read More

Sarabpreet Anand

Happy Deepawali - Say no to Crackers

11/13/2012 2:18:15 PM by Sarabpreet Anand  -  Comments: 0  -  Views: [4184]

Hi Friends, A Very Happy Diwali To You & Your Family May God Give You All That You Want & Need In Your Life. Enjoy The Festival With Lots Of Light. And Say NO to Crackers. With the evolution of the lifestyle, there has been certain change in the way people celebrate Diwali, as more and more technology has been included, but the zeal and the spirit of celebration remains the same. Earthen lamps may have been replaced with stunning electric illuminations, dress code may have changed, but t...

Read More

Sarabpreet Anand

Resource Governor Webcast - Demo Scripts

11/15/2012 11:03:52 AM by Sarabpreet Anand  -  Comments: 2  -  Views: [2392]

Hi Friends, Last week I took a webcast “ Tap is the only Solution Ver.2” (Resource Governor) . The webcast was very well received and there were a lot of questions regarding the Demo and the scripts. So in this blog post I’ll provide all the scripts to implement, test, tweak & remove Resource Governor. You can also use these scripts to do Proof of Concept (POC) on Resource Governor. Hope this will help you. Happy Learning\Testing – DO leave a comment to let us know ho...

Read More

Sarabpreet Anand

Find Jobs scheduled to run during a specific time range | SQL Server

11/15/2012 11:04:05 AM by Sarabpreet Anand  -  Comments: 2  -  Views: [22842]

Hi Friends, Today I was going to raise a change which was related to SAN Migration for one of our production servers. The affected server will not be available for almost 3 hours. I prepared the plan and asked Mr. X to look into this & execute the change during the weekend. Mr. X happily agreed to it, I asked him to go through the plan and clear the doubts if any. After reviewing the plan he asked multiple question which I answered promptly. His last question was “Sarab, you’ve m...

Read More

Sarabpreet Anand

Trace Flag 2371 to Control Auto update Statistics threshold and behavior in SQL Server

11/15/2012 11:02:44 AM by Sarabpreet Anand  -  Comments: 0  -  Views: [9480]

Hi Friends, As you already know, the auto update statistics feature of SQL Server relies on number of rows changed or updated to determine if statistics update is needed. The statistics of a table will only be automatically updated if the number of rows changed exceeds a threshold. AutoStat Threshold The sysindexes.rowmodctr column maintains a running total of all modifications to a table that, over time, can adversely affect the query processor's decision making process. This counter is updated...

Read More

Sarabpreet Anand

SQL Server - T-SQL | Calculate Number of Leaves - excluding weekends

11/15/2012 11:04:19 AM by Sarabpreet Anand  -  Comments: 5  -  Views: [4812]

Hi Friends, Yesterday I was answering some questions on TechNet Forum when I saw an interesting and genuine problem. The user wanted to calculate the number of leaves taken by an employee excluding weekends. Now calculating the number of leaves wasn’t a big deal all we need to do is just use datediff function but excluding the weekends was something which forced me to think harder. Then I started digging into this and finally I was able to make logic. Here is the script: http://gallery.tec...

Read More

Sarabpreet Anand

Memory Configuration Changes | SQL Server 2012

11/15/2012 11:03:06 AM by Sarabpreet Anand  -  Comments: 1  -  Views: [13903]

Hi Friends, Recently Microsoft introduced a few changes in SQL Server 2012 in Memory Manger, and a DBA should be aware of such changes before upgrading or installation of SQL 2012 Instance on production. In Earlier versions of SQL Server (before SQL 2012) SQL Server was also consuming memory outside of the limit defined in Max Server Memory. Those who don’t know, in earlier versions SQL Server was allocating memory mainly in two ways Single Page and Multi Page. Single page allocation was c...

Read More

Sarabpreet Anand

SQL Server - Memory Shortage for all 32 Bit SQL Server 2012 Instances

11/15/2012 9:37:38 AM by Sarabpreet Anand  -  Comments: 2  -  Views: [7784]

Hi Friends, As you all know AWE has been deprecated and not available from SQL Server 2012 (Find details here ), as a result when you upgrade from SQL Server 2008 R2 to the next version of SQL Server, you cannot use more memory than what the virtual address space limits in 32-bit instances of SQL Server. If you must have more memory for this instance of SQL Server, then you have to migrate to a 64-bit instance of SQL Server. After you upgrade to SQL Server 2012, the maximum amount of memory that...

Read More

Sarabpreet Anand

SQL Server - AWE Enabled Option in SQL Server | Deprecated

11/15/2012 9:37:42 AM by Sarabpreet Anand  -  Comments: 3  -  Views: [20829]

Like we already know, Windows-based applications can use Windows AWE (Address Windowing Extensions) APIs to allocate and to map physical memory into the process address space. AWE allow 32-bit operating systems to access large amounts of memory. Memory that is allocated by using this method is never paged out by the operating system, provided "Lock Pages In Memory" user right (LPIM) has been granted to the application Service account. Lock Pages in memory is by default given to Local system Serv...

Read More

Sarabpreet Anand

Super Hit SQL Server Sessions in Bangalore, Mumbai and Kolkata

4/24/2012 7:55:11 AM by Sarabpreet Anand  -  Comments: 1  -  Views: [2230]

Hi Friends, Attending SQL Server sessions in India was never been so easy and accessible at- least not for free, but now anyone can attend these sessions for free and easily while being in their own city. SQLServerGeeks.com started organizing In-Person SQL Server Sessions in Delhi\Gurgaon\NCR, Mumbai, Kolkata, Bangalore and Chennai two years ago and now moving to other parts of India. As you know we recently did couple of events in Bangalore, Kolkata & Mumbai and I am very happy to announce ...

Read More

Sarabpreet Anand

SQL Server: Which trigger will be triggered first?

2/21/2012 4:43:51 AM by Sarabpreet Anand  -  Comments: 2  -  Views: [33193]

Hi Friends, Yesterday I was working on a specific requirement of a client to create DDL trigger for a Database to log & validate certain things. While doing so I discovered there were already two DDL triggers created on the DB for the same event. Now I was not sure which one will be triggered first and after searching msdn I came across a stored procedure which can control this behavior to certain extent. SP_SetTriggerOrder, this is a system stored procedure which can specify the after trigg...

Read More

Sarabpreet Anand

Happy New Year 2012

11/15/2012 11:03:31 AM by Sarabpreet Anand  -  Comments: 0  -  Views: [2325]

Hi Friends, Happy New Year 2012 On Behalf of SQLServerGeek.com, I take this opportunity to thank all our Readers, Authors and the Admin Team (SSG) for their amazing contribution. Your passion and continuous effort helped us to achieve new heights. It’s been a wonderful year, our website www.SQLServerGeeks.com got launched, we did many great events\sessions, new and talented Authors joined us and our members list crossed a count of 1000 & all this happened in just Eight Months. On this ...

Read More

Sarabpreet Anand

SQL Server: Implement Step by Step Contained Databases

2/21/2012 4:51:57 AM by Sarabpreet Anand  -  Comments: 0  -  Views: [117585]

Hi Friends, Last week, I promised to publish an article on How to Implement Contained Databases but I couldn’t, so here it is: Step-by-Step guide to Implement Contained Databases This article will explain: How to create Partially Contained Databases. How to create Users without Login (Users with Passwords) How to connect to SQL Database without a Login You can read the article here: http://www.sqlservergeeks.com/articles/sql-server-bi/82/step-by-step-guide-to-implement-contained-databases-...

Read More

Sarabpreet Anand

SQL Server: What all changes require a restart of SQL Service?

2/21/2012 4:51:11 AM by Sarabpreet Anand  -  Comments: 5  -  Views: [18163]

Question of the day: What all changes require a restart of SQL Service? Let’s say you get a request to change a SQL Server setting and you don’t know whether it requires a restart of SQL Service to take into affect or not, now what will you do? You’ve left with following options: Ask someone you know (You think this person must know the answer but what if he is also guessing?) Search for it, now a days you find everything on internet (but you must hit the right keywords and som...

Read More

Sarabpreet Anand

Contained Databases in SQL Server 2012 – Denali

12/15/2011 1:58:29 PM by Sarabpreet Anand  -  Comments: 7  -  Views: [48157]

What is a contained database? A contained database is a database which includes all the required settings, metadata and operates in isolation from the SQL Server Database Engine. In other words it has no functional dependency on SQL Server Instance be it Login, collation setting or metadata info. The most popular feature being, user connecting to the database without having a Login at SQL Server Instance level; means there is no login registered for this user in Master DB. It’s very easy t...

Read More

Sarabpreet Anand

SQL Server: Unable to open Maintenance Plans in SSMS

2/21/2012 4:57:11 AM by Sarabpreet Anand  -  Comments: 14  -  Views: [25721]

Hi Friends, A few days back, I got a backup failure alert regarding one of our most critical SQL Instance. I quickly connected to our jump-box and took a session of the server to diagnose further. From the job history I concluded that I need to modify the maintenance plan in order to fix it, so I located the respective maintenance plan (we have many maintenance plans on this instance) and when I double clicked I was welcomed with a strange unique error. I was using SQL Server 2005 SP2 on this se...

Read More

Sarabpreet Anand

Expiry date on SQL Server Certification Exams

12/13/2011 6:25:48 AM by Sarabpreet Anand  -  Comments: 8  -  Views: [82581]

Hi Friends, I know most of you will not like it, but Now SQL Server Certification Exams will also come along with an Expiry Date. Microsoft is changing a few Certification Exam policies and from SQL 2012 Pro Exam launch you need to reappear (in other words you need to do recertification) to keep it under active status. This means: you need to plan for more money in your yearly education budget, More Stress and you need to be always up-to-date to be active and eligible for Certified positions. Re...

Read More

Sarabpreet Anand

SQL Server 2012 Express LocalDB RC0 is Available

12/7/2011 10:50:12 PM by Sarabpreet Anand  -  Comments: 0  -  Views: [10032]

Hi Friends, There is a good news, SQL Server 2012 Express LocalDB RC0 is Available Now. LocalDB is a new version of SQL Server Express created specifically for developers. It is very easy to install and requires no management, yet it offers the same T-SQL language, programming surface and client-side providers as the regular SQL Server Express. By using LocalDB, developers don’t have to manage a full instance of SQL Server Express. This is a dedicated version of SQL Express for developers....

Read More

Sarabpreet Anand

How to find weak and blank Passwords for SQL Server Logins

12/7/2011 12:02:27 AM by Sarabpreet Anand  -  Comments: 2  -  Views: [47172]

Password audit is a must for all SQL Servers. Weak or Blank passwords are invitation to trouble and such type of passwords may result in security breach. Everybody knows the importance of Strong and Complex password but as they say it’s always easy to preach but difficult to practice, we tend to make use of weak and sometimes blank passwords. Now the question arises how to get rid of these. Regular Password Audits is the solution. As a SQL DBA we must do a regular audit and remind the user...

Read More

Sarabpreet Anand

SQL Humour -- Marriage invitation in SQL Server Stored Procedure Style

12/6/2011 5:27:04 PM by Sarabpreet Anand  -  Comments: 0  -  Views: [3897]

Marriage Invitation CREATE PROCEDURE MyMarriage @ BrideGroom Char(NotBad), @Bride Char(Good) AS SELECT Bride FROM AndhraBrides WHERE FatherInLaw = 'Millionaire' AND CarCount > 2 AND HouseStatus ='TwoStoreyed' AND BrideEduStatus='PGorAbove' HavingBrothers='NO' AND HavingSisters ='No' AND AllowRelocate ='YES' AND SELECT Gold, Cash, Car, BankBalance FROM FatherInLaw UPDATE MyBankAccout SET MyBal = MyBal + FatherinLawBal UPDATE MyLocker SET MyLockerContents = MyLockerContents + FatherinLawGold IN...

Read More

Sarabpreet Anand

How to use new Built-in Functions in SQL Server 2012

12/6/2011 2:54:10 AM by Sarabpreet Anand  -  Comments: 0  -  Views: [44039]

Microsoft SQL Server 2012 Release Candidate 0 (RC 0) introduces 14 new built-in functions. In addition to the 14 new functions, one existing function has been changed. I’ve published an article to help you understand how to use new built-in functions, New Built-in Functions in SQL Server 2012 Denali you can read this article @ http://www.sqlservergeeks.com/articles/sql-server-bi/80/new-built-in-functions-in-sql-server-2012-denali Do leave us a comment. If you liked the posts, do like us on...

Read More

SQLServerGeeks FaceBook group

SQLServerGeeks Events

Email Subscriptions

   Get the Most Recent Blogs in your inbox