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 ««123»»

Using XP_EXECRESULTSET To Obtain Database Size Information and More Expand / Collapse
Author
Message
Posted Tuesday, June 27, 2006 12:51 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Saturday, September 6, 2014 11:27 PM
Points: 126, Visits: 505

J.T.

Wanted to get your opinion on using this method you proposed versus using Dynamic SQL and say sp_executesql or EXECUTE?

1. does the optimizer handle them diffrently. 

    a. Are they treated as two seperate query plans for the same query or one.

    b. Can XP_EXECRESULTSET take advantage of the caching of multiple calls from various users that sp_executesql can

    c. do you execute a XP_EXECRESULTSET  query with the same SPID as the current connection or is a new one made?

 

This is all I can think of off the top of my head anyone have other comparisions to ask let them rip.




Post #290525
Posted Tuesday, June 27, 2006 1:10 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 9:16 AM
Points: 316, Visits: 4
Make sense. Thanks for the explanation.


-- J.T.

"I may not always know what I'm talking about, and you may not either."

Post #290537
Posted Tuesday, June 27, 2006 2:00 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 9:16 AM
Points: 316, Visits: 4

I'm not sure how the optimizer would handle them. It would be easy enough to come up with a test query and then execute it via xp_execresultset and a dynamic sql method.

 

The main advantage of xp_execresultset is that is allows a query to be run in the context of another database without having to switch the current connection to that context. In the article I used FILEPROPERTY which only runs against the current database. I could do this in dynamic SQL (and someone in the forums of Boris Baliner’s article on “Quickly Viewing Available Space” (http://www.sqlservercentral.com/columnists/bBaliner/quicklyviewingavailablespace.asp) did just that) However, the result was quite long and required a lot of context switching.

 

I don't know if xp_execresultset can take advantage of caching but I wouldn't use it in any situation where that might be necessary in any case. As I stated, this is an undocumented procedure and Microsoft dropped it in SQL 2005. Using this procedure in an application would be ill-advised. I presented it as a tool for administrators as an alternative to writing long dynamic sql-based scripts.

 

Yes, it executes under the same SPID. To determine this add a ,@@SPID to the end of the query in the script, as shown below:

 

           SET @SQLCmd='SELECT ''SELECT name AS [File],

                  filename as [File Name],

                  CAST(size/128.0 as DECIMAL(10,2)) AS [Size in MB],

                  CAST(FILEPROPERTY(name, ''''SpaceUsed'''')/128.0 as DECIMAL(10,2)) as [Space Used],

                  CAST(size/128.0-(FILEPROPERTY(name, ''''SpaceUsed'''')/128.0) AS DECIMAL(10,2)) AS [Available Space] ,@@SPID

                  FROM SYSFILES'''




-- J.T.

"I may not always know what I'm talking about, and you may not either."

Post #290561
Posted Tuesday, June 27, 2006 9:16 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 6:21 PM
Points: 161, Visits: 73

Two questions:

1: the "sid > 1" 

   If the "sa" create a product database,the sid in the sysdatabases table will be 'Ox01'.Then the command doesn't work well.

 

2:the query listed below

Declare @DBName sysname
Declare @SqlCmd Nvarchar(2000)
Set @DBName = ''

While @DBName Is not NULL
Begin
 Select @DBName = Min(Name) From master..sysdatabases Where sid > 1 And Name > @DBName
 Set @SqlCmd = 'SELECT ''SELECT name AS [File], filename as [File Name]
    , CAST(size/128.0 as DECIMAL(10,2)) AS [Size in MB]
    , CAST(FILEPROPERTY(name, ''''SpaceUsed'''')/128.0 as DECIMAL(10,2)) as [Space Used]
   , CAST(size/128.0-(FILEPROPERTY(name, ''''SpaceUsed'''')/128.0) AS DECIMAL(10,2)) AS [Available Space] FROM SYSFILES'''

 IF @DBName is NULL Break
 Exec Master..xp_execresultset @SqlCmd,@DBName
End

It just return "Command(s) completed successfully."

 

 

Post #290644
Posted Tuesday, June 27, 2006 10:16 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 16, 2014 7:20 PM
Points: 63, Visits: 470

Thanks for mentioning that the xp_execresultset is missing from SQL Server 2005.  Yikes!  Before retiring your SQL Server 2000 servers, do this--

 

1) Copy proc sp_execresultset from the master database of any server, and create it in the master database of the SQL Server 2005 server.  (The xp_execresultset is unike other xp_ procs; it isn't compiled and reading it shows that it calls sp_execresultset.)

 

2) Copy proc xp_execresultset from the master database of any server, and create it in the master database of the SQL Server 2005 server, named sp_execresultset2.  (Doesn't work if you name it xp_ -- and, proc can be named sp_ to be called from databases other than master without "exec master.." -- at least that is true in SQL Server 2000.)

 

3) Edit the script to refer to sp_execresultset2 in place of master.dbo.xp_execresultset.




_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
Post #290648
Posted Wednesday, June 28, 2006 6:26 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 9:16 AM
Points: 316, Visits: 4

Take out the sid>1 conditional from the where clause.

One of the readers, Scott, pointed out that the SID=1 means sa created the database and that this isn't a good way to limit tha query to non-system databases. That should solve the problem. You are, as you pointed out, probably excluding all of your user databases as well because they were created by 'sa'.

Declare @DBName sysname
Declare @SqlCmd Nvarchar(2000)
Set @DBName = ''

While @DBName Is not NULL
Begin
 Select @DBName = Min(Name) From master..sysdatabases Where Name > @DBName
 Set @SqlCmd = 'SELECT ''SELECT name AS [File], filename as [File Name]
    , CAST(size/128.0 as DECIMAL(10,2)) AS [Size in MB]
    , CAST(FILEPROPERTY(name, ''''SpaceUsed'''')/128.0 as DECIMAL(10,2)) as [Space Used]
   , CAST(size/128.0-(FILEPROPERTY(name, ''''SpaceUsed'''')/128.0) AS DECIMAL(10,2)) AS [Available Space] FROM SYSFILES'''

 IF @DBName is NULL Break
 Exec Master..xp_execresultset @SqlCmd,@DBName
End




-- J.T.

"I may not always know what I'm talking about, and you may not either."

Post #290731
Posted Wednesday, June 28, 2006 6:32 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 9:16 AM
Points: 316, Visits: 4

Good ideas. Just remember that this is still unsupported so don't call Redmond if you have problems with it.

 

That's what we're all here for anyway.




-- J.T.

"I may not always know what I'm talking about, and you may not either."

Post #290733
Posted Wednesday, June 28, 2006 7:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 8, 2007 2:25 PM
Points: 53, Visits: 1

Really neat. Thanks.

I appreciated the cut and paste script in the comments. The graphic image of the script in the article was frustrating.

-Robert

 

Post #290748
Posted Wednesday, June 28, 2006 1:04 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 9:16 AM
Points: 316, Visits: 4
Yeah, I noticed that. Sorry about the original.

Glad you found this useful.



-- J.T.

"I may not always know what I'm talking about, and you may not either."

Post #290886
Posted Wednesday, June 28, 2006 7:48 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 6:21 PM
Points: 161, Visits: 73

Got it ...

Thanks

Post #290984
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse