SQL SERVER DBA and Azure SQL

SQL Server service does not start successfully because of a logon failure

When you restart Microsoft SQL Server or SQL Server Agent, the service may fail to start with the following error message:

Error 1069: The service did not start due to a logon failure.

Cause

This problem occurs because the password for the SQL Server startup service account or the SQL Server Agent startup service account is not correct. This problem can occur when the password for the account is changed but the password information was not updated for the SQL Server service or the SQL Server Agent service.

Workaround

To solve this problem, type the correct password in the Microsoft SQL Server service account on the SQL Server host computer.

To correct the password in Microsoft Windows 2000 Server and Microsoft Windows XP:

Click Start, point to Settings, and then click Control Panel.
Double-click Administrative Tools, and then double-click Services.
Use one of the following steps based on your instance type:

For a default instance of SQL Server, double-click MSSQLServer, and then click the Log On tab.
For a named instance of SQL Server, double-click MSSQL$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.
For a default instance of SQL Server Agent, double-click SQLAgent, and then click the Log On tab.
For a named instance of SQL Server Agent, double-click SQLAgent$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.
Type the correct password in the Password and Confirm password textbox, and then click OK.

To correct the password in Microsoft Windows NT 4.0:

Click Start, point to Settings, and then click Control Panel.
Use one of the following steps based on your instance type:
For a default instance of SQL Server, double-click MSSQLServer, and then click the Log On tab.
For a named instance of SQL Server, double-click MSSQL$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.
For a default instance of SQL Server Agent, double-click SQLAgent, and then click the Log On tab.
For a named instance of SQL Server Agent, double-click SQLAgent$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.
Type the correct password in the Password and Confirm password textbox, and then click OK.
NOTE: Changing the password through the Services applet allows SQL Server to start successfully; however, if Microsoft Search (Full-Text Service) is installed, it requires a subsequent password change through SQL Enterprise Manager (SEM).

MySQL Frequently Using Commands

MySQL is one of the most using database system. Almost all web sites are using MySQL database. I wrote most using commands of MySQL database.I will share various commands using examples for MySQL database .

Connect MySQL

[root@testdb ~]# mysql
[root@testdb ~]# mysql -h hostip
[root@testdb ~]# mysql -h hostip -u username
[root@testdb ~]# mysql dbname -u username

[root@testdb ~]# mysql dbname -u username -P portnumber

List, Add, Drop, Change and Grant User

mysql> use mysql
mysql> create user Ajay;
mysql> drop user Ajay;
mysql> update user set password=PASSWORD(“newpassword”) where user=’Ajay’;
mysql> grant all privileges on testdb.* to Ajay;
mysql> grant all privileges on *.* to Ajay;
mysql> revoke all privileges on testdb.* from Ajay;
mysql> revoke all privileges on *.* from Ajay;
mysql> flush privileges;
mysql> grant usage on testdb.* to Ajay identified by ‘password’;
mysql> grant usage on *.* to Ajay identified by ‘password’;
mysql> revoke usage on testdb.* from Ajay
mysql> revoke usage on *.* from Ajay

mysql> flush privileges;

Information Queries of MySQL Database

mysql> help;

mysql> use testdb;
mysql> show tables;
mysql> desc tablename;

mysql> connect mysql;
mysql> select user();

mysql> show variables;
mysql> show variables where variable_name = ‘Port’;

[root@testdb ~]# mysqladmin –help
[root@testdb ~]# mysqladmin –version
[root@testdb ~]# mysqladmin ping

[root@testdb ~]# mysqladmin variables

SQL Server Reporting Service Start Error 1053 The Service Did Not Respond

Reporting Server Error 1053: The service did not respond to the start or control request in a timely fashion.

Solution

1. Open regedit

2. Locate here : HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control

3. Change ServicesPipeTimeout value.

If the ServicesPipeTimeout entry does not exist, you have to create it. You can follow add steps like below.

Add Step 1: Below Control section Edit > New > DWORD Value.
Add Step 2: Type ServicesPipeTimeout
Add Step 3: Right-click ServicesPipeTimeout and click Modify.
Add Step 4: Decimal 60000, OK
This value represents the time in milliseconds before a service times out.

4. Restart computer.

Result

SQL SERVER – ERROR MESSAGES (severity levels)

Each error message displayed by SQL Server has an associated error message number that uniquely identifies the type of error. The error severity levels provide a quick reference for you about the nature of the error. The error state number is an integer value between 1 and 127; it represents information about the source that issued the error. The error message is a description of the error that occurred. The error messages are stored in the sysmessages system table.

We can see all the system messages running following statement in query analyser.

SELECT * FROM master.dbo.sysmessages

The severity level are displayed in the table below.

0 to 10 Messages with a severity level of 0 to 10 are informational messages and not actual errors.

11 to 16 Severity levels 11 to 16 are generated as a result of user problems and can be fixed by the user. For example, the error message returned in the invalid update query, used earlier, had a severity level of 16.

0 to 10 Messages with a severity level of 0 to 10 are informational messages and not actual errors.

17 Severity level 17 indicates that SQL Server has run out of a configurable resource, such as locks. Severity error 17 can be corrected by the DBA, and in some cases, by the database owner.

18 Severity level 18 messages indicate nonfatal internal software problems.

19 Severity level 19 indicates that a no configurable resource limit has been exceeded.

20 Severity level 20 indicates a problem with a statement issued by the current process.

21 Severity level 21 indicates that SQL Server has encountered a problem that affects all the processes in a database.

22 Severity level 22 means a table or index has been damaged. To try to determine the extent of the problem, stop and restart SQL Server. If the problem is in the cache and not on the disk, the restart corrects the problem. Otherwise, use DBCC to determine the extent of the damage and the required action to take.

23 Severity level 23 indicates a suspect database. To determine the extent of the damage and the proper action to take, use the DBCC commands.

24 Severity level 24 indicates a hardware problem.

25 Severity level 25 indicates some type of system error.

Finding Fragmentation script in SQL Server

select
 object_schema_name(ps.object_id) as ObjectSchema,
 object_name (ps.object_id) as ObjectName,
 ps.object_id ObjectId,
 i.name as IndexName,
 ps.avg_fragmentation_in_percent,
 ps.page_count
from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps
inner join sys.indexes i
 on i.object_id = ps.object_id and
  i.index_id = ps.index_id
where
 avg_fragmentation_in_percent > 5 -- reorganize and rebuild
 and ps.index_id > 0
order by avg_fragmentation_in_percent desc