MSSQLCorruptionTackle
A blog to deal with SQL Corruptions and related problems
Monday, July 17, 2017
How to Serialize Using C sharp
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
Monday, July 3, 2017
SQL Server DBA Interview Questions
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.
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 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
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>)
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
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.
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%’
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.
Sunday, July 2, 2017
Ready for the next layoff
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
- Parental guidance how to control your kid in elementary school not obeying teacher
- Parental guidance for child development post terrible twos
- Parental guidance on how to handle child when they show their first infatuation at elementary school
- How to Handle fit of temper by fixing baby screen time
- Are Words Important
Wednesday, June 28, 2017
HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed
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>
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
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
Actually enable-migrations -EnableAutomaticMigrations -> This allows visual studio to keep track of the changes made to the db
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
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.
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
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
Subscribe to:
Posts (Atom)