Build an Automated SQL Server Database Inventory System

By:   |   Updated: 2018-07-06   |   Comments (5)   |   Related: More > Database Administration


Problem

I have 140+ SQL Server instances to manage, I want to set up an inventory system for all the 2000+ databases in my environment, so I can log the various information of each database, such as the data / log file size, last full backup date, number of users, number of user tables / views in the database, etc.

So how can I build an efficient database inventory system?

Solution

A well-crafted database inventory system is very useful to DBAs in various ways, for example, a long-term inventory system may assist accurate capacity planning, it can also report any abnormal behaviors inside databases.

We first decide what database info we want to collect, then we will consider how to collect these data.

A database has many different properties and in theory, each property can be used as an inventory collection point.

We can find a database property list from SMO database object. For different SQL Server version, there may be different properties. Usually a later version has more properties than early version. For example, in SQL Server 2017, there are 20 new properties compared to SQL Server 2012. This can be demonstrated via the following code:

# Compare the database property difference between sql server 2017 and sql server 2012 
import-module sqlserver -DisableNameChecking;

#localhost\sql2017 is sql server 2017 instance
$svr17 = new-object microsoft.sqlserver.management.smo.server "localhost\sql2017";
$db17 = $svr.databases['mssqltips'];

#localhost is sql server 2012
$svr12 = new-object microsoft.sqlserver.management.smo.server "localhost";
$db12 = $svr2012.databases['mssqltips'];

$diff = $db17.Properties.name | where {$db12.Properties.name -notcontains $_};
$diff.count
$diff

We get the following result:

sql server attributes

We can see there are 20 new properties in SQL Server 2017 database object. It means, in a mixed environment, we may need to pick different sets of collection points for different versions of SQL Server instances.

But in real life, we usually do not need to get every database property for our inventory purpose, I prefer to choose a set of properties that are common in all SQL Server versions above SQL Server 2005. So, I choose the following set as shown in a table structure, about 25 properties in total. Each column name is self-evident in meaning, so I will not explain any further.

USE MSSQLTips

if object_id('dbo.DBInventory', 'U') is not null
   drop table dbo.DBInventory;

create table dbo.DBInventory(
  ServerName varchar(128) default @@servername
, DBName varchar(128) default db_name()
, DBOwner varchar(128)
, CreateDate datetime2
, RecoveryModel varchar(12) 
, Status varchar(60) -- online, recovering, offline etc..
, CompatibilityLevel int
, DataFileSizeMB int
, LogFileSizeMB int
, DataUsageMB int
, IndexUsageMB int
, SizeMB decimal(17,2)
, Collation varchar(60)
, UserCount int
, RoleCount int
, TableCount int
, SPCount int
, UDFCount int
, ViewCount int
, DMLTriggerCount int
, IsCaseSensitive bit
, IsTrustWorthy bit
, LastFullBackupDate datetime2
, LastDiffBackupDate datetime2
, LastLogBackupDate datetime2
);

To create a database inventory using PowerShell is actually not that difficult. Let’s first look at a workable solution for getting all database info on one SQL instance. In the following code, I am using my default SQL instance on my computer. The collected database inventory information will be exported to a csv file c:\temp\dbinventory.csv. 

import-modulesqlserver-DisableNameChecking;
$svr_name='localhost';
$csv_file='c:\temp\dbinventory.csv'

$svr=New-Objectmicrosoft.sqlserver.management.smo.server$svr_name;

$svr.Databases|wherename-ne'tempdb'|% {$_|select-property  @{l='Server'; e={$_.parent.name}} `
,Name `
,Owner `
,CreateDate `
,RecoveryModel `
,Status `
,CompatibilityLevel `
, @{l='DataFileSizeMB'; e= {$size=0;$_.FileGroups.foreach( {$_.files.foreach({$size+=$_.size})});$size/1024}} `
, @{l='LogFileSizeMB'; e= {$size=0; $_.Logfiles.foreach({$size+=$_.size});$size/1024}} `
, @{l='DataUsageMB'; e= {$_.DataSpaceUsage/1024} } `
, @{l='IndexUsageMB'; e= {$_.IndexSpaceUsage/1024} } `
,Size `
,Collation  `
, @{l='UserCount'; e={$_.users.count}} `
, @{l='RoleCount'; e={$_.Roles.count}}  `
, @{l='TableCount'; e={$_.Tables.count}}  `
, @{l='SPCount'; e={$_.StoredProcedures.count}} `
, @{l='UDFCount'; e={$_.UserDefinedFunctions.count}}  `
, @{l='ViewCount'; e={$_.Views.count}}  `
, @{l='DMLTriggerCount'; e={$_.Triggers.count}}  `
,CaseSensitive `
,TrustWorthy `
,LastBackupDate `
,LastDifferentialBackupDate `
,LastLogBackupDate;
}|Export-Csv-Path$csv_file-force-NoTypeInformation;

My databases in the default SQL instance are like the following:

ssms database list

After running the script in a PowerShell ISE window, we can open the $csv_file with Excel and see it like this:

sql server inventory data

If we want to upload the data into a SQL Server table instead of exporting to a CSV file, we can do so easily by using Write-SQLTableData, which is a cmdlet inside PowerShell SQLServer module.

This code is really simple in logic, but if we want to inventory databases across 100+ SQL Server instances and assume some instances may have 100+ databases (like those SharePoint databases), the code performance is far from satisfactory, it can take 3+ minutes when a SQL instance has more than 200 databases.

In my environment, for about 140+ instances with 2000+ databases, it took about 1 hr 37 min to do the database inventory collection using the code above.

To quicken the process, we need to rely on T-SQL to do the collection. The majority of the collection points can be fetched directly from sys.databases. The rest can be done via other queries and then get linked with the collected data. The detailed algorithm is as follows:

  1. Create a temp table (#T) with the same structure as the database inventory table
  2. Populate #T with sys.databases for those available properties, such as name, owner, CreateDate, Status etc.
  3. Use sp_msforeachdb to dynamically update #T for those missing columns (i.e. not available via sys.databases) per each database.

The whole T-SQL code is as follows:

if object_id('tempdb..#t', 'U') is not null
drop table #t;
create table #t(
  ServerName varchar(128) default @@servername
, DBName varchar(128) default db_name()
, DBOwner varchar(128)
, CreateDate datetime2
, RecoveryModel varchar(12) 
, StateDesc varchar(60)
, CompatibilityLevel int
, DataFileSizeMB int
, LogFileSizeMB int
, DataUsageMB int
, IndexUsageMB int
, SizeMB decimal(17,2)
, Collation varchar(60)
, UserCount int
, RoleCount int
, TableCount int
, SPCount int
, UDFCount int
, ViewCount int
, DMLTriggerCount int
, IsCaseSensitive bit
, IsTrustWorthy bit
, LastFullBackupDate datetime2
, LastDiffBackupDate datetime2
, LastLogBackupDate datetime2);

insert into #t(DBName, DBOwner, CreateDate, RecoveryModel, StateDesc, CompatibilityLevel, IsCaseSensitive
, IsTrustWorthy, Collation, LastFullBackupDate, LastDiffBackupDate, LastLogBackupDate)
select name, suser_sname(owner_sid), create_date, recovery_model_desc, state_desc,compatibility_level
, IsCaseSensitive=CAST(CHARINDEX(N'_CS_', collation_name) AS bit), is_trustworthy_on, Collation_Name
, t.LastFullBackup, t.LastDiffBackup, t.LastLogBackup
from master.sys.databases db
outer apply( SELECT
MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFullBackup,
MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS LastDiffBackup,
MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS LastLogBackup
FROM msdb.dbo.backupset b 
where b.database_name = db.name
) t;

EXEC master.dbo.sp_msforeachdb'use [?]
update t set SizeMB=(select sum(size)/128. from dbo.sysfiles)
, DataUsageMB=x.DataUsageMB, IndexUsageMB=x.IndexUsageMB
   , DataFileSizeMB = u.DBSize, LogFileSizeMB = u.LogSize 
   , TableCount=y.TC, UDFCount=y.UC, SPCount = y.SC, ViewCount=y.VC
   , DMLTriggerCount=y.DC
   , UserCount = z.UC, RoleCount = z.RC
from #t t
   outer apply (
   SELECT SUM(case when df.type in (0,2,4) then df.size else 0 end)/128
   , SUM(case when df.type in (1,3) then df.size else 0 end)/128 
   FROM sys.database_files df 
   ) u(DBSize, LogSize)
   outer apply(select  DataUsageMB=sum(
    CASE
    When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
    When a.type <> 1 and p.index_id < 2 Then a.used_pages
    When p.index_id < 2 Then a.data_pages
    Else 0
    END)/128,
IndexUsageMB=(sum(a.used_pages)-sum(
    CASE
    When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
    When a.type <> 1 and p.index_id < 2 Then a.used_pages
    When p.index_id < 2 Then a.data_pages
    Else 0
    END
    ))/128
    from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
    left join sys.internal_tables it on p.object_id = it.object_id
   ) x
   outer apply 
   ( select SC=Sum(case Type when ''P'' then 1 else 0 end)
    , DC=Sum(case Type when ''TR'' then 1 else 0 end)
    , TC=Sum(case Type when ''U'' then 1 end)
    , UC= sum(case when Type in (''TF'', ''IF'', ''FN'') then 1 else 0 end)
    , VC=Sum(case Type when ''V'' then 1 else 0 end)
    from sys.objects where object_id > 1024
    and type in (''U'',''P'',''TR'',''V'',''TF'',''IF'',''FN'')
   ) y
   outer apply 
   ( select UC = sum(case when [Type] in (''G'',''S'',''U'') then 1 else 0 end)
      , RC = sum(case when Type = ''R'' then 1 else 0 end)
      from sys.database_principals
      where principal_id > 4
   ) z where t.DBName=db_name();
'
SELECT * FROM #t

With the T-SQL script as the core, we will create a PowerShell function, Get-SQLDBInventory, this function takes one parameter, -ServerInstance, which may contain names of one or more SQL Server instances, and the function will return the inventory data of all databases on this or these instances.

# Assume you have SQLServer PowerShell module installed
# on the server where you execute this script

Import-Module sqlserver -DisableNameChecking;

function Get-SQLDBInventory 
{
    [cmdletbinding()]
    Param(  [Parameter(Mandatory=$false, ValueFromPipeline=$true)]
            [Alias("SQLServer","Instance")]
            [string[]]$ServerInstance = $env:computername
         )

    [string]$qry = @"
set nocount on;
if object_id('tempdb..#t', 'U') is not null
 drop table #t;
create table #t (
  ServerName varchar(128) default @@servername
, DBName varchar(128) default db_name()
, DBOwner varchar(128)
, CreateDate datetime2
, RecoveryModel varchar(12)
, StateDesc varchar(60)
, CompatibilityLevel int
, DataFileSizeMB int
, LogFileSizeMB int
, DataUsageMB int
, IndexUsageMB int
, SizeMB decimal(17,2)
, Collation varchar(60)
, UserCount int
, RoleCount int
, TableCount int
, SPCount int
, UDFCount int
, ViewCount int
, DMLTriggerCount int
, IsCaseSensitive bit
, IsTrustWorthy bit
, LastFullBackupDate datetime2
, LastDiffBackupDate datetime2
, LastLogBackupDate datetime2);

   insert into #t (DBName, DBOwner, CreateDate, RecoveryModel, StateDesc, CompatibilityLevel, IsCaseSensitive
   , IsTrustWorthy, Collation, LastFullBackupDate, LastDiffBackupDate, LastLogBackupDate)
   select name, suser_sname(owner_sid), create_date, recovery_model_desc, state_desc,compatibility_level
   , IsCaseSensitive=CAST(CHARINDEX(N'_CS_', collation_name) AS bit), is_trustworthy_on, Collation_Name
   , t.LastFullBackup, t.LastDiffBackup, t.LastLogBackup
   from master.sys.databases db
   outer apply ( SELECT
    MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFullBackup,
    MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS LastDiffBackup,
    MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS LastLogBackup
    FROM msdb.dbo.backupset b
    where b.database_name = db.name
    ) t;

EXEC master.dbo.sp_msforeachdb 'use [?]
update t set SizeMB=(select sum(size)/128. from dbo.sysfiles)
, DataUsageMB=x.DataUsageMB, IndexUsageMB=x.IndexUsageMB
   , DataFileSizeMB = u.DBSize, LogFileSizeMB = u.LogSize
   , TableCount=y.TC, UDFCount=y.UC, SPCount = y.SC, ViewCount=y.VC
   , DMLTriggerCount=y.DC
   , UserCount = z.UC, RoleCount = z.RC
from #t t
   outer apply (
   SELECT SUM(case when df.type in (0,2,4) then df.size else 0 end)/128
   , SUM(case when df.type in (1,3) then df.size else 0 end)/128
   FROM sys.database_files df
   ) u(DBSize, LogSize)
   outer apply(select  DataUsageMB=sum(
    CASE
    When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
    When a.type <> 1 and p.index_id < 2 Then a.used_pages
    When p.index_id < 2 Then a.data_pages
    Else 0
    END)/128,
IndexUsageMB=(sum(a.used_pages)-sum(
    CASE
    When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
    When a.type <> 1 and p.index_id < 2 Then a.used_pages
    When p.index_id < 2 Then a.data_pages
    Else 0
    END
    ))/128
    from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
    left join sys.internal_tables it on p.object_id = it.object_id
   ) x
   outer apply
   ( select SC=Sum(case Type when ''P'' then 1 else 0 end)
    , DC=Sum(case Type when ''TR'' then 1 else 0 end)
    , TC=Sum(case Type when ''U'' then 1 end)
    , UC= sum(case when Type in (''TF'', ''IF'', ''FN'') then 1 else 0 end)
    , VC=Sum(case Type when ''V'' then 1 else 0 end)
    from sys.objects where object_id > 1024
    and type in (''U'',''P'',''TR'',''V'',''TF'',''IF'',''FN'')
   ) y
   outer apply
   ( select UC = sum(case when [Type] in (''G'',''S'',''U'') then 1 else 0 end)
      , RC = sum(case when Type = ''R'' then 1 else 0 end)
      from sys.database_principals
      where principal_id > 4
   ) z where t.DBName=db_name();
'
SELECT * FROM #T
"@
    $dt2 = new-object System.Data.DataTable;
    $dt2.columns.add((new-object System.Data.DataColumn('ServerName'         , [System.String])));
    $dt2.columns.add((new-object System.Data.DataColumn('DBName'             , [System.String])));  
    $dt2.columns.add((new-object System.Data.DataColumn('DBOwner'            , [System.String])));  
    $dt2.columns.add((new-object System.Data.DataColumn('CreateDate'         , [System.DateTime])));  
    $dt2.columns.add((new-object System.Data.DataColumn('RecoveryModel'      , [System.String])));  
    $dt2.columns.add((new-object System.Data.DataColumn('StateDesc'          , [System.String])));  
    $dt2.columns.add((new-object System.Data.DataColumn('CompatibilityLevel' , [System.Int32])));  
    $dt2.columns.add((new-object System.Data.DataColumn('DataFileSizeMB'     , [System.Int32])));  
    $dt2.columns.add((new-object System.Data.DataColumn('LogFileSizeMB'      , [System.Int32])));  
    $dt2.columns.add((new-object System.Data.DataColumn('DataUsageMB'        , [System.Int32])));  
    $dt2.columns.add((new-object System.Data.DataColumn('IndexUsageMB'       , [System.Int32])));  
    $dt2.columns.add((new-object System.Data.DataColumn('SizeMB'             , [System.Decimal])));  
    $dt2.columns.add((new-object System.Data.DataColumn('Collation'          , [System.String])));  
    $dt2.columns.add((new-object System.Data.DataColumn('UserCount'          , [System.Int32])));  
    $dt2.columns.add((new-object System.Data.DataColumn('RoleCount'          , [System.Int32])));  
    $dt2.columns.add((new-object System.Data.DataColumn('TableCount'         , [System.Int32])));  
    $dt2.columns.add((new-object System.Data.DataColumn('SPCount'            , [System.Int32])));  
    $dt2.columns.add((new-object System.Data.DataColumn('UDFCount'           , [System.Int32])));  
    $dt2.columns.add((new-object System.Data.DataColumn('ViewCount'          , [System.Int32])));  
    $dt2.columns.add((new-object System.Data.DataColumn('DMLTriggerCount'    , [System.Int32])));  
    $dt2.columns.add((new-object System.Data.DataColumn('IsCaseSensitive'    , [System.Boolean]))); 
    $dt2.columns.add((new-object System.Data.DataColumn('IsTrustWorthy'      , [System.Boolean]))); 
    $dt2.columns.add((new-object System.Data.DataColumn('LastFullBackupDate', [System.DateTime])));
    $dt2.columns.add((new-object System.Data.DataColumn('LastDiffBackupDate', [System.DateTime])));
    $dt2.columns.add((new-object System.Data.DataColumn('LastLogBackupDate' , [System.DateTime])));

    foreach ($svr in $ServerInstance)
    { Write-verbose "processing:$svr" 
      try {
            $dt=invoke-sqlcmd -ServerInstance $svr -Database master -Query $qry `
        -QueryTimeout 120 -OutputAs DataTables;
            $dt2.merge($dt); #append result to $dt2
      }
      catch
      { 
        $r = $dt2.NewRow()
        $r.ServerName = $svr;
        $r.DBName = 'Server-Unaccessible';
        $dt2.Rows.add($r);    
        write-Error "Error Processing$svr" ; 
       }
    }#foreach $svr
    Write-Output $dt2;
}#Get-SQLDBInventory

Once the script is run and thus Get-SQLDBInventory, we can run the following:

Get-SQLDBinventory -serverinstance 'sql_instance_1', 'sql_instance_2', 'sql_instance_3'

And get the inventory info of all databases on sql_instance_1/2/3.

Now we will look at an example to dump the collected data into a central repository table.

First I assume, the central repository table (dbo.DBInventory) is created on my default SQL Server instance inside database [MSSQLTips].

On my computer, I have 4 SQL Server instances, they are default instance and three named instances, i.e. SQL2014, SQL2016, SQL2017. So, to inventory the databases on these four instances, I use the following two lines of codes:

$dt = get-sqldbinventory -ServerInstance 'localhost', '.\sql2014', '.\sql2016', '.\sql2017'

Write-SqlTableData -ServerInstance localhost -DatabaseName mssqltips `
-SchemaName dbo -TableName DBInventory -InputData $dt;

Summary

In this tip, we looked at how to quickly export database inventory data into a CSV file with very simple PowerShell script and we then further discussed an efficient way to inventory databases in SQL Server environment and deposit the data into a central repository table.

Once we have the solution, we can schedule the task to run regularly (i.e. daily or weekly), and after we get enough data, we may use the inventory table to do some interesting findings. For example, to check whether there is any database that are in full recovery mode, yet never have its log backup done, or how the database grows in the past 3 months, whether any database owner is of a particular account, etc.

Next Steps

A central database inventory table is very handy for DBA work, and it can boost both DBA’s work efficiency and work quality.

Using this tip, you can design your own inventory collection points, for example, you may want to add database replication state or database snapshot isolation state, etc. in your inventory data.

Here are a few similar SQL Server component inventories tips:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2018-07-06

Comments For This Article




Tuesday, March 19, 2019 - 3:22:45 PM - Parthiban Back To Top (79340)

Hi ,

Thank you for the wonderful script. But I am getting the following error.

get-sqldbinventory : Error Processinglocalhost

At C:\Powershell\SQLDBInventory.ps1:133 char:7

+ $dt = get-sqldbinventory -ServerInstance 'localhost'

+       ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException

    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Get-SQLDBInventory

Here is the code which I am trying to execute. 

$dt = get-sqldbinventory -ServerInstance 'localhost'

Write-SqlTableData -ServerInstance localhost -DatabaseName RCA -SchemaName dbo -TableName Test -InputData $dt; 

Tuesday, July 10, 2018 - 1:12:29 PM - jeff_yao Back To Top (76604)

 

 Hi David Waller,

For that problematic sql instance, what is the version number? 

To debug it, you may need to open an SSMS window and copy the sql statement in @qry, i.e. the following sql statement and run the sql against that problematic instance and see what error is (on which db). 

set nocount on;
if object_id('tempdb..#t', 'U') is not null
 drop table #t;
create table #t (
  ServerName varchar(128) default @@servername
, DBName varchar(128) default db_name()
, DBOwner varchar(128)
, CreateDate datetime2
, RecoveryModel varchar(12)
, StateDesc varchar(60)
, CompatibilityLevel int
, DataFileSizeMB int
, LogFileSizeMB int
, DataUsageMB int
, IndexUsageMB int
, SizeMB decimal(17,2)
, Collation varchar(60)
, UserCount int
, RoleCount int
, TableCount int
, SPCount int
, UDFCount int
, ViewCount int
, DMLTriggerCount int
, IsCaseSensitive bit
, IsTrustWorthy bit
, LastFullBackupDate datetime2
, LastDiffBackupDate datetime2
, LastLogBackupDate datetime2);

   insert into #t (DBName, DBOwner, CreateDate, RecoveryModel, StateDesc, CompatibilityLevel, IsCaseSensitive
   , IsTrustWorthy, Collation, LastFullBackupDate, LastDiffBackupDate, LastLogBackupDate)
   select name, suser_sname(owner_sid), create_date, recovery_model_desc, state_desc,compatibility_level
   , IsCaseSensitive=CAST(CHARINDEX(N'_CS_', collation_name) AS bit), is_trustworthy_on, Collation_Name
   , t.LastFullBackup, t.LastDiffBackup, t.LastLogBackup
   from master.sys.databases db
   outer apply ( SELECT
    MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFullBackup,
    MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS LastDiffBackup,
    MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS LastLogBackup
    FROM msdb.dbo.backupset b
    where b.database_name = db.name
    ) t;

EXEC master.dbo.sp_msforeachdb 'use [?]
update t set SizeMB=(select sum(size)/128. from dbo.sysfiles)
, DataUsageMB=x.DataUsageMB, IndexUsageMB=x.IndexUsageMB
   , DataFileSizeMB = u.DBSize, LogFileSizeMB = u.LogSize
   , TableCount=y.TC, UDFCount=y.UC, SPCount = y.SC, ViewCount=y.VC
   , DMLTriggerCount=y.DC
   , UserCount = z.UC, RoleCount = z.RC
from #t t
   outer apply (
   SELECT SUM(case when df.type in (0,2,4) then df.size else 0 end)/128
   , SUM(case when df.type in (1,3) then df.size else 0 end)/128
   FROM sys.database_files df
   ) u(DBSize, LogSize)
   outer apply(select  DataUsageMB=sum(
    CASE
    When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
    When a.type <> 1 and p.index_id < 2 Then a.used_pages
    When p.index_id < 2 Then a.data_pages
    Else 0
    END)/128,
IndexUsageMB=(sum(a.used_pages)-sum(
    CASE
    When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
    When a.type <> 1 and p.index_id < 2 Then a.used_pages
    When p.index_id < 2 Then a.data_pages
    Else 0
    END
    ))/128
    from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
    left join sys.internal_tables it on p.object_id = it.object_id
   ) x
   outer apply
   ( select SC=Sum(case Type when ''P'' then 1 else 0 end)
    , DC=Sum(case Type when ''TR'' then 1 else 0 end)
    , TC=Sum(case Type when ''U'' then 1 end)
    , UC= sum(case when Type in (''TF'', ''IF'', ''FN'') then 1 else 0 end)
    , VC=Sum(case Type when ''V'' then 1 else 0 end)
    from sys.objects where object_id > 1024
    and type in (''U'',''P'',''TR'',''V'',''TF'',''IF'',''FN'')
   ) y
   outer apply
   ( select UC = sum(case when [Type] in (''G'',''S'',''U'') then 1 else 0 end)
      , RC = sum(case when Type = ''R'' then 1 else 0 end)
      from sys.database_principals
      where principal_id > 4
   ) z where t.DBName=db_name();
'
SELECT * FROM #T

Tuesday, July 10, 2018 - 12:23:01 PM - David Waller Back To Top (76602)

 The change to the Timeout has helped. I still have 1 server now causing an error.

invoke-sqlcmd : Arithmetic overflow error converting expression to data type int.

The statement has been terminated.

Warning: Null value is eliminated by an aggregate or other SET operation.

Warning: Null value is eliminated by an aggregate or other SET operation.

At line:139 char:17

+ ...         $dt=invoke-sqlcmd -ServerInstance $svr -Database master -Quer ...

+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException

    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

 

get-sqldbinventory : Error Processingraider17\i1

At line:1 char:7

+ $dt = get-sqldbinventory -ServerInstance 'raider10\i1','raider10\i2', ...

+       ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException

    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Get-SQLDBInventory

 

Monday, July 9, 2018 - 3:51:08 PM - jeff_yao Back To Top (76588)

Hi @David Waller,

Thanks for reading/trying this tip. 

The error message you received means the query cannot complete, you may try the following to figure out why

1. Ensure that each each sql instance is available (you can simply connect to them one by one in SSMS and run a simple query to check everything is OK)

2. Change -QueryTimeOut value from 120 to 1200

3. Add the following line before line: $dt=invoke-sqlcmd -ServerInstance $svr -Database master -Query ....

write-host "Processing $svr" -ForegroundColor Green

This will print out each sql instance name so you can pinpoint the exact problematic sql instance.

Hope this helps you in trouble-shooting your problem.

Thanks,

jeff_yao

Monday, July 9, 2018 - 12:55:25 PM - David Waller Back To Top (76585)

 Great script and I am trying to set it up. Howerver I am getting an error and not sure what is causing it.

invoke-sqlcmd : Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

At line:138 char:17

+ ...         $dt=invoke-sqlcmd -ServerInstance $svr -Database master -Quer ...

+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException

    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

 

get-sqldbinventory : Error Processingraider12\i1

At line:1 char:7

+ $dt = get-sqldbinventory -ServerInstance 'raider10\i1', 'raider10\i2' ...

+       ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException

 

Code I am running:

$dt = get-sqldbinventory -ServerInstance 'raider10\i1', 'raider10\i2', 'raider10\i3', 'raider11\i1', 'raider11\i11', 'raider11\i2', 'raider12\i1', 'raider12\i2', 'raider12\i3', 'raider14\i1', 

'raider14\i2', 'raider14\i3', 'raider15\i1', 'raider15\i2', 'raider16\i1', 'raider17\i1', 'raidersql7\i3', 'raidersql7\i4', 'resadmindb'

 

 

Write-SqlTableData -ServerInstance DB2\STN2016 -DatabaseName DBA `

-SchemaName dbo -TableName DBInventory -InputData $dt; 

 














get free sql tips
agree to terms