Tuesday, October 20, 2015

Error 233 - No process is on the other end of the pipe

Hi Guys,

As we have seen here an error related to SQL Server Restart. Today we will see another error i.e. Error 233 "A connection was successfully established with the server, but then an error occurred during the login process."

You might have faced this common issue in your DBA career and yes the solution is relatively simple.

So what I was doing, I was connecting to the SQL Server from Windows Login but during the login process it failed and prompted the below error message:

Error 233 in SQL Server
As I said this seems an common problem so there might be multiple workaround for this.

Now, for me the solution was to start the SQL Server management Studio (SSMS) under "Run as Administrator" (When I was fresher, I use to always wonder what difference it makes if I start any program under Run as Administrator?? Let's find out the reason for that).

Reason for this error was:

a. When we are running an application under "Run As Administrator" we get extra privileges which we may not have under the local user account.

b. So running the program under Run As Administrator will grant extra rights to the account (but it should have admin rights in Active Directory).

Once I started the SSMS under Run as Administrator and connected to SQL Server it succeeded.

There might be chances that this may not solve this  issue, check here to find more work around on the issue.

Do you know?? We should properly set the Auto Growth parameter in Database else it would end up with consuming extra space. Check here.

Thanks,
Vikas B Sahu

Keep Learning and Enjoy Learning!!!

Thursday, October 8, 2015

How to Start SQL Server Instance when TempDB is unavaliable

Hi Guys,

Here is my last post on error while starting the SQL Server Analysis Services (SSAS) and here I've shared earlier on a issue, I request you please read that before proceeding.

Today we will learn a fact on the internal of SQL Server Tempdb (which was little unusual for me before I actually faced it. So I must say it was a learning experience).

Following are some questions that comes to my mind when I think about TempDB:

1. Where and how exactly the TempDB Database is used?

2. What exactly happened when your TempDB is corrupted?

3. Have you ever though of restarting the SQL Server Services without TempDB? Is it possible to do that?

4. Is it possible to Backups (Full, Differential or Log) and Restore the TempDB Database?

Let's see the possible answers to these questions:

Q 1. Where and how exactly the TempDB Database is used?

Ans. i. It is a System Database. Database ID for TempDB is 4
        ii. It is use to store temporary Data. Whenever we create a hash table (#abc) it gets created in TempDB.  
        iii. It also used for Sorting of Data (for e.g. If we use Order by clause in a query it uses TempDB to sort).
        iv. Also used for Row-Versioning.
        v. Recovery model of the TempDB is "Simple". 
        vi. We cannot run DBCC CheckDB on TempDB. 
        vii. We cannot detach the TempDB files. (Rather I must say one cannot detach the system Database files) 
        viii. Most important it is re-created every time whenever SQL Server is restarted. By checking the "Last Creation Date" we can see when was the last SQL Server was restarted (can expect as an interview question).

2. What exactly happened when your TempDB is corrupted?

Ans. i. Since we cannot run DBCC CheckDB; we cannot identify if there is any corruption on TempDB Database.
        ii. The only way to get rid of  corruption is to restart the SQL Server Services. Since it will recreate the Database files again.
        iii. Without TempDB Database SQL Server Services cannot be started. 
        iv. Model Database acts as the template for all the user created Database as well as for TempDB. So if model Database is unavailable TempDB will not get create and hence SQL Server  Service cannot be started.


TempDB Cycle
TempDB Cycle

3. Is it possible to Backups (Full, Differential or Log) and Restore the TempDB Database?

Ans. i. Since TempDB is recreated every time when ever we restart the instance. We cannot Backup this Database.

4. Have you ever though of restarting the SQL Server Services without TempDB? Is it possible to do that?

Ans. (Now here is the question what made to write this post)

i. As said above, TempDB is restarted every time when a SQL Server is restarted. (This will create both the mdf and ldf files on the location present for TempDB in the Master Database). 

ii. But what if there is a disk level corruption or the defined location is not present due to xyz reason.

iii. Now here comes my question: Will the SQL Server Services will start??? So, answer for this is YES!!! it is possible (Till now it was NO for me). 

iv. If  you will start the SQL Services normally (assuming there is a disk level corruption on the disk were the TempDB was placed) the disk is unavailable you will get the below error message:


TempDB Error Log
TempDB Error Log
v. So basically starting the SQL Services normally will not help you. Therefore we have to start the SQL Server Instance in Single User Mode as well as Minimally Configured

Startup Parameter
Adding Parameter in Startup parameter
vi. You can do that by adding -m (Single User Mode) and -f (Minimal Configuration) parameter in the startup parameter in the SQL Server Instance.

vii. This will start your SQL Server in minimal configuration (of course TempDB files is created) placing the TempDB files i.e. Data file on the User Database Data file location and the Log file on the User Log file location.

viii. And by this way your TempDB files are placed on different location other than the actual location for TempDB files.

ix. But this different location is temporary and only if you will start your SQL Server Instance in minimal configuration. So it is important to change the TempDB location to another drive by using the following command:


USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\TEMPDB\Tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\TEMPDB\Tempdb.ldf');
GO

SELECT name, physical_name AS Location
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

Hope this post will give you a different angle on concept of TempDB. Further we will see so more findings on TempDB. Share your comment on this to discuss more on it. You might be aware on SQL Server 2016 check here for the new features in it.

Thank You !!!

Wednesday, October 7, 2015

Error - Analysis Services Failed To Start in SQL Server 2012

Hi Guys,

Read my last blog on Error while configuring SQL Server Cluster here. As a DBA, we cannot or say we should not limit our self to only Database Engine related stuff. Today let's learn something related to Analysis Service i.e. SSAS.

In one of the migration project, I wanted to migrate an SSAS Database which is called as "Cube" to another Server.

So firstly, I needed the backup of the Cube. See the following command to take the backup of the Cube.
SSAS Cube Backup Code
SSAS Cube Backup Code
Where Test is the Database Name and Test.abf is the Backup Name (Which will take the Backup in the default location).

Now, I wanted to restore this backup to my new Server. But when I tried to connect the SSAS, it got  failed due to SSAS was disabled. So tried to start the SSAS and it gave the following error:

SQL Server Analysis Services Stopped

And when I checked Windows Event Viewer it captured the below error message:

Windows Event Viewer Error
Windows Event Viewer
From the Error in the Event Viewer it is confirmed that it is related to something with permission issue. So what we commonly do is "Going to that particular drive\ Folder and give full right to the account which runs the SQL Server Analysis Services". But unfortunately it din't helped.

I started searching more in google related to this error and in most of the sites they will provide the same solution which we saw above. And the search continued and continued till I landed to the next solution. And solution was to check the msmdsrv.ini file (It is an Configuration file for AS).

Basically when we configure AS, we have to pass a location where Data and Log file will create, but in my case no such files where created. So what did was as follows:

1. Created two Folder's Data & Log on E:\ and F:\ drive respectively and gave full permission to it.

2. Open the msmdsrc.ini file (in notepad) from this location (default):

C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config

msmdsrv.ini
msmdsrv.ini File
3. As we can see DataDir and LogDir tags which contains the location for the files; Change the path save it.

4. Then tried to re-start the AS and it was successfully started.

So the resolution for this seems simple. Want to know what is SQL Server Azure read my introduction post on Azure. Do comment if you like of dislike the page.

Thank You !!!
Keep Learning and Enjoy Learning !!!

Monday, October 5, 2015

SQL Server 2012 Clustering Errors

Hey Friends,

So till now we have seen the installation and configuration of SQL Server 2012 Clustering in our three part series as Part 1, Part 2 and Part 3 of SQL Server 2012 Clustering.


Now let us see few errors which I have faced during the Installation and Configuration of SQL Server 2012 Clustering:

1. Error: Cluster Service verification Failed
    Description: During one of the Server Migration i.e. Windows Server 2012 and SQL Server 2008       R2

Add a failover Cluster Node
Error-1
Solution: The solution was basically we have run the below command from the power shell:

Install-WindowsFeature -Name RSAT-Clustering-AutomationServer

Basically MsClus.dll library is by default disabled in Windows Server 2012. So by running the above command it enables it which is require for installation of SQL Server 2008 R2 Cluster.

I've got the solution of this from this msdn link.

2. Error: Cluster Service verification Failed
    Description: During one of the Server Migration i.e. Windows Server 2012 and SQL Server 2008       R2

Install a SQL Server Failover Cluster

Solution: 

Follow the below steps:

Copy  C:\Windows\Cluster\Clusres.dll TO C:\Windows\system32 and rename the file to W03a2409.dll

For this error, if you will search in internet you might get multiple solutions. I found this solution is the shortest and best way to resolve the error.

I've got the solution of this from this Microsoft link.

Keep Learning and Happy Learning!!!