Reinventing ME(Restoring!!!!) June 26, 2012

Offlate I find hard to blog and I find my mind so hard to think about some qualitative stuff(this is called the DUMBNESS)

But a hell lot of things hapeening in and around me and thought of using this situation to kick start my blogging skills again.

Changing the Company

I am now blogging this from a reputed financial company S&P Capital IQ as i did quit the TCS 1 week ago. The decision I made was so harsh to me and my company.

As all other IT companies my previous employer tried to stop me but i just called it off and doesnt know the value I will pay for it.

But the most painful thing for me in leaving TCS is TCS’ers(This is the way to call the EMPLOYEES of TCS). Its really became so hard for me to digest the fact of leaving them, this happened the same when I am joining in TCS as well.

I owe to all my dear freinds a big time for making me better.

With that note I joined S&P leaving all fun,carrer and challenges behind on 21st June 2012.

Unlike my previous employer the formalities of joining and getting in to project were like cake walk (Thank god!!! no inductions, no bunch of signatures)

During first two days when I am in the  floor and started browsing on the machine I felt like I am alone here , this is due to the fact that people around me stick to either computers or go down for TT matches.

MR.Aditya!!! Welcome to Financial Product Company 

Here we have a bunch of talented SQL Server DBA’s( previously I Was the only DBA) and talented Technologists(Thanks to IIT and NIT,IIT or what ever)

Lots to learn and lots to swim in this SQL world….The only thing I never ever get bohred Off

As of Now Fingers Crossed anmd just hoping for the BEST to COME

MS TechedIn- Dream Big March 26, 2012

I have dreamt in my earlier carrer days of attending an event where some one speaks about the technology I work and I get Goosebumps while listening to it.

Microsoft and TCS made my dream come true. Lemme tell you and drive you through the experience

1st Day of Teched

I was at registration counter and as I don’t have companions I ended up in listening to ppl who were in queue. I heard ppl talking abt their manager I heard ppl talking abt Microsoft, I heard ppl talking abt girls around. But one thing commonly I heard was the excitement, Excitement to see What Is BIG

I got my goodies packet and saw the passport in it(of course excited about Reebok Watch!!!). Guided by the team Teched I walked down to the stage. AW!!! is my first impression by looking at the stage, I was in an impression whether I walked down to some film fare awards function

With in no time Sanket started his Keynote, he walked through the journey of MS and its success story. He concluded the session by making an oath that we are going BIG.

With that awesome keynote I stepped in to SQL Server Developer track

Highlight of all the sessions was Pinal and Vinod’s session on demystifying many bunks around SQL Server. I was discounted by vinod to benefit the others on the session. Ofcourse I felt great to be recognized

When the brain was almost out of memory there comes the reliever with Demo Extravaganza which was enjoyed by one and all thoroughly

In between I had a super chat with MTC architects and I myself debunked many of my own myths.

Day-2

Basically this was not the day for me as I don’t have any track in particular, but the key note by Amit Chatterjee made me awestruck

I dreamt of being successful as him( Am i Dreaming BIG here!!!)

Followed by super key note I entered in to Hands on lab for Power view and Always on, though the former was not as useful to me, but the later was so helpful and i enjoyed to keep up the pace of the speakers.

Later in the afternoon I explored all the Expo showrooms. The thing that attracted me was Surface table and Windows Server 8 itself

I got to learn a lot on and around Win Server 8. I really have to say its gonna change the way the Server functions.

And again this followed with a great Demo Xtravaganza where Pinal rocked the floor. East or West SQL is the Best

Day-3

Some how i was totally affected with sunstroke and I hardly slept on the day before, but as the sessions on this day are totally sql oriented. I gained all the energy and went back to Day-3

First Session by Vinod and Second by Pinal…Both were Awesome.

I basically a fan of SQL internals and I love to discuss the same with my fellow members.

The memory changes and the indirect checkpoints internals in SQL 2012 made my brain restless to go over and work on them.

I thank Vinod for shedding light on these topics

I really don’t know that Parallelism was joined by three other functions. Thanks to Pinal and his energy in dealing and bringing up this together

I thank Pinal to stop by in the middle and recognizing me(Also i need to thank him for giving away me the card)

My energy by then was totally exhausted and I felt really guilty on my luck as i was not able to attend the sessions of my favorite speakers Amit and Balmukund.

I took medicine and travelled back to hotel for calling it as a day.

But when I am here and thinking about the Takeaways here is the list

*Dream Big

*Dream Big……

Yes, Teched brought a great change in my thought process and my the way I design my career.

I am dreaming Big to be MCM on SQL Server

I am dreaming Bigger to be speaker in teched

I am dreaming Big to reimagine myself and discover myself each day.

Thank you very very much TechED and Microsoft. This 3 Days means a lot to me

The Curious Case of SQL Server Disaster February 19, 2012

Many people especially in IT industry won’t realize the importance of the DBA and his advices until they see DISASTER of their databases.

Last week I am evident of 3 continuous disasters of databases in 3 successive days and following is my experience with this curious case

First Disaster (Wednesday):

I am yawning in home to see a TV channel forcibly by Grand Ma, at that very moment I got a call from office saying that they are 823 Error Mails flooding in to mailbox.

I immediately got rid of my grand ma and switched on my desktop to see the mails and found that the databases were not accessible.

As I don’t have access from my home I asked my pal to just run the following command to check the status of the databases

SELECT state_desc,* FROM SYS.DATABASES

 

And the output we got is

I then asked to run the following command on which the 823 error is reported

As the database is online in state why still my database is not opening? I know the following table from BOL

State	Definition
ONLINE	Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed.
OFFLINE	Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.
RESTORING	One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable.
RECOVERING	Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable.
RECOVERY PENDING	SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.
SUSPECT	At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem.
EMERGENCY	User has changed the database and set the status to EMERGENCY. The database is in single-user mode and may be repaired or restored. The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect can be set to the EMERGENCY state. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.

 

We don’t have any of the above except online. So I then decided to access something and see what actually error is

Alter database adventureworks set online

 

Then I got the error

The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0×00000000074000 in file ‘H:\MSSQL\Data\AdventureWorks.mdf’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

So what is this?????

This is one more state of database that is being INCONSISTENT:

In this case the database isn’t accessible at all, because recovery hasn’t run or completed and so the database is in an inconsistent state. It could just be logically inconsistent (e.g. a transaction modifying data hasn’t recovered) or worse it could structurally inconsistent (e.g. a system transaction modifying index linkages has’t recovered). Either way, SQL Server wants to prevent you from getting into the database because it doesn’t know what state the data and structures in the database are in. But if you don’t have a backup, you need to get into the database, no matter what state things are in.(courtesy : Paul)

The way to get rid of this is to bring the database in to Emergency,Run CheckDB and then if no errors bring the database online

 

Alter database adventureworks

set EMERGENCY

go

Once the database is in Emergency mode then it is only accessible by sysadmin J . Now it’s time to run DBCC CHECKDB

DBCC CHECKDB(‘AdventureWorks’,with_no_info_msgs)

 

Once you find there are no errors then it’s time to bring the database online

 

Alter database adventureworks

set ONLINE

go

 

Second Disaster (Wednesday):

We are happy that databases are online and I documented the issue to client by finding the root cause. After that I am on my way to home when I got the phone  from my pal saying we encountered same situation with another server, then I asked him to take the action as yesterday.

But by then we had a situation where one of our databases went to suspect state and we did approach with the following procedure

Alter database adventureworks

set EMERGENCY

go

 

Alter database adventureworks

set SINGLE_USER

go

 

DBCC CHECKDB(‘AdventureWorks’,with_no_info_msgs)

 

Here we saw bulk of allocation and consistency errors. Then we had two options

1.       Restoring the latest backup

2.       Running database consistency allowing data loss

Unfortunately we have a  SUPER backup team who failed to give us backup immediately. After a long discussion we decided to run database consistency as our client is confident that the data load will be done next day any ways.

 

So we then ran the following steps

 

DBCC CHECKDB (AdventuerWorks, REPAIR_ALLOW_DATA_LOSS) (Check before adapting to your environment)

 

We got the output as the following

 

The log for database ‘AdventureWorks’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

 

Followed by long chain where the database repaired and where it deleted the corrupted database.

We then brought database as multi user and online.

 

Alter database adventureworks

set MULTI_USER

go

 

Alter database adventureworks

set Online

go

Third Disaster (Wednesday):

Thus because of poor backup strategy we ended up in repairing the database with data loss. Fortunately we didn’t see huge data loss.

The disaster happened today is worst where the total system data files disk got corrupted. Again here I am explaining a situation where you should not be(HAVE BACKUPS PLEASE!!!!!)

Here we don’t have backups and somehow the server team managed to get sql server installed and now our task is to attach the production database

We tried to attach the database with single mdf file then we got the error as following

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Then what we tried to do is

·         Create a new database with same name

·         Stop SQL Server

·         Swap the MDF Files and delete the log file

·         Start SQL Server

·         Take the database in to Emergency mode

·         Then in to Single User mode

·         Run Database consistency with allow data loss so that the log can be rebuilt forcibly

·         Yayyyyyyyyyy!!! We now have the log file J

·         Bring the database back to multi user and online.

Thus you have the database online.

Above methods are applicable ONLY when

·        You don’t have valid database backups

As an administrator its our first duty to satisfy above constraint but if we have left with no choice we always have Hacking methods J

Happy Troubleshooting J

Performance Metric Collection Made Easy December 27, 2011

Many times DBA’s will be in a position where they need to collect the data over a period of time for list of performance counters. Many will depend on 3rd party tools and also scripts

Thanks to Sql Server 2008 R2 where we have a feature of collecting data through System Data Collector set.

The following is the word document which tells the clear story of configuring system data collection

Collection Performance Metrics

Please let me know your feed back

Discovering VAS December 26, 2011

Knowingly/Unknowingly many people will ignore the virtual memory settings in their machines which is a huge performance bottleneck. Today I like to discuss something on this front.

The Memory of a server an be understood by the combination of physical memory and virtual memory.

The virtual address space of each process can be smaller or larger than the total physical memory available on the computer.  If the threads of a process attempt to use more physical memory than is currently available, the system pages some of the memory contents to disk. Both physical memory and the free space on disk available for the paging file computes the total amount of virtual address space available to a process .
 
Physical storage and the virtual address space of each process are organized into pages, units of memory, whose size depends on the host computer. For example, on x86 computers the host page size is 4 kilobytes.
 
To maximize its flexibility in managing memory, the system can move pages of physical memory to and from a paging file on disk. When a page is moved in physical memory, the system updates the page maps of the affected processes. When the system needs space in physical memory, it moves the least recently used pages of physical memory to the paging file. Manipulation of physical memory by the system is completely transparent to applications, which operate only in their virtual address spaces.

Having learned basics about VAS, now we will discuss on the main pain areas or bottlenecks around VAS.

The basic configuration setting for page file according to Microsoft is as follows.

Configuration	VAS	Max physical memory	AWE/locked pages support
Native 32-bit on 32-bit OS	2 GB	64 GB	Yes
with /3GB boot parameter1	3 GB	16 GB	Yes
32-bit on x64 OS (WOW)	4 GB	64 GB	Yes
32-bit on IA64 OS (WOW)	2 GB	2 GB	No
Native 64-bit on x64 OS	8 terabyte	1 terabyte	Yes
Native 64-bit on IA64 OS	7 terabyte	1 terabyte	Yes

We need to make sure that our server is configured with right VAS(page file) configuration.

Detecting virtual memory pressure:

To detect virtual memory pressure we need to check following counters in perfmon(performance monitor)

Paging File: %Usage, Paging File: %Usage Peak.

If %Usage Peak is 100 we can see as page file is increased in event viewer. This is also a clear indication that there is certain virtual memory pressure on the server and this needs to be addressed by increasing the paging file size.

Also we can get the amount of memory that is paged out by subtracting Process: Working Set from the Process: Private Bytes counters.

I have the machine which is virtual memory intensive and following are the figures from Perfmon

Above figure is the snapshot of my systems memory counters. When I did subtraction of prcess counters i got a value of 0.39 GB per process and my usage peak is at high which means that i need to extend my page file configuration.

We also can get the information by sys.dm_os_virtual_address_dump. To get the info from this DMV we can use the following View(By Microsoft) to get the insight.

CREATE VIEW VASummary AS
SELECT
    Size = VaDump.Size,
    Reserved =  SUM(CASE(CONVERT(INT, VaDump.Base)^0)
    WHEN 0 THEN 0 ELSE 1 END),
    Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
    WHEN 0 THEN 1 ELSE 0 END)
FROM
(
    — combine all allocation according with allocation base, don’t take into   
     — account allocations with zero allocation_base
    SELECT 
        CONVERT(VARBINARY, SUM(region_size_in_bytes))
        AS Size, 
        region_allocation_base_address AS Base
    FROM sys.dm_os_virtual_address_dump 
    WHERE region_allocation_base_address <> 0×0
    GROUP BY region_allocation_base_address 
UNION  
       — we shouldn’t be grouping allocations with
       zero allocation base
       — just get them as is
    SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
    FROM sys.dm_os_virtual_address_dump
    WHERE region_allocation_base_address  = 0×0
)
AS VaDump
GROUP BY Size

After creating the above view use the following code to get the value of largest available region and if the value is less than 4MB we are likely to have VAS Memory Pressure

– get size of largest available region

SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB] FROM VASummary WHERE Free <> 0

Thus we can come to conclusion of VAS and its bottlenecks. Please let me know the feed back.

HAPPY TROUBLESHOOTING

Yeah..I AM A Speaker December 24, 2011

After a long wait I am here and I am a Speaker(YEPIEEE)….

The experinece was simply astounding… Talking about something which drives you crazy is always best(may it be a girl or Technolgy)

I love the technology I am working on… and It inspires me to stay late , talk lot…

The same happened today at DEVCON-December in User group hyderabad chapter of Microsoft.

I enjoyed each and every bit of the moment i talked and I really didnt know how 1 hour passed.

I thank all the great audience,Hima,Pranov and Chakri.

I also thank my Gurus Paul Randal,Kimberly,Pinal,Adam Machanic for making me what I am today…

Looking forward for delivering more intersting stuff on SQL Server….

By the way following is the link to download all the Scripts stuff I have talked about ..

SCRIPTS_STUFF

Performance_SQL_SERVER_2008

Its time for MI4…TOm here I come…Merry Christmas and Advance Happy New Year Fellas….

Gotta Inspired July 22, 2011

Dedication or foreword for this one page blog:

To all the ppl who really inspired me through out my life though i am mentioning few but many did..

I had many inspirations in my life and often small and single moments also inspire me a lot..

I don’t know whether this is a good thing or bad..some say one should have own attitude ..some say copy cat..But
I am used to this behaviour and by gods grace with this I am the happiest person..

I am a software engineer not by force or not by chance…I am here because i was inspired..

We live in small towns of Andhra Pradesh where parents are more ambitious than students..parents know lot abt IIt and GRE than students..

My parents are no exception… My Dad used to say abt many ppl(did he know that i like to inspire or did he inject this) who are doctors ..collectors whatever

But I got inspired by my brother Raju who is physically challenged and flew to US as a software engineer when i was 8th standard.

i dont know how but from that day i dreamt of being s/w engineer..work with soul less brains(haha u found it right!!) and of course fly to US atleast once..

God is so kind to me and he adjusted my way to be right there in no time,,,

Should I thank God… More than God its my Dad who sold his only asset of 2 acres land just to give me a computer…(Thanks Dad!! Ur the Best Dad ever)

So after beautiful engineering journey I am at a software company who asked me a bond of 2 lakhs if i quit the job in 2 years(Crazy ppl)

I stepped in to the office …greeted my manager..team lead & then got to know two idiotic creatures in this world(Kanchan & Manu)..

By looking at them i thought by no chance these are nt the ppl who gonna inspire me(haha infact i thought i would be inspired by my manager..How foolish)

Next Day After Office me & Kanchan were in auto

ME: DUDE !! Look there!! Watta Girlll..She is nothing but awesome..
Kan:Sorry Mama!! Girls are not My cup of TEA
ME: Sorry!
Kan:Yeah Dude u heard it right..
I jus moved aside of him..sesing fishy in his behaviour.
Kan: I am already committed and i dont feel like looking other girl except sandhya.
ME: Are you kidding me??

I have many friends in my list but i havent seen this kind of person..The way he loves his girl and the way he cares is awesome ..

I thought and i got inspired onwe day I if was to love I should love so trully as my pal did…

In the journey of being software engineer …I being from a rural brack drop really doesnt know how to write mails or such(thanks to my engg education)

it is then Kanchan who didnt help me but did inspired me in writing a good stuff,,,

Thus my carrer which i have chosen was totally an inspired one..

The other side of the coin is how to lead life…In this part I got inspired by many but I like to mention some who really did…

****Conitnues*******(should i say this in Varma Style) hahaha