Collect Database and Table Index Grow Statistics for all SQL Servers Using PowerShell


By:   |   Updated: 2014-03-26   |   Comments (16)   |   Related: More > Monitoring


Is the Database the Culprit of Your Application Issues?

Free MSSQLTips Webinar: Is the Database the Culprit of Your Application Issues?

When you're troubleshooting application performance issues, have you ever found the problem residing a few layers deep in the database or not at all? How long did it take you to find and fix the issue? Don't worry if you said "a while". Learn how to solve performance problems fast.


Problem

You want to collect growth statistics on your databases and report on them. Management would like to see how fast databases are growing. Users would like to know how big their main tables are becoming. You have over a hundred servers with thousands of databases and you don't have $500 per server to spend. You'd like to do it yourself. But how?

Solution

Use a small PowerShell script to collect these statistics into two tables. In Management Studio schedule several T-SQL queries in SQL Server Agent Jobs to produce simple text reports. Setup another job to use SQL Server Database Mail to email the reports to all who want them.

As a DBA, everyone likes to look at growth reports. Databases grow, tables grow, indexes grow. I'm even still growing, but at age 55, it's not in a positive way. This year as we were moving our monitoring stuff from SQL Server 2000 to SQL Server 2008 (yes we're that far behind) and our JavaScript programs wouldn't work on a Windows 2008 box because SQL DMO did not come along. Oh there was some kind of backward compatibility thing you could install, but I wanted to get within 5 years of being current so I decided to get going with PowerShell and see if I couldn't replace the JavaScript stuff with that. Well, I got the Windows PowerShell Bible (Lee et al. Wiley Press) and between that and sites like MSSQLTips.com, I threw together something that works.

STEP 1: Create Tables

You need someplace to hold the data so create two tables to store the information. The first, is_sql_database, will hold data on databases, size, index size, and space available. The other, is_sql_tables, will hold data on tables within each database such as # rows or index size. These tables were created on a 2008 R2 instance. I don't think the date datatype will work on SQL Server 2005 or earlier editions, so you could change to a datetime datatype if your running this on a SQL Server 2005 or earlier instance. Here are the CREATE statements:

CREATE TABLE [dbo].[is_sql_databases](
 [is_identity] [int] IDENTITY(1,1) NOT NULL,
 [is_sqlserver] [varchar](60) NOT NULL,
 [is_name] [varchar](60) NOT NULL,
 [is_date_stamp] [date] NOT NULL,
 [is_size] [real] NULL,
 [is_available] [real] NULL,
 [is_indexsize] [real] NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_is_sql_databases] ON [dbo].[is_sql_databases] 
(
 [is_date_stamp] ASC,
 [is_identity] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
       DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 CREATE TABLE [dbo].[is_sql_tables](
 [is_identity] [int] IDENTITY(1,1) NOT NULL,
 [is_sqlserver] [varchar](60) NOT NULL,
 [is_database_name] [varchar](60) NOT NULL,
 [is_name] [varchar](60) NOT NULL,
 [is_date_stamp] [date] NOT NULL,
 [is_rows] [decimal](15, 0) NULL,
 [is_data_space_used] [decimal](15, 0) NULL,
 [is_index_space_used] [decimal](15, 0) NULL
CREATE CLUSTERED INDEX [IX_is_sql_tables] ON [dbo].[is_sql_tables] 
(
 [is_date_stamp] ASC,
 [is_identity] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
       DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Notice I created each table with clustered indexes based on the collection date and an identity field. This goes on the theory that all your reporting will be searching based on dates.

STEP 2: The PowerShell Script

These tables are populated by a PowerShell script that is driven by a text file that contains the names of the servers you want to track. Note that this script needs a text file called SQLSERVERLIST.TXT which is a simple list of the servers to connect to. The server name is STATS_2008R2, and the database is is_dba. This script runs on the same server where the two tables were created. Here is the PowerShell script:

$today = Get-Date
$srvlist = @(get-content "c:\sql_scripts\DB_Stats\SQLSERVERLIST.TXT") 
ForEach($server in $srvlist){
    $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
if ($srv.Status -EQ "Offline" )
{
$dbs = $srv.Databases
     ForEach ($db in $dbs) 
     {
       if ( $db.IsAccessible ) {
       $name1 =$db.name
       $size1 =$db.size
       $dbspace1 = $db.DataSpaceUsage/1KB
       $dbindexsp1 = $db.IndexSpaceUsage/1KB
       $dbspaceavail1 = $db.SpaceAvailable/1KB
#######     Write-host "dbname=" $name1 $size1 $dbspace1 $dbindexsp1 $dbspaceavail1 $db.IsAccessible
      switch ($name1 ) {
       'master' {}
       'model'  {}
       'Northwind' {}
       'tempdb' {}
       Default {
Invoke-SQLcmd -ServerInstance "STATS_2008R2" -Database "is_dba" -Query "INSERT INTO is_sql_databases VALUES ('$server','$name1','$today',$dbspace1,$dbspaceavail1,$dbindexsp1) "
       ForEach ( $tbl in $db.tables)
       {
         $tname1     =$tbl.Name
         $tindexsp1  =$tbl.IndexSpaceUsed/1KB
         $trows1     =$tbl.RowCount
         $tspace1    =$tbl.DataSpaceUsed/1KB
#######         Write-host "tbl name=" $tname1 $tindexsp1 $trows $tspace1
   if ($trows1 -gt 999 ) {
Invoke-SQLcmd -ServerInstance "STATS_2008R2" -Database "is_dba" -Query "INSERT INTO is_sql_tables VALUES ('$server','$name1','$tname1','$today',$trows1,$tspace1,$tindexsp1) "
         } ####end RowCount > 0
       } #####end Row loop
       } ###### end Default 
       } #####end switch 
     }  #####end IsAccessible      
     }  #####end database loop
 }   ########end Status Offline check
} ###end server loop

Use SQL Server Management Studio to create a SQL Server Agent Job that simply has the script pasted in the script area and runs under type=PowerShell. The job needs to run under a dba account that has rights on each of the servers it connects to. Notice there are three loops going on. The server loop is on top and is driven by each entry in SQLSERVERLIST.TXT. The second loop is by each database on the server and omits most system databases (master, model, etc.). An 'Invoke-SQLcmd' populates database stats into the is_sql_databases table. The innermost loop is the table loop which puts table information into the is_sql_tables table. The Write-Host statements are commented out, but are there for debugging. Note the condition where it tests if the database is OFFLINE. Yeah that line took two hours of my life because I think its a Microsoft bug. When it says offline, it actually means its online. Without this check, the script will hang on OFFLINE databases. I should send Microsoft a bill for that misnomer. I'd schedule the job to run late. My run starts at 3:00am ish and takes over an hour to go thru over 100 servers and thousands of databases. I only save table data on tables with 500 or more rows which you may want to change. I put about 15,000 records a day into is_sql_tables. The server I run it on is a SQL 2008 R2 standard. It gets the data from SQL 2000, 2005, and 2008 servers.

The server I run it on is a SQL 2008 R2 standard. It gets the data from SQL 2000, 2005, and 2008 servers.

STEP 3: Queries and Reports

Some time after this job finishes (or the next step) I run several queries in a job and simply point the results to a textfile. These queries give daily and weekly increases (note the minimums in WHERE clause). You could use these queries in a fancier SQL Server Reporting Services or Crystal Reports, but at my age I like plain text. (Watching someone on an iPhone makes me cringe). Again, use Management Studio and create a job that runs a T-SQL script. On the Advanced Tab, send the output to a textfile. In a later step email the file to whoever wants to see it. When you really want to impress someone, take out the growth restraints (5MB or 500 Rows) from the queries and send that report. In my case that would be a 15,000 line report. And all in beautiful text. Here is the query to show which databases on your servers have grown 5MB or more since yesterday.

------Query to get DB growth for today, yesterday, and a week ago
select  LEFT(is_sqlserver,25) as 'SERVERNAME'
  ,LEFT(is_name,25) AS 'DATABASE'
  ,MAX( CASE DateDiff(Day,is_date_stamp, getdate() )
      WHEN 0 THEN is_date_stamp
      Else '02/02/1947'
      END ) as 'TODAY'
 ,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 0 THEN cast(is_size as decimal(12,1) )
      Else 0
      END ) ,12)  as 'SIZE TODAY'
 ,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 1 THEN cast(is_size as decimal(12,1) )
      Else 0
      END ) ,12) as 'SIZE YESTERDAY'
      
  ,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 0 THEN cast(is_size as decimal(12,1) )
      Else 0 END ) -
  MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 1 THEN cast(is_size as decimal(12,1) )
      Else 0
      END ) ,12) as 'DAILY INCREASE mb'
      
 ,LEFT( MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 7 THEN cast(is_size as decimal(12,1) )
      Else 0
      END ) ,12 ) as 'SIZE WEEK-AGO'  
      
     ,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 0 THEN cast(is_size as decimal(12,1) )
      Else 0 END ) -
      MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 7 THEN cast(is_size as decimal(12,1) )
      Else 0
      END ) ,12) as 'WEEKLY INCREASE mb'  
      
from is_sql_databases
where  DateDiff(Day, is_date_stamp, getdate() ) < 8    
group by is_sqlserver, is_name
having 
MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 0 THEN is_size
      Else 0
      END )  -   
 MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 1 THEN is_size
      Else 0
      END ) > 5
      
ORDER BY is_sqlserver, is_name      

Here is the output that goes to a good old text file. The SERVERNAME has been changed to protect the innocent.

Job 'SQL_Reports_RUN' : Step 6, 'Run DB Growth Report' : Began Executing 2014-02-12 04:25:48
SERVERNAME                DATABASE                  TODAY      SIZE TODAY   SIZE YESTERDAY DAILY INCREASE mb SIZE WEEK-AGO WEEKLY INCREASE mb
------------------------- ------------------------- ---------- ------------ -------------- ----------------- ------------- ------------------
xxxxxxxxx                 AcuityProd                2014-02-12 2217.1       2210.2         6.9               2179.4        37.7
apixx                     APIHealthcare_Live        2014-02-12 8933.6       8910.5         23.1              8917.1        16.5
astafxxxxxx               distribution              2014-02-12 471.9        438.8          33.1              413.1         58.8
astafxxxxxx               LaborWorkx_History_Live   2014-02-12 27456.1      26996.6        459.5             26996.6       459.5
astafxxxxxx               LaborWorkx_Live           2014-02-12 9392.4       9292.7         99.7              9022.1        370.3
astafxxxxxx               LaborWorkx_Storage_Live   2014-02-12 5895.2       5834.6         60.6              5834.6        60.6
capxxxx                   CAPROD                    2014-02-12 15190.7      15183.2        7.5               15121.6       69.1
CVCxxxxxxx                CISChartingDB8            2014-02-12 9541.9       9513.4         28.5              9322.3        219.6
CVCxxxxxxx                CISChartingDB9            2014-02-12 331.5        297.7          33.8              159.4         172.1
echoxxxxxx                mcf                       2014-02-12 9975.5       9923.2         52.3              9680.1        295.4
endpxxxxxx                BFEnterprise              2014-02-12 708.0        702.9          5.1               708.1         -0.1
fastrxxxxxxx              fstrk_img                 2014-02-12 10329.3      9988.1         341.2             9057.5        1271.8
fastrxxxxxxx              fstrkdb_1                 2014-02-12 32549.0      32529.5        19.5              31308.0       1241.0
fpxxxxxxxxxxx             EMR                       2014-02-12 48885.4      48807.5        77.9              48621.7       263.7
hbixxxxxxxx               WebData                   2014-02-12 62188.1      62145.7        42.4              61581.9       606.2
mmgxxxxxxxxx              EMR                       2014-02-12 340134.3     339594.0       540.3             337432.8      2701.5
mmgxxxxxxxxx              Ntier_57209               2014-02-12 23838.1      23367.6        470.5             23444.1       394.0
mmgxxxxxxxxx              TouchChart                2014-02-12 2960.3       2953.9         6.4               2927.2        33.1
momdbxxxxxxxx             OnePoint                  2014-02-12 8671.3       8617.1         54.2              8292.4        378.9
passxxxxxxxxxx            hl7_live                  2014-02-12 39953.6      39894.9        58.7              39699.4       254.2
passxxxxxxxxxx            hne_live                  2014-02-12 8866.9       8857.9         9.0               8819.2        47.7
paydatxxxxxxxxx           api_en_mhsi118_live       2014-02-12 37.8         31.1           6.7               25.3          12.5
paydatxxxxxxxxx           api_pm_mhsi118_live       2014-02-12 6046.9       6013.7         33.2              6021.3        25.6
pcoxxxxxxxxxxxx           payercm                   2014-02-12 8099.7       8091.8         7.9               8050.0        49.7
sharxxxxxxxxxxxx          SharedServices1_Search_DB 2014-02-12 404.9        381.5          23.4              357.7         47.2
sharxxxxxxxxxxxx          WSS_Content               2014-02-12 22952.7      22936.7        16.0              22834.9       117.8
smsxxxxxxxx               sms_mhs                   2014-02-12 2074.3       2065.6         8.7               2079.7        -5.4
vmsxxxxxxxxxx             VMwareVirtualCenter       2014-02-12 1092.4       1083.3         9.1               1125.2        -32.8
VMSQLxxxxxx               VMwareVirtualCenterDR     2014-02-12 6502.4       6495.3         7.1               6453.8        48.6

Here is a query to list tables that have grown 500 rows since yesterday. I created another job in management studio and on the Advanced tab sent the output to a text file.

-------------Query that shows tables that have grown by 500 rows since yesterday
select  LEFT(is_sqlserver,25) as 'SERVERNAME'
, LEFT(is_database_name,25) AS 'DATABASE'
, LEFT(is_name, 20) as 'TABLE'
,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 0 THEN cast(is_rows as decimal(10,0) )
      Else 0
      END ) ,10)  as '#ROWS TODAY'
,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 1 THEN cast(is_rows as decimal(10,0) )
      Else 0
      END ) ,10) as '#ROWS YESTER'
      
,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 0 THEN cast(is_rows as decimal(10,0) )
      Else 0 END ) -
  MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 1 THEN cast(is_rows as decimal(10,0) )
      Else 0
      END ) ,10) as 'DAY INCR'
  
,LEFT(
   CAST(100*((MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 0 THEN cast(is_rows as decimal(10,0) )
      Else 0 END ) -
  MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 1 THEN cast(is_rows as decimal(10,0) )
      Else 0
      END ))/
     MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 1 THEN cast(is_rows as decimal(10,0) )
      Else 0
      END ) )  
        AS decimal(6,2) )
       ,7) as 'DAY INCR %'    
      
 ,LEFT( MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 7 THEN cast(is_rows as decimal(10,0) )
      Else 0
      END ) ,10 ) as '#ROWS WEEK'  
      
     ,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 0 THEN cast(is_rows as decimal(10,0) )
      Else 0 END ) -
      MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 7 THEN cast(is_rows as decimal(10,0) )
      Else 0
      END ) ,10) as 'WEEK INCR'  
      
from is_sql_tables 
where  DateDiff(Day, is_date_stamp, getdate() ) < 8   
group by is_sqlserver, is_database_name , is_name
having 
MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 0 THEN is_rows 
      Else 0
      END )  -   
 MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 1 THEN is_rows 
      Else 0
      END ) > 500
      
 AND  MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
      WHEN 1 THEN cast(is_rows as decimal(10,0) )
      Else 0
      END ) > 0 
      
ORDER BY 
is_sqlserver 
,is_database_name      
,is_name
 

Here is a sampling of the output that shows what tables have grown by 500 rows since yesterday.

Job 'SQL_Reports_RUN' : Step 7, 'Run Table Growth Report - 500 row increase' : Began Executing 2014-02-11 04:25:21
SERVERNAME                DATABASE                  TABLE                #ROWS TODAY #ROWS YESTER DAY INCR   DAY INCR % #ROWS WEEK WEEK INCR 
------------------------- ------------------------- -------------------- ----------- ------------ ---------- ---------- ---------- ----------
3mdbyyyyyyyy              MMM360DB                  InboundInterfaceLogs 5293        4745         548        11.55      3932       1361
accesyyyyyyyyy            AcsLog                    EvnLog               12374743    12363390     11353      0.09       12306465   68278
acuiyyyyyyyy              AcuityProd                hipaa_audit_history  397972      396662       1310       0.33       391981     5991
acuiyyyyyyyy              AcuityProd                person_tree_queue_in 106819      106312       507        0.48       105028     1791
apiyyyyyyyyyyy            APIHealthcare_Live        EmployeeSummary      2946        2256         690        30.59      3668       -722
apiyyyyyyyyyyy            APIHealthcare_Live        PatientCensusFactorA 196924      196392       532        0.27       196924     0
apiyyyyyyyyyyy            APIHealthcare_Live        StaffingPlanAuditDet 174249      173728       521        0.30       173450     799
astafyyyyyyyyyy           distribution              MSrepl_commands      395076      391284       3792       0.97       383635     11441
astafyyyyyyyyyy           LaborWorkx_Live           AuthDataAccessAuditD 82524       81771        753        0.92       84915      -2391
astafyyyyyyyyyy           LaborWorkx_Live           AuthenticationHistor 700679      696550       4129       0.59       697533     3146
astafyyyyyyyyyy           LaborWorkx_Live           BenefitBalanceHistor 1616082     1613438      2644       0.16       1600448    15634
astafyyyyyyyyyy           LaborWorkx_Live           EmployeeAdjustment   220196      219351       845        0.39       216057     4139
astafyyyyyyyyyy           LaborWorkx_Live           EmployeeAuditDetail  226536      225961       575        0.25       227661     -1125
astafyyyyyyyyyy           LaborWorkx_Live           EmployeeCalendar     2926015     2922160      3855       0.13       2908168    17847

STEP 4: Getting the Report

If you want the reports emailed, create another job or another step and run this dbmail script. It's just another T-SQL script, and it assumes you've turned on dbmail. It grabs a text file as an attachment and sends it out. You could also just make a shortcut to the report on your desktop and double click on it each day you want to view it. Since its extension is a txt file, NOTEPAD should open and display the report. Here's the dbmail script for printing one of the reports I called'Table_Growth.txt':

EXEC sp_send_dbmail @profile_name='Edward P. Dowds',
@recipients='[email protected];[email protected]',
@subject='DB Growth Report',
@body='Attached shows SQL Databases that have grown since yesterday',
@file_attachments= 'g:\sql_scripts\DB_Stats\DB_Growth.txt;g:\sql_scripts\DB_Stats\Table_Growth.txt'
Next Steps


Last Updated: 2014-03-26


get scripts

next tip button



About the author
MSSQLTips author Bernard Black Bernard Black is a DBA for Beacon Health System in South Bend, IN and has been working with databases for over 15 years.

View all my tips
Related Resources





Comments For This Article




Friday, September 05, 2014 - 8:08:08 AM - skhu Back To Top (34396)

guys i think i been stuck for hrs here... am super new to powershell.. on the server list..i have 5 servers with at least 5 instances in each..and i did put my insances name on sqlserverlist. do i have to change anything on the code because i see on the powershell script that it says  Invoke-SQLcmd -ServerInstance "STATS_2008R2" -Database "is_dba" -Query "INSERT INTO ..... becuase when i ru it it had problems with the assmblies which i did load, but when i run the reports scripts and also whn i check the tables, its blank..WHERE AM I GOING WRONG HERE>>>>.....Thanks a lot


Friday, April 25, 2014 - 11:43:29 AM - Bernie Black Back To Top (30519)

Jim,

Yeah I saw how to do this somewhere after I did the stats article.  Here is some powershell that creates a table and fills it with a query from your server table.  Then you can loop thru it.

----------------------------------

$SQLServer = "servername where is_sql_servers table is..."   ########use Server\Instance for named SQL instances!
$SQLDBName = "is_dba"

$SqlQuery = "select is_sqlserver from is_sql_servers  "
$SqlQuery = $SqlQuery + " WHERE CHARINDEX ( 'test' , is_sqlserver ) = 0 " #####wont grab servers with 'TEST' in them.

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet, "Server_Names") | Out-Null

$SqlConnection.Close()

#Counter variable for rows
$intRow = 1

#Read thru the contents of the SQL_Servers.txt file
####foreach ($instance in get-content "c:\junk\SQL_Servers.txt")
foreach ($row in $DataSet.Tables["Server_Names"].rows)
{
 ###    $instance = $row[1].ToString().Trim()$row
      $instance = $row.is_sqlserver
Write-Host $instance
..................................do your stuff here....

}


Wednesday, April 23, 2014 - 6:02:12 PM - Jim Back To Top (30488)
 
If you wanted to substitute $srvlist value text file for a database table instead can that be done?
 
thanks,
 
Jim

Wednesday, April 16, 2014 - 12:05:03 PM - Bernie Black Back To Top (30081)

Isn't that interesting !  The "Offline" check sounds like it works OK on you system but not on mine.  (below)

if ($srv.Status -EQ "Offline" )

This is probably a good illustration of why you keep up with service paks.  I bet my system is behind yours.

Regards,

Bernie b.

 


Tuesday, April 15, 2014 - 3:46:33 AM - Azim Back To Top (30068)

 Hi Bernie

Thank you very much for the prompt reply.

Finally I got the script in running mode. Infect it was due to the check you mentioned for your 2 days hard work caused me more than 2 days to diagnose. On my SQL Server 2012 instance the script succeeded after removing that offline check (do not what was happing behind the scene).

Anyhow the script is an excellent addition to by DBA library and I am planning to implement it in my production for my DB size capacity planning.

Regards,

Azim

 


Wednesday, April 09, 2014 - 2:21:13 PM - Bernie Black Back To Top (30023)

Azim,

Well first I'd check if there are records in the tables or Database tables.  If those are getting updated properly you can then run the queries and see if they're pulling data.

If no rows are being added to your tables, I would uncomment (the ### lines) some of the WRITE statements and run the powershell script interactively.  Using SQL Management Studio, if you right click a database there should be a "Start Powershell" choice.  Choose this and paste your script at the powershell line.  You may have to hit RETURN a few times but it should run.

I would start by only using 2 or 3 servers in your textfile.  Uncomment the WRITEs and see what happens.

Also, IDERA has a free powershell GUI called PowerShellPlus you can download and use.

Good Luck, its probably something simple.  Bernie B.


Wednesday, April 09, 2014 - 5:58:11 AM - Azim Back To Top (30016)

Hi Bernie

Thanks for the excellent post.

While I am trying to implement it on my Test instance, I am unable to get anything although the job is successfully running.

I am new to PS

Regards

 Azim 

 


Thursday, April 03, 2014 - 8:56:29 AM - Bernie Black Back To Top (29959)

.Ranger,

Yeah, if the query returns no rows then there were no growth of tables GREATER than 500 rows.  If you want to see any growth make it greater than one.  (Note the "> 500" in the query).  Note also that I only keep track of tables with 1000 rows or greater in the Powershell code.  You may want to change that....if the tables you want to see grow are less than 1000.

Rajendra,

For a quarter, note the CASE statements that are like "CASE DateDiff(Day, is_date_stamp, getdate() ".   You'll probably have to change things around a bit.  How about

DECLARE @DATE1 DATE

SET @DATE1 = '12/31/2013'

DECLARE @DATE2 DATE

SET @DATE2 = '03/31/2014'

,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, @DATE1 )
      WHEN 0 THEN cast(is_rows as decimal(10,0) )
      Else 0
      END ) ,10)  as '#ROWS STARTING QTR'
,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, @DATE2 )
      WHEN 0 THEN cast(is_rows as decimal(10,0) )
      Else 0
      END ) ,10) as '#ROWS END QTR'

 same stuff thru out.

 


Wednesday, April 02, 2014 - 12:34:14 PM - rajendra Back To Top (29946)

But it will show the growth on daily basis what if on a particular day i want to check  DB growth for like last quater.


Wednesday, April 02, 2014 - 4:32:20 AM - Ranger Back To Top (29938)

Bernie

Hi , great scripts. Could you confirm something for me. The query - Query to get DB growth for today, yesterday, and a week ago

If I see no output for databases that I have in my text file , does that mean that there has been no growth activity ?

I.e. no output = no growth ?

Cheers

Ranger


Friday, March 28, 2014 - 7:53:17 AM - Mbourgon Back To Top (29909)

*grin* glad you liked. I do need to tweak it some - the default field lengths suck, but I'm quite happy with it. Always glad to have another reader! :)


Thursday, March 27, 2014 - 12:30:44 PM - Bernie Black Back To Top (29904)

Whoa Mbourgon !  Your stuff compared to what I threw together is like comparing a doctoral disertation to a high school essay. I like your blog and will be a regular.  Thanks for the heads up.


Wednesday, March 26, 2014 - 1:35:28 PM - Mbourgon Back To Top (29891)

Bernard, I wrote something similar, but made more scalable via the use of parallelization via managed runspaces. It also adds the server name and when it was collected, automatically creates the table based off the script name, adds Powershell capabilities and more. Please give it a shot!  Let me know if you have any questions.

 

http://thebakingdba.blogspot.com/2013/04/servers-extensible-powershell.html


Wednesday, March 26, 2014 - 11:35:29 AM - Bernie Black Back To Top (29889)

Lynn,

It also just hit me.  Make sure you have a EOL or carriage return after the final record.  Might hang on that.......

bernie black


Wednesday, March 26, 2014 - 11:20:18 AM - Bernie Black Back To Top (29888)

Lynn,

I double checked my system and the last server was there. (It was a small one and I wasn't sure for a minute).  My guess is that security is keeping your job out.  Some things you can do:

1) on the server your running it on, right click on a database and run powershell.  Paste the powershell code in and see what happens. You can un-comment the WRITE statements in the powershell for debugging.

2) Start a profile on the server you can't connect to. (Under tools in Management studio).  Run the script and see if there is any connection or attempts to connect. 

3) Actually, before #2, check the error log on the server you can't connect to and see if any failed logins.  Make sure failed logins are being logged if you don't see any.

Good Luck.

 


Wednesday, March 26, 2014 - 10:02:12 AM - Lynn Nguyen Back To Top (29884)

Thank you for the article.  I set up it and it worked as expected.  But I entered two servers to collect the data it only did one. Jut want to know the format of the server in the SQLSERVERLIST.TXT.  I typed the server name like:

servername1

servername2

and it only returned data for servername1

 

Thanks.



download





Recommended Reading

How to Read Log File in SQL Server using TSQL

How to setup SQL Server alerts and email operator notifications

SQL Server Wait Stats Monitoring with PowerShell

Posting SQL Server Notifications to Slack

SQL Server High CPU Query Use Monitoring with PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools