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.
| Author |
Topic |
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
 Posted - 2009-12-29 : 16:23:06
|
| I have an SSIS package that does the following, read list of server names from table A. Pass servername[var of type object] to a for each loop.For each loop iterates thru servername being passed.Within for each loop, Im executing exec sp_msforeachdb 'exec ?.dbo.sp_spaceused' for each servername using a Execute SQL Task.The Execute SQL Task has the following properties.Result Set = Full Result SetSQL Statment = sp_msforeachdb 'exec ?.dbo.sp_spaceused' Parameter mapping = var name [Input] , var type [String]Result Set= var name [Output] , var type [Object]I would like to store the results of the execute sql task within the for each loop to a table.How can I achive this? What is my next step ? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
Posted - 2009-12-29 : 16:46:33
|
| Tara,I need to capture the db usage of each database within a list of servers provided in table A.How can I use sp_database to achive this as I understand sp_databases returns list of database, I need both the database list and database usage [size,index_size,unallocated...]Could you advice?Thanks |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
Posted - 2009-12-30 : 09:22:00
|
| Tara,Yes,clarified with my superior its ok not to include the other information.My manager needs to see Total Size vs Size used.How can I get this information for all the db within the list of database provided ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-30 : 10:35:21
|
Here's what I use for a single database:declare @dbFile sysname, @sql varchar(200), @increment int, @size int, @used intset @dbFile = 'gomdata'select @used = fileproperty(@dbFile, 'SpaceUsed')/128.0, @size = [size]/128.0 from sysfiles where name = @dbFileselect @used as 'used', @size as 'size' I don't have one for all of the databases, but the above can be modified to get it. You'd need to loop through sys.databases and then loop through sysfiles if you have multiples.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
|
|
|
|
|
Read results of exec sp_msforeachdb 'exec ?.dbo.sp