×


Category: SQL Issues and Solutions


Fix SQL server error 233 in Microsoft

Most Microsoft SQL Server users experience an error 233 when they are trying to login to their database.


Workgroup Failover Cluster without Active Directory in Windows

This article covers how to go about Workgroup Failover Cluster. While Availability Group was a viable replacement for Database Mirroring, there were a couple of blocking issues that prevented customers from upgrading. In older Windows Server versions prior to Windows Server 2016, you could create a failover cluster only between the servers in the same Active Directory domain. The new version allows to create two- (or more) nodes failover cluster between servers joined to different domains, and even between workgroup servers (not AD domain joined) – a so-called Workgroup Cluster.


SQL Server divide by zero error encountered - Fix it Now ?

This article covers methods to resolve resolve error 'SQL Server divide by zero error encountered'. This error is caused by performing a division operation wherein the denominator or the divisor is 0. This error is not encountered when the denominator or divisor is NULL because this will result to a NULL value.


Many client applications or drivers provide a default value of ARITHABORT is OFF.
The different values might force SQL Server to produces a different execution plan, and it might create performance issues.
You should also match the setting similar to a client application while troubleshooting the performance issues.


SQL Server Error 9002 - Fix it now

This article covers methods to resolve SQL Server Error 9002 for our customers. SQL Server Error 9002 happens when the SQL Transaction Log file becomes full or indicated the database is running out of space. A transaction log file increases until the log file utilizes all the available space in disk. A quick fix is to create more space for log file.


To Fix SQL Server Error 9002:


1. Backup Transaction Log File

Incase, SQL database that you are using is full or out of space, you should free the space. For this purpose, it is needed to create a backup of transaction log file immediately. Once the backup is created, the transaction log is truncated. If you do not take back up of log files, you can also use full or Bulk-Logged Recovery model to simple recovery model.


2. Free Disk Space

Generally, the transaction Log file is saved on the disk drive. So, you can free the disk space which contains Log file by deleting or moving other files on order to create some new space in the drive. The free space on disk will allow users to perform other task and resolve SQL Error Log 9002 The Transaction Log for Database is Full.


3. Move Log File to a Different Disk

If you are not able to free the space on a disk drive, then another option is to transfer the log file into a different disk. Make sure another disk in which you are going to transfer your log file, has enough space.

i. Execute sp_detach_db command to detach the database.

ii. Transfer the transaction log files to another disk.

iii. Now, attach the SQL database by running sp_attach_db command.


4. Enlarge Log File

If the sufficient space is available on the disk then you should increase the size of your log file. Because, the maximum size for a log file is considered as 2 TB per .ldf file.


To enlarge log file, there is an Autogrow option but if it is disabled, then you need to manually increase the log file size.

i. To increase log file size, you need to use the MODIFY FILE clause in ALTER DATABASE statement. Then define the particular SIZE and MAXSIZE.

ii. You can also add the log file to the specific SQL database. For this, use ADD FILE clause in ALTER DATABASE statement.

Then, add an additional .ldf file which allows to increase the log file.


SQL Error 17051 – Server Evaluation Edition to Enterprise Edition

This article covers method to resolve SQL Error 17051.

This SQL error  sometimes happens when the SQL Server Evaluation period has expired.

To fix Microsoft SQL Server Management Studio error:
1. Now as per the error message you open the Event Viewer and see that the Event with ID 17051, which shows SQL Server Evaluation period has expired under Details section.
2. Now, you recall that the Instance that you had installed was under Evaluation of 180 days, because you didn’t applied any Product Key. So, now how can you make it live again? All you need is a Product key of SQL Server and some clicks.
3. Open the SQL Server Installation Center and click on Maintenance link, and then click on Edition Upgrade.
4. Now on the Upgrade window Click Next and you will reach the Product Key page, apply the Key and click Next.
5. On the Select Instance page, select the SQL Instance that you want to fix and Click next. It will take some time and finally you will see a final window and click Upgrade.
6. Finally you will see the successful window, click on Close button.

Also, you can use following command line installation option to skip this specific rule to allow the upgrade process to complete successfully:
a) Open Command Prompt (CMD)
b) Go to the folder where SQL Server Setup, setup.exe file is located (like C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\)
c) Apply following command:

setup.exe /q /ACTION=editionupgrade /InstanceName=MSSQLSERVER /PID=<appropriatePid> /SkipRules= Engine_SqlEngineHealthCheck

The <appropriatePid> should be the 25 digit Key.
The above command line runs SQL Server setup in silent mode.
Now Restart the SQL Server Service for this Instance, and you will see it running fine.
Finally, go back to SSMS and now you can connect to the SQL Instance.


Microsoft SQL Server Error 916 - Fix it now

This article covers method to resolve Microsoft SQL Server Error 916 for our customers.

The main problem is due to bugs present in SQL Server Management Studio that prevents the user to connect to the database and refuses to display the database list.

How to solve Microsoft SQL server error 916:
If you receive the following error while using Microsoft SQL Server Management Studio 2008:
The server principal "username" is not able to access the database "dbname" under the current security context. (Microsoft SQL Server, Error: 916).

Causes of SQL Server Error 916:
1. The user is not permitted to view the data of selected column within the database.
2. The database is currently not available.
3. There are multiple columns such as Size, Space available, Data Space Used, Default file group, Index Space Used, Mail host, Primary file path and user has added at least one of them to the list of Object Explorer Details.
4. If the Auto Close option for the database is enabled, then Collation column cannot be retrieved by SQL Server Management Studio (SSMS).
5. For a database, Collation column contains NULL.

In order to view the granted permissions for the guest-user, the following command is helpful if run by a member of a Sysadmin fixed server.
The query is as follows:

USE msdb;
 SELECT prins.name AS grantee_name, perms.*
 FROM sys.database_permissions AS perms
 JOIN sys.database_principals AS prins
 ON perms.grantee_principal_id = prins.principal_id
 WHERE prins.name = ‘guest’ AND perms.permission_name = ‘CONNECT’;
 GO

On running the above command, a table is presented to the user containing all the attributes of guest user.
However, an empty result shows that guest user is disabled in the database and again the SQL Server error 916 will be displayed.
To overcome the error you may perform the following available solutions.

To fix this SQL server error 916:
1. In Object Explorer, click databases.
2. Display "Object Explorer Details" (F7) or "View –> Object Explorer Details".
3. Right-click the column headers and deselect "collation".
4. Refresh the databases.