Resources, Articles, Tricks, and Solutions in connection to SQL Issues and Solutions

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.

Read More


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.

Read More


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.

Read More


SQL Error 50000 - Best method to fix

This article covers method to fix SQL Error 50000.

This SQL error happens in the process of installing SQL Server Management Studio on one of the client machines.
If you receive SQL Server 50000 error on a SQL Agent Job, you need to Quit the job reporting failure.

To fix this SQL error, you can try the following queries:
1. Enable the SQL Broker on the Site database:

USE master; 
GO 
ALTER DATABASE CM_PR1 SET ENABLE_BROKER 
GO

2. SET the Site Database as trustworthy:
USE master;

GO 
ALTER DATABASE CM_PR1 SET TRUSTWORTHY ON 
GO

3. SET the Database to honor the HONOR_BROKER_PRIORITY:

USE master; 
GO 
ALTER DATABASE CM_PR1 SET HONOR_BROKER_PRIORITY ON; 
GO

Read More


SQL Server Error 5009 – Different scenarios and fixes

This article covers how to fix SQL Server Error 5009 error which occurs while adding or removing a database file or extending the database file size.
 
To fix Microsoft SQL Server Error 5009 While Adding Database File:
1. Set the Operating system permission on the mentioned .ldf file to full permission for your login account and SQL Server service account.
2. Change the location to the path where you have enough permission to create or add database files.

Read More


SQL error 300 - Fix it Now

This article covers tips to resolve SQL error 300. The reason of the error is related with user permission on VIEW SERVER STATE. You can upgrade SQL Server Management studio with the same version like SQL Instance or higher. 


Also, To mitigate this error, give the following GRANT and the error message will disappear:

USE master
GO
GRANT VIEW SERVER STATE TO "LoginName"

Read More