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

SSIS Warning: Truncation May Occur

I was working on SSIS package to load data from several sources into final destination table. And I see a yellow exclamation mark on data flow task from one of the sources. The message was 'Warning: Truncation may occur due to inserting data from data flow column "Description" with a length of 255 to database column "Description" with a length of 250. When I checked the source column length and destination it was obvious that the lengths of the columns were different and SSIS was correct about complaining. I verified that there was not a record that was inserted with the length greater than 175 char (the field is populated by app in one of the 5 possible defined descriptions). So I did the changes below to get SSIS stop complaining about the length of the column. To fix the issue follow the steps below:

Open the Data Flow task

On the source task right click and select Advanced Editor

Click on Input and Output Properties tab

Expand OLE DB Source Output

Expand External columns and check the columns we need

Expand Output Columns

Click on corresponding column

Change Length property of the column to desired length and refresh.



Also on Destination side:

Open Advanced Editor (on OLE DB Destination)

Click on Input and Output Properties tab

Click on Refresh button

Check the value of the length is correct



HTH,

Bulent

SQL Server 2008 Invalid SKU error

I was trying to set up SQL Server 2008 Cluster on Windows 2008 OS. Creating the cluster with a single node went pretty smooth. However when I wanted to add the second node to cluster I kept getting 'Invalid SKU error' and installation kept aborting the operation. I downloaded the new ISO of SQL Server 2008 from MSDN website with no help. Searching the net came out with the link below which stated that it was a know bug and ways to workaround it. I started the setup process from command line using the command below. At the end the node has been added with success.

Here is the command that I started the installation:

setup.exe /q /ACTION=AddNode /INSTANCENAME=""

Microsoft Connect bug report site:

HTH

SQL Server 2008 R2 Availalbe to Download

SQL Server 2008 R2 is available for downloading to MSDN and Technet subscribers. Here are the links:

• http://msdn.microsoft.com/subscriptions
• http://technet.microsoft.com/subscriptions

SQL Server 2008 R2

Here is the link to free ebook download from Microsoft. Start learning before the product releases on May 2010.

SQL Server SA Login Disabled and Can Not Login To Server

I needed to fix the login issue on newly installed SQL Server 2008 developer edition for a developer who is just starting to work. The tech who prepared the workstations forgot to add the local admin and the AD user account and he was out of the office. So even with the local administrator account no one can log in to SQL Server. Thanks to new security settings of SQL Server 2008. I did a little research and found out the solution. Here is the list of actions for the solution. First open the SQL Server Configuration Manager.

1. Stop SQL Server Service.
2. Double click on SQL Server service and goto advanced tab.
3. add -m; to the beginning of Startup Parameters
4. Start SQL Server Service
5. Open command prompt and connect to your the server using SQLCMD
6. Create a log in (CREATE LOGIN BillGates WITH PASSWORD = 'password')
7. Now add the log in to sysadmin group (EXEC sp_addsrvrolemember 'BillGates','sysadmin')
8. Remember you need to issue GO command to execute the commands.
9. Once you created the login and add it to sysadmin role go to SQL Server Configuration Manager.
10. Stop SQL Server service.
11. Remove -m; from startup parameters.
12. Start SQL Server Service and login with the user that you just created.
13. Now you have sysadmin rights.

HTH,

Bulent

SQL Server 2008 BOL July 2009 Update

July 2009 Sql Server 2008 BOL update has been released by Microsoft. You can download and install it from here or use the online version. BOL is full of great information and code examples.