Get Size Information For All Tables in All Databases

Hello there,

I have been working on a small project to log and track the size and growth of the tables in all databases.  So that overtime I can show the growth size and estimate  the disk space allocation requirements for databases in production environment.  Here is the script I used to capture the information.  I used SQL Server Agent job scheduled to run once a day after midnight and store the results in a repository to be monitored and reported on later.  You can uncomment the where  clause to exclude system tables and tables with no records in them if you want.


SET NOCOUNT ON;
-- Get Table Statistics (Row Count, total space used)
IF OBJECT_ID('tempdb..#TableStatistics') IS NOT NULL
    BEGIN
        DROP TABLE #TableStatistics;
    END
CREATE TABLE #TableStatistics (DatabaseName SYSNAME, SchemaName SYSNAME, TableName VARCHAR(128), TableRowCount BIGINT, TotalSpaceKB VARCHAR(20), UsedSpaceKB VARCHAR(20), UnusedSpaceKB VARCHAR(20));

EXEC sp_msforeachdb 'USE [?];
    INSERT INTO #TableStatistics
    SELECT
          ''?'' as DatabaseName
        , s.Name AS SchemaName
        , t.NAME AS TableName
        , p.rows AS TableRowCount
        , SUM(a.total_pages) * 8 AS TotalSpaceKB
        , SUM(a.used_pages) * 8 AS UsedSpaceKB
        , (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
    FROM    sys.tables AS t
        INNER JOIN sys.indexes AS i ON t.OBJECT_ID = i.object_id
        INNER JOIN sys.partitions AS p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
        INNER JOIN sys.allocation_units AS a ON p.partition_id = a.container_id
        LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
    --WHERE    p.rows > 0 AND t.is_ms_shipped = 0    AND i.OBJECT_ID > 255
    GROUP BY    t.Name, s.Name, p.Rows
    ORDER BY s.name,  t.name' ;

SELECT    *
FROM    #TableStatistics;

DROP TABLE #TableStatistics;


HTH,
Bulent

How To Extend VirtualBox Dynamic Disk For Windows Server 2012 R2

I wanted to set up a lab environment using VirtualBox to test SQL Server 2016.  I  first started with the installation of Windows Server 2012 R2 and 15GB space configured as dynamic disk.  Installation went smooth and I then started the windows update process. After a while I started getting low disk free space and updates failed.  Here are the steps I took to extend the disk and screenshots below them.

1. Shutdown the virtual machine that needs additional space
2. Open up a command prompt with administrator privileges
3. Change your folder path to where VirtualBox is installed
4. Type the command above the first screenshot and provide the full path of the virtual disk you want to extend and the size you want to extend  the disk to, command provided below extends  the dynamic disk to 20GB
5. Start the virtual machine and log in  to windows
6. Open the 'Computer Management' and under the 'Storage' section on the left side of the pane and click on 'Disk Management' and you will see the unallocated space for the disk you just extended using the command in step  4
7. Right click on the disk you want want to extend to bring up the context menu where you will see 'Extend Volume' option
8. Click on 'Extend Volume'
9. This will bring the 'Extend Volume Wizard' and follow the steps in the wizard

VirtualBox command to extend the dynamic disk:
vboxmanage modifyhd "pathToYourVHDfile" --resize 20480

And here are the screenshots.

 

 

HTH,
Bulent

Find Size of the Indexes for Indexed Views

Hello all,

I have several scripts in my tool box to look into the indexes for tables, but did not have one for indexed views.  I wanted to share a script that return the size of clustered and non clustered indexes for indexed views if exist in the context of the database where the script is executed.

HTH,
Bulent

SELECT
      OBJECT_SCHEMA_NAME(v.object_id) AS 'SchemaName'
    , v.NAME AS 'ViewName'
    , i.index_id
    , i.name AS 'IndexName'
    , p.rows AS 'RowCounts'
    , SUM(a.total_pages) * 8 AS 'TotalSpaceKB'
    , SUM(a.used_pages) * 8 AS 'UsedSpaceKB'
    , SUM(a.data_pages) * 8 AS 'DataSpaceKB'
FROM    sys.views AS v
    INNER JOIN sys.indexes AS i ON v.OBJECT_ID = i.object_id
    INNER JOIN sys.partitions AS p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN sys.allocation_units AS a ON p.partition_id = a.container_id
GROUP BY v.object_id, v.NAME, i.object_id, i.index_id, i.name, p.Rows;