2014 MVP (Most Valuable Professional) Award

October 1, 2014

It is an honor to receive the 2014 MVP award. There are a lot of people that make this community as good as it is, and I’m glad to be part of that.

This anniversary is also a special one for me, marking 20 consecutive years as an MVP, crossing three product disciplines. Thank you for reading and for your continued feedback.

Microsoft MVP Banner
Dear Steve Thompson,
Congratulations! We are pleased to present you with the 2014 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others. We appreciate your outstanding contributions in Enterprise Client Management technical communities during the past year.
The Microsoft MVP Award provides us the unique opportunity to celebrate and honor your significant contributions and say "Thank you for your technical leadership."

Mike Hickman
Community Engagement

Dev IT Connections In-Depth Site review session demo

September 19, 2014


In-Depth Configuration Manager site review – presented in Las Vegas on Sept 16, 2014. This was a shared session with Kent Agerlund.

It was a phenomenal time presenting with Kent. Here are the demo files and relevant links for the SQL Server portion.

<Update> includes Kent’s link regarding WSUS and the SUSDB:


Demo2 – SQL Scripts

-- Change to current CM database
-- So, how many collections are set to incremental?

-- AGGRAGATE, counts only (Note: >250 incremental updates may cause issues)
    CASE RefreshType
        WHEN 1 THEN 'No Scheduled Update'
        WHEN 2 THEN 'Full Scheduled Update'
        WHEN 4 THEN 'Incremental Update (only)'
        WHEN 6 THEN 'Incremental and Full Update Scheduled'
        ELSE 'Unknown'
    END AS RefreshType,
    COUNT(*) AS Total
FROM  dbo.v_Collection
GROUP BY RefreshType

-- Top Cached SPs By Execution Count (SQL 2008) (SQL 2008 R2 SP1 only)
-- Tells you which cached stored procedures are called the most often
-- This helps you characterize and baseline your workload

SELECT TOP(50) p.name AS [SP Name], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime],  
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);

-- Top Cached SPs By Avg Elapsed Time (SQL 2008)
-- This helps you find long-running cached stored procedures that
-- may be easy to optimize with standard query tuning techniques
SELECT TOP(25) p.name AS [SP Name], qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], 
qs.total_elapsed_time, qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, 
GETDATE()), 0) AS [Calls/Second], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 
qs.total_worker_time AS [TotalWorkerTime], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()


-- Table row counts
SELECT sc.name +'.'+ ta.name TableName
 ,SUM(pa.rows) RowCnt
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 INNER JOIN sys.schemas sc
 ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
 GROUP BY sc.name,ta.name
 -- Tables with Byte Length, useful for calculating space used
 SELECT CASE WHEN (GROUPING(sob.name)=1) THEN 'All_Tables'
    ELSE ISNULL(sob.name, 'unknown') END AS Table_name,
    SUM(sys.length) AS Byte_Length
 FROM sysobjects sob, syscolumns sys
 WHERE sob.xtype='u' AND sys.id=sob.id
 GROUP BY sob.name
 -- http://blog.sqlauthority.com/2010/09/08/sql-server-find-row-count-in-table-find-largest-table-in-database-part-2/


-- Calculate table name/space used 
-- Select query to end!
-- http://mlichtenberg.wordpress.com/2012/02/13/determine-the-space-used-by-each-table-in-a-sql-server-database/ 


DECLARE @table_name VARCHAR(500) 

DECLARE @SpaceUsed TABLE (     
    tablename sysname, 
    row_count INT, 
    reserved VARCHAR(50), 
    data VARCHAR(50), 
    index_size VARCHAR(50), 
    unused VARCHAR(50) 

SELECT s.name + '.' + t.name   
FROM sys.tables t  INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 

OPEN curTables 
FETCH NEXT FROM curTables INTO @table_name 
    SET @table_name = REPLACE(REPLACE(@table_name, '[',''), ']', '') 

    -- make sure the object exists before calling sp_spacedused 
        INSERT INTO @SpaceUsed EXEC sp_spaceused @table_name, false 
    FETCH NEXT FROM curTables INTO @table_name 
CLOSE curTables 

-- Compute the total size of each table and add the schemaname to the result set
-- Order by Table size (Total MB) 

SELECT  s.name as Schemaname, 
        CONVERT(decimal, CONVERT(bigint, REPLACE(su.data, ' KB', '')) + 
            CONVERT(bigint, REPLACE(su.index_size, ' KB', '')) + 
            CONVERT(bigint, REPLACE(su.unused, ' KB', ''))) / 1000 AS [total (MB)] 
FROM    @SpaceUsed su INNER JOIN sys.tables t 
            ON t.name = su.tablename 
        INNER JOIN sys.schemas s 
            ON t.schema_id = s.schema_id 
        [total (MB)] DESC, 

Powershell scripts – place both in C:\TEMP, view comments in the SQL Audit script



PS-CreateXLS from CSV files


Information on Optimizing SQL Server databases:

Optimizing CM dbs:


How to recover a ConfigMgr 2012 site using a restored db

August 1, 2014


This process makes the assumption that the ConfigMgr 2012 Sp1 (or later) SQL Server database was manually restored. There are two ways to restore a CM database; use the SQL Server Enterprise Management Studio restore database wizard or use the SQL RESTORE command.

Now that the database was restored, located your media for ConfigMgr 2012 Sp1 (or later) and launch setup.exe from the *.ISO


Click Next >



Note: if run on a ConfigMgr site server, only two options are available: Recover a site, or Uninstall the CM site. Choose Recover & Next >


Choose option, use a site database that has been manually restored

Click Next >


Site Database Recovery Options

When you run Setup, you have the following recovery options for the site database:

  • Recover the site database using a backup set: Use this option when you have a backup of the Configuration Manager site database that was created as part of the Backup Site Server maintenance task run on the site before the site database failure. When you have a hierarchy, the changes that were made to the site database after the last site database backup are retrieved from the central administration site for a primary site, or from a reference primary site for a central administration site. When you recover the site database for a stand-alone primary site, you lose site changes after the last backup.
    When you recover the site database for a site in a hierarchy, the recovery behavior is different for a central administration site and primary site, and when the last backup is inside or outside of the SQL Server change tracking retention period. For more information, see the Site Database Recovery Scenarios section in this topic.


The recovery fails if you select to restore the site database by using a backup set, but the site database already exists.

  • Create a new database for this site: Use this option when you do not have a backup of the Configuration Manager site database. When you have a hierarchy, a new site database is created, and the data is recovered by using replicated data from the central administration site for a primary site, or a reference primary site for a central administration site. This option is not available when you are recovering a stand-alone primary site or a central administration site that does not have primary sites.
  • Use a site database that has been manually recovered: Use this option when you have already recovered the Configuration Manager site database but have to complete the recovery process. Configuration Manager can recover the site database from the Configuration Manager backup maintenance task or from a site database backup that you perform by using DPM or another process. After you restore the site database by using a method outside Configuration Manager, you must run Setup and select this option to complete the site database recovery. When you have a hierarchy, the changes that were made to the site database after the last site database backup are retrieved from the central administration site for a primary site, or from a reference primary site for a central administration site. When you recover the site database for a stand-alone primary site, you lose site changes after the last backup.


When you use DPM to back up your site database, use the DPM procedures to restore the site database to a specified location before you continue the restore process in Configuration Manager. For more information about DPM, see the Data Protection Manager Documentation Library on TechNet.

  • Skip database recovery: Use this option when no data loss has occurred on the Configuration Manager site database server. This option is only valid when the site database is on a different computer than the site server that you are recovering.

Take Default option, Click Next >


Click Next >


Click Next >


Select the agreement options & Click Next >


Enter path for previously downloaded prereqs (you did download them, right?)

Click Next >


Click Next >


Click Next >


No change needed, but new with R2, Click Next >


Click Next >


Click Next >


Click Begin Install >


View log will show details…




SQL Server NTFS Cluster Size

July 25, 2014

If you are using Windows Server drives dedicated for SQL Server database files, did you know that the NTFS cluster size should be 64KB?

The reason that SQL Server prefers 64KB NTFS cluster size, this happens to correspond to the way SQL Server allocates storage. One page = 8K, and SQL Server allocates an Extent, which is 8 pages in size. 8 pages x 8KB = 64KB/extent.

Now that we have the math out of the way…

Normally, a default NTFS, formatted drive is 4KB. How to check the current drive cluster size?

Two ways, to check logical drives C & D, open a command prompt as an administrator, copy/paste and run:

fsutil fsinfo ntfsinfo c:
fsutil fsinfo ntfsinfo d:

Note the highlighted part below for the cluster size:


Another simple test, create a small text file on the disk, then open the file properties. The size on disk, will also indicate cluster size:


If you are having disk related performance issues, you may want to consider reformatting your drives to NTFS 64KB cluster size for better performance.

ConfigMgr 2012 RTM to R2 Upgrade checklist

July 9, 2014

There are a number of good articles on how to upgrade from ConfigMgr 2012 RTM to Sp1, and from Sp1 to R2. If anyone needs to upgrade from RTM to R2, here are the steps:

Note: that the upgrade was performed on a single site, SQL Server was local, and on Server 2008 R2.  OSD was not in use. WSUS used a SUSDB database on SQL Server.

    Review system status/heath check – the ConfigMgr 2012 site was given a clean bill of health, with the exception of native SQL backup missing the ConfigMgr database. This was corrected.

    Backup – reviewed and corrected backup strategy for ConfigMgr 2012. Starting with ConfigMgr 2012 Sp1 and later, only native SQL Server backups are needed. (For more information, see: ConfigMgr 2012 Site Backup and Recovery)

    SQL Server database optimizationImplement Ola Hallengrens’ script; index & statistic optimization plan, to run 1x/week on all user databases. The benefit of this optimization, SUSDB will process updates faster, and ConfigMgr database optimization will benefit console users and update processes.

    ConfigMgr 2012 SP1 Upgrade – using installer, download all ConfigMgr prerequisites locally.


    In SCCM 2012 the download pre-reqs command is:

    SetupDL.exe <target dir>

    The SetupDL.exe file is located in the SMSSETUP\BIN\X64 directory.

    Install ADK 8.1 – (ADK – required for Sp1 & R2 upgrade: select deployment tools, Win PE & USMT)

    Note: if the ADK was previously installed, an uninstall would be required here…

    Run SP1 installer upgrade – verify upgrade was successful by checking console version, and sitecomp.log.

    Backup SQL Server DB

    SQL Server upgrade – SQL Server was presently at SQL Server 2008 R2, decision was made upgrade to SQL Server 2012 Sp1, then apply SQL Server 2012 Sp1 Cu9

    ConfigMgr 2012 R2 Upgrade – using installer, download all ConfigMgr prerequisites

    Run R2 upgrade – verify upgrade was successful by checking console version, and sitecomp.log.

    Run R2 CU1 – verify upgrade was successful by checking console version, and sitecomp.log.

    Client Update – ConfigMgr 2012 RTM to R2

    Enable Automatic Site Hierarchy client upgrade – this upgraded the clients directly from RTM to R2.

    ConfigMgr R2 Cu1 Update – verified update.

    Create client deployment to upgrade clients from ConfigMgr R2 to R2 Cu1 – x64 & x86.

Post deployment, Software Updates refused to synchronize.

The WCM.log file will indicate:

Checking for supported version of WSUS (min WSUS 3.0 SP2 + KB2720211 + KB2734608)

Supported WSUS version not found

Resolution: These WSUS updates needed to be deployed post ConfigMgr 2012 SP1 to correct the supported WSUS version failure.

The updates are:

· KB2734608: http://support.microsoft.com/kb/2734608

· KB2720211: http://support.microsoft.com/kb/2720211

How to Uninstall Lync 2010 using ConfigMgr 2012

July 8, 2014


Once you’ve deployed Office 2013, with integrated Lync 2013; you may have noticed that Lync 2010 is still installed.

And, that Lync 2010 does not always uninstall cleanly.

Here’s how to remove Lync 2010.

Create a task sequence.

Add > General > Run command Line

Rename Name: Taskkill – Communicator

Copy/paste into the command line

taskkill /f /im communicator.exe /t


For the first two steps, click option tab and enable "continue on error"


Add > General > Run command Line

Rename Name: Taskkill – ucmapi.exe

Copy/paste into the command line

taskkill /f /im ucmapi.exe /t


Add > General > Run command Line

Rename Name: Uninstall Lync 2010

Copy/paste into the command line

msiexec /x {81BE0B17-563B-45D4-B198-5721E6C665CD} /q



To locate MSI uninstall GUIDs:

When you install an Office 2010 suite or one of the stand-alone Office 2010 programs, one or more product codes, or GUIDs, are created in the following registry subkey:


From <http://support.microsoft.com/kb/2186281>

Softchoice – Pre-sales position

July 7, 2014

We’re hiring! Softchoice is looking for a motivated individual for a pre-sales technical position.

Position will be located in Southern California, would consider someone located in Seattle.


  • Responsible for Microsoft Product & Services Target Attainment
  • Project Manage Microsoft Enterprise Agreement Renewal & True Up Process
  • Drive Key Renewal & True Up Metrics
  • Manage a pipeline of Microsoft Sales Opportunities
  • Provide Pre Sales Support to Sales Reps & Customers on Microsoft Licensing & Solutions
  • Identify new Enterprise Agreement Prospects
  • Identify upsell & cross sell opportunities on existing customer base
  • Successfully sell & position Microsoft product and services to customers
  • Develop customer proposals & pricing comparisons for Microsoft licensing solutions
  • Deliver Microsoft product roadmap, licensing and financial content to customers
  • Participate in local customer events to deliver Softchoice content
  • Develop & Deliver training to Softchoice sales teams on Microsoft products & services
  • Engage with local Softchoice sales teams to sell jointly into sales territories and customer accounts
  • Engage with local and phone based Microsoft sales teams to accomplish joint goals
  • Engage with Softchoice Enterprise Architects and Professional Services for full technology solution design and deployment
  • Identify and recommend remediation solutions to customers as a result of Softchoice Asset Management licensing audits


  • Minimum Education – Bachelor’s Degree or equivalent
  • Minimum 2 years relevant industry experience;
  • Minimum 3 years in either a sales or marketing capacity
  • Strong Microsoft background
  • Must have work experience in the IT industry
  • Solid understanding of the commercial IT market for hardware and software
  • Strong knowledge of Microsoft Office (Outlook, Word, Excel, PowerPoint)
  • Excellent verbal and written communication skills
  • Experience managing executive level relationships
  • Creative and strategic thinker with strong presentation skills
  • Strong analytical skills are required
  • Self-managed and motivated individual with strong leadership skills

For more information, please contact Steve at steve (dot) thompson (at) softchoice (dot) com

SQL Server 2012 SP2 released

June 16, 2014


Download here:


List of bugs that are fixed:


ConfigMgr 2012 Index Optimization

May 27, 2014

A recent blog post on index defragmentation and comments deserve some space here.

The blog:


The blog does a really good job exposing how the Index optimizer decides to rebuild indexes. That is a good thing!

Lets walk through some of the key points:

This query is what ConfigMgr executes when running the Rebuild Index site maintenance task

SELECT DISTINCT sch.name + ‘.’ + Object_name(stat.object_id),
                CONVERT(INT, stat.avg_fragmentation_in_percent)
FROM   sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, ‘LIMITED’) stat
       JOIN sys.indexes ind
         ON stat.object_id = ind.object_id
            AND stat.index_id = ind.index_id
       JOIN sys.objects obj
         ON obj.object_id = stat.object_id
       JOIN sys.schemas sch
         ON obj.schema_id = sch.schema_id
       AND stat.avg_fragmentation_in_percent > 10.0
       AND ind.type > 0
ORDER  BY CONVERT(INT, stat.avg_fragmentation_in_percent) DESC 

[ST] Identify any table index with more than 10% fragmentation.


EXEC Sp_msforeachtable
  @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)" 

[ST] The DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table.

Using the sp_MSforeachtable will run this statement for each table, allowing an 80% fill factor for each index. (20% index free space will reserved for additional index growth). Assumption is made here that the re-indexing will occur for each table located within the previous query.

The remainder of the article shows as an alternative to the (broken) maintenance plan how to create an SQL Server Maintenance Plan Wizard to rebuild indexes and update statistics.

Then summarizes with how to validate the degree of index fragmentation, both before and after.

Now to the comment…

…that basically involves creating a maintenance database and then running Ola Hallengren’s SQL script against it to create the necessary objects and then run the index optimizer script against the ConfigMgr 2012 database on a weekly basis. There seems to be some, dare I say disagreement, amongst some SCCM MVPs regarding the best way to go about doing this. Some of the MVPs believe that the Rebuild Indexes Site Maintenance Task in ConfigMgr 2012 still has some issues. Therefore, some of them are stating that the best method is to keep the Rebuild Indexes disabled and then use the Steve Thompson method. I just want a consensus and use a method that is going to work, but one that is not going to create additional overhead for me.


Consensus is always a good thing, however, it is always not that simple or easy. Also, I do not believe there is really any disagreement within the MVP community about this information. In light of that, I’d like to share what I know.

I first discovered Ola’s excellent index and statistics optimization task after looking for a solution to the Configuration Manager Index Maintenance task not always running. We’ve seen this failure on SCCM 2007 and ConfigMgr 2012. This has been reported by other MVPs, and observed first hand by this MVP on multiple occasions, on multiple sites. This led to a presentation at MMS 2013:


The script usage was documented here:


Ironically, at the MMS 2013 conference, we learned that Microsoft IT that is responsible for running their ConfigMgr 2012 site runs Ola’s Index Maintenance script as well.

What makes Ola’s script unique, it does not take a blunt force approach to rebuilding every index for every table, rather only rebuild indexes that need to be reorganized or rebuilt if necessary. And, this is based on thresholds you can configure.

Other advantages:

Further, it will only update the Statistics that need to be updated.

If indexes are only lightly fragmented a reorganization might be the most efficient method. However, if heavily fragmented (>30%), then it might be faster to rebuild (essentially drop and create an index).

It is possible to set the number of pages the index occupies before running any tuning… if the index falls on less than 1,500 pages, SQL will likely read the entire index into memory. No real need to optimize that index scenario.

Furthermore, the more frequently this task is run, the less time it takes to complete, because there is less work to do!


Will the other method optimize your indexes and statistics? Yes it will.

Is it better than a non functional site maintenance task? Yes it is.

Is it the most efficient way to optimize a ConfigMgr database? Not in my opinion. Further, this approach does not scale well to very large sites and/or databases.

My recommendation, do your own benchmark testing and then decide.

PowerShell – SQL Audit Script

May 19, 2014


As demonstrated at TechEd 2014, here is the PowerShell script.

Actually there are two scripts.

The first script is contained in this page, copy/paste (as Johan says ‘steal with pride’ ;) and name the first:


The second was a community script that converts the CSV files to Excel.

Download that one, and name it:


Note that these scripts need to be copied to, and run from  the C:\Temp drive, change first (3) variables as needed.

As requested, if there are any enhancements that you would like to see, please let me know?


$SQLServer = "CM01" #use Server\Instance for SQL instances
$SQLCMDBName = "CM_Poc"

# Change path as appropriate
$dir = "C:\TEMP\"

# Default SQL Server initial DB connection
$SQLDBName = "Master"
# Create and Open new connection 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

# 01-Server Properties
$SqlQuery = "SELECT SERVERPROPERTY('MachineName') AS [MachineName], SERVERPROPERTY('ServerName') AS [ServerName],  
SERVERPROPERTY('InstanceName') AS [Instance], SERVERPROPERTY('IsClustered') AS [IsClustered], 
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS], 
SERVERPROPERTY('Edition') AS [Edition], SERVERPROPERTY('ProductLevel') AS [ProductLevel], 
SERVERPROPERTY('ProductVersion') AS [ProductVersion], SERVERPROPERTY('ProcessID') AS [ProcessID],
SERVERPROPERTY('Collation') AS [Collation], SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled], 
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly];"
$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
$DataSet.Tables[0] | export-csv ($dir + "01-Server Properties.csv") -notypeinformation

# 02-Windows Info
$SqlQuery = "SELECT windows_release, windows_service_pack_level, 
       windows_sku, os_language_version
FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataSet.Tables[0] | export-csv ($dir + "02-Windows Info.csv") -notypeinformation

# 03-Version Info
$SqlQuery = "SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataSet.Tables[0] | export-csv ($dir + "03-Version Info.csv") -notypeinformation

# 04-SQL Server Install Date
$SqlQuery = "SELECT @@SERVERNAME AS [Server Name], createdate AS [SQL Server Install Date] 
FROM sys.syslogins WITH (NOLOCK)
WHERE [sid] = 0x010100000000000512000000;" 

$SqlQuery = "SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataSet.Tables[0] | export-csv ($dir + "04-SQL Server Install Date.csv") –notypeinformation

# 05-Configuration Values
$SqlQuery = "SELECT name, value, value_in_use, [description] 
FROM sys.configurations WITH (NOLOCK)

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataSet.Tables[0] | export-csv ($dir + "05-Configuration Values.csv") -notypeinformation

# 06-DB File Names and paths
$SqlQuery = "SELECT DB_NAME([database_id])AS [Database Name], 
       [file_id], name, physical_name, type_desc, state_desc, 
       CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4 
AND [database_id] <> 32767
OR [database_id] = 2

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataSet.Tables[0] | export-csv ($dir + "06-DB File Names and paths.csv") -notypeinformation

# 07-DB Properties
$SqlQuery = "SELECT db.[name] AS [Database Name], 
suser_sname( owner_sid ) As [Database Owner],
db.recovery_model_desc AS [Recovery Model], 
db.log_reuse_wait_desc AS [Log Reuse Wait Description], 
ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %], 
db.[compatibility_level] AS [DB Compatibility Level], 
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,
db.is_auto_update_stats_async_on, db.is_parameterization_forced, 
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
db.is_auto_close_on, db.is_auto_shrink_on, db.is_cdc_enabled
FROM sys.databases AS db WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK) 
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%' 
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0 OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataSet.Tables[0] | export-csv ($dir + "07-DB Properties.csv") -notypeinformation

# 08-Server Hardware
$SqlQuery = "SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)], 
sqlserver_start_time --, affinity_type_desc -- (affinity_type_desc is only in 2008 R2)
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataSet.Tables[0]  | export-csv ($dir + "08-Server Hardware.csv") -notypeinformation

# 09-System Manufacturer
$SqlQuery = "EXEC xp_readerrorlog 0,1,'Manufacturer';" 

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataSet.Tables[0] | export-csv ($dir + "09-System Manufacturer.csv") -notypeinformation

# 10-Fixed Drive Freespace
$SqlQuery = "EXEC xp_fixeddrives;" 

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataSet.Tables[0] | export-csv ($dir + "10-Fixed Drive Freespace.csv") -notypeinformation

# 11-IO Util by DB
$SqlQuery = "WITH Aggregate_IO_Statistics
(SELECT DB_NAME(database_id) AS [Database Name],
CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
GROUP BY database_id)
SELECT ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS [I/O Rank], [Database Name], io_in_mb AS [Total I/O (MB)],
       CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O Percent]
FROM Aggregate_IO_Statistics

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataSet.Tables[0] | export-csv ($dir + "11-IO Util by DB.csv") -notypeinformation

# 12-System Memory
$SqlQuery = "SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)], 
       available_physical_memory_kb/1024 AS [Available Memory (MB)], 
       total_page_file_kb/1024 AS [Total Page File (MB)], 
       available_page_file_kb/1024 AS [Available Page File (MB)], 
       system_cache_kb/1024 AS [System Cache (MB)],
       system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);" 

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataSet.Tables[0] | export-csv ($dir + "12-System Memory.csv") -notypeinformation

# 13-Process Memory
$SqlQuery = "SELECT physical_memory_in_use_kb/1024 AS [SQL Server Memory Usage (MB)],
       large_page_allocations_kb, locked_page_allocations_kb, page_fault_count, 
       memory_utilization_percentage, available_commit_limit_kb, 
       process_physical_memory_low, process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataSet.Tables[0] | export-csv ($dir + "13-Process Memory.csv") -notypeinformation

# 14-SQL Log file freespace

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataSet.Tables[0] | export-csv ($dir + "14-SQL Log file freespace.csv") -notypeinformation

# Part 2
# Change to CM database for remainder of queries
# Reconnect to previous connection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLCMDBName; Integrated Security = True"

# 15-CM File Sizes
# DB Name needed here...
$SqlQuery = "SELECT f.name AS [File Name] , f.physical_name AS [Physical Name], 
CAST((f.size/128.0) AS decimal(15,2)) AS [Total Size in MB],
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS decimal(15,2)) 
AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK) 
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) 
ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataSet.Tables[0] | export-csv ($dir + "15-CM File Sizes.csv") -notypeinformation

# 16-CM DB Statistics
     OBJECT_NAME(s.[object_id]) AS TableName,
     c.name AS ColumnName,
     s.name AS StatName,
     STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated
 FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
     JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
     JOIN sys.partitions par ON par.[object_id] = s.[object_id]
     JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
    AND (s.auto_created = 1 OR s.user_created = 1);"  
$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataSet.Tables[0] | export-csv ($dir + "16-CM DB Statistics.csv") -notypeinformation

# 17-CM Index Frag
# This one may be a long running query, it is important to determine if the indeses are fragmented.
$SqlQuery = "SELECT DB_NAME(database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name], 
i.name AS [Index Name], ps.index_id, index_type_desc,
CONVERT(decimal,  avg_fragmentation_in_percent), fragment_count, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,N'LIMITED') AS ps 
ON ps.[object_id] = i.[object_id] 
AND ps.index_id = i.index_id
WHERE database_id = DB_ID()
AND page_count > 1500
ORDER BY avg_fragmentation_in_percent DESC OPTION (RECOMPILE);" 

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataSet.Tables[0] | export-csv ($dir + "17-CM Index Frag.csv") -notypeinformation

# 18 - VLF Information
# Virtual Log File - numbers below 50 are generally good. Large numbers of VLF can affect write performance, and startup times
$SqlQuery = "DBCC LOGINFO;" 

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataSet.Tables[0] | export-csv ($dir + "18-CM VLF Info.csv") -notypeinformation


# Call *.CSV to Excel converter!
Invoke-Expression c:\temp\compilereports.ps1


Get every new post delivered to your Inbox.

Join 26 other followers