Regular usage scripts sql Server 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