SQL Server interview questions

Are you planning for your SQL interview ? Want to crack your SQL Interview ? then go for the SQL Server interview questions book, 

        This book gives you a complete idea about the SQL database. It starts with a very basic concept like what is a database, its usage, types, creation, and data storage, security, sorting, and searching for a stored procedure. This book is a complete set of interview breaking questions and answers with live examples and plenty of screenshots. This book takes you on a journey to mastering the SQL database, including SQL datatypes, functions, triggers, and stored procedures. This book also covers the latest and new features of SQL 2016, 2017 and 2019 CTP with examples.

In the beginner section, we start with very basic concepts like what is a database, why to use a database, different types of database types, what is SQL, its usages, advantage and disadvantages, SQL datatypes, its different operators and how to use them with samples.
In the intermediate section, we will learn about the different SQL functions, SQL Joins (used to fetch values from multiple SQL tables) and SQL DDL, DCL, and DTL commands.
(About the last chapters) This is the advanced section of the book where we have provided an explanation of the SQL stored procedure, triggers, and SQL view concepts, additionally, we have covered SQL core concepts like keys, indexes, injections and constraints. We have also introduced cutting-edge concepts like SSRS, SSIS, SQL Cloud database (Azure), JSON Support and a list of the new features of SQL 2016, 2017, CTP-2019 with SQL performance improvement tips. Finally, we have ended the book with a series of random SQL questions and answers.

TAGLINE 
Let us break the SQL interview with the help of SQL Server interview questions.

KEY FEATURES  

• Database Basic Concepts

• SQL Fundamentals

• DDMS, SQL Statements, and Clauses

• SQL Operators, Datatypes, and Keywords

• SQL Functions, Wildcards and Dates

• SQL Joins and CASE Statement

• SQL DDL, DCL, and DTL Statements

• SQL Stored procedures, Triggers, Views, and Transactions

• SQL Keys, Indexes, Injection, and Constraints

• SSRS, SSIS, SQL Cloud database (Azure), and JSON Support

• New features of SQL 2016, 2017, and 2019

• SQL Performance Improvement Tips

• Fuzzy Interview Questions and Answers

WHAT WILL YOU LEARN  
After reading this book, you will be able to understand SQL database concepts, handle core database activities like data security, searching, migration, and sorting. You will be able to handle the database transactions, use different SQL datatypes, functions, triggers, and stored procedures to save and retrieve data from the database. You will also be able to understand advanced SQL concepts like SQL reporting services, integration services, cloud database and new features from the latest SQL versions like 2016, 2017, and 2019.

WHO THIS BOOK IS FOR 
This book is built in such a way that it is useful for all categories such as technical or non-technical readers. This book is perfect. If you are a fresher and you want to learn about SQL, or if you are a teacher and you want to spread SQL knowledge, this book is very helpful. If you want to crack the database interview or if you are working as a DBA and you want to upgrade your knowledge, or if you are backend developer, database tester, performance , or if your role is that of a database admin, SQL developer, data analyst, mobile app developer or if you are working on core SQL concepts, this book is just right for you.
This book is very useful as it contains many simple real-time scenarios for each concept. All functionalities are explained with real SQL screenshots and database records.

CONTENTS
1. Database and SQL Basics
2. DDMS SQL Statements and Clauses
3. SQL Operators, Keywords, and Datatypes
4. SQL Operators
5. SQL Functions, Wildcards, and Dates
6. SQL Joins and CASE Statement
7. SQL DDL, DCL, and DTL Statements
8. SQL Stored Procedures, Triggers, Views, and Transactions
9. SQL Keys, Indexes, Injections, and Constraints
10. SSRS, SSIS, SQL Cloud database (Azure), and JSON Support
11. New features of SQL 2016, 2017, and 2019
12. SQL Performance Improvement Tips and Fuzzy Interview Questions

https://bpbonline.com/collections/new-relases/products/sql-interview-questions-a-complete-question-bank-to-crack-your-ann-sql-interview-with-real-time-examples

Thanks
Prasad

Debug your stored procedure.

Getting started

We can debug our .NET application easily using a debugger from debug menu, .NET application has its own .PDB (Program Debug Database) file that used to store Debug location and increment linking of debug configuration. This all about .NET but can we debug our stored procedures? Answer is YES, we can debug our stored procedure. Let's find out How to do it.

Stored procedures are popular enough due to their features like enhanced security, separation is possible in data functionality and application, improved performance (as fewer call made to database), basically it's a one time and one place processing theme. SP (stored procedure) is now more popular in DBA and Developer crowd, But after writing large SP's, how to debug them ? How to test them if they are running properly

So here is the need to Debug stored procedure, Let's see what we need to debug it.

Bit by bit

1. First step is to create a Stored procedure (if you have stored procedure already created then you can skip this step), Here i am creating a simple Stored procedure, which select record from EMP table for specific ID, see below snap

2. After stored procedure created successfully, we are ready to debug it. Basically There are couple of ways to debug stored procedure

1. Using SQL Management studio (SSMS)
2. Using Visual Studio

Will see how to Debug it using SQL Management studio (I am using SQL 2008R2 Express)

• Open SQL 2008 R2
• Click on ViewMenu --> Object Explorer
• Click on Connect database engine  --> Enter username and password for SQL database --> Connect to database, see below snippet

            

• Expand Database --> Select Programmability folder --> Stored Procedures (You will see your procedure here)

            

• Open New Query window by right click on database and click on 'New Query'
• Write statement 'Exec ProcedureName' (in my case its procedure name is 'sp_empInfo')
• Put Debugger on that line using key F9

            

• Select Green arrow (debug) from SQL and your debugger gets start, Press F11 to step in to procedure
• You can see output in 'local' and 'callstack' window

          

Debug it using Visual Studio

Same way you can use Visual Studio to debug the stored procedure

• Go to Visual studio and connect database. follow below steps
•  Open Visual studio editor
•  Click on ViewMenu --> SQL Server object Explorer (Before VS 2012 it was Server explorer)

            

•  In SQL Server object Explorer Pane right click on Data Connections
•  Select Add connection
•  Enter credential (like SQL instance, SQL server user name, Password, database name)
• Right click on database and allow 'Application debugging' and 'Allow SQL/CLR debugging', see below snap

           

• Expand Database --> Select Programmability folder --> Stored Procedures
• Right click procedure --> Select 'Debug Procedure'

           

You can start Debug from now.

Exceptions

Access permission is the biggest issue while debug stored procedure,

Common Error Messages

following are the common error messages occurred during debug stored procedure

1. Unable to start the transact-SQL debugger, could not connect to the Database Engine instance...
2. Unable to start T-SQL debugging, Could not connect to computer...
3. Logon failure: Unknown username or bad password...
4. Failed to start debugger, The execute permission was...
5. Could not attach to SQL Server process on...

see some exceptions/errors snap below

Following exception occurred when you tried to debug Remote stored procedure

Common points to avoid error while debugging

To resolve above errors you can try out following workarounds

1. If you are debugging stored procedure on local SQL server then your local SQL user should be in 'sysadmin' role, to add user in sysadmin role, run below stored procedure (it is inbuilt), see below syntax
2. Hide   Copy Code

   sp_addsrvrolemember '<Login>', 'sysadmin'

3. Configure TCP port in Windows firewall to enable t-SQL Debugging, for details see MSDN link
4. Configure TCP port in Windows firewall to enable for remote t-SQL Debugging (when SQL server is different and want to Debug it from client machine) see MSDN link, For remote debugging in either case you can use Windows local or domain account authentication
5. Open firewall ports on both SQLserver machine as well as client, PORTS: TCP 139, TCP 445, UDP 137 and UDP 138 
6. SQL management Studio is should be lunched with “Run an administrator…”
7. Management studio(Client machine) and Server (SQL) should be in same domain, otherwise you need to setup account with same Username and password

What we learn !

We can debug stored procedure by using Visual studio or by using SSMS (Sql management studio), we can use local variables, call stack windows for that. You can provide parameters to stored procedure (if any) and get instant debugger on it. 

Hope it will be useful in daily .NET/SQL life. 

Thanking You

- Prasad

How to choose between .NET Framework and .NET Core for server apps

Introduction

DevOps is rapidly transform in last few years, There was a time when people used to code on VB 6 to create EXE that will used on Win XP to check if .NET framework is installed, Now a days we travel from .NET 1.0 to 4.5.x and Now .NET Core. 

.NET structure, CLR, MSIL has changed a lot over years, .NET is not limited to only Windows or specific version of OS, it has improved a lot and supporting modern features like Cross platform, Microservices, Docker container, Mobility, Cloud computing and reliability, But the question remain same, When to choose .NET Framework and When to choose .NET Core, Lets walk through this article, it will help you to choose between .NET Framework and .Net Core for server Apps.

Preparation

If you want to develop web based applications/web apps then there exist two strong foundations from which you can either use .NET framework or .NET Core, No doubt, both are having many similar components but there exist some fundamentals difference too. Depending on your requirement and nature of application you can choose either of them.

When to choose .NET Framework

The one-liner answer for this question is "You need to choose it, when .NET Core not able to replace .NET framework in all server applications and its related component"

1.     If you are having existing application in .NET framework and want to develop some new part in .NET Core then Don't try it, instead of Using .NET Core, just create a separate addon (in .NET Core) and call it from .NET Framework (e.g. Microservices using .NET Core)

2.     There are much chances that you are using any 3rd Party DLL which is in .NET framework but not available in .NET Core, then you should go with .NET framework only

3.     If there are any .NET framework technologies that are not available in .NET Core then do not go for .NET Core, Here is the list of .NET framework common technologies that are not available in .NET Core

1.     WCF Services implementation : You cannot consume WCF services from ASP.NET Core, but may be in the future they will consider it.

2.     WWF  (Windows work flow foundation) is also not available in .NET Core.

3.     ASP.NET Webforms : There are no web forms exist in .NET Core and might be there is no future plan too.

4.     Limited Languages support : VB (Visual Basic) and F# are currently there in .NET Core but not supported by all project types.

5.     ASP.NET Web pages are also example of it.

But Microsoft are working on those things and trying to fetch as many things in .NET Core as they can, you can check the list here

When to choose .NET Core

This is the brand new .NET technology with different base structure than .NET, no doubt, it is not having that much classes and libraries that support in .NET framework, but it is faster and modular than .NET Framework

1.     If you want to run your application on multiple platforms then you can go with it, .Net Core supports all popular platforms like Windows, Mac, Linux, Cross platform is the biggest advantage of .NET Core

2.     If you want to develop application on MACOs  or Linux then you can go with .NET Core, as you can do it using Visual Studio code (Which is open source and free, visit Code.Visualstudio.com for more details) , Additionally there are some third party editors available for it.

3.     When you want to use Microservices then you can choose .NET Core. (In Micro services we break down large project in smaller independent runnable modules, these modules are communicated with each other via API calling) This will also help us to mix different technologies in single project (Like we can mix Java, ruby, .NET Framework)

4.     You can use .NET Core, when you need scalability with good performance. .NET core is faster than .NET framework, it helps to reduced server and VM cost, it loads with minimum set of libraries that are really needed.

5.     .NET Core has seamless support to Containers, .NET Core can be used with different design patterns and microservices. ASP.NET Core is cross platform it is much easier to deploy docker containers in .NET Core  than in .NET framework.

6.     .NET Core allows you to installed different version of .NET Core on same machine, so that you can run .NET Core application side by side that targeted to their specific installed .NET Core versions

Finally

Developers are very curious about learning and implementing  .NET Core but there are some pros and cons to it. If you are running large enterprise application that built on .NET Framework  do not try to migrate it, Consider your requirement first and then proceed.

              Technologies are introduced and enhanced to overcome earlier technology problems, but some where we need decide to move on or not. Here time, cost, resources and need are the four factors affecting to take this decision. Finally it's your choice that what you want to accomplish

 Thanks

koolprasad2003

How Search Engine works ? How SEO works ?

History

Last year my friend has deployed his own website on internet and tries to search on Google/yahoo and other search engines but they are unable to enlist the website in it, even Google failed to show the name of his website in auto complete/suggestion textbox too. Everyone wants to see their website name on first search page of Google/yahoo/other search engines when they search.
But it needs some technique that should be apply and used for each webpage that we have deployed on internet.
This technique is known as Search Engine Optimisation. (in simple words, Help search engine to search the things quickly by applying some simple rules on your web pages)

Introduction

Search engines are the heart of the internet. we know there are zillions websites available on internet and still increasing day by day, In such a case search engine plays a very important role to search any content/pages on this ocean.
SEO optimization is not a single cup of tea, it needs a optimization of WebPages and follow different rules while generating them. In my previous article i have explain some of the SEO tips, you may check this article on this link
By following SEO tips it will improve sites' interaction of both users and search engines. This article does not give a magic pill that can show your website always on top page of search engine, but this article tell you about the best practices
that outlined below and will make it easier for search engines to crawl, index and understand your content.

Start with Optimization tips

Every year search make changes in their way of working and try to optimize the search patterns, your site needs to update as well to persist in this race. 
Contents are the most important of your web-page, the contents are the things that collects new users/visitors for you, that make your site/web-page popular, while following SEO tips modifying content with good keywords is most essential

PageTitle- The start of search engine life

The most important part of search engine is Page-title. Search engine begin the search from here only. Your application/website should have good accurate page title. By using title user and search engine should know "what the topic of a particular page is"

         Many times i have seen there is no page title for many Web-pages. Avoid doing that, add Page title to each pages, the Page title should be present under 'HEAD' section of page. When user search any 'term' in Google then if the searched 'term' is exist in title then it will displayed as BOLD in Google search result. Most of the time the Google suggestion list is picked from Page title itself.
see below snap

Points to Note

- Search engine optimization affects only organic search results, not paid or "sponsored" results such as Google Ad Words.
Now the question is, what is Paid/sponsored ad words and what is organic search
The search result highlighted with some light color and appear at the top the search page is called as 'Paid/sponsored ad words', and the rest of search result is come under 'Organic Search' result
Below snap will clear your idea

How search engine works

When search engine find the pages in whole web ocean, Search engine works with the help of the page elements itself, it consider following things while searching.

• Proper Title of the page which helps people to know more about your article without opening it, we have seen if your title is proper and useful then search engine like Google show them in BOLD in top of the page
• Keywords that are used in page. The keywords are the words that are put by the user in search engine criteria for searching, so our website should contain proper and popular keywords. Frequency and location of keywords is most important in Web page.
• Do not try to include 'FAKE' keywords in your website that are not related to your web content otherwise your website may be BLACK listed by search engine. If the keyword only appears once within the body of a page, it will receive a low score for that keyword.
• You should use your main keyword for each page in the file name, preferably at the beginning of the first sentence. 
• Links used in page are played important role in searching. Search engine uses 'crawing and indexing' like algorithms for link search, it follows the link and check for the rating of linked page.
• Broken link, spam link may decrease your website ratings, Google like search engine looks at how many Web pages link to a particular site to determine its relevance
• Uniqueness of the content is also a noticed by search engine and proper use of Meta tags is also important.
• One of the noticeable aspect is "How long the Web page has existed on web?", The age of the page is also considered while searching. People create new Web pages every day, and not all of them stick around for long, Google link search engines places more value on pages with an established history.
• Below snap show you how Google locate a title in possible list

Google like search engine take use of automated robots for searching like "crawlers" or "spiders", with the help of them it can reach to many billions of interconnected documents. Currently, the major engines typically interpret importance as popularity.
The more popular a site, page or document, the more your rating is. Here in this case search engines have continued to increase users’ satisfaction by using metrics that interpret popularity.

Make our pages popular

• While designing content of the page you should keep a thing in mind that, pages should primarily designed for users, not for search engines. 
• Don't be dishonest with your users or present them different content that are used for search optimization, this is commonly referred to as cloaking.
• While you make website you should make a clear hierarchy of text and links. Every page should be reachable from at least one static text link.
• Create a useful, information-rich site, and write pages that clearly and accurately describe your content. 
• Make sure that your "TITLE" elements and "ALT" attributes are descriptive and accurate.
• Give rel='nofollows' attributes to every link that you used in your websites, it will stop the search engines to follow the link, it will improve performance.
• First you need to understand the Use of keywords, it is the pathfinder for websites, You should create descriptive, human friendly URLs. 
• Bing like search engines recommend the following things to get better rankings in their search engine:
• You should develop a clean and keyword rich URL structure is in place
• Make sure that your content is not buried inside rich media like (Adobe Flash Player, JavaScript, Ajax) and you should need to take are that rich media doesn't hide links from crawlers.
• Create keyword-rich content based on research to match what users are searching for.
• Always update your website to produce fresh content regularly.
• Don’t put the text that you want indexed inside images. For example, if you want your company name or address to be indexed, make sure it is not displayed inside a company logo.

Finally, internet is the big ocean and search engines always try to search for golden fishes in it, lets help it to make a better outcome.

-Happy Searching

KooooL

When to use ASP.NET Webforms and ASP.NET MVC

In this article I have explained, When to use ASP.NET Webforms and ASP.NET MVC. We know what is ASP.NET and what is ASP.NET MVC, but we are confused when to favor which technology




Last week my friend has started a new web based project in .NET, he asked me In which technology he should go ASP.NET or ASP.NET MVC ? 

Now a days everyone talk about ASP.NET and ASP.NET MVC but if we asked them, when to favor which technology ? all goes confused. There is no clear-cut answer, and rightly so. This article will help you to choose between ASP.NET and MVC depend upon your requirement and tell you Advantages / Disadvantages of ASP.NET MVC. Lets start with it.

ASP.NET:

Basically ASP.NET was developed to improve the speed and performance of classic ASP, Microsoft's ASP.NET, one of the most successful web application development frameworks ever.You can use ASP.NET to fast develop and deploy highly scalable, high-performance web applications in a managed environment

When to use ASP.NET

1. RAD – Rapid Application Development

RAD is the strong point of ASP.NET, ASP.NET does not deal with HTML markup directly, rather it gives set of powerful tools to developer to design and develop web application, compiler then convert it to Markup and show it on web application, so developer does have to take care of internal architecture and can develop application rapidly.

2. Cost

When Cost is matter ASP.NET is really helpful to you. As it is bit old technology and start of the web development phase, maximum developer community is aware of it and develop using ASP.NET, where as MVC is newer than comparing with ASP.NET, so there is cost incurred to development with ASP.NET MVC

3. Stable implementation

ASP.NET Web Forms implementation is now a stable and robust with many years of deployment experience. over the year ASP.NET is upgraded and is now more firm and dependable.

Drawbacks/Limitation of ASP.NET

• Customization is not possible and easy with built in ASP.NET control set.
• As ASP.NET create its own markup tags while rendering on browsers which is very much different than actual HTML tags so flexibility among cross browser compatibility and devices are less
• The generation of each control ID is vary framework to framework, which needs to make changes in scripting as well.
• maintaining data between post back is additional task for ASP.NET, as it's page life cycle is depend upon ViewState
• Final web page render size is also an issue for ASP.NET as view state increase each page size.
• ASPX pages has fixed URL render pattern which will slow down SEO and search engine crawling.
• ASPX page does not render without its code behind, so there is additional load on web page each time

ASP.NET MVC

I have read somewhere, "MVC framework is not efficient unless you're willing to roll up your sleeves and build your own project specific infrastructure ", I think it is really true, MVC is complex for Oldies and Simple for New projects, ASP.NET MVC is support Test-driven development (TDD) . I have discuss some points about ASP.NET MVC below. which includes When to favor, Advantages and limitations.

 When to use ASP.NET MVC

ASP.NET MVC has very scatter structure so code separation is strong point, No doubt ASP.NET MVC has many great features like Bundling, minification, Parallel Development, Test-driven development ( It actually leads to very short development life cycle), Web API (for building HTTP service), URL Routing Scaffolding.
But there are some facts and things that we need to consider before using ASP.NET MVC, so lets consider some priorities while going with ASP.NET MVC

1. Complex form structure

ASP.NET MVC has more hold on HTML so When you want to develop a complex form structure, you can use it, MVC allows for any number of forms on the page

2. Purpose of website is for public

If you are build website or web application targeting to public like banking or reservation then you need to go for MVC as it has very SEO friendly and lightweight.

3. Separation of concerns

When your concern is code separation you can go for MVC as it has very scattered structure with Model, View and controller, here is very less chances of getting things more complex.

4. Different view device wise

If you are targeting you development towards multiple render devices then go for MVC it offer design flexibility so that same web page can be easily render on desktop browser, mobile and smart phone devices in simple render mode

5. Easy Unit testing

As with the help of MVC we can developed loosely coupled application, it is very easy to test each module independently, Dependency Injection is more useful in such cases

6. Easily blend with other technology

ASP.NET MVC has capacity to easily blend with other technology, jQuery and other JavaScript frameworks can be simply add in ASP.NET MVC

7. Automatic Unit Testing

If you want to go for automatic unit testing then ASP.NET MVC is good for you.

Drawbacks and limitation of ASP.NET MVC

- It is not easy to understand and code, within limited time frame
- ASP.NET structure is very much different from MVC it is Difficult to convert an existing site from ASP.NET to ASP.NET MVC.

Facts of life

• More often practical conditions are not same as hypothec conditions the choice between ASP.NET and ASP.NET MVC is not a single cup of tea, it is vary from project to project and team to team 
• If you have very large ASP.NET project then do not go for MVC as it is like to build your project from scratch, it may be leads to time and cost wasting
• If you want to deploy project targeting more user volume and devices then go with MVC 
• Friendly URL's for SEO with no viewstate and cleaner HTML is the powerful point which leads MVC to perform faster

Finally, There are Pros and Cons for both ASP.NET and ASP.NET MVC, This article is small attempt to put focus on features point. It helps you to take decision easy between these two. But the final Answer is remain same "it depends".

Suggestion and Queries are always welcome

Thanks

KOOOOL

Find out who Delete your data from SQL table

Find out who Delete your data from SQL table

Have you lost your data from SQL table ? Have you lost your SQL table ? want to know who ran DELETE or DROP Query on your database ? Then go through this step by step article, it will help you to find the culprit

Introduction

Last month my friend has called me and said, some has delete his important data from SQL table and now no one confess it. He asked me to search a way to find culprit. After couple of here and there i got a way and now want to share with you. So, let's enjoy this article.

This article will help you find the user who fire DELETE or DROP statement on your table or database

Things we need

To search culprit, we need to read transaction log entries of database. Yes...You heard is right, you can read SQL transaction log data (i.e. LDF file).  let's begin with the steps

1.  We will create some sample table with data
2. Delete rows from it
3. Try to track the user who delete (soft or hard) data entries (Here Soft Delete means delete records using Query and Hard delete indicates delete data using 'DEL' button (or may be with mouse) from SQL table directly)

LDF :
         (Those who don't know what is LDF) LDF is a file extension for log data files these files are exist with MDF files (which contains actual data). LDF file store all transactions with time stamp and help to recover database in case of data loss.

         Now, to read LDF file we need to use 'fn_dblog' function, (which is undocumented function of SQL), after executing this function on particular database you will able to see live transaction logs and operations executed on that database.

Let's create sample database and table, with the help of following Query

CREATE DATABASE [Sample] ON  PRIMARY
( NAME = N'Sample_dat', FILENAME = N'D:\Sample\Sample.mdf' , SIZE = 13760KB , MAXSIZE = UNLIMITED, 
FILEGROWTH = 10%)
 LOG ON
( NAME = N'Sample_log', FILENAME = N'D:\Sample\Sample.ldf' , SIZE = 9216KB , MAXSIZE = UNLIMITED, 
FILEGROWTH = 10%)
GO

sample table:

USE [Sample]
GO
CREATE TABLE [Emp] (
    [No] INT ,
    [Name] VARCHAR (50),
    [Address] VARCHAR (50)
);

Now let's check what has been recorded in LDF logs

run 'fn_dblog' function in Sample database

select * from fn_dblog(null,null)

If you see above result pane, there are almost 35 rows are recorded for just CREATE DATABASE and CREATE TABLE script

Let's insert some rows in table

Insert into Emp values(1,'name1', 'address1')
Insert into Emp values(2,'name2', 'address2')
Insert into Emp values(3,'name3', 'address3')
Insert into Emp values(4,'name4', 'address4') 

Track DELETE Activity

Now, just go and delete all rows from database, use below simple query

Delete from Emp

Our Emp table is now empty as, we have delete all the queries

Let's examine the log table, having operation type is 'LOP_DELETE_ROWS', fire fn_dblob function again and see what you get

select * from fn_dblog(null,null) where Operation = 'LOP_DELETE_ROWS'

Result:



Above result pane show us, all the transaction rows which are having 'DELETE' entries on specific database table, you need to search for the your 'specific' table (from where you have lost your data), check out column 'AllocUnitName', this column contains your table name on which 'DELETE' statement has fired.

In our case, Table name is 'Emp', now get the transaction ID for that particular 'table' entry record, execute below query to get record of particular table

select Operation, [Transaction ID], AllocUnitName,  * from fn_dblog(null,null) 
where Operation = 'LOP_DELETE_ROWS' and allocUnitName = 'dbo.emp'

Result:



in our case, transaction ID is same, as all entries are deleted with single 'DELETE' statement (at once) (e.g.  0000:0000079f)

with the help of above transaction ID, we will find when our entries are deleted from database. for that purpose we need to search record with operation LOP_BEGIN_XACT, fire below query on database

select  [Operation], [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
 
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = '0000:0000079f' AND [Operation] = 'LOP_BEGIN_XACT'

above query will give you Start time of the transaction

Now we got the exact time when someone fire DELETE query on database, to know the activity End Time, you can try below query

SELECT
   [Begin Time], [End Time]
FROM
    fn_dblog(NULL, NULL)
WHERE
[transaction id] = '0000:000007a1' and [Operation] = 'LOP_BEGIN_XACT' or [operation] = 'LOP_COMMIT_XACT'

Here is the result of above query



Now let's find who is the culprit, we will find the real database user who fire delete query

Transaction SID column contains encrypted Hexa decimal text which is nothing but the user name who fire 'Delete' query

Fire below query to get [Transaction SID] column with the help of Transaction ID and Operation = 'DELETE'

select  [Operation], [Transaction Name], [Transaction SID]
FROM fn_dblog(NULL, NULL) where [Transaction ID] = '0000:000007a1' and [Transaction Name]='DELETE'

Output of above query is



Just you need to copy, encrypted hexadecimal contents from [Transaction SID] column and execute below query on master database, as per our result my Hexadecimal string is 0x01

SELECT SUSER_SNAME(0x01)

**SUSER_SNAME is the inbuilt function, it just checks security identification number (SID) and back with the login name associated it.

when i run above query i got below output



Yes...we finally got real culprit who fire Delete query

Track DROP activity

Similarly, if anyone DROP your table from database we can track that activity by using following queries,
Let's Drop table with below simple query

Drop table Emp

Now track activity using Transaction Name 'DROPOBJ'
check below query

SELECT [Transaction Name], Operation, [Transaction Id], [Transaction SID],  [Begin Time] 
FROM fn_dblog (NULL, NULL) WHERE [Transaction Name] = 'DROPOBJ'

i got below result


Same as DELETE scenario execute below query on master database, as per our result Hexadecimal string is 0x01

SELECT SUSER_SNAME(0x01)

which is the same user 'sa'

So, to conclude

SQL store all its transactions in log table, we can read transaction log file using fn_dblob function, we can do more research on each transaction with the help of this function.  All transactions are logged with different operations,  With the help of SUSER_SNAME function we can easily trace out encrypted user name.

In my next article i will cover deep dive points on 'Reading Transaction Log of SQL (LDF)', so Please stay tuned

and Enjoy this article
**DO NOT alter enties of fn_dblog or DO NOT run these command on production unless you have backup.

*Suggestions and comments are always welcome

-Happy Tracing
Koolprasadd

FIX : A network-related or instance-specific error occurred while establishing a connection to SQL

In this article i explained you to Fix SQL error occurred while establishing a connection to remote computer, so we can say we can resolve error "Network-related or instance-specific error occurred while establishing a connection to SQL Server [2005/2008/2012]"

Background

Couple of years back when i was not much familier with SQL, i try to connect to my SQL Server from my client. I put servername and try to login with SQL Server authentication with user name and password and i got error "A network-related or instance-specific error occurred while establishing a connection to SQL Server...", after spending couple of hours on it i was able to get rid of it, this article is for those who got same error and still they are put their head in SQL to resolve the issue.

Introduction

Many times we are trying to connect to SQL server with SQL management studio either by express version or by enterprise version and we got following error
"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) "
Above error is really headache for sql users.
Here is the snap of error

Fix it

To fix the issue we might need to do some configuration changes in SQL management studio (Here i use SQL 2008 for it)

Allow remote connections 
The very first thing you want to check is, your Remote Connections are enabled on your SQL Server database, to check it go through below steps
1. Select SQL server on object explorer -> Right click and click on properties -> select 'connections' -> under Remote server connections sections -> check 'Allow Remote connections to this server'
see below snap

This is the basic and simple check it may resolve your error, if not then continue with this article

Enable TCP/IP protocol
Next step is the enable the TCP/IP, just navigate to 'the SQL Server Configuration Manager' -> in left pane Open 'SQL server network configuration' -> select protocol for SQLEXPRESS in right pane select TCP/IP and make it 'enable'
see below snap

This is one of the helpful step and resolve many users issue, if the problem still continues then continue with this article

Handle Firewall port
Many times your firewall security settings does not allow SQL to pass through, you need to handle that firewall port to work with SQL stuff. To allow in firewall there need to configure couple of settings 
1. set 1433 port in TCP/IP proerpties
2. set Inbound and Outbound rules in windows firewall settings (Applicable for window 7 and above operating system)

set TCP port 1433 in configuration window as below snap shows

Now to set Inbound and Outbound rule we need to navigate to windows firewall, Just Open the Control Panel and navigate to Windows Firewall.
Click on Advanced Settings on the left hand side and you should see the Windows Firewall with Advanced Security. Select the Inboud Rules on the left hand side and click on New Rule… on the right hand side.
go through the following snap sequence it will help you to enable and set the windows firewall settings
Open 'windows firewall with advanced security' screen, select 'Inbound Rule' and click on 'New Rule', see below snap

New Inbound wizard screen' will open, Now in left hand side of the window select 'Rule Types', in right hand side select 'Port' now click 'Next'

In protocol and ports section select 'specific local ports' and give port number as '1433' (default sql port), now click 'Next'

In action section, select 'Allow the connection', and click on Next

In Name section give name and description of the rule, which help us to identify the rule in firewall, now click on Finish

Yes, we are almost done with the settings now close SQL server and restart it again and try to connect, it will resolve your issue.

Summing up

Above error is very basic Many time occur due to different conditions, if you have database installed on SQL 2008 and if you try to connect it with SQL 2005 management console then also same error occurred, when you have install a new instance of SQL server on machine then also this error arise, above are some settings that help you to get rid of the error

Suggestion and Queries most welcome

Thanks

Prasad