MSSQLCorruptionTackle

Tuesday, June 27, 2017

Unspecified error Exception 0x80004005 (E_FAIL)

There could be many reasons why we get below error

Unspecified error Exception 0x80004005 (E_FAIL)

One of them i observed was that when i try to create a ASP .NET project at the onedrive directly. When i try this i get below error HRESULT: 0x80004005.

To avoid this i created my project on local folder and it worked. Now after you create on local, transfer this to the onedrive and work on your ASP .NET project.

CS0234 The type or namespace name does not exist in the namespace (are you missing an assembly reference?)

The description of the message informs you which is the missing assembly reference. In my case it was System.Data

This is how you fix the assembly reference-

Look at the error -> my error was "The type or namespace name 'Entity' doesn't exist in the namespace System.Data.
It means it is asking me to add System.Data.Entity library explicitly
Go to your project
Right click on References
Add Reference -> in my case it was System.Data.Entity

Sunday, June 4, 2017

SQL SERVER DBA “INTERVIEW QUESTIONS AND ANSWERS”

Following are some “sql server dba interview questions” to help you in interviews.

How to Handle Sexual Harassment at Workplace

I have got many requests to publish first set of MSSQL DBA Interview questions. I observed that many interviewers are interested in finding out whether you worked on DB corruption or not. Here you go http://mssqlcorruptiontackle.blogspot.com/2010/12/ms-sql-corruption.html . Go ahead with ten most important Microsoft SQL Server DBA Interview Questions.

Interviewers are testing at least basic knowledge of clustering. Read following wires to be ready for this surprise: MSSQL Interview Question Clustering- Part1, Part2, Part3 and Part4

What is the difference between lock, block and deadlock?
What is the meaning of lock escalation and why/how to stop this?
How to truncate the log in sql server 2008?
How to Start Service Using Powershell Commands?
What changes in the front end code is needed if mirroring is implemented for the high availability?
Where does the copy job runs in the logshipping… Primary or secondary?
What are the ways to find what code is running for any spid?
When you get following error? Error 3154: The backup set holds a backup of a database other than the existing database.
Does DBCC CHECKDB requires DB to be in SINGLE_USER mode?
How to view the error log for any specific instance?

Want to prepare in ten minutes - read article Interview and Beyond in 10 Min

———————————————-

Answer 1:
Lock: DB engine locks the rows/page/table to access the data which is worked upon according to the query.
Block: When one process blocks the resources of another process then blocking happens. Blocking can be identified by using
SELECT * FROM sys.dm_exec_requests where blocked <> 0
SELECT * FROM master..sysprocesses where blocked <> 0
Deadlock: When something happens as follows: Error 1205 is reported by SQL Server for deadlock.

Answer 2: When the DB engine would try to lock page first and then it escalates locks to page and then table. If we

understand that whole table would be locked for the processing thenn this is better to use TABLOCK hint and get complete table blocked. This is a nice way to avoid the wastage of sql server DB engine processing for lock escalation. Somewhere you may also need to use TABLOCKX when you want an exclusive lock on the table in the query.

Answer 3: BACKUP LOG TestDB WITH TRUNCATE_ONLY is gone. SQL server doesn’t allow you to truncate the log now otherwise whole purpose of a DB is defeated. Read article http://mssqlcorruptiontackle.blogspot.com/2010/12/mssql-server-dbcc-loginfo-status-2-log.html to surprise interviewer with your answer. You have to make sure whether you need log or not. If you don’t need log then have the recovery model simple instead of full. If you don’t want the log to be accumulated in some particular bulk logging then change the recovery model BULK LOGGED for that duration and take one tlog backup just before and after this change. I shall discuss this later in my later blog. BACKUP LOG command backs up the t-log and frees the space in the log file.

Answer 4: How to Start Service Using Powershell Commands.

Answer 5: You need to add only FAILOVER PARTNER information in your front end code. “Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=AdventureWorks;Integrated Security=True;”.

Answer 6: Secondary server. This question is basically asked to find out whether you have a handson work on logshipping or not. I came through many cases when candidates have mentioned logshipping experience in many projects and they can’t answer this question. I never selected any candidate if he/she don’t answer these kind of small questions.

Answer 7: Well there are many ways to do this.
1. find the spid which you want to analyze. An spid is assigned as soon as a client connection is established with the SQL server. To find the spid you can run any of the following command:
a) SP_WHO2 ‘ACTIVE’ — This will give you only active spids.
b) SELECT * FROM sys.dm_exec_requests

2. Get the spid from above two queries and use any of the following query to get what is happening behind that spid.
a) dbcc inputbuffer(<spid>)
b) sql2005 and sql2008 – SELECT * FROM sys.dm_exec_sql_text(<sqlhandle of that spid>)
c) sql2005 and sql2008 – SELECT * FROM fn_get_sql(<sqlhandle of that spid>)

Answer 8: The error comes when you are trying to restore the DB which already exists. Use WITH REPLACE option to restore the DB with a different name.

Answer 9: Yes and No. This is tricky question. If you are using repair option with CHECKDB then you have to have the DB in single user mode. Following is the method to have your DB in a single user mode.

Use master
go
sp_dboption dbname, single, true

Following is the error which you get when you run the DBCC CHECKDB with repair option w\o having the DB in single user mode.

The same is true for DBCC CHECKDB also.

Answer 10: There are many ways but I prefer following method. Take a scenario when you want to find the error log when the DB was put in a single user mode.

CREATE TABLE #Errorlog (Logdate Datetime, Processinfo VARCHAR(20),Text VARCHAR(2000))

INSERT INTO #Errorlog
EXEC xp_readerrorlog
SELECT * FROM #Errorlog
WHERE Text Like ‘%SINGLE%USER%’

Most of the readers of this blog also read following interview questions so linking them here:
Powershell Interview Questions and Answers
SQL Azure Interview Questions and Answers Part – 1
SQL Azure Interview Questions and Answers Part – 2

Read following blogs to surprise your interviewer and create chances to get into IT sector:

Along with technical learning I would like to share some great articles for anyone interested in the betterment of his/her family life

How to Handle Sexual Harassment at workplace

Sexual harassment at work place is prevalent. It can be termed as bullying, unwelcome sexual advances and asking sexual favors at workplace. Harassment can happen to anyone and probability of recurrence increases if not tackled professionally. This is often observed that we don't talk enough on this topic. Some EEOC stats as per link

79% of victims are women, 21% are men.
51% are harassed by a supervisor.
Business, trade, banking, and finance are the biggest industries where sexual harassment occurs.
12% received threats of termination if they did not comply with their requests.

How to handle the sexual harassment-

This needs to be documented. Victim should send out a mail to the offender informing that she/he is shocked with his/her actions on date at particular venue. I discussed this situaltion with my relatives and they asked me to inform you about my feelings. The victim has to be very professional.
Victim needs to make sure that he seek for the read receipt of the mail, if possible. This will help procure proof against the offender in case he/she doesn't respond the mail.
After you have proof and you shared your reluctance/objection towards offender's actions, feel free to send out mail to HR/skip if it repeats.

There are some weird advices from colleagues to tackle this situation. It makes the situation worse than before. Not to do:

1. Call police especially when you don't have proof.
2. Send out mail to skip or HR without any hard proof.
3. Find out her/his spouse and set-up a sync up.

Disclaimer: This is a personal blog. Any views or opinions represented in this blog are personal and belong solely to the blog owner and do not represent those of people, institutions or organizations that the owner may or may not be associated with in professional or personal capacity, unless explicitly stated. Any views or opinions are not intended to malign any religion, ethnic group, club, organization, company, or individual.

Along with technical learning I would like to share some great articles for anyone interested in the betterment of his/her family life

Thursday, October 31, 2013

Powershell Script to get RAM information

Powershell makes it easy to get information from remote servers.

Below is the query to "calculate RAM using powershell" of all the remote servers you are connected with-

Step1- Create the list of all the servers in a notepad with name "serverlist.txt". I have saved this file on location "D:\Powershell" you can change it with your own location.

Step2- Execute below script-

clear-host
$servers = Get-Content "D:\PSScripts\bstServerlist.txt"
$SystemInfo = "D:\PSScripts\bstSystemInfo.txt"
$ErrorInfo = "D:\PSScripts\bstErrorInfo.txt"

FOREACH($server in $servers)
{
$System = gwmi -computer $server -class win32_computersystem -ErrorVariable MyError -ErrorAction Silentlycontinue

$Ram = [math]::round($System.Totalphysicalmemory/(1024*1024*1024),2)
write-output "$server - $Ram " | out-file -filepath $SystemInfo -append
}

You can also save the script in a notepad and save it with extension ".ps1". When you will execute the file RAM information for all the servers in the server list will store in the RAMInformation file.

Regards,
http://tuitionaffordable.webstarts.com

robocopy data transfer for only new updates

Every DBA knows about of these asks when we need to transfer the updates and not the complete data every time. eg Logshipping transaction logs need to be copied between two cluster. How to achieve this goal.

robocopy \\SourceServer\Sourcefolder \\DestinationServer\Destinationfolder /Z /S /MIR

Switch /MIR is to apply only new updates.
Switch /S is to apply all the file levels -recursive in the folder.

Most of the readers of this blog also read "robocopy data transfer is very sluggish" to understand the use of /Z in this scenario so linking them here:
robocopy data transfer is very sluggish
Share your feedback which is very important to make the blogs more informative.

robocopy data transfer is very sluggish

We often see DBAs talking that the robocopy data transfer is very sluggish.

Recently I was involved in a project where 14 TB data was being transferred. My DBA informed me that the data transfer rate is just 325 KB/sec. With this rate of transfer it would take 1.4 years to transfer 14 TB of data. There was an absolute need fix either n\w design or robocopy. I chose to review robocopy command.

My DBA was using command with /Z option and without any /MT multi threading. The original command looked like as follows:

robocopy \\SourceServer\Sourcefolder \\DestinationServer\Destinationfolder /Z

I recommended following changes and confirm the speed

robocopy \\SourceServer\Sourcefolder \\DestinationServer\Destinationfolder /MT:64
/Z actually slows the speed a lot. This is absolutely great option to avoid any data discrepancy but I opted not to use this option and in case of any failures, the copy will be retried.

The speed was 595 MB/sec after this change. The copy time decreased from 1.4 years to whopping 6.8 hours. I am completely ok to retry this if any bit transfer is flipped because of n\w issue and causes corruption.