Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts
Wednesday, February 7, 2018

Solved: Microsoft SQL Server Error 3403

This is a Guest Post from “Andrew Jackson”.

Bio of Andrew Jackon:

He is freelance SQL Server DBA. He is  fan of technology, programming, and entrepreneurship. He is  also interested in writing and web development and SQL Server blogger too. He like to share about SQL Server and the problems related to it as well as their solution and also He also  handles database related user queries, server or database maintenance, database management etc.

You can find his social handles at following.

Facebook: https://www.facebook.com/people/Andrew-Jackson/100008825676608
Twitter: https://twitter.com/jacksonandrew32
LinkedIn: https://www.linkedin.com/in/jackson-andrew-401147a5/

You find more about him at : https://about.me/theandrewjackson
From its powerful internal structure, fast performance, and reliability, SQL server is now widely accessed by several enterprise users. It is used to deal with bulk transaction processing, business programs, and content management. The Microsoft SQL server is chosen for its high-level security, scalability, and performance. Sometimes, corruption in the SQL database occurs due to the saving of a large amount of business information. This problem makes the server inaccessible, flashing an error message on the window.

One such message is ‘SQL server error 3403’, resulting in an unplanned downtime of the server.

image

Reason for SQL Server Error 3403:

Corruption and the server crash these two are the most common and root causes of the problem. Once the server crash takes place, the database verifies the transaction log. But, if the provided information does not match Object ID then, the application throws an error code 3403. It is possible that instead of these reasons there is some other cause of the problem. Following are those additional causes of SQL server error 3403 :
  • While updating the allocation page, the data gets written on the transaction log before the crashing takes place.
  • Another cause of the problem might be bad allocation activity by Microsoft SQL server.

How to Fix SQL Error 3403?:

The best way to deal with any of the SQL server error (including 3403) is to restore data from the recently created backup file. If you are having the backup file then, you just need to perform following steps :
  • First of all, drop the SQL server database and then, create a database for loading.
  • It’s time to restore database file and utilize online commands for activating restored files for use.
Note: Verify that the database files created at present must be having same size as of Sysusages value.

Alternative Solution to Fix SQL Server Error 3403:

It is possible that users might not be having a backup file with them. In such case, the only approach is to use SQL Recovery software, which restores the corrupt database with an ease. The software is a rapid technique for fixing SQL database files corruption without any efforts. What all you need to do is just browse corrupt MDF file. If you are unaware of corrupt SQL database location then, no need to worry because the software provides the solution for it. This product is also capable of recovering data from corrupt files, which are encrypted by Wallet Ransomware attack.

Conclusion:

Errors are uncertain situations, which acts as challenges for an individual. If an accurate workaround is not executed then, these situations can also result in another hazardous condition. It should be the duty of each and every server user to fix the error, just after their occurrence. There are two solutions on how to fix SQL error 3403 i.e., backup file restoration and SQL database recovery program. As per the availability, readers are free to catch any of the workarounds for resolving their problem.
Share:
Monday, March 27, 2017

A Review of Stellar Phoenix SQL Database Repair

SQL Server is a database server developed by Microsoft, the primary function of which is to store and retrieve data in the database MDF and NDF files. It retrieves data as requested by several software applications running on same or different computer across a network. Plus, it has a lot of concurrent users. Aimed at various enterprise segments, it caters to different workloads ranging from a small single-machine to large Internet-facing applications.

Although data on it is stored in a safe and secure manner, yet there are instances when it’s MDF and NDF files get damaged or turns inaccessible due to one or more reasons. Moreover, this happens irrespective of the SQL Server versions that can be 2016, 2014, 2012, 2008, etc. When they get damaged, they come up with several and unique error messages. Such a situation restricts you from accessing the stored data of the database. This creates the necessity for using a third-party software that can repair both these files and extract the complete data out of it. Here, Stellar Phoenix SQL Database Repair would serve the purpose. Let’s look at how.

Developed and marketed by Stellar Data Recovery, Stellar Phoenix SQL Database Repair is a software the purpose of which is to repair the damaged or corrupt MDF and NDF files of Microsoft SQL Server and save all the extracted data in multiple formats such as MS SQL, HTML, XLS and CSV. Here, we present to you a review on this product for you to get in-depth details about it. With this, you can check whether the software achieves your purpose of perfectly repairing the database files and recovering the entire data; and, that whether you can use it in case the primary MDF and secondary NDF files of the SQL Server database gets damaged or turns corrupt.

First, it is imperative to acquaint with its salient features as well as benefits. Let us look at the features and benefits embedded in the software.

Features & Benefits:


· It can repair the damaged or corrupt MDF and NDF files of your SQL Server database.
· It can search for MDF files on the PC and repairs it successfully.
· It can retrieve almost all the database components ranging from Tables, Triggers, Views, Rules, Keys, Indexes, to Defaults.
Note - The Keys that can be recovered are Primary Keys, Unique Keys, Foreign Keys, and Identity, while the Indexes that can be recovered are both Clustered as well as Non-clustered indexes.
· It can recover both ROW and PAGE compressed data of the SQL Server database files.
· It has an exceedingly fast Scan feature with which you can scan the damaged MDF and NDF files.
Note - With this, you can identify and fix the issues quickly and recover all the data objects without fail.
· It allows you to preview the objects that can be retrieved and appear as a scan result after the end of the scanning process.
· It connects to the server automatically when interrupted for completing the repair process and restoring the data.
· It can even recover the Deleted records by using the remarkable feature embedded in the software.
· It offers you the option to move and save database files to Live SQL Database after the repair process gets over.
· It gives you the option to save the fixed database files in multiple formats such as CSV, XLS, and HTML in addition to MS SQL.
· It supports the latest released version i.e. MS SQL Server 2016 in addition to lower releases such as MS SQL Server 2014, 2012, 2008, and all others.

Now, to be able to execute these features and avail the benefits of this software when you have a damaged or corrupt MS SQL Server, your system must fulfil minimum system requirements, which are as follows

· The system must be embed with Pentium Class processor only.
· The supported operating system are Windows 10, 8, 8.1, 7, Vista, and XP as well as Windows Server 2008/2003.
· The system must possess a minimum of 1 GB memory; however, the recommended size is 2 GB.
Lastly, the free space in the system hard drive should be at least 50 MB to get the software installed correctly.

Note - These system specifications are easy to achieve and almost all Windows systems possess these minimum system specifications by default.

Working:

The working of the software is described here and takes you through the steps to repair the corrupted MDF & NDF files of MS SQL Server.

Select, Repair, and Save are the three most important steps that you need to carry out with this software to successfully repair the damaged SQL database files.

· Select – As displayed in the image below, with the ‘Select Database’ option that is present in the interface of the tool; you need to select the damaged database file from any location in your system. However, if you do not know the same, you must use the ‘Search in Folder’ and ‘Find’ tab to search and find all the database files. If you want to locate the file in subfolders as well, you must mark the option ‘Search in subfolders.’ Further, to scan for and recover the deleted records, you must check the ‘Include deleted record’ option.

image

· Repair – Once you have selected the database MDF file, you must click on the ‘Repair’ button to scan the MDF file. With this, you get a list of all the recoverable objects as shown in the image below. Further, you can select the ones for which you require a preview.

image

· Save – Finally, there is a message that the selected SQL database file has been successfully repaired, and the software prompts to click on the OK tab to save the file in a new database. This is the third and last step that you have to follow with the software. When you do this, you get options to save the file in MS SQL, HTML, CSV, and XLS that you can select as per your choice. Next, it gives you the option to save in ‘New database’ and ‘Live database’, and again you can choose as per your requirement. Finally, the retrieved objects of the SQL Server database file get saved. The images below display the ‘Saving’ step.

User have four options to save the database.
· MSSQL
· CSV
· HTML
· XLS

image

After selecting MSSQL, two options are there
· New Database
· Live Database

image

image

Choose any options from these two and save the database.

image

Conclusion:

Now you know the features, system requirements, and working of the Stellar Phoenix SQL Database Repair software, you can analyze the tool and check whether it meets the purpose. A better way to try this utility is to download its demo version. You can then register the software if you find it satisfactory. Given its efficiency in repairing the SQL database file with precision and speed, I would give it an 8.5 on 10.
Share:
Saturday, January 18, 2014

Various way of finding duplicate rows in SQL Server

Recently One of the friend ask how we can find duplicate rows in SQL server so I said there are various way of finding duplicates and then I show him few ways of findings duplicate based on criteria. I thought it is a very basic question and still lots of people does not know how to find duplicate rows in SQL Server. So it is good idea to write blog post about it. In this blog post I’m going to explain the same various way of finding duplicates.

First all, We need a table to find duplicate rows. So following is a table for that.I have created Employee table with four fields.

CREATE TABLE [dbo].[Employee](
    [Id] [int] NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [Designation] [nvarchar](50) NULL
) ON [PRIMARY]

Now once you create this “Employee” table now it’s time to write insert data. I have inserted following data.

Find-Duplicate-Row-SQL-Server

Finding duplicate rows in SQL Server:

Now let’s first write query to find duplicate rows in “Employee” table. Following is a query for that.

SELECT
     Id,
     FirstName,
     LastName,
     Designation,
     Count(*) as 'Number of Rows'
FROM Employee 
GROUP BY Id,FirstName,LastName,Designation HAVING COUNT(*) > 1

Once you run this query following result will be there.

Find-exact-duplicate-row-sql-server

If you can see the data of “Employee” table clearly then you will see that there are two people with first name“Tushar” and “Jalpesh”. Let’s find that via following queries.

SELECT
     FirstName,
     Count(*) as 'Number of Rows'
FROM Employee 
GROUP BY FirstName HAVING COUNT(FirstName) > 1

Now when you run this query it will load following result.

Find-Duplicate-Row-SQL-Server-based-on-Column


That’s it. You can see its very easy to find duplicate rows in SQL Server. Hope you like it. Stay tuned for more.
Share:
Friday, November 8, 2013

How to find space used by table in SQL Server

Recently there was requirement for a client where we need to find space occupied/used  by particular table in SQL Server. I did some research a found a very cool way to get space used information from SQL Server.

With SQL server 2005 and higher version you can use ‘sp_spaceused’ to find space used by table in SQL Server.

Following is a syntax for that.
sp_spaceused N'YourTableName'

You can find more information about it from the following link.
http://msdn.microsoft.com/en-us/library/ms188776.aspx

When you run this in SQL Server, Its displays Number of Rows, disk space served and disk space used by a table or indexed view and also displays disk space reserved and used by database.

SpaceUsedByTableSQLServer

Here in the above example, You have seen that I have used to find space for employee table in my blog sample database and its providing information about rows, reserved space, data size, index_size space and unused space.

That’s it. Hope you like it. Stay tuned for more.
Share:
Thursday, November 7, 2013

How to find path of database data files and log files in SQL Server

In this post, We will learn about different technique to find path of data files and log files on SQL Server. Let’s go through one by one of them.

The first technique, We are going to use is very simple way to finding file path for database. But with this you can find path for one database only. It’s very simple, Select database on object explorer and right click and select properties.


HowToSelectDatabaseFilePathSQLServer1

Now once you click on properties a dialog box appear like following. Select files and go to path column it will show path of database data files and log files.

HowToSelectDatabaseFilePathSQLServer2
Now we will learn another technique where we can find database data file and log file path from query. Below is query for that.

SELECT name, physical_name FROM sys.master_files where database_id= db_id('blog')

Now once you run this query it will load result like following.

HowToSelectDatabaseFilePathSQLServer3


That’s it you can find you database file path very easily. You can use above query to find files path for multiple databases also without using where clause. Hope you like it. Stay tuned for more.
Share:
Wednesday, October 9, 2013

Database diagram support objects cannot be installed because this database does not have a valid owner- SQL server error solution

Before some days, I have copied one database from the one of server and restored it on my machine. The server was not having ‘sa’ user login and I was having the ‘sa’ user in my local machine. The database was restored perfectly with no problem but when I tried to create a database diagram I got following error.
Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.
After digging into that I found that the database does not have any valid owner and that’s why its generating errors. To resolve this error following is a step to change or add owner of database.

Step-1:


Right click on your database and click properties it will open a dialog like following. Goto-> Fiile Tab.

DatabasePropertiesDatabaseOwner

Step-2:


Click on .. button it will open a dialog box like following.

BrowseDatabasePropertiesDatabaseOwner

Step-3:


Click on browse button. It will open a list of user available for SQL Server.You need to select user whom you want to make database owner. In my case I want to make ‘sa’ user database owner so that I have selected like following.

SaDatabaseOwner

After selecting user you need to close all dialog via pressing ‘OK’ buttons. That’s it your database is now having proper owner and error for database diagram is gone now.  Hope you like it. Stay tune for more..
Share:
Sunday, July 31, 2011

SQL Management Studio in Visual Studio 2010

Microsoft Visual Studio 2010 is a great IDE and everyday I am discovering something new about it. Today I am also going to explain new feature of Visual Studio 2010. In this post I am going to Transact SQL Editor feature in Visual Studio 2010.

Visual Studio 2010 Ultimate Edition provides this great feature. You can run you SQL Queries in visual studio 2010 itself with all intellisense and all the stuff that SQL Server provides. Let’s explore in details.
To connect the database of your SQL Server in Visual studio Go to Data->Transact SQL Editor –>New Query connection like following.

SSMS 

Now once you click it will open the dialog box for connection SQL server like it. If you don’t have install anything it will install SQL Server 2008 express edition with Visual Studio 2010 Ultimate Edition. The dialog appear like following.


SQLServer

Once you are done with the connection it will open full fledge Query editor windows with intellisesnse like following.

Intellisense

Also it can connect to any databases including your SQL Azure databases also. Isn’t that great. You don’t need SQL management studio at all for the basic database operations. That’s it. Hope you like it. Stay tuned for more.. Till then happy programming.


Shout itkick it on DotNetKicks.com
Share:
Sunday, May 8, 2011

What’s new in SQL Server ‘Denali’ Management Studio.

Before sometime Microsoft has launched SQL Server Denali CTP a new version of SQL Server. I have downloaded it and install it on my machine. I have found variety of new features in SQL Server. Today I am going to explore some of the new features in SQL Server Denali Management Studio. Following are some of the new features of SQL Server Denali Management Studio.

New Font and New Colour Scheme:

First thing you notice when you start SQL Server Management Studio is the colour scheme it has nice blue theme just like Visual Studio 2010. Also if you see that now the default font is ‘Consolas’. Just like following.

ManagementStudio1

Multi monitor Support:

Now SQL Server management Studio also can be work in multi monitor also. You can drag each window and set it for another monitor.Now you can each window can appeared out of shell of SSMS and You can drag with each window in different monitor like following.

ManagementStudio2

Code Snippets:

Now there are lots of code snippets are available and you can that code snippets via right Click Query window-> Insert snippets like following.

ManagementStudio3

Once you click Insert snippets it will open lots of built in snippets and create syntax for you directly.

Zoom Functionality:

Now you can zoom the query editor window. There is a dropdown given in left bottom corner of the window and you can zoom the query editor windows as you need like following.

ManagementStudio4

There are many more features in SQL Server Denali Management studio. I have just explore few of them. For more details please visit following link which contains lots of list of new features.

http://msdn.microsoft.com/en-us/library/ms174219(v=sql.110).aspx

That’s it. Hope you like it.. Stay tuned for more.. Happy programming.

kick it on DotNetKicks.com
Share:
Wednesday, April 20, 2011

Installing SQL Server 2011(Denali) CTP Step by Step overview

Today I have downloaded the SQL Server 2011 CTP and I am very excited about the new features provided by it. I am going to post new features of the SQL Server 2011(Denali) CTP in future post. Today I am going to post about how install SQL Server 2011(Denali) CTP on Windows7 machine.

First you need to download the SQL Server 2001(Denali) CTP setup from the following URL. You need download the setup as per your operating system version 32bit or 64 bit version.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9

After once you download the setup. You need to extract files to a location and from that you have to click setup.exe.  After clicking on setup.exe first screen will appear like following.

Step1

Once this screen appear you need to click on installation. Once you click the Installation following screen will appear.

Step2

Above screen will give you few options about installing SQL Server. As I want to install Stand Alone installation I have clicked on Stand alone installation. After that it will perform several test like system requirement and other stuff and then if successfully passed all the test then following screen will appear.

Step3

Then Click OK to continue to setup. Then following screen will appear.

Step4

As I want to install the express edition I have selected the express with advance service. After that terms and conditions screen Like following will appear.

Step5

Accept the licence terms and then click Next and Install. It will start installing the it. After that it will checked about rules and will bring following screen.

Step6

Click on Next and then you select the checkbox for SQL Server features and then following screen will appear.

Step7

Select the features you wan to install then click next then following screen will appear.

Step8

As I have to install default instance I have selected default instance and then click next. Then again one validation screen will appear you need to click next on that and then service account screen will appear you need to select the account via clicking on Use the same service account for SQL Server services like following.

Step9

Once you are done click next and then SQL Server Authentication mode screen will appear like following.

Step10

As I have to select mixed mode which will allow both windows authentication as well normal sql server account. Click on Next there will be several screens which are there for validation once you click next next and then Install it will start installing the SQL Server. Once you are dong with installing SQL Server following screen will appear.

Step11

That’s it You have installed SQL Serve 2011 Denali!!. Hope you liked it. Stay tuned for more..

Shout it

kick it on DotNetKicks.com
Share:
Monday, March 14, 2011

TSQL Quiz 2011 on beyondrelational.com

One of the my friend Jacob Sebastian running a SQL Server TSQL quiz on his site beyondrelational.com. This is a great opportunity to learn TSQL and win great price Like Apple IPad and other lots of cool stuff. So if you are expert and if you learning TSQL then its a great way to test your knowledge.

For whole month of march selected quiz master will ask a question and you have to answer all this question day by day and at the end of month you will have great chance to win Apple Ipad.

For more details you can visit following link:

http://beyondrelational.com/quiz/SQLServer/TSQL/2011/default.aspx

Hope you liked it.Stay tuned for more..

Shout it
Share:
Saturday, December 18, 2010

DotNetQuiz 2011 on BeyondRelational.com- Want to be quiz master or participant?

Test your knowledge with 31 Reputed persons (MVPS and bloggers) will ask question on each day of January and you need to give reply on that. You can win cool stuff.My friend Jacob Sebastian organizing this event on his site Beyondrelational.com to sharpen your dot net related knowledge. This Dot NET Quiz is a platform to verify your understanding of Microsoft .NET Technologies and enhance your skills around it. This is a general quiz which covers most of the .NET technology areas.

Want to be Quiz Master?

Also if you are well known blogger or Microsoft MVP then you can be Quiz master on the dotnetquiz 2011. Following are requirements to be quiz master on beyondrelational.com. I am also a quiz master on beyondrelational.com and

Quiz master eligibility:

You will be eligible to nominate yourself to become a quiz master if one of the following condition satisfies:

  • You are a Microsoft MVP
  • You are a Former Microsoft MVP
  • You are a recognized blogger
  • You are a recognized web master running one or more technology websites
  • You are an active participant of one or more technical forums
  • You are a consultant with considerable exposure to your technology area
  • You believe that you can be a good Quiz Master and got a passion for that

 

Selection Process:

Once you submit your nomination, the Quiz team will evaluate the details and will inform you the status of your submission. This usually takes a few weeks.

Quiz Master's Responsibilities:

Once you become a Quiz Master for a specific quiz, you are requested to take the following responsibilities.

  • Moderate the discussion thread after your question is published
  • Answer any clarification about your question that people ask in the forum
  • Review the answers and help us to award grades to the participants

For more information Please visit following page on beyondrelational.com

http://beyondrelational.com/quiz/nominations/0/new.aspx

Hope you liked it. Stay tuned!!!

Shout it
Share:
Wednesday, December 1, 2010

GUID Vs Int data type as primary key

Recently one of my friend ask me when I should go for GUID and When I should go for Int as primary key in table. So decided to write a blog post for it. Here are advantages and disadvantage of the GUID and INT.

INT Data Type:

Advantages:

  1. Its required small space in terms of the storage it will only allocates 4 bytes to store data.
  2. Insert and update performance will be faster then the GUID. It will increase the performance of the application.
  3. Easy to index and Join will give best performance with the integer.
  4. Easy to understand and remember
  5. Support of function that will give last value generated like Scope_Indentity()

Disadvantages:

  1. If you are going to merge table frequently then there may be a chance to duplicated primary key.
  2. Limited range of uniqueness if you are going to store lots of data then it may be chance to run out of storage for INT data type.
  3. Hard to work with distributed tables.

GUID Data Type:

Advantages:

  1. It is unique for the current domains. For primary key is uniquely identifies the table.
  2. Less chances of for duplication.
  3. Suitable for inserting and updating large amount of data.
  4. Easy for merging data across servers.

Disadvantages:

  1. Bigger storage size (16bytes) will occupy more disk size then integer.
  2. Hard to remember and lower performance with Join then integer.
  3. Don’t have function to get last uniquely generated primary key.
  4. A GUID primary Key will added to all the other indexes on tables. So it will decrease the performance.

Conclusion:

From above the advantages and disadvantages we can conclude that if you are having very large amount of data in table then go for the GUID as primary key in database. Otherwise INT will give best performance. Hope this will help you. Please post your opinion as comment.

Shout it
kick it on DotNetKicks.com
Share:
Sunday, August 1, 2010

Visual Studio –>Add Database –> Named pipe Provider Error for SQL Server

Recently I have been working on a article for my blog for that I just tried to add a database file on my solution with visual studio and I have received following error.

An error has occurred while establishing a connection to the server.
(provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)
An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2008, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

After checking my configuration I have found that in my machine there was more then one instance of SQL Server and the Default Instance was not properly configured and that’s why I am getting this error. This error was occurred because my Default Instance of SQL Server Express was not having TCP/IP Protocol Enabled. So I have enabled it just like following.

Go to All Programs->Microsoft SQL Server 2008-> Configuration Tools –>SQL Server configuration manager. It will open up windows like following.

SQLServerConfiguration

After that Go To SQL Server Network Configuration and Select Protocols for your default instances and then enabled TCP/IP like following and that’s it. Now error is resolved.

SQL Server TCP/Ip Protol configuration
Hope this will help you…

Technorati Tags: ,
Shout it
kick it on DotNetKicks.com
Share:

TSQL Challenges on beyondrelational.com

My friend and Guide Jacob Sebastian who is an Microsoft SQL Server MVP running a very popular series of TSQL on his site beyondrelational.com. If you know something about SQL Server then this challenge is for you. And if you are master of SQL then this challenge is for you to test you knowledge. If you have some time and If you want to test you knowledge or you want enhance your knowledge challenge then please spare some time to take it. Here is link for that.

http://beyondrelational.com/blogs/tc/archive/2010/07/26/tsql-challenge-35-find-the-total-number-of-full-attendees-in-each-24-hop-session.aspx

Currently he is running TSQL Challenge Number 35 which is about fine number full attendee for a conference.

Shout it
Share:

Support this blog-Buy me a coffee

Buy me a coffeeBuy me a coffee
Search This Blog
Subscribe to my blog

  

My Mvp Profile
Follow us on facebook
Blog Archive
Total Pageviews