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 |
|
sqlpal2007
Posting Yak Master
200 Posts |
 Posted - 2011-08-15 : 08:46:34
|
| Hello All,I have to get size ldf and mdf files of all the databases on my production server. Moreover, this SSIS package will be doing lot of other database diagnostic tasks like table fragmentation, file names and paths, I/O statistics, average stalls per read, write etc.This is my first SSIS package. I created 2 tables on a admin database. First, has the list of databases I need to monitor and second table will store output of file sizes.In SSIS - I created a OLEDB connection to my SQL 2005 server and connected to a admin database.Execute SQL task with SQL command - SELECT DBName from adminDB.Then the ForEach Loop container. And inside that another Execute SQL task to actually execute the SQL to calculate file sizes and insert them into the table.But the package is not working. Do I have to specify connection string in SQL table or the database connection takes care of it?Please helpThanks, |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-08-15 : 12:43:20
|
| There's also:SELECT DB_NAME(database_id) DB, name, physical_name, SIZE/128. MB FROM sys.master_files |
 |
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2011-08-15 : 13:32:17
|
| As I said, I have to execute other database diagnostic commands and store output to SQL tables. Then have the SSRS report pull all the information and present it in graphical manner (charts / pie)I have to do it thr' SSIS. Any input would be greatly appreciated. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-08-15 : 13:42:07
|
| The statements that Tara and I posted will give you names and sizes of all the databases on a single server. You don't need a ForEach to evaluate each database. You can also modify your other diagnostic queries to include file sizes to combine steps. |
|
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2011-08-15 : 14:16:14
|
| Thanks Robvolk and Tara.For this task I agree with your suggestion but I would still need ForEach loop for the rest - --Top Cached SPs By Execution Count (SQL 2008)--Top Cached SPs By Avg Elapsed Time (SQL 2008)--Top Cached SPs By Total Worker time (SQL 2008). Worker time relates to CPU costetc.Currently I am executing these statements in a stored procedure for only one database. And now I have to loop thr' multiple databases to get all of these details. i don't want to use dynamic SQL.I want to learn SSIS.Thanks, |
|
|
|
|
|
|
|
|
Multiple databases dynamically
Tara Kizer