Tuesday, July 22, 2008

Useful common sql commands for daily dba grind


--get the name of database files

select * from sysfiles

--C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\nwind_report.mdf
--C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\nwind_report_log.ldf

--get version and name of SQL Server you are running

select @@version
select @@servername
select serverproperty('INSTANCENAME')
select '\\' + @@servername
select '\\' + cast(serverproperty('servername')as varchar)

--Find active processes and related commands

sp_who2 active
kill 60
dbcc inputbuffer(61)

--one of favorites commands. used when log file runs out of space

backup log database with no_log

--some useful extended stored procedures

master..xp_fixeddrives
master..xp_readerrorlog

--old school system objects

select * from sysobjects
where type='u'
select * from sysdatabases
where name not in ('northwind', 'pubs', 'tempdb')

--text for a stored procedure

sp_helptext cs_Process_t_IPContact

--xp_cmdshell at work

master..xp_cmdshell 'dir c:\' --12GB
master..xp_cmdshell 'dir d:\' --12GB
master..xp_cmdshell 'dir data\dba'

Welcome to my Slacker DBA blog

Hope all is well with you and the world.

I am sitting here working on this blog even though I could be working on important and vital DBA related stuff. Oh well, it can wait. I was aimlessly surfing the net (Does anyone use that term anymore), when I came across this blogger site. Figured I could do this on the side. It may add a little excitement to my
mundane life.

So here we go. I am a Senior DBA at an IT company. Most days I work with SQL Server, although I have indulged into Oracle and Sybase as well. I would also consider myself a lazy dba as I hate to do stuff that has already been done by someone more intelligent and aspiring than me.

With that introduction, I am officially open for business. Feel free to send in comments and database related stuff.

Kash