Archive for April, 2012

SQL Server: Identify UnUsed Databases April 30th, 2012

Vinod Kumar

too-many

As part of my day job, I solve lot of interesting customer problems and recently one of our customers had an interesting question to stump me. It got me really thinking hard and I wanted to share some of the solutions I shared with them. Though these solutions were given, want to also see if you have a better way to solve this question.

The Enterprise I was talking to was on more than 70+ SQL Server server installations and had close to 500+ databases running in their datacenters for various businesses. The CIO asked the datacenter administrator how many of these databases are actually used by the business and how many were stale and not used but just there because of legacy reasons. And as a database expert in the room the datacenter Administrator just turned to me and asked – “Why don’t you answer this Vinod?”

Simple solution

For every complex problems there always is a simple solution in hand. I just thought over and gave them a method almost that was convincing enough for the team. My suggestion was simple, just query the index_usage_stats DMV for some stats and then use the same to decide if the database was used or not. I just gave a simple script to the DBA like below:

Sp_msforeachdb 

‘Select @@Servername As Servername, ”?” AS DBName,

IsSystemDatabase,     

MAX(last_user_lookup) last_user_lookup,      

MAX(last_user_scan) last_user_scan,      

MAX(last_user_seek) last_user_seek,      

MAX(last_user_update) last_user_update

from [?].sys.dm_db_index_usage_stats

INNER JOIN  (      

SELECT dtb.name,

CAST(case

when dtb.name in (”master”,”model”,”msdb”,”tempdb”)

then 1

else dtb.is_distributor end AS bit) IsSystemDatabase      

FROM master.sys.databases AS dtb ) systemdatabases

ON systemdatabases.name = ”?”

GROUP BY IsSystemDatabase’ 

At least this is giving you a great starting point for discussion with the stakeholders that the database has not been accessed for the past 5+ months and if there is still business justification. The Administrator did call me back after a month that this script (simple yet powerful) was really useful. Thought to share with you all today.

PS: Remember, the statistics values can get reset if SQL Server is restarted hence this is not 100% foolproof implementation though.

What are the other options?

There are obviously other solutions that can be tried out:

  1. Auditing is one of the most precise and obviously a deterministic solution.
  2. Can even use Logon-triggers (but remember the user can switch context of the database hence will be difficult).
  3. The above Usage_Stats is great for databases of version higher than SQL Server 2005+. BTW, are you still running SQL Server 2000 versions?

Tell me your story and options to answer such situations. Would love to learn from you too.

Continue reading...


 

SQL Server 2012: Offline Logs viewing April 23rd, 2012

Vinod Kumar

As part of WMI events in SQL Server 2012, I saw these two new additions for offline error log files:

SqlErrorLogFile: http://msdn.microsoft.com/en-us/library/ff963576.aspx

SqlErrorLogEvent: http://msdn.microsoft.com/en-us/library/ff963582.aspx

Suddenly this got my attention and this blog post gets inspired because of the above events. These WMI classes are used inside SSMS’s Log Viewer when SQL Server is offline or is not responding. If the Log Viewer determines that the connection to a SQL Server instance could not be established, it will fall back to using WMI to enumerate and read the SQL error logs.

Since WMI classes are used for log viewing, it requires the WMI Service be started and only privileged Windows users with access to SQL Server logs can view the log data. By default the privileges are given only to the Administrators group of the machine by the SQL Server setup. As a result, SSMS must be launched using the Run as administrator option.

Offline Log Viewer can read error logs from SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012 versions.

How to use SSMS

With Microsoft SQL Server 2012, you can view SQL Server log files from a local or remote instance of SQL Server when the target instance is offline or cannot start. Steps to get this is:

  1. Start by opening SQL Server Management Studio (SSMS) as an Administrator.
  2. Register a SQL Server 2012 instance from Registered Servers (more about Registered Server on MSDN)
  3. Make sure the instance is stopped or offline.
  4. Once the SQL Server instance is registered, right-clicking on a registered SQL Server instance displays the following context menu to “View SQL Server Log
    image
  5. The SQL Server ErrorLog is displayed like it normally would with an online instance.

Some of the things to consider before using the offline viewing capability are:

  1. The instance of SQL Server that you want to connect to should be registered in Registered Servers.
  2. The account that you use to connect must have the required permissions.

Final words

I found this feature quite helpful and for DBA’s this will be a great addition. You can access the offline log files from Registered Servers in SSMS (as in example above) or programmatically through WMI with WQL (WMI Query Language) queries. PowerShell can also leverage these WMI components to programmatically read the error log files. The other methods for access will be for a different blog post.

Feel free to drop your comments.

Continue reading...


 

Challenge of receiving feedbacks April 15th, 2012

Vinod Kumar

feedback1One of the biggest challenge in the corporate world is to receive feedbacks. And I have seen managers do this with less skill and faltering to balance confidence on one side and still offer constructive feedbacks. Any feedback system has two parts – one who gives the feedback and the other who receives feedbacks. The former cannot be changed or influenced and hence this post is about how we set out a positive perception to receiving the feedbacks. I truly understand that being on the receiving end of feedback is one of the most difficult things to accept. No one is perfect and it is a mere disillusionment of perception that we carry in our minds that we are perfect :) (if you disagree, well here is a classic example then :)).

As you start growing in the organizational ladder it is important to have the very best relationship with your peers, managers and leaders. This is very critical because in case you run into giving your boss a candid feedback, you shouldn’t get intimidated with the situation – just because he/she is your boss. Hence investing upfront time in building a rapport is crucial.

Receive feedbacks like a generous gift that you will richly benefit from. This is a foundation for your own future !!!

I know it is tough to accept everything that is thrown in front of you. But I just want to give you a different dimension / perspective to take it positively and introspect later based on the conversation. I know it is doable, just that we need to be prepared for this. Here are my Top 3 feedbacks accepting tactics:

Accept it

What ever be the feedback, it is critical that you accept it as it comes (unfiltered). Understand someone is making time to give you that feedback. People feel encouraged when they feel you appreciate them without any risk of taking them the wrong way – most importantly without any consequences. Keeping an open mind and receiving doesn’t really hurt.

Don’t jump to conclusions

feedback2The feedbacks are best absorbed without debate. There is no point in arguing and reasoning about what you are being told as it will be counter-productive. If required, ask for details and examples – this makes sure that you are indeed having a open mind and listening to them carefully. Make them feel that you truly and sincerely value their input and are making efforts to improve.

Make an environment where both the parties (giver/seeker) feel there is a value in the discussion.

Act on your feedbacks

As we called out before, receiving feedbacks doesn’t mean you jump onto every thing that is said to you. The art here is to understand, verify (with actions mentioned), internalize the feedback and make the right change where appropriate. You don’t change yourself completely just because you got a feedback.

Being yourself is an identity, don’t make a mess of that.

Final words

If you personally feel getting constructive feedbacks are adding value don’t miss the opportunity to thank the person. Accepting feedbacks and acknowledging encourages the relation you have with the other person. Best part is you are the beneficiary of having such people around.

Constructive feedback is one that enables you to feed-forward in life.

Don’t think I am painting only one side of the coin – remember how you receive and perceive the feedbacks is something within our control and hence the emphasis is on that. Do tell me your perspectives and experiences.

Continue reading...


 

Reliving #TechEdIn at Blr UG April 3rd, 2012

Vinod Kumar

Reliving TechEd 2012 with Vinod Kumar 1TechEd India 2012 has been an event to remember and we take great pride in bringing this event every year from Microsoft. As I posted before on my other blog post around recap it takes a lot of preparation and execution to do sessions at the TechEd. This time I was tweeting almost live at the event using the #TechEdIn tag and everyone following me will vouch for the same. Having said that, there are many out there who really want to relive the experience of TechEd again. So here we go again – I will be doing a session on April 1st (Saturday).

BDotNet.in and BITPro.in Brings you

The Bangalore Usergroups are a bunch of bubbling individuals who bring so much joy to the members by conducting regular Usergroup meets. If you are in Bangalore then I strongly urge you to join these groups to get an update on an constant basis.

  1. BDotNet.in – Bangalore .NET Usergroup
  2. BITPro.in – Bangalore ITPro Usergroup
  3. SQLBangalore – Bangalore SQL User Group

Not strange, being in this city for close to 12 years, I have had the opportunity to talk and be part of both the UG’s since they started. Still nostalgic !!!

Reliving TechEd Sessions

TechEd2012Switching gears, yes – if you want to hear to my sessions delivered at #TechEdIn now in a much more relaxed manner. This is the chance again. For folks who are wondering when it will happen in their UG – don’t worry. If I travel to your town, I promise to do them for sure. This UG meeting we plan to have 2 sessions (back-to-back) on Internals.

1. Understanding Windows with SysInternals Tools
2. Peeling the Onion: SQL Server Internals Demystified

I can assure you one thing for sure – it will be a learning and sharing experience for me in a free flow format :).

Feel free to comment if you want any specific area to be covered as part of the UG event. I will be more than happy to expand the scope to include them :). For it is reliving the experience and get great content – right?

Venue: Microsoft Corporation, Krishna Hall, Embassy Building, 3rd Floor, Domlur.

Register at our FB page : http://www.facebook.com/#!/events/139444029517882/

PS: Feel free to walk in too – it is your group and the session is completely FREE. Entry on a first-come-first-serve basis only.

Community talk: Manas Blogged on this event. Wonderful Community Networking opportunity. Track us via #ReLiveTechEdIn over twitter.

Continue reading...


 

Time Management–no fuss April 2nd, 2012

Vinod Kumar

The topic of time management is something I have talked a number of times over this blog. So we are not going to spend time rehashing the same. But I still was so motivated to write this blog because I thought of picking some of the tips and traits that everyone out there does and will be a great learning opportunity for me to share.

An effective time management revolves around understanding and implementing the right principles of personal management. As avid readers, workers, employers, parent, spouse, explorer etc – do we really understand the fundamental difference between being busy VS being productive?

doha

I am a big fan of writing English quotes, but this time I couldn’t resist myself from sharing this Kabir’s Dhohe which I learnt when I was a kid at school. And this applies to our lives even today and so relevant too. Translating the above verse:

Tomorrows work do today, today’s work now
If the moment is lost, the work be done how?

Apply the 80/20 rule

This is a commonly used phenomenon, 20% of the tasks that you do bring 80% of the business. Hence it is imperative to go attack this in this way. So keep constantly asking – am I doing the right thing at work NOW? Will it affect the 80% of business we do? How productive am I.

Plan Ahead

Plan your day, week and if possible a roadmap on the month. I completely agree this is not possible 20% of the time (read the rule above :)). So given all variations in life, it is quite possible to plan, prepare and organize atleast 80% of the time you have at work. I understand a lot of you will say – “Well change is the ONLY constant and this is the very reason not to do too much on planning in this chaotic organization”

I hear you loud and clear – but remember if you don’t plan, you are forced to work in an adhoc way without any plan. So you choose it – hence don’t complain.

If this needs to be done NOW, there is no better time than to start NOW.

Make a note

I am a big believer of this. Generally I have a note that has a set of bullets that needs to be done. It is very easy to get sidelined by getting into too many chaotic work that eats away 80% of your time. Organize, plan and execute (rule #2 above).

Stop procrastination and eliminate one at a time. The sculpture is made by chipping the rock not by any explosion.

How to handle interruptions

Well, let me take a step back to solicit some feedbacks. So how do you handle interruptions. Drop me a line over comments. How do you differ or delegate the interruptions that come at work. I think there is loads to learn from each other.

Please share your experiences and thanks for reading this far !!!

Continue reading...