Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Undocumented Extended and Stored Procedures Expand / Collapse
Author
Message
Posted Thursday, May 8, 2008 11:19 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
Comments posted to this topic are about the item Undocumented Extended and Stored Procedures

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #497570
Posted Thursday, May 8, 2008 11:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 11:50 AM
Points: 15, Visits: 150
timothyawiseman (5/8/2008)
Comments posted to this topic are about the item
...edited from article...
Both sp_who and sp_who2 with its added columns can be tremendously valuable in scripts and programs, but for use on an ad hoc basis it is often more convenient to invoke the Activity Monitor GUI.
...edited from article...


Are you kidding me? If something is hanging up the server, the GUI will take minutes or longer to show you what's happening. Using sp_who2 is generally pretty fast even if the server is getting creamed by something.

I have a custom sp_who3 script which simply modifies the sp_who2 script to only show active processes. That way you can quickly see the active processes when something is slowing the server.

I find the GUI to be worthless.
Post #497573
Posted Friday, May 9, 2008 12:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 22, 2010 8:40 AM
Points: 6, Visits: 13
I would use Powershell for the first one and maybe write my own SP for the second. It takes me back to my unix days when wee had a whole list of shell scripts that we laid over the OS.
Post #497609
Posted Friday, May 9, 2008 1:17 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
rnjohnson10 (5/8/2008)
Are you kidding me? If something is hanging up the server, the GUI will take minutes or longer to show you what's happening. Using sp_who2 is generally pretty fast even if the server is getting creamed by something.

....


You have a good point. Sp_who and sp_who2 will always be faster than the gui and that is particularly true if something is causing a general slow down.

With that said, I personally find the gui is often more convenient and more user friendly when looking for a specific block rather than a general slowdown. At least for me, both the procedures and the gui have their place depending on the situation.


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #497625
Posted Friday, May 9, 2008 1:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 11:38 PM
Points: 10, Visits: 18
All extended stored procedures mentioned in the article are well known to most of the people working with SQL Server for some years. They have been discussed many times in SQL server books and on popular SQL Server websites. Google search on "undocumented sql server stored procedures" returned 89,000 web pages. xp_FileExists for example is discussed at sqlservercentral, developersdex, databasejournal, sqlteam, dbforums, etc. The article lacks novelty but may be useful for some community members. That's why I rated it as 3-star.
Post #497648
Posted Friday, May 9, 2008 5:06 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
Nice work! Pretty comprehensive list, I've used most of these xp's in the past.
Post #497738
Posted Friday, May 9, 2008 6:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 10:06 AM
Points: 184, Visits: 1,018
Nice list of stored procedures. You pointed out a few I was unaware of.

Thanks for sharing the info


David Bird

My PC Quick Reference Guide
Post #497772
Posted Friday, May 9, 2008 6:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 999, Visits: 8,657
Several of the examples were unreadable, displaying only a horizontal scroll bar. What might have been the example code was in a window which appeared to be 1 or 2 pixels tall.



Steve Eckhart
Post #497789
Posted Friday, May 9, 2008 7:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:53 PM
Points: 209, Visits: 367
Here's a caveat you can add to running sp_who2 to get just the running processes.

exec sp_who2 active
Post #497851
Posted Friday, May 9, 2008 8:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 9, 2011 1:09 PM
Points: 28, Visits: 48
Steve Eckhart (5/9/2008)
Several of the examples were unreadable, displaying only a horizontal scroll bar. What might have been the example code was in a window which appeared to be 1 or 2 pixels tall.


I was able to work around this glitch by printing the article to PDF. The code listings are printed inline with the article.

I agree with Steve that this UI bug should be fixed.



Post #497923
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse