MSSQLCorruptionTackle

How to Serialize Using C sharp

noreply@blogger.com (tuitionaffordable) — Tue, 18 Jul 2017 02:03:00 +0000

Serialize means changing the object in JSON key value pair. Following is a method to serialize the objects in C sharp.

class Program
{
static void Main(string[] args)
{

Boy objBoy = new Boy();
objBoy.FirstName = "FirstName";
objBoy.LastName = "Lastname";
objBoy.SchoolName = "School";
objBoy.Number = 100;
objBoy.CycleColor = "Color";

//Uncomment below line to test serialization
Console.WriteLine(JsonSerializeMethod(objBoy));

//Uncomment below line to test deserialization
//Person objPerson1 = new Person();
//string str = JsonSerializeMethod(objBoy);
//var result = JsonDeserializeMethod(str);

//var results = JsonConvert.DeserializeObject<dynamic>(str);

//Console.WriteLine(objPerson1.FirstNameOfPerson);

Console.ReadLine();
}
//Here is the link on How to fix 404 Issue
public static string JsonSerializeMethod(object o)
{
var str = JsonConvert.SerializeObject(o);
return str;
}

public static object JsonDeserializeMethod(string str)
{
var results = JsonConvert.DeserializeObject<dynamic>(str);
return results;
}

public class Boy
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string SchoolName { get; set; }
public int Number { get; set; }
public string CycleColor { get; set; }
}

public class Person
{
public string FirstNameOfPerson { get; set; }
public string LastNameOfPerson { get; set; }
public string SchoolNameOfPerson { get; set; }
public string PhoneNumberOfPerson { get; set; }
public string CycleColorOfPerson { get; set; }

public static explicit operator JObject(Person v)
{
throw new NotImplementedException();
}
}
}

//A great post on Job Flip Guidance and Improving Relations With Boss

SQL Server DBA Interview Questions

noreply@blogger.com (tuitionaffordable) — Mon, 03 Jul 2017 18:01:00 +0000

Following are some sql server 2016 dba interview questions to help you in prep and revise interviews in fifteen minutes.

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 is the link to help you. 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

How the data is stored physically in Columnstore Index?
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?
Why are you resigning from current position?

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

Answer 1:

The data is logically represented as a table but physically stored in column-wise data format. A clustered columnstore index is the physical storage of whole table. Some interviewers also ask what is Deltastore so the candidate should be ready with the answer. The deltastore is used by SQL engine to improve the performance of columnstore. Deltastore is actually a clustered index where some data is temporarily stored.

Answer 2:
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 3: 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 4: 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 MSSQL Server - DBCC LOGINFO 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 5: How to Start Service Using Powershell Commands.

Answer 6: 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 7: 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 8: 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 9: 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 10: 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 11: 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%’

Answer 12: This is pretty important question. Even though you are able to answer technical questions but if you don't answer this question appropriately then your changes of getting hired could vanish. You should be able to explain how the learning from your current role will help in the new role. You should be able to answer how you will be valuable to the new role for which you are interviewing. If you are interviewing because you want to change your company for better prospects, i would insist you read Are you Ready for Layoff and Boss Compatibility Matrix articles which can give you right focus on changing the job. If you are changing your employer due to sexual harassment reasons then please read How to Handle Sexual Harassment at Workplace and spread the words for public awareness.

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

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.

Ready for the next layoff

noreply@blogger.com (tuitionaffordable) — Sun, 02 Jul 2017 20:57:00 +0000

Layoff has become a norm. We observe that many companies fire bulk of employees. Automation, Robotics and Cloud business are important factors which are causing huge layoffs.

This is a new paradigm shift which all of us will be observing for a couple of years. How to perp yourself to make sure you are not laid off. To make sure that you are not the soft target you should know your Boss Compatibility Matrix. If your boss compatibility matrix is negative then you could be a soft target for the next layoff and hence work on the below guidance.

Job-Flip Preparedness Guidance

Are you relevant in the current market, if not then focus on your technical and domain skills?
Are you prepared for the interview? Working and interview preparation are different so focus on your interview preparation. Good article on INTERVIEW QUESTIONS AND ANSWERS can give you a glimpse how interview could be different from office work.
Are you expecting your bonus in next two or three months? If yes then are you aware how much bonus are you expecting? If it is not huge amount then better to flip the job now because after bonus cycle there will be more competition.
Did you submit your resume to all the important job seeking sites? There are couple of sites which have better functionality than others eg some seekers like dice, some like careerbuilder while other like naukari.com. Great link to know about which site is better.
Are you ready to relocate? If not then you are limiting your options. If you are on rent then my suggestion is to remain open for the relocation if you don't have other huge dependencies.

Personal Guidance

Do you have enough liquidity to survive if you are laid off? If you don't then you should figure out what expenditures could you stop, what are other options of saving, how could you avoid late fees on any credit card or utility bills.
Do you have your own house or live on rent? If you live on rent then it will really bite your pocket right after lay off. Make sure that you provision enough money for at least next couple of months.
Do you have a huge credit card bill to be paid? Make sure you transfer your bill to another card to buy time or pay at least minimum so that it doesn't attract the late fees.
Are you married and have family? If yes then there could be other liabilities as well which you should focus on eg school fees, school session, entertain wife and kids, visit close relatives etc.
Are you healthy? If not then make sure you start healthy practices ie Yoga, multivitamin tablets etc which will help you manage stress.

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, race, gender 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

HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed

noreply@blogger.com (tuitionaffordable) — Wed, 28 Jun 2017 19:37:00 +0000

HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable. Please review the following URL and make sure that it is spelled correctly.

Just observed that the view name was Employees instead of Employee

This is how i fixed it @MVC project

<li>@Html.ActionLink("Employees", "Index", "Employees")</li>

MVC Project - Enable background work to udpate the records

noreply@blogger.com (tuitionaffordable) — Wed, 28 Jun 2017 18:22:00 +0000

Below two commands are important in building MVC project on ASP .Net platform to make sure that with the first command you are able to change the schema of the objects and with the second command you are able to update the database.

Below Are these two commands

enable-migrations -EnableAutomaticMigrations
Update-Database

Actually enable-migrations -EnableAutomaticMigrations -> This allows visual studio to keep track of the changes made to the db

Unspecified error Exception 0x80004005 (E_FAIL)

noreply@blogger.com (tuitionaffordable) — Tue, 27 Jun 2017 23:45:00 +0000

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?)

noreply@blogger.com (tuitionaffordable) — Tue, 27 Jun 2017 23:11:00 +0000

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

SQL SERVER DBA “INTERVIEW QUESTIONS AND ANSWERS”

noreply@blogger.com (tuitionaffordable) — Mon, 05 Jun 2017 01:41:00 +0000

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

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%’

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

noreply@blogger.com (tuitionaffordable) — Sun, 04 Jun 2017 18:32:00 +0000

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.

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

Powershell Script to get RAM information

noreply@blogger.com (tuitionaffordable) — Fri, 01 Nov 2013 06:18:00 +0000

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

noreply@blogger.com (tuitionaffordable) — Fri, 01 Nov 2013 06:04:00 +0000

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

noreply@blogger.com (tuitionaffordable) — Fri, 01 Nov 2013 05:55:00 +0000

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.

The service was started and then stopped

noreply@blogger.com (tuitionaffordable) — Tue, 29 Oct 2013 18:29:00 +0000

Sql server agent service issue: My junior DBA met me on an interesting issue. Sql service account is not able to start the Sql server agent whereas Sql server service is running. When he tries to start sql agent service then he gets following issue:
“The request failed or the service didn’t respond in a timely fashion. Consult the event log or other application error logs for details.”
“The service was started and then stopped”
Let’s look at the errorlogs from I:\MSSQL10.MSSQLSERVER\MSSQL\LOG. Following error is observed:
Here is the actual problem: “The service account phx\_ctpfort doesn't have perms to start Eventlog service.”
Lets look at the permsisions:

1. Run -> cmd -> rsop.msc and press enter.
2. Click on Windows settings.
3. Click on System services.
4. Click the properties on Windows event log.
5. Click on View security to look for the service account under which the sql service is running.

We need to have it added there. Once added, the problem was resolved.
Please share your feedback and let me know if the blog was useful.

SCOM Powershell Queries

noreply@blogger.com (tuitionaffordable) — Mon, 28 Oct 2013 19:47:00 +0000

Recently, I was thrown in a drill where SCOM health needs to be verified.

"What is SCOM": SCOM is a server/client model that monitors the server health. Server heath can be categorized on multiple criteria. These criteria could be written in MP
(management pack) and then the servers are monitored on the basis of these parameters.
It would have been impossible for me to monitor 800+ servers without SCOM powershell commands.

Following are some commands where servers could be categorized in three
different states. The powershell command is also written against each and every category.

# of Servers where SCOM HeartBeat is Healthy get-scomagent | Where-Object {$_.healthstate -eq "success"} | Measure
# of Servers where SCOM Heartbeat has Issues get-scomagent | Where-Object {$_.healthstate -eq "uninitialized"} | Measure
# of Servers which are not healthy in SCOM get-scomagent | Where-Object {$_.healthstate -eq "Error"} | Measure

Please share your feedback which help me improve my articles.

Script of Getting OS Version using Powershell

noreply@blogger.com (tuitionaffordable) — Mon, 11 Apr 2011 20:10:00 +0000

Powershell makes it easy to get information from remote servers.

Below is the query to find OS Version 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-

PS C:\Windows\system32>$serverlist = Get-Content "D:\Powershell\serverlist.txt"

PS C:\Windows\system32>foreach($server in $serverlist)

{

$serverversion = Get-WmiObject -computer $server -cl Win32_OperatingSystem

write-output $server, $serverversion.version | out-file "D:\Powershell\OSVersion.txt" -append

}

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

Regards,
http://tuitionaffordable.webstarts.com

Error 1067: The cluster service is stopped on passive node of my two node sql cluster. It throws following error when I try to start this:

noreply@blogger.com (tuitionaffordable) — Sat, 09 Apr 2011 16:36:00 +0000

This is found that the cluster service in one of the passive stops. This throws following error when I try to start this.

Error 1067: The process terminated unexpectedly

I verified the password under which this cluster service is running. This account is perfect. So there should be some other problem.

Let's go ahead and see the problem in the cluster.log file.

Info from %WINDIR%\Cluster\cluster.log is as follows:

WARN [EVT] EvtBroadcaster: EvPropEvents for node 2 failed. status 1727
WARN [NM] RpcExtErrorInfo: Error info not found.

Error from clusterlog

Error: Failed to read (sector 12), error 170. and Failed to write (sector 12), error 170.

1. started from cmd. net start clussvc
2. The Windows Firewall/Internet Connection Sharing service should neither be enabled nor set to start automatically.

FmGetQuorumResource failed, error 170. Failed to read. Failed to form cluster, status 5086.

Follwoing was the problem:

The Admin NIC threw an error. This caused the netowrk connectionos in the cluster disabled. I disabled and enabled the NIC and this started working.

Regards,
tuitionaffordable

Sql Server Error 8951 Data row does not have a matching index row in the index

noreply@blogger.com (tuitionaffordable) — Sat, 19 Feb 2011 13:37:00 +0000

The error 8951 emerges most often when there is a duplicate row exists on PK. You would not be able to find the duplicate row

till you drop the PK and try as follows:

Let's Understand: There is a simple table EmployeeInfo where the PK is created on Empid, Rid, KeyInfo, DateTimeSlot columns. There are duplicate rows in the table for the columns on which PK is established but following query throws only one value for the following query even though two rows exist:

select Empid, Rid, KeyInfo, DateTimeSlot, count(1)
from Tuitionaffordable..EmployeeInfo
GROUP BY Empid, Rid, KeyInfo, DateTimeSlot
HAVING COUNT(1) > 1

Let's drop the PK and run the above query again. This shows me duplicate values for the columns on which PK is created. You need to filter the bad data out or modify these rows to avoid this info.

Let's again restore the same DB on a Test server and use following command for any specific value where this had duplicate rows:

select Empid, Rid, KeyInfo, DateTimeSlot, count(1)
from Tuitionaffordable..EmployeeInfo
where LTRIM(RTRIM(Empid)) = 97
and LTRIM(RTRIM(Rid))=2
and KeyInfo = 'ACAF387865567973E43C3ADB96C'
and DateTimeSlot= '2011-01-29 00:00:00.000'

This doesn't show the duplicate rows but following query will show you the data

select Empid, Rid, KeyInfo, DateTimeSlot,count(bit_delete)
from Tuitionaffordable..EmployeeInfo
where LTRIM(RTRIM(Empid)) = 97
and LTRIM(RTRIM(Rid))=2
and LTRIM(RTRIM(KeyInfo)) = 'ACAF31DBC69556EA9A973E43C3ADB96C'
and DateTimeSlot= '2011-01-29 00:00:00.000'
GROUP BY Empid, Rid, KeyInfo, DateTimeSlot

Following is the data:

Empid Rid KeyInfo DateTimeSlot (No column name)
97 2 ACAF387865567973E43C3ADB96C 2011-01-29 00:00:00.000 1
97 2 ACAF387865567973E43C3ADB96C 2011-01-29 00:00:00.000 1

The problem is in your DateTimeSlot column. drop the PK and this will catch where your duplicate data is.

Regards,
http://tuitionaffordable.webstarts.com

Restoring the stripped backup Files

noreply@blogger.com (tuitionaffordable) — Tue, 08 Feb 2011 22:16:00 +0000

The blog can give you good understanding on "MSSQLServer stripped backup".

Why anyone wants to go to stripped backups?
The answer is that when you have your drives on SAN and you want to use the I\O

from each more than one SAN while taking the backup then this is a good option to go for the stripped backups. Another point is that in some scenario the free space in one drive is not enough to accomodate your complete backup file.

After you are clear on why you decide stripping the backup file, lets talk on how to stripe the backupfile.

Command to create the stripped backups

BACKUP TO
DISK = 'E:\MSSQL\BAK\Tuitionaffordable_1of2.BAK'
,DISK = 'J:\MSSQL\BAK\Tuitionaffordable_2of2.BAK'
WITH INIT

Let's go ahead and restore the command
RESTORE   DATABASE Tuitionaffordable
WITH   RECOVERY, REPLACE

FROM DISK= 'K:\MSSQL\Test\Tuitionaffordable_1of4.BAK'
,DISK = 'K:\MSSQL\Test\Tuitionaffordable_2of4.BAK'
,DISK= 'K:\MSSQL\Test\Tuitionaffordable_3of4.BAK'
,DISK= 'K:\MSSQL\Test\Tuitionaffordable_4of4.BAK'

There is one more important thing. In case your destination server doesn't have enouth free space in the corresponding drives then above command will fail. Backup file has the information where it needs to create the mdf, ndf and ldf files. Let's take a scenario when you are going to MOVE all the files to only one drive eg K because you have enough free space only in K drive of your destination server. The command will be as follows:

RESTORE DATABASE Tuitionaffordable
FROM DISK= 'K:\MSSQL\Test\Tuitionaffordable_1of4.BAK',
DISK= 'K:\MSSQL\Test\Tuitionaffordable_2of4.BAK',
DISK= 'K:\MSSQL\Test\Tuitionaffordable_3of4.BAK',
DISK= 'K:\MSSQL\Test\Tuitionaffordable_4of4.BAK',
WITH RECOVERY, REPLACE

MOVE 'Tuitionaffordable' TO 'K:\MSSQL\Test\Tuitionaffordable.MDF',
MOVE 'Tuitionaffordable_Data2' TO 'K:\MSSQL\Test\Tuitionaffordable_Data2.NDF',
MOVE 'Tuitionaffordable_Data3' TO 'K:\MSSQL\Test\Tuitionaffordable_Data3.NDF',
MOVE 'Tuitionaffordable_Data4' TO 'K:\MSSQL\Test\Tuitionaffordable_Data4.NDF',
MOVE 'Tuitionaffordable_Data5' TO 'K:\MSSQL\Test\Tuitionaffordable_Data5.NDF',
MOVE 'Tuitionaffordable_Data6' TO 'K:\MSSQL\Test\Tuitionaffordable_Data6.NDF',
MOVE 'Tuitionaffordable_Data7' TO 'K:\MSSQL\Test\Tuitionaffordable_Data7.NDF',
MOVE 'Tuitionaffordable_Data8' TO 'K:\MSSQL\Test\Tuitionaffordable_Data8.NDF',
MOVE 'Tuitionaffordable_Data9' TO 'K:\MSSQL\Test\Tuitionaffordable_Data9.NDF',
MOVE 'Tuitionaffordable_Index' TO 'K:\MSSQL\Test\Tuitionaffordable_Index.NDF',
MOVE 'Tuitionaffordable_Index2' TO 'K:\MSSQL\Test\Tuitionaffordable_Index2.NDF',
MOVE 'Tuitionaffordable_Log' TO 'K:\MSSQL\Test\Tuitionaffordable_Log.lDF'

Regards,
http://tuitionaffordable.webstarts.com

Logshipping Error: "Skipping log backup file" and "Could not apply log backup file"

noreply@blogger.com (tuitionaffordable) — Tue, 25 Jan 2011 06:20:00 +0000

This was a bad day for log shipping. I was on a leave for last two days and got my junior DBA screeming over logshipping latency as soon I stepped in.

DBA got many skip messages in logshipping as follows:

2011-01-24 21:50:11.17 Skipping log backup file 'j:\logshipping\Tuitionaffordable\Tuitionaffordable_20110123100410.trn' for secondary database 'Tuitionaffordable' because the file could not be verified.

Another interesting error was as follows:

2011-01-24 21:50:10.92 *** Error: Could not apply log backup file 'j:\logshipping\Tuitionaffordable\Tuitionaffordable_20110123100410.trn' to secondary database 'Tuitionaffordable'.(Microsoft.SqlServer.Management.LogShipping) ***
2011-01-24 21:50:10.92 *** Error: Cannot open backup device 'j:\logshipping\Tuitionaffordable\Tuitionaffordable_20110123100410.trn'. Operating system error 32(The process cannot access the file because it is being used by another process.).
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

Let me discuss about these errors one by one.

Error 1 indicates that some transactional log file is missed in between. It just means that log chain is broken and you need to copy those logs manually to the secondary server.

Error 2 indicates that the log file is corrupt. Let's run following command to verify the log file.

RESTORE VERIFYONLY
FROM DISK = 'J:\logshipping\Tuitionaffordable\bak\Tuitionaffordable_20110123100410.trn'

Msg 3203, Level 16, State 1, Line 1
Read on "J:\logshipping\Tuitionaffordable\bak\Tuitionaffordable_20110123100410.trn" failed: 13(The data is invalid.)
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

And this told that your log file is invalid. This happens when you use robocopy or xcopy to transfer file manually and your session is logged off. So get those files again from primary DB manually. I prefer to create a job and run it so that session logoff doesn't cause any problem.

Most of the readers of this blog also read following interview questions so linking them here:
Powershell Interview Questions and Answers
SQL Server DBA “Interview Questions And Answers”
SQL Azure Interview Questions and Answers Part - 1

Regards,
http://tuitionaffordable.webstarts.com

Windows Azure Interview Questions and Answers

noreply@blogger.com (tuitionaffordable) — Mon, 17 Jan 2011 00:06:00 +0000

Custom Search

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

Qu1: What is Windows Azure?

Ans: Windows Azure is Microsoft Cloud Computing OS. Someone needs to worry about the h\w requirement, n\w requirement and the correct OS and much other stuff when he starts designing an application. Windows Azure will help the developer to get rid of these things and let him concentrate on main area which is nothing but automation and business needs.

Qu2: What is fabric?

Ans: In the Windows Azure cloud fabric is nothing but a combination of many virtualized instances which run client application.

Qu3: What is the downtime for applications in case of any patching?

Ans: Windows Azure will have replicas for each and every application and provide zero downtime in case of any security patching.

Qu4: How many copies of data are maintained in Windows Azure?

Ans: Windows Azure provides you with three copies of data. This makes your application running on very reliable data.

Qu5: What is queue storage in the Windows Azure?

Ans: Queue storage gives you capability of sending the small data just as messages. Queue storage basically helps informing the task to the worker threads.

Most of the readers of this blog also read following interview questions so linking them here:
Powershell Interview Questions and Answers
SQL Server DBA “Interview Questions And Answers”
SQL Azure Interview Questions and Answers Part - 1

Regards,
http://tuitionaffordable.webstarts.com

MS SQL SERVER 2008 Fix Fragmented Index

noreply@blogger.com (tuitionaffordable) — Fri, 14 Jan 2011 22:03:00 +0000

Who Should Read This Blog: MS SQL Server DBA facing following issues:

1. MS SQL Server DBA - Index getting fragmented often.
2. MS SQL Server DBA - Index not being used by the queries properly.

I can see many DBAs asking the same questions again and again regarding index fragmentation. Most of them take a wrong path to fix this problem. Let's understand how index fragmentation happens and how to fix this.

Index fragmentation is of two types: Internal and External. Internal is not very harmful. Let's talk External now.

Before you start fixing index fragmentation, you should be able to identify whether the system is OLTP or OLAP. Please never go for high fill factor if you have an OLTP system and low fill factor if you have an OLAP system.

Let's Understand: If you go for high fill factor then this means you are allowing more data
to absorbe into the index pages. And when new data will come in the order, page split will
occur. So wait before you look for high fill factor. The same rule applies on Low fill
factor also. If your system is OLAP then never ever go for low fill factor as this will
unnecessarily consume the space on each page. So the key is to understand your system
before you understand the problem.

Reason of Index Fragmentation:

1. Autoshrink. Verify that autoshrink is not on. The sql server will cost high fragmentation in case it needs to shrink the data. I believe to confirm index defragmentation as soon as you ever need to shrink the DB.

2. Having less Fill factor on OLTP env can cause the more page splits. Please understand your system before you go for the fill factor and index managment. You can fix the fill factor with following command.

ALTER INDEX PK_Powershelltute ON [Tuitionaffordable].dbo.Powershelltute REBUILD WITH
(ONLINE=ON, MAXDOP=1, SORT_IN_TEMPDB=ON, FILLFACTOR=95)

3. Following is the query where you can identify the fill factor of your tables:

SELECT tbl.name AS table_name,
        indx.name AS index_name,
        indx.fill_factor
FROM    sys.tables tbl
        JOIN
        sys.indexes indx
        ON tbl.object_id = indx.object_id
WHERE
        indx.type in (1,2)
ORDER BY
        tbl.name,
        indx.name

Regards,
http://tuitionaffordable.webstarts.com

SQL SERVER 2008 R2 - Trasaction and Isolation Levels - "Cannot roll back Transaction. No transaction or savepoint of that name was found."

noreply@blogger.com (tuitionaffordable) — Wed, 12 Jan 2011 07:39:00 +0000

Who Should Read This Blog: DBA who wants to understan how Transaction works.

Let's run following command first:

1. SELECT @@TRANCOUNT
--Result 0

2. DBCC OPENTRAN()
--Result as follows
Transaction information for database 'Tuition'.
Oldest active transaction:
    SPID (server process ID): 54
    UID (user ID) : -1
    Name          : Affordable
    LSN           : (3073:52373:1)
    Start time    : Jan 11 2011 6:23:47:993PM
    SID           : 0x0105000000000005150000001ac6c4f30376dea8123bc97be8030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So the difference between these two commands are as follows:

1. @@TRANCOUNT will tell you how many open transactions are there whereas DBCC OPENTRAN() tells only about the oldest trasaction.

2. @@TRANCOUNT will tell you the open transaction as soon as you run BEGIN TRAN but DBCC OPENTRAN() will tell some information only when any other command is run under BEGIN TRAN.

There is more to understand about the transactions and two popular ISOLATION Levels ie READ COMMITTED AND READ UNCOMMITTED:

Let's create a simple test table TestTran with a column server. Populate the table with any data.

Execute the commands as follows:

SELECT @@TRANCOUNT --0
DBCC OPENTRAN()
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Let's run following command now and verify the difference between @@TRANCOUNT and DBCC
OPENTRAN()
BEGIN TRAN Tuition

Let's verify

SELECT @@TRANCOUNT --1
DBCC OPENTRAN()
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I want to add one popular error in the blog which most of the DBAs would get when they try to rollback the transaction. Use follwoing code to see the error:

BEGIN TRAN Affordable

ROLLBACK tran affordable
Msg 6401, Level 16, State 1, Line 1
Cannot roll back Affordable. No transaction or savepoint of that name was found.

Try this now:

ROLLBACK tran Affordable
Command(s) completed successfully.

Now this is the time to go ahead with understanding how nested transactions behave:

Custom Search

Run following command:

BEGIN TRAN Affordable1
UPDATE TestTran
SET Server= 'Affordable1'

BEGIN TRAN Affordable2
UPDATE TestTran
SET Server= 'Affordable2'

BEGIN TRAN Affordable3
UPDATE TestTran
SET Server= 'Affordable3'

Now we shall try to rollback the last trans Affordable3. We get following error msg:

Msg 6401, Level 16, State 1, Line 1
Cannot roll back Afford. No transaction or savepoint of that name was found.

You can commit any internal transaction but cann't rollback. Now if you rollback the transaction the changes affected by the internal transaction would go away.

Let's also understand READ COMMITTED and READ UNCOMMITTED Transaction Isolation Level.

Let's run following command on one connection.

BEGIN TRAN Affordable1
UPDATE TestTran
SET Server= 'Affordable1'

Run following command on another session

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT *
FROM TestTran

The session will hung as you asked the sql server to show you only committed data.

Now run following command on another session

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT *
FROM TestTran

This data is nothing but the "dirty read" because if you rollback the transaction in the previous session then the data would change.

Regards,
http://tuitionaffordable.webstarts.com

Cost Analysis Among EXISTS (SELECT 1), EXISTS (SELECT COUNT(1)), EXISTS (SELECT *) AND EXISTS (SELECT TOP 1)

noreply@blogger.com (tuitionaffordable) — Mon, 10 Jan 2011 23:36:00 +0000

Let's understand how 'SELECT 1', 'SELECT COUNT(1)', 'SELECT *' and 'SELECT TOP 1' would behave when they are called in the WHERE EXISTS clause. Let's look at following queries and find out which one will cost least.

Query 1:
SELECT a.ProductId, a.ProductName
FROM Product.Catalog AS a
WHERE EXISTS
(SELECT *
FROM Product.Products AS b
WHERE a.ProductId = b.ProductId
AND a.ProductName = 'RAM');
GO

Query 2:
SELECT a.ProductId, a.ProductName
FROM Product.Catalog AS a
WHERE EXISTS
(SELECT 1
FROM Product.Products AS b
WHERE a.ProductId = b.ProductId
AND a.ProductName = 'RAM');
GO

Query 3:
SELECT a.ProductId, a.ProductName
FROM Product.Catalog AS a
WHERE EXISTS
(SELECT TOP 1
FROM Product.Products AS b
WHERE a.ProductId = b.ProductId
AND a.ProductName = 'RAM');
GO

Custom Search

Query 4:
SELECT a.ProductId, a.ProductName
FROM Product.Catalog AS a
WHERE EXISTS
(SELECT COUNT(1)
FROM Product.Products AS b
WHERE a.ProductId = b.ProductId
AND a.ProductName = 'RAM');
GO

There is a grave misconception among DBAs and I saw most of them choosing query 3 and 4. The clue is that work under 'WHERE EXISTS' completes as soon as the first record is returned. Query 1 and 2 are same here. Now Query 3 is more costlier than the first two and query 4 is most costly. Also query 4 is nowhere equivalent to others as this will always execute even this returns 0.

We can arrange the queries Cost wise: query 1 = query 2 < query3 < query 4

Regards,
http://tuitionaffordable.webstarts.com

Comparison between Litespeed Backup and Native Backup Compression

noreply@blogger.com (tuitionaffordable) — Fri, 07 Jan 2011 04:18:00 +0000

Following article would help you understand the benefit of taking backup using litespeed. The backup file size with litespeed is far lighter than the native backup with compression. This is a well known fact that the compression of any backup depends on the data in the DB. This is why the ratio of this analysis can vary in different scenarios. I tested this with 5 different kind of DBs and always got better results with Litespeed. Let’s walk through:

Database size- 3GB

Comparison between native backup and litespeed backup-

--Native Backup

BACKUP DATABASE Tower

TO DISK= 'D:\towerbknative.bak'

WITH COMPRESSION

Custom Search

--Litespeed Backup

EXEC master.dbo.xp_backup_database

@database='Tower'

, @filename='D:\towerbknative.bak'

, @init=1

, @compressionlevel = 8

, @encryptionkey='Password'

--Native Restore

RESTORE DATABASE Tower

FROM DISK= 'D:\towerbknative.bak'

--Litespeed Restore

EXEC master.dbo.xp_restore_database

@database='Tower'

, @filename='D:\towerbknative.bak'

, @encryptionkey='Password'

Considering the Newbies in litespeed, let me explain how to take the litespeed backup at Litespeed console.

Step 1- Go to Backup Manager wizard in LiteSpeed consol

Step 2- Select Backup type as Fast Compression-

Step 3- Next > Select the backup destination>Next Fast Compression type- Select Self-containded backup sets.

Step 4- Next>Next> Compression :Select Compression level as 8 which is highest level of compression.

Give your encryption password

Step 5- Finish. Your backup is created in the destination.

In my case the backup was of 3GB and my LiteSpeed backup size after fast Compression, is only 753KB.

You can see the status of your backup job in the job manager window by clicking Cntr+5:

Same way you can restore the DB using Litespeed Restore Backup Wizard in Backup Manager.-

Regards,
http://tuitionaffordable.webstarts.com

Sql Job Failure - Unable to Determine if the Owner (PROD\tuitionaffordable) of Job has Server Access "error code 0x2"

noreply@blogger.com (tuitionaffordable) — Fri, 07 Jan 2011 02:40:00 +0000

Unable to determine if the owner (PROD\tuitionaffordable) of job has server access error code 0x2

Got following error for some job which runs every day without any issue-

Error

Date 1/6/2011 2:25:00 PM

Log Job History (Tuitionaffordable-REPLDB-Tuition-28)

Step ID 0

Server Tuitionaffordable

Job Name Tuitionaffordable-REPLDB-Tuition-28

Step Name (Job outcome)

Duration 00:00:09

Sql Severity 0

Sql Message ID 0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted 0

Message

The job failed. Unable to determine if the owner (PROD\tuitionaffordable) of job Tuitionaffordable-REPLDB-Tuition-28 has server access (reason: Could not obtain information about Windows NT group/user 'PROD\tuitionaffordable', error code 0x2. [SQLSTATE 42000] (Error 15404)).

Let's Understand: The error is thrown because the account PROD\tuitionaffordable is either disabled or doesn't have access to the shared folders where the job tries to save/access files.

Solution: Change the owner of the job to some other account which has access to the shared folders where the job tries to save/access files.

Another Problem: What if there are 50 jobs with the same owner id and I want all of them to change.

Solution:

UPDATE sysjobs

SET owner_sid = 0x01

FROM sysjobs

INNER JOIN sysjobhistory hist

ON hist.job_id = sysjobs.job_id

AND hist.run_status = 0

AND hist.message LIKE '%PROD\tuitionaffordable%'

Note: When the owner of the job is sa then the job runs under the account under which sql agent runs.

Custom Search

Regards,
http://tuitionaffordable.webstarts.com