imp system procedures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

imp system procedures

Author

Topic

rucha3
Starting Member

7 Posts

Posted - 2008-03-16 : 22:44:06

hi, i see there are lots of system store procedures available but could you specify me the ones that are used in day to day work as a DBA??

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-16 : 22:46:11

You can find out what they do in books online, then pick whatever you need.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-16 : 23:47:18

xp_fixeddrives,sp_lock,'sp_who2 active',sp_databases,sp_tables,sp_helpuser etc.etc

rucha3
Starting Member

7 Posts

Posted - 2008-03-17 : 11:06:07

hey thanks a lot!!!

craig79
Starting Member

33 Posts

Posted - 2008-03-20 : 11:04:42

Some imp queries if u need :

select * from dbo.sysprocesses
where blocked <> 0

select * from dbo.sysprocesses
where waittime <> 0

select 'DBCC INPUTBUFFER (' + rtrim(convert (char (2),SPID))+ ')' from dbo.sysprocesses
where waittime <> 0

select SPID,WAITTIME,LASTWAITTYPE,DBID,STATUS,CMD from dbo.sysprocesses
where waittime <> 0

sp_helpdb

select * from dbo.sysprocesses
where waittype <> 0x0000

select SPID,WAITTIME,LASTWAITTYPE,DBID,STATUS,CMD from dbo.sysprocesses /* Serious Proble in getting Threades */
where LASTWAITTYPE = 'UMSTHREAD'

xp_dirtree

This extended stored procedure can be used to get a list of all the folders for the folder named in the xp.
To get a list of all the folders in the C:\MSSQL7 folder, run: EXEC master..xp_dirtree 'C:\MSSQL7'

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-21 : 14:13:01

Great job Craig!!!

PABluesMan
Starting Member

26 Posts

Posted - 2008-03-25 : 15:22:42

Here's one that has been extremely useful for me. It overcomes the 255 character limit from DBCC INPUTBUFFER, and it also shows the exact statement that executing in a multi-step proc instead of just the proc name:

/****************************************/
DECLARE @Handle VARBINARY (85)
DECLARE @SPID INT

SET @SPID = <SPID in question>

SELECT @Handle = sql_handle
FROM master.dbo.sysprocesses
WHERE spid = @SPID

SELECT text
FROM ::fn_get_sql (@Handle)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources