Error 42d during Fail Over SQL 2008R2 Enterprise in a Server 2008R2 Ent. Cluster

Hi Friends,

Today morning I was just logging in Cluster instances to check certain parameters, while checking I found one of the SQL cluster is in failed state.

Error Messages in Event logs are given below:

…………………………………………………………………..

[sqsrvres] StartResourceService: StartService
(MSSQLSERVER) failed. Error: 42d

………………………………………………………………….

………………………………………………………………………………

[sqsrvres] OnlineThread: ResUtilsStartResourceService
failed (status 42d)
……………………………………………………………………………..

…………………………………………………………………………….
[sqsrvres] OnlineThread: Error 42d bringing resource
online.

…………………………………………………………………………..

Here 42d is expressed in Hexadecimal. The decimal
equivalent is 1069.

From the command prompt, c:\> net helpmsg 1069

Once you execute the above command to will get the message:

“The service did not start due to a logon failure”.

Solution:

  1. First check the service startup account for both the SQL and SQL agent service. It must be domain account.
  2. Incase if password changed for SQL service account and you forget to do the changes in service account than immediately change the password and try to make the resource online from Cluster Manager.
  3. Check SQL service account password expired or not. Incase expired change the password and change the password of service account on both node and make the resource online form Cluster Manager.

Note: The service startup account is same in both the nodes of your cluster.

 

 

 

 

Property Owner is not available for Database ‘Database Name’.

Hi Friends,

One of my team member getting error message “Cannot execute as the database principal because the principal “Database Name” does not exist, this type of principal cannot be impersonated, or you do not have permission.

SSMSError

 

 

 

 

 

 

There are multiple cause of this problem.

  1. May be your database is in recovery state, in this case also you may face this problem as User will not be able to access the database.
  2. This error happened because the original DBO login of that database was removed recently from the SQL Server instance.

Solution for the above problem:

In first case wait for the database recovery. Once the database recovery completed successfully than try to make database connection. It will be successful.

In second case you have to change the login permission using below TSQL.

 

USE [Database_Name]
EXEC sp_changedbowner ‘User Name’

 

 

 

 

 

 

 

 

 

 

 

 

The trust relationship between this workstation and the primary domain failed in windows server 2008R2

Friends:

Today I reached office and found some issue in  one the Database Instance, installed on Windows Server 2008R2 .

Error:

The trust relationship between this workstation and the primary domain failed.

Solution:

Disjoining and joining again the computer to the domain should fix the trust relationship issue.

 

 

 

MySQL Workbench cannot load MYSQL.PROC

One of my team member starting to use MySQL Workbench tool especially for development task but not able to see the tables, stored procedures and views, Getting a strange error message:

Error: Cannot load from mysql.proc. The table is probably corrupted

Considering the error message. I tried to repair the table but no solution. Basically I upgraded the MYSQL from version 5.1 to 5.6.

 

I fixed the problem by executing the below command.

…………………………………….

mysql_upgrade uroot p

…………………………………….

Create MySQL User to Backup and Restore Databases!!

Create MySQL User to Backup and Restore  Databases

It is always recommended to create a Backup and Restore User. I always recommend not to use MYSQL root user for database backup and restore. There is a slightly difference in the User privileges for Restore and backup.

 

User Privileges Required for Backup  and Restore are:

Backup

……………………………………………………………………………………………….

Select, Show View, Lock Tables, File, Reload and Super

……………………………………………………………………………………………….

Restore

……………………………………………………………………………………………….

Create, Insert, Alter, View, Drop, Index, Replication Client, Shutdown

……………………………………………………………………………………………….

Query for User creation are:

Backup

……………………………………………………………………………………………………………………………….

Create User ‘db_backup’@’localhost’ identified by ‘Password’

Grant Select, Show View, Lock Tables, File, Reload, Super on * . * To  ‘db_backup’@’localhost’ identified by ‘Password’ With Max_Queries_Per_Hour 0 Max_Connections_Per_Hour 0 Max_Updates_Per_Hour 0 Max_User_Connections 0

………………………………………………………………………………………………………………………………..

Restore

………………………………………………………………………………………………………………………………..

Create User ‘db_restore@’localhost’ identified by ‘Password’

Grant Create, Insert, Alter, View, Drop, Index, Replication Client, Shutdown on * . * To  ‘db_restore’@’localhost’ identified by ‘Password’ With Max_Queries_Per_Hour 0 Max_Connections_Per_Hour 0 Max_Updates_Per_Hour 0 Max_User_Connections 0

…………………………………………………………………………………………………………………………………

 

MySQL Binary Logging

How to enable MySQL binary logging.

To enable MySQL binary logging stop the MYSQL service and then add the mentioned parameter in “my.cnf” configuration file.

……………………………………………………….

log-bin=/var/lib/mysql/mysql-bin
server-id = 1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

……………………………………………………….

Once the above parameter added than start the MySQL service “Service  mysqld Start”.  This is the mistake which majority of people will do.

I strongly recommend before starting MySQL service check the MYSQL permission to write in the logs. If you find that MYSQL is not the owner of the Bin-Logging directory than follow the below steps.

……………………………………………

cd /var/lib
chown -R mysql:mysql mysql
service mysql start

……………………………………………

Also check  is there any presence of another my.cnf in the server

Try the following
cd /var/lib/mysql
ls -l my.cnf
If you find a my.cnf file, than just rename the file.
mv /var/lib/mysql/my.cnf /var/lib/mysql/my_old.cnf

Now restart the MySQL service
service mysql restart

 

 

Up gradation of SQL Server 2005 to SQL 2012 Instance!!

High Level document for Implementation, Migration and Up gradation of SQL Server Database to SQL 2012 Instance!!
One of my friend asked to have a High level document for Implementation, Migration and Up gradation of SQL Server Database to SQL 2012 This document might help you in your up gradation assignments. I appreciate if you read and share your comments to improvise or to add in this document if I missed something..

SQL Database Implementation, Migration and Up-Gradation

SQL Server Error Log Consuming Lots of Disk Space

SQL Server Error Log Consuming lots of Disk Space!!

I got a call from one of my Friend that in one of the SQL Server drive was very low on space. As a DBA my first question, This is a dedicated Data file or Log file drive, check the utilization of SQL Data and Log file or any folder other than SQL files in this drive or not but after conversation I came to know that SQL Logs is utilizing the maximum space on disk so the culprit is “SQL Error Logs” which is utilizing the Disk space in GB.

Where is the SQL Server Error Log

“C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\Log”

This location may be differ in your environment based on the Configuration done at the time of Binaries Installation.

How to Recycle the SQL Server Error Log and View the Logs in SQL

To view the logs connect to SQL Instance using “Management Studio” and Expand the “Management”  under Management you can find the “SQL Server Logs”.

SQL Error LogNow we need to recycle the Error Log to clean make the availability of Space in Disk. Open the Query Window and Execute  a stored procedure called “SP_CYCLE_ERRORLOG” which will create a new log and move the old log to a new file “ErrorLog.1” inside your error log directory.

Before executing make sure you have taken the backup of Error Log file. Reason is some times we are going with default number of file values which is by default is “6” Log files and based on the Project Audit policy and Quality procedures you have to maintain the Error logs file for a desired period of time. If you reach to the limit than it will cycle the existing logs. In short , when the maximum log files reached than it will remove the old file. In this case you can increase the number of files from default value “6” to desired number of files maximum 99, to configure the files, Right click the SQL Server Logs and choosing the “Configure” option.

 

Hope: This will help you!!

 

 

 

 

 

 

 

 

 

 

Cluster network name resource ‘SQL Network Name (TESTDBCLU)’ failed to create its associated computer object

Cluster network name resource ‘SQL Network Name (TESTDBCLU)’ failed to create its associated computer object in domain ‘ABC.CORP.XYZ.IN’ for the following reason: Unable to update password for computer account.

The text for the associated error code is: Access is denied.
Please work with your domain administrator to ensure that:
– The cluster identity ‘TESTOSCLUCTR$’ can create computer objects. By default all computer objects are created in the ‘Computers’ container; consult the domain administrator if this location has been changed.
– The quota for computer objects has not been reached.
– If there is an existing computer object, verify the Cluster Identity ‘TESTOSCLUCTR$’ has ‘Full Control’ permission to that computer object using the Active Directory Users and Computers tool.

Solution:

Coordinate with your Domain Administrator and ask him to perform below steps.

1. Create computer object  for SQL Network Name in DC “TESTDBCLU”.

2. Give Full Permission to Node1, Node2, Domain Account through witch Installation Performed and OS Cluster Name in Our case “TESTOSCLUCTR” on SQL Network Name “TESTDBCLU”.

3. Once above steps done and You will get the confirmation from AD team than start the SQL Failover Cluster installation.

 

Note: There is no Domain Admin rights required to configure a SQL and OS cluster.

 

 

SQLSERVER Broadcast ‘SQLSERVER 2016 CTP2 Available for download’

Friends :

SQLSERVER 2016 CTP 2 available for download

http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016?i=1

WP2Social Auto Publish Powered By : XYZScripts.com