Commonly used scripts to troubleshooting...

Regular usage scripts for SQL Server 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 particlular 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

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%'

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 memory ocupation of eache query running under Query Analyzer

select * from sys.dm_exec_query_memory_grants

17.How to clear the data from buffer cashe

DBCC DROPCLEANBUFFERS

18.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 

19.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

20.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

21.Which Query is taking more cpu time ?

select * from sys.dm_exec_query_stats 

22.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 

23. if sql server installation failed in sql2000 whrere can i see tha failed info?

verify the sqlstp.log and find the error and troubleshoot the error.

24. How to see the lock info?

 select * from sys.dm_tran_locks

25. How to open cluadmin from windows 2008 server?

   cluadmin.msc