Soumya's Database Blog : November 2016

SQL Server Warning Fatal Error 7105: Reasons And Solutions

MS SQL Server supports Large Object (LOB) data types for storing a large amount of data. Such data types includes Binary LOB (BLOB), Character LOB (CLOB) and Double-byte Character LOB (DBCCLOB) as well. They use a unique structure which is distinct from normal data types. In certain situations, Microsoft SQL server unable to access LOB data that is provided by database page. Users will receive SQL error 7105 while accessing these type of data and the process terminates. It creates critical situations and data loss necessitates MS SQL repairs to be sorted out.

Consequences of SQL Server Error 7105

Users may get “MSG 7105” when LOB referenced through the SQL database page row may not be accessed. At this end, SQL server application encounters an error message, which resembles the following as:

Because of the high severity of errors, SQL server ends the connection. The same error message appears in Windows Application Event Log and SQL ERRORLOG with EventID.

Causes Behind Error 7105 in SQL Server

There are following reasons those are mentioned below:

The corruption problem may occurs inside the LOB page structure that is given by the database.
The query that is failed with NOLOCK or READ UNCOMMITTED ISOLATION query hint.
Most probably the error is coming inside the SQL Server Engine leading to the failure of the database query.

Solutions to Resolve Error Message

Hence, the following workarounds are discussed as a solution for the respective error:

Run DBCC CHECKDB on the SQL Server database

It is an inbuilt utility that must be executed to restore or repair the SQL Server database file. Still, in the beginning, it must be run without any repair clause thus, to verify the damage level. After that run DBCC CHECKDB with recommended repair clause to fix SQL Server fatal error 7105. It it possible then, it can fix the error but the problem is that it justifies the data loss which is not suitable.

Restore from backup file

If the above mentioned method is unable to resolve the error, then users must use the different solution to eliminate the issues. Hence, if a clean and healthy backup is available then, restore the database from backup file for regaining the availability of the data items which is stored in SQL database. It must be possible, only if the backup is available. The steps for restoring procedure are mentioned below:

Restore the selected database by clicking on database >> Tasks >> Restore >> Database.

After that, select the backup file and then, click OK button.

Now, the screen will display the Execution Process and you will have to wait for some time until the process has been completed.

After finishing the execution process, a restore database message will be shown on the screen successfully. Click on OK.

Performing above manual steps, users will be able to restore SQL server database. But if the backup is not available then users can go for third party solution to fix SQL fatal error 7105.

Effortless Solution to Resolve SQL Server Error 7105

The most suitable solution for fixing this error code is MS SQL data recovery. It is safe and secure style to troubleshoot the error with the help of below steps:

Step1: First, launch the software, select the MDF file and then, press Open option.

Step 2: Once the files are added, the application will Preview the complete recovered database of MDF and NDF files like tables, stored procedures, triggers, and views and so on.

Step 3: Finally, select an option between two i.e., SQL Server Database and SQL Server Compatible Scripts to export/save the database.

In the end, a user will be able to get database file without SQL error 7105.

Conclusion

While working, various server errors may occur which creates obstacle while accessing the SQL Server database. However, error 7105 in SQL Server is faced due to inaccessibility of large object data that was referenced by a database. We have covered a best possible solution which helps to overcome this issue. Initially, it is suggested to restore the backup of the SQL database, if a user is already having it. Meanwhile, a user can go for another solutions to resolve SQL server 7105.

How to drop all tables in mysql database using a single command

Command:-
mysqldump -u[user] -p[password] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[user] -p[password] [DATABASE]

Here test is the database name. I'm going to drop all the tables from this database.
Example:-
mysqldump -uroot -predhat --add-drop-table --no-data test | grep ^DROP | mysql -uroot -predhat test

Know How To Resolve SQL Server Error 3271?

When a user is backing up a database or restoration procedure is in process, most of the SQL users faces an error i.e. SQL error 3271. This a type of error encounters when a system is performing an I/O operation.

The main reason behind this error message is related to I/O operation that displays a message of nonrecoverable I/O error. Therefore, in this article, we have discussed the all the major causes of occurrence of this error and what all are the possible solution to fix this error.

Causes of Microsoft SQL Server Error 3271

There are various major causes that are responsible for this error. Therefore, in this section, all the major reasons of occurrence of this error are discussed:

Lack of Storage Space

The error occurs mainly because of unavailability of space in the storage media. Therefore, it is impossible to fit backup created on the disk. This error message also has some additional text that determines the storage device memory is full, which leads to this I/O error like:

“A nonrecoverable I/O error occurred on file ‘%ab:’ %ab”

VSS Writer of SQL Server

The another major reason of the error is an issue with VSS. Volume Shadow Copy Snapshot (VSS) is a replica that is backed up via Window server. Therefore, it can also be possible that an error encountered due to any of the VSS writer that results in failure of the backup operation and further leads to entire backup process failure. Moreover, any error any problem, which is created by VSS writer results an SQL server error 3271.

SQL Database Corruption

One of the reasons that cause the error is corruption in the SQL database for which user was trying to create a backup. If the user tries to backup the corrupted SQL database then it is more likely to be possible that an I/O error occurs that further leads to error 3271.

A user must run a check through the SQL error logs to determine the exact reason of the error. After identifying the exact reason, a correct measure or solution can be used to fix the error message and prevent the SQL database from this error in future.

How To Fix SQL Server Error 3271?

If the reason of the error message is one of the above mentioned, then a user must follow the measures discussed below:

Increase Space On Disk

If the reason of the error is a lack of storage space, then first, a user needs to check the amount of storage space available on the disk on which backup needs to be created.
If there is no space available, first a user needs to free up some space on the storage disk or can use some external storage to save the created backup.

Resolve VSS Writer Issue

If the reason of Microsoft SQL error 3271 is VSS writer then, a user must review the instances by running the SQL instance check because the error is due to problematic SQL instance that prevents from taking a snapshot of the database.
After identifying the instance, a user needs to stop that instance and run the backup process without that instance.
If the backup process is completed successfully then it is clear that this particular instance is responsible the error. A user can again check SQL error log to determines the reason behind the inappropriate functionality of that particular instance.
If the user is not able to identify the instance that causing problem on a server, then one needs to stop all the instances on the server to run the backup process.

Note: When you stop all the available instances then, always keep in mind SQL VSS writer is not in use while taking backup.

With the help of these above mentioned manual procedures, a user is able to avoid and fix the error and run the backup process successfully.

Conclusion

While taking backup of SQL database, a user faces an I/O error generally known as Microsoft SQL Server error 3271. Therefore, all the causes of this error need to determined first, then depending upon the cause of error a respective measure should be taken to resolve it. Hence, in this post manual solutions to resolve these issues are discussed with respect to the cause of the error that a user can use to overcome this issue. However, if the error message occurs while restoring backup then there may be possibility of backup file corruption. In such situation you can take the help of third party SQL backup recovery tool to repair corrupt SQL backup file and fix the error message.

Steps to Rename Physical Database Files in SQL Server

The users can manually rename the physical files according to choice using the Transact-SQL commands in the following manner:

Firstly, we will create a database “new_db” using SQL query. Naturally, the physical files will be named as “new_db.mdf”. Our main aim is to rename SQL Server physical files from “new_db.mdf” to “old_db.mdf”. Follow the below mentioned steps to manually rename the files:

Firstly, run the following command to create a new database with name ”new_db”.

CREATE DATABASE [new_db] 

 CONTAINMENT = NONE 

 ON  PRIMARY 

( NAME = 'name', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\new_db.mdf',SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB )

Thus, a new database is created with physical file “new_db”. The following steps can be used to rename SQL Server physical files name.

Now, locate the physical location of file on your system using the following command:

USE new_db 

GO 

SELECT file_id, name as [logical_file_name], physical_name 

FROM sys.database_files

Also, the database should be brought to OFFLINE state because the files cannot be renamed in the online mode. Run the following command to bring database to offline state:

USE [master]; 

GO 

--Disconnect all existing session. 

ALTER DATABASE new_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

GO 

--Change database in to OFFLINE mode. 

ALTER DATABASE new_db SET OFFLINE

Since the database is offline now, so browse to the location of the file as found from Step(1). Rename the file to desired name e.g., old_db.mdf.
Now, update the system catalog for updating the new name of the physical file.

ALTER DATABASE new_db MODIFY FILE (Name='new_db', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\old_db.mdf')
GO

This updates the physical file name in system catalog file.

Run the following command to bring the database in ONLINE mode:

ALTER DATABASE new_db SET ONLINE 

Go 

ALTER DATABASE new_db SET MULTI_USER 

Go

So, the database physical file name has been renamed to “old_db.mdf” and the database is also in the Online mode. The users can now work normally on the database to perform any operation.

Conclusion

The user may feel it necessary to rename physical file name in SQL Server due to any reason. However, to avoid confusion between the physical files and database, the users are always advised to rename SQL Server physical database files using T-SQL. All the detailed steps have been mentioned in the above section.

Queries regarding Mysql Proceduers

To find out all the procedures of a database:-

SHOW PROCEDURE STATUS WHERE Db = 'thirstydb';

To set a new definer:-
mysql> UPDATE `mysql`.`proc` p SET definer = 'thirstydev@%' WHERE definer='thirstylive@localhost' AND db='devthirstydb';
Query OK, 0 rows affected (0.02 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> UPDATE `mysql`.`proc` p SET definer = 'thirstydev@%' WHERE definer='thirstylive@%' AND db='devthirstydb';
Query OK, 85 rows affected (0.04 sec)
Rows matched: 85 Changed: 85 Warnings: 0

To select all the procdure of a particular db:-
select name FROM mysql.proc WHERE db = 'devthirstydb' AND type = 'PROCEDURE';

select name FROM mysql.proc WHERE db = 'devthirstydb' AND type = 'FUNCTION';

To delete all the procdure of a particular db:-
DELETE FROM mysql.proc WHERE db = 'devthirstydb' AND type = 'PROCEDURE';

DELETE FROM mysql.proc WHERE db = 'devthirstydb' AND type = 'FUNCTION';

Mysql procedures:-

To find out all the procedures:-

mysql> show procedure status;
+--------------+---------------------------------------+-----------+---------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+--------------+---------------------------------------+-----------+---------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| thirstydb | advanced_vendor_filtering | PROCEDURE | thirstylive@% | 2016-08-12 05:13:27 | 2016-08-12 05:13:27 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | advanced_vendor_filtering_my_bookings | PROCEDURE | thirstylive@% | 2016-08-12 05:22:29 | 2016-08-12 05:22:29 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | advanced_vendor_filtering_temp | PROCEDURE | thirstylive@% | 2016-08-08 08:29:11 | 2016-08-08 08:29:11 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | all_food_categories | PROCEDURE | thirstylive@% | 2016-08-08 08:29:20 | 2016-08-08 08:29:20 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | apikeyValidity | PROCEDURE | thirstylive@% | 2016-08-08 08:29:29 | 2016-08-08 08:29:29 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | apikeyValidity_vendor | PROCEDURE | thirstylive@% | 2016-08-08 08:29:41 | 2016-08-08 08:29:41 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | directory | PROCEDURE | thirstylive@% | 2016-08-19 03:00:51 | 2016-08-19 03:00:51 | DEFINER | | utf8

To find out a procedure by a certain definer name and dbname:-
mysq> use mysql
mysql> select name from proc where definer = 'thirstydev@%' AND db='devthirstydb';

+---------------------------------------+
| name |
+---------------------------------------+
| advanced_vendor_filtering |
| advanced_vendor_filtering_my_bookings |
| advanced_vendor_filtering_temp |
| all_food_categories |
| apikeyValidity |
| apikeyValidity_vendor |
| base_url |
| directory |
| facebook_login

To call a procedure:-
mysql> call search_result(1);
Empty set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

To view a procedure:-

mysql> show create procedure user_login;
+------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| user_login | | CREATE DEFINER=`thirstylive`@`%` PROCEDURE `user_login`(email_in VARCHAR(255), password_in VARCHAR(255),
api_key_in VARCHAR(50), base_url_in TEXT)
BEGIN

DECLARE email_verified_declared INT(2);
DECLARE user_id_declared BIGINT;

SELECT email_verified INTO email_verified_declared FROM user_profile WHERE email = email_in AND `password` = password_in;
SELECT user_id INTO user_id_declared FROM user_profile WHERE email = email_in AND `password` = password_in;

IF email_verified_declared = 0 THEN

SELECT 'unverified_email' AS col_name;

ELSEIF user_id_declared IS NULL THEN

SELECT 'invalid_credentials' AS col_name;

ELSE

UPDATE user_profile SET api_key = api_key_in WHERE user_id = user_id_declared;
CALL user_profile(user_id_declared, base_url_in);

END IF;

END | utf8 | utf8_general_ci | latin1_swedish_ci |
+------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+-------------------

How to find out highest table size in sql server 2014 ?

SELECT
t.NAME AS TableName,
i.name as indexName,
sum(p.rows) as RowCounts,
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%' AND
i.OBJECT_ID > 255 AND
i.index_id <= 1
GROUP BY
t.NAME, i.object_id, i.index_id, i.name
ORDER BY
TotalSpaceMB desc