DBA Regular usage scripts-SQL DBA


1. On which drive the  database files(mdf & ldf) are located?

use master
select *from sysaltfiles where filename like 'd%'
                      (OR)
select *from master..sysaltfiles where filename like 'd%'

2.How to shrinklog file if it is full?

backup log dbname with no_log
dbcc shrinkfile(2)

3. how to see open transations in a database?
  dbcc opentran

4. HOW TO see if perticlular process is blocked?

Use master
select * from sysprocessess where blocked>0 and spid=57

5. why buffer cache hit ratio <90?

 if the bottlenecks are there
 then the number of pages read in to the cache will vary
 and the ratio of finding the data by the server in the cache will decrease

6 how to find attribute of perticula file?

  Execute spFileDetails 'c:\autoexec.bat'

7. how to see the status of database?

  dbcc showfilestats

8.How to see the  Cpu usage?(if buffer cache hit raio<90)

 select *from sys.sysprocesses order by cpu asc

9.How to find out the info of a specific table on a database?

 select * from sysobjects where name like '%table_name%'

10. What is Undo File? Why it is required?

 Undo file is needed in standby state because while restoring the log backup, uncommited transactions will be recoreded to the undo file and only commited transactions will be written to disk there by making users to read the database. When you restore next tlog backup SQL server will fetch the uncommited transactions from undo file and check with the new tlog backup whether the same is commited or not. If its commited the transactions will be written to disk else it will be stored in undo file until it gets commited or rolledback.

11.How to get the data the from linked server?

 Select * from LinkedServer.DBName.SchemaName.TableName

12.How to find the service pack version?

SELECT SERVERPROPERTY('PRODUCTLEVEL')

13.How to see the collation setting for server level?

SP_HELPSORT

14.How to see log usage info in a database?

select * from  sys.databases where name like '%log_reuse_wait_desc%'

15.How to see the connection info by using DMV'S?

Select * from  sys.dm_exec_sessions

16. How to see the transaction isolation level?

DBCC USEROPTIONS

17.SQL Server 2000 database compatible level to SQL Server 2005

EXEC sp_dbcmptlevel AdventureWorks, 90;

18.How to see the memory ocupation of eache query running under Query Analyzer

select * from sys.dm_exec_query_memory_grants

19.How to clear the data from buffer cashe

DBCC DROPCLEANBUFFERS

20.How to see how much space tempdb is allocated for perticulas task

Select * from sys.dm_db_session_space_usage          
                  OR
sys.dm_db_task_space_usage 

21.The following DMV query can be used to get useful information about the index usage for all objects in all databases

Select * from from sys.dm_db_index_usage_stats 
order by object_id, index_id

22.The following query to list all the schedulers and look at the number of runnable tasks.

 Select * from from 
    sys.dm_os_schedulers where   scheduler_id < 255

23.Which Query is taking more cpu time 

select * from sys.dm_exec_query_stats 

24.How to find whether any active requests are running in parallel for a given session by using the following query.

select * from sys.dm_exec_requests 

25. if sql server installation failed in sql2000 whrere can i see tha failed info?
 sqlstp.log

26. How to see the lock info?

 select * from sys.dm_tran_locks

27. How to open cluadmin from windows 2008 server?

   cluadmin.msc

28. How to backup by using T-sql Query?

backup database DB name to disk = ‘path\full.bak’ with compression,stats=1
backup database DB name to disk = ‘path\diff.bak’ with compression,stats=1
backup log DB name to disk = ‘path\log_trn’ with compression,stats=1

29. How to Restore by using T-sql Query?

Restore database dbname from disk =’path\full.bak’ with move logical name to ‘path.mdf’,move  logical name to ‘path log.ldf’,norecovery,stats=1
Restore database DB name from disk =’path\diff.bak’ with norecovery,stats=1
Restore log DB name from disk = ‘path\log.trn’ with norecovery,stats=1

29.How to find Logical names?

Restore filelistonly from disk = ‘path’


T-SQL – Checking SQL Server Version and Service Packs

Often, you will need to check for SQL Server versions and service packs. Some of the features that you are using are only available from a service pack onward.

Microsoft provides the function server property to query for that kind of information.


SELECT
CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
AS VARCHAR(17)) AS 'Computer physical name',
CAST(SERVERPROPERTY('ServerName')
AS VARCHAR(17)) AS 'Server name',
CAST(SERVERPROPERTY('MachineName')
AS VARCHAR(17)) AS 'Machine name',
CAST(SERVERPROPERTY('Edition')
AS
VARCHAR(25)) AS 'Server edition',
CAST(SERVERPROPERTY('InstanceName')
AS VARCHAR(17)) AS 'Instance name',
CAST(SERVERPROPERTY('LicenseType')
AS VARCHAR(15)) AS 'License type',
CAST(SERVERPROPERTY('ProductVersion')
AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel')
AS VARCHAR(15)) AS 'Service pack';

Go




T-SQL – whether the databases are configured for mirroring or not

If you want to check whether the databases are configured for mirroring or not, then below script would be a great help.

SELECT

A.name,
CASE

   WHEN B.mirroring_state is NULL THEN 'Mirroring is not configured'

   ELSE 'Mirroring is configured'

END as MirroringState

FROM

sys.databases A

INNER JOIN sys.database_mirroring B

ON A.database_id=B.database_id

WHERE a.database_id > 4

ORDER BY A.NAME




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