SQL Server Basic Prod DBA commands..

Blocking in db server..
select spid,cmd,blocked from sysprocesses where blocked>0
select spid,blocked,cmd,memusage,physical_io from sysprocesses order by memusage desc
select spid,blocked,cmd,memusage,physical_io from sysprocesses order by physical_io desc
select spid, loginame, db_name (dbid) 'dbname',status, cmd,hostname from sysprocesses where db_name (dbid) = 'P_DBAPMCOR01' order by dbid
sp_who2 "spid"
dbcc traceon(3604)
dbcc inputbuffer(spid)
dbcc sqltext(spid)
sp_showplan spid,null,null,null
Space Usage check for tempdb or Userdb:
xp_fixeddrives
dbcc sqlperf(logspace)
dbcc shrinkdatabase(tempdb)
sp_helpdb d_dbdba002 - db name
dbcc shrinkfile('L001') - logfilename
other commands
sp_whodo n_cycle
xp_cmdshell "tlist"
xp_cmdshell "nc_restart servername jobname"
xp_readerrorlog
select @@errorlog
select @@boottime
select @@servername
select getdate()
select @@version
nc_task "jobname"
select crdate from sysdatabases where dbid=2
select suser_name(suid),count(*) from sysprocesses group by suid
select paramname, paramvalue from d_dbdba002..task_params where paramname = 'DUMP_GENERATIONS'
select loginname , createdate,updatedate,accdate from master..syslogins where loginname like '%sysfaonycapmsvc%'
Last Gather Stats
STATS_DATE function takes two arguments table_id and index_id. Following is the SQL followed by output. You can run it from query analyzer or management studio.
SELECT t.table_name, i.name as Index_Name, STATS_DATE(i.id, i.indid) as Statistics_last_collected FROM information_Schema.tables t INNER JOIN sysindexes i ON object_id(t.table_name) = i.id WHERE t.table_type = ‘BASE TABLE’ AND i.Name is NOT NULL AND i.Name not like ‘_WA_Sys%’ GO
connetin sqlserver frm command prompt:
H:\>net use \\GPRI11D11013 /user:csfb\sys0amdbadm1
Enter the password for 'csfb\sys0amdbadm1' to connect to 'GPRI11D11013':The command completed successfully.
H:\>osql -SGPRI11D11013 -E