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

Cycling SQL Server Error Log

I have a daily check list that I go through everyday. And checking the SQL Server error log is one item on the list. Depends on how your server is configured SQL Server logs informational and error messages to the log file. By default your server is configured to have 6 log files that it keeps and when the SQL Server Service starts it removes the oldest log file. In the meantime if your server is up for a long time the information in that log file just keeps growing and makes the log file hard to open from remote workstation.

In the environments that I support I change the configuration so that I keep more than 6 files. I set up to store 99 files (which is the max allowed files for the SQL Server error log). Then I create a job that runs every night right after midnight. This way when I come in every morning there is a brand new log file and since the daily log files are much smaller it makes the file easy to open and check. I use the script below to change the number of log files (note you can do this using SSMS GUI and just make a right click on SQL Server Logs under Management folder than click on Configure and change the value to what you need). After the change using second script I create SQL Server Agent Job that is scheduled to run right after midnight to cycle the error log. This make the daily task of checking the error logs easier (at least for me). Use the scripts below in a test environment and once you find out it could add some value to your daily tasks you could use it in your production environment.

HTH,
Bulent

Script 1:
This changes the number of log files stored to a value you set at the end and uses extended stored procedure to update the registry. Remember this can be done through GUI but it does executed the same in the background.

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 60

GO



Script 2:
This script creates a job (make sure you sql server agent is running) and schedules it to run at 12:00:01 am everyday.

USE [msdb]

GO

/****** Object: Job [A DBA SQL Server Error Log Cycle] Script Date: 01/27/2012 14:01:38 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [Database Maintenance] Script Date: 01/27/2012 14:01:38 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'A DBA SQL Server Error Log Cycle',

@enabled=1,

@notify_level_eventlog=2,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'Cycle sql serve error log and create a new error log file',

@category_name=N'Database Maintenance',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Cycle Error Log] Script Date: 01/27/2012 14:01:38 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cycle Error Log',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'EXEC sp_cycle_errorlog ;

',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Cycle Error Log',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=1,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20111013,

@active_end_date=99991231,

@active_start_time=1,

@active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

SQL Server Trimming miliseconds (Zero out miliseconds)

Hi,
Where I work we needed to zero out the milliseconds from a datetime field in one of the database. I found out that somebody created function below which returns the passed datetime with the milliseconds zero out. Instead of 2010-04-15 14:50:49.953 this time we needed to return 2010-04-15 14:50:49.000. When I looked at the function I thought there is just to much DATEPART, CONVERT and concatenation being used. And I asked myself If I could make it much shorter, cleaner. So I did come up with a result and it works. Here is the original function:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[TrimMilliseconds] (@D datetime)
RETURNS datetime
AS
BEGIN
DECLARE @Date datetime
SET @Date = CONVERT(datetime,
CONVERT(varchar(4), DATEPART(yyyy, @D)) + '-' +
CONVERT(varchar(2), DATEPART(mm, @D)) + '-' +
CONVERT(varchar(2), DATEPART(dd, @D)) + ' ' +
CONVERT(varchar(2), DATEPART(Hh, @D)) + ':' +
CONVERT(varchar(2), DATEPART(mi, @D)) + ':' +
CONVERT(varchar(2), DATEPART(ss, @D)))

return @Date
END
GO

Here is my updated version:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[TrimMillisecondsNew] (@D datetime)
RETURNS datetime
AS
BEGIN
DECLARE @Date datetime
SET @Date = CAST(CONVERT(VARCHAR(19),@D,20) AS DATETIME)

return @Date
END
GO

As you see the second function is much cleaner and simpler. I only used convert and cast functions once to do the work. Less typing, less t-sql, work is done.