Sunday, 19 May 2019

How to Re-solve “There is not enough space on the disk” Query error?

How to Re-solve “There is not enough space on the disk” Query error?


One of my friends communicated to me “Whenever I queried in SQL Server Management Studio, I keep on getting this error. How do I solve this?”

An error occurred while executing batch\Big query. Error message is: There is not enough space on the disk.

There can be two reasons
1 There is no space in the Drive where SQL Server files are stored
2 The drive where Tempdb is stored may not have enough space

But these are not issues to my friend as the Drive in which SQL Server files (Both mdf and ldf) and TempDB are in E drive which has enough space (200 GB+ free space).

It should be noted that whenever Queries are executed in SSMS that need result to be returned, SSMS caches the resulsets in a C drive under the Users Folder. So I asked my friend to check this folder

Tools–>Options–>Query Results–>SQL Server


It was found that it was pointing to C Drive and the drive does not have any free space at all. I asked him to change this path into different drive or clear some space on C drive by deleting any unwanted files and the problem is solved.

Tuesday, 14 May 2019

Database Restoration error User does not have permission to perform this action

User does not have permission to perform this action

When we trying to restore a database we got below error.

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore of database 'xxxxxxx' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: User does not have permission to perform this action. (Microsoft.SqlServer.SmoExtended)

Cause:

No active connection on that database even though got same error finally we identified the reason is one of the orphaned replication job

Fix:

We removed that orphaned replication job from SQL agent, after that we are able to complete the restoration.

Monday, 13 May 2019

An existing connection was forcibly closed by the remote host

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)

When you receive below error will show if a connection is drawn from the remove client connection and the connection to the server has been lost.

Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)


There is no way for a connection from the client to host to know that the connection has been severed.

CAUSE

1.The cause is environmental and can be a number of things regarding the SQL server and configuration.

It can be a deadlock in the database and time outs need to be increased.

It can also be a network connectivity issue.---My Scenario

In this scenario this issue mainly when we worked with one of my customer, if the customer is running any query the connection is establishing successfully between the client and server, when the query execution complete , during the query out put getting connection loss error. We are able to find the issue, the customer side network packets are dropping. Customer is worked with their network engineer they fixed the network issue, after that customer is able to get the query results.

2: It can also be caused when the Orion server is unable to resolve the IP address from the SQL server's host-name.

Resolution:

1:Enable Remote Connection, right click on the server node and select Properties:
a. Go to the left tab of Connections and select “Allow remote connections to this server”.
b. Make sure that MSDTC is enabled in the firewall.
Enable SQL Server Browser Service:
a. Go to All Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager > SQL Server Browser.
b. Right click on SQL Server Browser and click Enable.
Create an exception of sqlbrowser.exe in Firewall, Windows Firewall may prevent sqlbrowser.exe to execute.
a. Search for sqlbrowser.exe on your local drive where SQL Server is installed.
b. Copy the path of the sqlbrowser.exe like
C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe(Note:If SQL 2008\8R2\12\14\16\17 paths should be C:\Program Files\Microsoft SQL Server\100\110\120\130\140\Shared\sqlbrowser.exe) and create the exception of the file in Firewall.
Go to All Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager > Select TCP/IP.
a. Right Click on TCP/IP and click Enable.
b. Restart SQL Server Services for all the changes to take effect.
c. Right click and go to the menu properties to select a location where the default port of SQL server can be changed.
Set the commandtimeout property of the command object to an appropriate value. Use a value of zero to wait without an exception being thrown. (Increased the "connect timeout"  in connectionstring of app.config).
Check the network cabling, the NIC for any driver updates or physical issues, or any network equipment that  the SQL server connects to such as the ports.

2:Run Configuration Wizard, and instead of indicating the hostname or FQDN of the SQL server, input the IP address


A transport-level error has occurred when sending the request to the server (Msg 233)

The error “A transport-level error has occurred when sending the request to the server”  can occur when SQL Server client cannot connect to the server. The reason for the lack of connection can become a wrong configured remote connections. In this scenario, SQL Server will send the following message:

Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)


To solve this problem try to allow SQL Server to accept remote connections or check your firewall settings. It is possible to enable remote connections with the help of the SQL Server Configuration Manager tool.





Disable CDC at table level

 How to Disable CDC( Change Data Capture) for tables Change Data Capture (cdc) property is disabled as default.  here I will Query you how t...