Useful SQL Server commands

SQLServer :

xp_fixeddrives Returns the list of hard drives and free space

dbcc sqlperf (logspace) Chk Logspace.

Shrink Database and log:

dbcc shrinkdatabase (tempdb)

sp_helpdb d_dbdba002

dbcc shrinkfile ('L001')

dbcc shrinkfile ('MSDBLog')

Chk Blocking on the server :

Blocking in Sql Server:

select spid,cmd,blocked from sysprocesses where blocked>0
sp_who2"SPID"
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

xp_cmdshell "tlist" - Displays the host processes with ‘pid’s

Restarting job from commandshell
xp_cmdshell "nc_restart servername jobname"

select paramname, paramvalue from d_dbdba002..task_params where paramname = 'DUMP_GENERATIONS'

sp_whodo n_cycle

select @@errorlog
select @@boottime

nc_task "DBAUS"

Sqltext capture

dbcc traceon(3604)
dbcc inputbuffer(121)

Db Uptime
select crdate from sysdatabases where dbid=2
Process count
select suser_name(suid),count(*) from sysprocesses group by suid

xp_readerrorlog

select loginname , createdate,updatedate,accdate from master..syslogins where loginname like '%username%'

--SQLserve Bkp compression issues

select databasename, backup_time, compression
from d_dbdba002..backups
where backup_time >= ''
order by backup_time
go

update d_dbdba002..backups
set compression = 'zip'
where backup_time = '2009-02-14 21:32:27.880' --the milli seconds.
and compression != 'zip'
go

--Update the file name.
update d_dbdba002..backup_files
set filename = filename + '.gz'
where backup_time = '2009-02-14 21:32:27.880'
and filename not like '%.gz'

dbcc opentran

sp_monitor - statistics about sqlserver

sp_tempdbspace - returns the total size and the space used by the tempdb database

xp_ServiceControl ‘START’,’SQLServerAgent’ - to start or stop sql server agent > sql 2k and above

sp_MStablespace - returns the number of rows in a table and the space the table and index use

select * from ::fn_virtualservernodes( ) -- returns the list of nodes on which the virtual server can run

select serverproperty('option')
MachineName – Returns the hostname on which server currently running
IsClustered - Returns 1 if server in cluster, else 0.


select has_dbaccess('da name')-- Returns 1 if user has access to the specified database, else 0.

select databaseproperty ('db name', 'option')

Returns 1 for True, 0 for false, else specific.
Useful options:
IsInLoad
IsOffline
IsAutoUpdateStatistics
IsInRecovery
IsSuspect
IsTruncLog
IsAutoShrink
Status


Tsadmin -- to login to host and check active loggedin sessions


How to find out when was an index rebuilt last?

SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = 'object name' AND o.id = i.id
go

sp_helpdb
sp_helpdb tempdb
dump tran tempdb with truncate_only