March 27, 2009 at 10:33 am
Good day,
I'd like to monitor the growth of our databases and watch for trends, make sure we don't run out of space, etc.
sp_spaceused is inaccurate unless you run dbcc updateusage and I've got many databases that are either too big or I don't have the window, etc... I've looked at the sysfiles system table but that gives you the "allocated" size of the file not the actual size.
I am specifically targeting 2000 boxes, since according to BOL in 2005 dbcc updateusage is unnecessary so long as the database was created in 2005 (versus upgraded to 2005).
So, my question is how are you all monitoring your db growth?
Thanks!
March 27, 2009 at 10:39 am
http://www.sqlservercentral.com/articles/Administering/20010422115754/85/
I used to monitor backup size quite a bit. It told me if data was growing a lot, and that's all I care about. Knowing if things are changing and how much space I need to add every xxx days.
March 27, 2009 at 11:03 am
Thanks for the reply Steve.
It appears sp_helpdb returns the "allocated" size, just like the sysfiles table. I've never peeked at the backup tables, I can do that, but it just seems like there's got to be a better way.
I love the .NET SMO libraries that come with 2005, but under the hood that must use sp_spaceused since the results are inaccurate for my 2000 boxes (until I run dbcc updateusage).
March 27, 2009 at 11:07 am
I should have updated that article. I looked through it after I posted, and realized that I had some VBScript scripts there looked at sizes of actual backup files.
These provided a good idea of how much data I had, and then if there was a change. If I captured that info, inserted it into a table and then ran a comparison day to day, I could easily see how much data I had, and how it was growing.
March 3, 2010 at 8:17 pm
Henry..
Check my post out.. I write this script specially for this purpose.
http://www.sqlservercentral.com/scripts/Database+growth/69634/
Regards
IT
March 5, 2010 at 6:47 am
Thanks for the suggestion IT.
Interesting script, I was not aware of DBCC SHOWFILESTATS. I do wonder, though, if it has the same shortcomings as sp_spaceused - meaning you first must run DBCC UPDATEUSAGE to get accurate results.
We have few 2000 machines left, so I'm not sure I'm going to spend any time on this but if I get a chance to test, I'll post back.
Thanks again.
March 24, 2010 at 1:21 pm
Forgot to mention... the following comes with no warranties or guarantee that it wont totally mess things up... but it works great for me.
I am using various scripts and jobs to track growth and other capacity information on both SQL 2000 and 2005 databases. Almost all of this was taken from various scripts here and there (many from this very forum)... I wish I had tracked everyone whose code is included here so I could credit each of them.
It tracks and reports on the following:
-version, service pack, servername
- memory and load informatoin
- I/O stats over time
-average and max connections per database
-top wait types
-current disk space usage
-disk usage rate over time
-current datafile size and usages
-datafile growth rate over time
-total growth rate of datafiles over time
-current transaction log size and space used
-most recent backup time for each db
-databases where backups are over 24 hours old
-top queries by elapsed time (this is reported for sql 2005 only)
Here is what I am doing:
Before running this, you need a database called DXDBA. Dont just copy and paste the whole thing below, you need to do it step by step. Also for the queries that generate the report, if you do "results to text" it looks very readable.
PART 1: Capturing disk space information
1.)Create a table to hold disk space information:
USE [DXDBA]
CREATE TABLE [dbo].[Capacity_DiskSpaceTracking](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TimeCollected] [smalldatetime] NOT NULL CONSTRAINT [Capacity_DiskSpaceTracking_TimeCollected] DEFAULT (getdate()),
DriveLetter CHAR(1)
, TotalSpace bigint
, FreeSpace bigint
, Label varchar(10)
) ON [PRIMARY]
2.)Create a procedure to write disk space information to the table:
CREATE PROC [CAPACITY_RECORD_DISK_SPACE]
AS
BEGIN
SET NOCOUNT ON
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')
DROP TABLE ##_DriveSpace
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')
DROP TABLE ##_DriveInfo
DECLARE @Result INT
, @objFSO INT
, @Drv INT
, @cDrive VARCHAR(13)
, @Size VARCHAR(50)
, @Free VARCHAR(50)
, @Label varchar(10)
CREATE TABLE ##_DriveSpace
(
DriveLetter CHAR(1) not null
, FreeSpace VARCHAR(10) not null
)
CREATE TABLE ##_DriveInfo
(
DriveLetter CHAR(1)
, TotalSpace bigint
, FreeSpace bigint
, Label varchar(10)
)
INSERT INTO ##_DriveSpace
EXEC master.dbo.xp_fixeddrives
-- Iterate through drive letters.
DECLARE curDriveLetters CURSOR
FOR SELECT driveletter FROM ##_DriveSpace
DECLARE @DriveLetter char(1)
OPEN curDriveLetters
FETCH NEXT FROM curDriveLetters INTO @DriveLetter
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @cDrive = 'GetDrive("' + @DriveLetter + '")'
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT
IF @Result = 0
EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT
IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT
IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT
IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT
IF @Result <> 0
EXEC sp_OADestroy @Drv
EXEC sp_OADestroy @objFSO
SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )
SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )
INSERT INTO ##_DriveInfo
VALUES (@DriveLetter, @Size, @Free, @Label)
END
FETCH NEXT FROM curDriveLetters INTO @DriveLetter
END
CLOSE curDriveLetters
DEALLOCATE curDriveLetters
INSERT INTO Capacity_DiskSpaceTracking
(DriveLetter, Label, TotalSpace, FreeSpace)
SELECT DriveLetter, Label, TotalSpace, FreeSpace FROM ##_DriveInfo
/* Clean up. Drop the temp table */
DROP TABLE ##_DriveSpace
DROP TABLE ##_DriveInfo
END
3.)Create Job to Capture Disk Space Info every 12 hours and delete records older than 90 Days:
USE [msdb]
GO
/****** Object: Job [_DXDBA_Capacity_Disk_Space_Capture] Script Date: 09/29/2009 09:25:32 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 09/29/2009 09:25:32 ******/
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'_DXDBA_Capacity_Disk_Space_Capture',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'DXDBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [CaptureDiskSpaceInfo] Script Date: 09/29/2009 09:25:32 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CaptureDiskSpaceInfo',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@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 [CAPACITY_RECORD_DISK_SPACE]',
@database_name=N'DXDBA',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Delete older than 90 days] Script Date: 09/29/2009 09:25:32 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete older than 90 days',
@step_id=2,
@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'delete from Capacity_DiskSpaceTracking where 90 <DATEDIFF(day, timecollected, GETDATE())',
@database_name=N'DXDBA',
@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'_Capture_disk_space_every_12_hours',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=12,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20090929,
@active_end_date=99991231,
@active_start_time=0,
@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:
4.)Create a table to hold connection information:
USE [DXDBA]
GO
/****** Object: Table [dbo].[capacity_collection_info] Script Date: 09/29/2009 10:05:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[capacity_connection_info](
[db_name] [char](40) NULL,
[num_of_connx] [bigint] NULL,
[time_collected] [smalldatetime] NOT NULL CONSTRAINT [DF__capacity___time___300424B4] DEFAULT (getdate())
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
5.)Create a procedure to capture connection information:
IF it is SQL 2005:
create proc [_dxdba_capacity_user_connections]
as
begin
--create temp table
create table #users (
spid int,
ecid int,
status char( 40),
loginname char( 100),
hostname char( 100),
blk int,
dbname char( 40),
cmd varchar( 200),
request_id int
)
-- load the table
insert #users
exec sp_who
-- get the totals
insert into capacity_connection_info (db_name, num_of_connx)
(select dbname,
count(spid)
from #users
group by dbname)
drop table #users
end
IF it is SQL 2000:
create proc [_dxdba_capacity_user_connections]
as
begin
--create temp table
create table #users (
spid int,
ecid int,
status char( 40),
loginname char( 100),
hostname char( 100),
blk int,
dbname char( 40),
cmd varchar( 200))
-- load the table
insert #users
exec sp_who
-- get the totals
insert into capacity_connection_info (db_name, num_of_connx)
(select dbname,
count(spid)
from #users
group by dbname)
drop table #users
end
6.)Create a Job to capture connection information every 3 hours and delete records older than 30 days:
USE [msdb]
GO
/****** Object: Job [_DXDBA_Capacity_Connection_Tracking] Script Date: 09/29/2009 10:33:36 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 09/29/2009 10:33:36 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'_DXDBA_Capacity_Connection_Tracking',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'DXDBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Capture connection info] Script Date: 09/29/2009 10:33:36 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Capture connection info',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@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 _dxdba_capacity_user_connections',
@database_name=N'DXDBA',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [delete records older than 30 days] Script Date: 09/29/2009 10:33:37 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'delete records older than 30 days',
@step_id=2,
@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'delete from capacity_connection_info where 30 <DATEDIFF(day, time_collected, GETDATE())',
@database_name=N'DXDBA',
@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'connection info every 3 hours',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=3,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20090929,
@active_end_date=99991231,
@active_start_time=0,
@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:
7.)Create a tables to hold data file information:
USE [DXDBA]
GO
/****** Object: Table [dbo].[capacity_datafile_info] Script Date: 10/01/2009 11:51:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[capacity_datafile_info](
[server_name] [char](40) NULL,
[db_name] [char](40) NULL,
[logicalfilename] [varchar](100) NULL,
[OSFileName] [varchar](250) NULL,
[FileSizeMb] [bigint] NULL,
[FileSpaceUsedMB] [bigint] NULL,
[FileSpaceAvailableMB] [bigint] NULL,
[FilePercentUsed] [bigint] NULL,
[time_collected] [smalldatetime] NULL,
[groupid] [numeric](3, 0) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CAPACITY_DB_Growth](
[Database_Name] [varchar](150) NULL,
[Logical_File_Name] [varchar](250) NULL,
[File_Size_MB] [varhcar](200) NULL,
[Growth_Factor] [varchar](100) NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
8.)Create Proc to capture datafile info:
USE [DXDBA]
GO
/****** Object: StoredProcedure [dbo].[CAPACITY_Record_datafile_info] Script Date: 10/09/2009 08:04:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[CAPACITY_Record_datafile_info]
as
begin
set ansi_warnings off
declare @capturetime datetime
set @capturetime = getdate()
/*** datafile size and utilization for each database**/
exec master..sp_MSForeachdb 'USE [?]
insert into dxdba.[dbo].[capacity_datafile_info] ([server_name],[db_name],[logicalfilename],[OSFileName],[FileSizeMb]
,[FileSpaceUsedMB],[FileSpaceAvailableMB],[FilePercentUsed],[time_collected],[groupid])
SELECT @@servername as servername, db_name() as databasename,name AS logicalfilename, filename as OSFileName,
size/128.0 as TotalSizeInMB,
CAST(FILEPROPERTY(name, ''SpaceUsed'' )AS int)/128.0 AS SpacesUsedInMB,
size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' )AS int)/128.0 AS AvailableSpaceInMB,
(CAST(FILEPROPERTY(name, ''SpaceUsed'' )AS int)/128.0) / (size/128.0)*100 as PercentUsed, getdate() as timecollected,groupid
FROM ?.dbo.SYSFILES'
update dxdba.[dbo].[capacity_datafile_info]set time_collected= getdate() where time_collected in (select max(time_collected) from dxdba.[dbo].[capacity_datafile_info] group by logicalfilename)
declare @l_db_name varchar(50)
,@l_sql_string varchar(1000)
set nocount on
if object_id('DB_Growth') is not null
truncate table CAPACITY_DB_Growth
declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases
open db_name_cursor
fetch next from db_name_cursor into
@l_db_name
While (@@fetch_status = 0)
begin
select @l_sql_string = 'select ' + '''' + @l_db_name + '''' + ', name, ceiling((size * 8192)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from ' + @l_db_name + '.dbo.sysfiles'
insert into CAPACITY_DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@l_sql_string)
fetch next from db_name_cursor into
@l_db_name
end
close db_name_cursor
deallocate db_name_cursor
--select timecollected, logicalfilename,filespaceusedmb from capacity_datafile_info order by logicalfilename with (nolock)
set nocount off
set ansi_warnings on
return
end
9.)Create Job to capture datafile info every 12 hours and keep for 90 days
USE [msdb]
GO
/****** Object: Job [_DXDBA_capacity_datafile_usage_capture] Script Date: 10/01/2009 12:26:13 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 10/01/2009 12:26:13 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'_DXDBA_capacity_datafile_usage_capture',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'DXDBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [capture data] Script Date: 10/01/2009 12:26:14 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'capture data',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@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 CAPACITY_Record_datafile_info',
@database_name=N'DXDBA',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [delete older than 90 days] Script Date: 10/01/2009 12:26:14 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'delete older than 90 days',
@step_id=2,
@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'delete from capacity_datafile_info where 90 <DATEDIFF(day, time_collected, GETDATE())',
@database_name=N'DXDBA',
@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'_Capture_disk_space_every_12_hours',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=12,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20090929,
@active_end_date=99991231,
@active_start_time=0,
@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:
10.)Create table to store wait statistics:
USE [DXDBA]
GO
/****** Object: Table [dbo].[CAPACITY_DB_Growth] Script Date: 10/05/2009 09:06:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CAPACITY_Wait_stats](
[Wait_Type] [varchar](50) NULL,
[Requests] [bigint] NULL,
[Wait_time] [bigint] NULL,
[Signal_Wait_Time] [bigint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
11.)Create procedure to capture wait statistics:
USE [DXDBA]
GO
/****** Object: StoredProcedure [dbo].[CAPACITY_Record_datafile_info] Script Date: 10/05/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[CAPACITY_Record_wait_stat_info]
as
begin
set ansi_warnings off
truncate table CAPACITY_Wait_stats
insert into CAPACITY_Wait_stats
exec('dbcc sqlperf(waitstats)')
/** TO return top ten waits an percentages
select top 10
wait_type "Wait Type",
wait_time / 1000 "Wait time (s)",
Convert(Decimal(12,2), (wait_time * 100.0 )
/ (select wait_time from capacity_wait_stats where wait_type ='total')) "% Waiting"
from capacity_wait_stats
where wait_type not like '%SLEEP%'
order by wait_time desc
**/
end
/**NOTE: we don’t create a job for this since these stats are automatically created. We’ll just execute the proc when we generate a report**/
12.)Create table to store memory and transactions statistics:
USE [DXDBA]
GO
/****** Object: Table [dbo].[CAPACITY_DB_Growth] Script Date: 10/05/2009 12:51:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CAPACITY_Transactions_and_memory](
[BufferCacheHitRatio] [varchar](100) NULL,
[SQLServerMemory] [varchar](100) NULL,
[SQLServerOptimalMemory] [varchar](100) NULL,
[Transactions] [varchar](100) NULL,
[Timecollected] [datetime]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
13.)Create proc to capture memory and transactions statistics:
create proc [Capacity_transactions_and_memory_proc]
as begin
DECLARE @value numeric(25, 2), @basevalue numeric(25, 2)
declare @size int
declare @fileexist int
declare @error varchar(1000)
declare @Cachehitratio varchar(100)
declare @SQLTargetMemory varchar(100)
declare @SQLTotMemory varchar(100)
declare @Freepages varchar(100)
declare @PageSplits varchar(100)
declare @transactions varchar(100)
declare @userconnections varchar(100)
declare @deadlocks varchar(100)
declare @date varchar(25)
declare @lockwait varchar(100)
declare @compilation varchar(100)
declare @totaldatagrowth varchar(100)
declare @totalloggrowth varchar(100)
declare @batchrequest varchar(100)
set @Cachehitratio =''
set @SQLTargetMemory =''
set @SQLTotMemory =''
set @Freepages =''
set @PageSplits =''
set @transactions=''
set @userconnections =''
set @deadlocks =''
set @date =''
set @lockwait =''
set @compilation=''
set @totaldatagrowth =''
set @totalloggrowth =''
set @batchrequest =''
set @date =convert(varchar(25),getdate(),109)
--Cache hit ratio
SELECT @value = cntr_value FROM master.dbo.sysperfinfo (nolock)
WHERE counter_name = 'Buffer cache hit ratio'
SELECT @basevalue = cntr_value FROM master.dbo.sysperfinfo (nolock)
WHERE counter_name = 'Buffer cache hit ratio base'
set @Cachehitratio= convert(varchar(100), (@value / @basevalue) *100)
--Free Pages
SELECT @value = cntr_value FROM master.dbo.sysperfinfo (nolock)
WHERE counter_name = 'Free Pages' and
object_name = 'SQLServer:Buffer Manager'
set @Freepages= convert (varchar(100),@value )
--SQL Memory
select @value =cntr_value/1024 from master.dbo.sysperfinfo (nolock)
where object_name ='SQLServer:Memory Manager' and
counter_name ='Total Server Memory (KB)'
set @SQLTotMemory=convert(varchar(100),@value)
select @basevalue=cntr_value/1024 from master.dbo.sysperfinfo (nolock)
where object_name ='SQLServer:Memory Manager' and
counter_name ='Target Server Memory(KB)'
set @SQLTargetMemory=convert(varchar(100),@basevalue)
--Page Splits
select @value =cntr_value from master.dbo.sysperfinfo (nolock)
where object_name ='SQLServer:Access Methods' and
counter_name ='Page Splits/sec'
set @PageSplits=convert(varchar(100),@value )
--# of transactions/sec
select @value = cntr_value from master.dbo.sysperfinfo (nolock)
where object_name = 'SQLServer:Databases'
and counter_name ='Transactions/sec' and instance_name ='_Total'
set @transactions= convert(varchar(100),@value )
--User Connections
select @value = cntr_value from master.dbo.sysperfinfo (nolock)
where object_name = 'SQLServer:General Statistics'
and counter_name ='User Connections'
set @userconnections=convert(varchar(100),@value )
--Deadlocks
select @value = cntr_value from master.dbo.sysperfinfo (nolock)
where object_name = 'SQLServer:Locks'
and counter_name ='Number of Deadlocks/sec'
set @deadlocks = convert(varchar(25),@value)
--Lock waits/sec
select @value= cntr_value from master.dbo.sysperfinfo (nolock)
where object_name = 'SQLServer:Locks'
and counter_name ='Average Wait Time (ms)'
set @lockwait = convert(varchar(25),@value)
--Database dataFile growth
select @value = cntr_value/1024 from master.dbo.sysperfinfo (nolock)
where object_name ='SQLServer:Databases' and
counter_name ='Data File(s) Size (KB)' and instance_name ='_Total'
set @totaldatagrowth = convert(varchar(100),@value)
--Database Log File growth
select @value = cntr_value/1024 from master.dbo.sysperfinfo (nolock)
where object_name ='SQLServer:Databases' and
counter_name ='Log File(s) Size (KB)' and instance_name ='_Total'
set @totalloggrowth = convert(varchar(100),@value)
--declare @LogMB numeric(25, 2)
select @value = cntr_value from master.dbo.sysperfinfo (nolock)
where object_name ='SQLServer:SQL Statistics' and
counter_name ='Batch Requests/sec'
set @batchrequest = convert(varchar(100),@value)
--Compilations/sec
select @value= cntr_value from master.dbo.sysperfinfo (nolock)
where object_name ='SQLServer:SQL Statistics' and
counter_name ='SQL Compilations/Sec'
set @compilation= convert(varchar(100),@value)
insert into [CAPACITY_Transactions_and_memory]
select @Cachehitratio, @SQLTotMemory, @SQLTargetMemory , @transactions , getdate()
end
14.)Create Job to capture Mem and transact info every 12 hrs and keep for 30 days:
USE [msdb]
GO
/****** Object: Job [_DXDBA_Capacity_transactions_and_memory_capture] Script Date: 10/06/2009 07:34:19 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 10/06/2009 07:34:20 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'_DXDBA_Capacity_transactions_and_memory_capture',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Capacity_transactions_and_memory_proc- every 12 hours, purge after 30 days',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'DXDBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Capacity_transactions_and_memory_proc] Script Date: 10/06/2009 07:34:20 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Capacity_transactions_and_memory_proc',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@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 Capacity_transactions_and_memory_proc',
@database_name=N'DXDBA',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [purge after 30 days] Script Date: 10/06/2009 07:34:20 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'purge after 30 days',
@step_id=2,
@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'delete from CAPACITY_Transactions_and_memory where 30 <DATEDIFF(day, timecollected, GETDATE())',
@database_name=N'DXDBA',
@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'memory and transaction info every 12 hrs',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=12,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20091005,
@active_end_date=99991231,
@active_start_time=200000,
@active_end_time=195959
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:
15.)Create Proc to capture IO stats:
USE [DXDBA]
GO
/****** Object: StoredProcedure [dbo].[LOG_FILESTATS] Script Date: 10/07/2009 10:16:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LOG_FILESTATS]
( @BIT_DELETE_RESULTS BIT = 0 )
AS
SET NOCOUNT ON
DECLARE @INT_LOOPCOUNTER INTEGER
DECLARE @INT_MAXCOUNTER INTEGER
DECLARE @INT_DBID INTEGER
DECLARE @INT_FILEID INTEGER
DECLARE @SNM_DATABASENAME SYSNAME
DECLARE @SNM_FILENAME SYSNAME
DECLARE @NVC_EXECUTESTRING NVARCHAR(500)
DECLARE @MTB_DATABASES TABLE (
ID INT IDENTITY,
DBID INT,
DBNAME SYSNAME )
IF OBJECT_ID('TBL_DATABASEFILES') IS NOT NULL
BEGIN
TRUNCATE TABLE TBL_DATABASEFILES
END
ELSE
BEGIN
CREATE TABLE TBL_DATABASEFILES (
ID INT IDENTITY,
DBID INT,
FILEID INT,
FILENAME SYSNAME,
DATABASENAME SYSNAME)
END
INSERT INTO @MTB_DATABASES (DBID,DBNAME) SELECT DBID,NAME FROM MASTER.DBO.SYSDATABASES ORDER BY DBID
SET @INT_LOOPCOUNTER = 1
SELECT @INT_MAXCOUNTER=MAX(ID) FROM @MTB_DATABASES
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER
BEGIN
SELECT @INT_DBID = DBID,@SNM_DATABASENAME=DBNAME FROM @MTB_DATABASES WHERE ID = @INT_LOOPCOUNTER
SET @NVC_EXECUTESTRING = 'INSERT INTO TBL_DATABASEFILES(DBID,FILEID,FILENAME,DATABASENAME) SELECT '+STR(@INT_DBID)+',FILEID,NAME,'''+@SNM_DATABASENAME+''' AS DATABASENAME FROM ['+@SNM_DATABASENAME+'].DBO.SYSFILES'
EXEC SP_EXECUTESQL @NVC_EXECUTESTRING
SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1
END
--'OK WE NOW HAVE ALL THE DATABASES AND FILENAMES ETC....
IF OBJECT_ID('TBL_FILESTATISTICS') IS NOT NULL
BEGIN
IF @BIT_DELETE_RESULTS = 1 TRUNCATE TABLE TBL_FILESTATISTICS
END
ELSE
BEGIN
CREATE TABLE TBL_FILESTATISTICS (
ID INT IDENTITY,
DBID INT,
FILEID INT,
DATABASENAME SYSNAME,
FILENAME SYSNAME,
SAMPLETIME DATETIME,
NUMBERREADS BIGINT,
NUMBERWRITES BIGINT,
BYTESREAD BIGINT,
BYTESWRITTEN BIGINT,
IOSTALLMS BIGINT)
END
SELECT @INT_MAXCOUNTER=MAX(ID) FROM TBL_DATABASEFILES
SET @INT_LOOPCOUNTER = 1
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER
BEGIN
SELECT @INT_DBID = DBID,@INT_FILEID=FILEID,@SNM_DATABASENAME=DATABASENAME,@SNM_FILENAME=FILENAME FROM TBL_DATABASEFILES WHERE ID = @INT_LOOPCOUNTER
INSERT INTO TBL_FILESTATISTICS(DBID,FILEID,SAMPLETIME,NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,DATABASENAME,FILENAME)
SELECT DBID,FILEID,GETDATE(),NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,@SNM_DATABASENAME AS DATABASENAME,@SNM_FILENAME AS FILENAME FROM :: FN_VIRTUALFILESTATS(@INT_DBID,@INT_FILEID)
SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1
END
16.)Execute proc to log initial IO stat info twice:
Exec LOG_FILESTATS
Exec LOG_FILESTATS
17.)Before a report can be generated, each job must run at least twice. So you can wait 48 hours, or just manually trigger each job twice, then execute the following code to generate the report. Best formatting is achieved by executing in SSMS and specifying “results to text”:
QUERY TO GENERATE REPORT for 2005:
USE DXDBA
SET ANSI_PADDING OFF
set nocount on
declare @disk_space_start_date datetime
declare @disk_space_end_date datetime
declare @disk_space_time_period numeric(2,0)
Print @@servername+' Capacity Report'+' ' SELECT GETDATE()
Print''
Print 'SQL Server Version:' Select serverproperty('productversion')
Print 'SQL Server Service Pack' Select serverproperty('productlevel')
/**calculate transactions/sec**/
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_temp_Transactions_and_memory')
DROP TABLE ##_temp_Transactions_and_memory
CREATE TABLE ##_temp_Transactions_and_memory(
[Transactions] [varchar](100) NULL,
[Timecollected] [datetime])
insert into ##_temp_Transactions_and_memory
select top 2 Transactions, Timecollected from CAPACITY_Transactions_and_memory order by timecollected desc
declare @transactions_timediff bigint
declare @transactions_timestart datetime
declare @transactions_timeend datetime
declare @transaction_start bigint
declare @transaction_end bigint
declare @transaction_dif bigint
declare @transactions_persec numeric(10,2)
--select @transaction_start =min(convert(numeric(10,2),transactions)) from ##_temp_Transactions_and_memory
select @transaction_start =min(convert(decimal,transactions)) from ##_temp_Transactions_and_memory
select @transaction_end = max(convert(decimal,transactions)) from ##_temp_Transactions_and_memory
set @transaction_dif = @transaction_end - @transaction_start
--select * from ##_temp_Transactions_and_memory
select @transactions_timestart = min(timecollected) from ##_temp_Transactions_and_memory
select @transactions_timeend = max(timecollected) from ##_temp_Transactions_and_memory
set @transactions_timediff = datediff(second, @transactions_timestart,@transactions_timeend)
/**output transactions/sec and memory info**/
Print 'Memory and Load Information:'
Print ''
select @transactions_persec = convert(numeric(10,2),@transaction_dif) /convert(numeric(10,2),@transactions_timediff)
select @transactions_timestart as StartSnap, @transactions_timeend as EndSnap,@transactions_persec as TransactionsPerSecond,
buffercachehitratio as BufferCacheHitRatio, sqlservermemory as CurrentSQLMemory, SqlServerOptimalMemory as SqlServerOptimalMemory
, @transaction_dif as numberoftransactions, @transactions_timediff as numberofseconds
from CAPACITY_Transactions_and_memory where timecollected = (select max(timecollected) from CAPACITY_Transactions_and_memory)
/**end Memory section**/
exec LOG_FILESTATS
Print 'I/O Statistics'
/** to get IO sample now, "exec LOG_FILESTATS"**/
/**to clear old IO data and take a new sample now "exec LOG_FILESTATS 0"**/
--select * from TBL_FILESTATISTICS
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_IO_stats')
DROP TABLE ##_IO_stats
CREATE TABLE ##_IO_stats(
[databasename] [varchar](40) NULL,
[filename] [varchar] (40),
[durationseconds] [real],
[numberofreads] [real],
[endreads] [real],
[startreads] [real],
[endwrites] [real],
[startwrites] [real],
[numberofwrites] [real],
[totalreadwrite] [real],
readpersecond [real],
writepersecond [real],
totalreadwritepersecond [real],
endtime [datetime],
starttime [datetime])
insert into ##_IO_stats (databasename,filename,endtime, starttime)select databasename, filename,max(sampletime), min(sampletime) from TBL_FILESTATISTICS group by filename,databasename
update ##_IO_stats set durationseconds =datediff(second,starttime,endtime)
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_IO_stats2')
DROP TABLE ##_IO_stats2
create table ##_IO_stats2(
[filename] [varchar] (40),
[stat] [real])
insert into ##_IO_stats2 select filename, max(numberwrites) from TBL_FILESTATISTICS group by filename
update ##_IO_stats set endwrites= stat from ##_IO_stats2 where ##_IO_stats2.filename = ##_IO_stats.filename
truncate table ##_IO_stats2
insert into ##_IO_stats2 select filename, min(numberwrites) from TBL_FILESTATISTICS group by filename
update ##_IO_stats set startwrites= stat from ##_IO_stats2 where ##_IO_stats2.filename = ##_IO_stats.filename
truncate table ##_IO_stats2
insert into ##_IO_stats2 select filename, max(numberreads) from TBL_FILESTATISTICS group by filename
update ##_IO_stats set endreads= stat from ##_IO_stats2 where ##_IO_stats2.filename = ##_IO_stats.filename
truncate table ##_IO_stats2
insert into ##_IO_stats2 select filename, min(numberreads) from TBL_FILESTATISTICS group by filename
update ##_IO_stats set startreads= stat from ##_IO_stats2 where ##_IO_stats2.filename = ##_IO_stats.filename
truncate table ##_IO_stats2
drop table ##_IO_stats2
update ##_IO_stats set numberofreads = endreads-startreads
update ##_IO_stats set numberofwrites= endwrites-startwrites
update ##_IO_stats set totalreadwrite=numberofwrites+numberofreads
update ##_IO_stats set readpersecond =numberofreads/durationseconds
update ##_IO_stats set writepersecond =numberofwrites/durationseconds
update ##_IO_stats set totalreadwritepersecond =totalreadwrite/durationseconds
/**START SELECTS
select * from ##_IO_reads
select * from ##_IO_stats
select * from ##_IO_stats2 order by filename
select * from TBL_FILESTATISTICS
**/
select sum(totalreadwritepersecond) as Total_IO_Per_Sec_All_databases from ##_IO_stats
select top 1 durationseconds/60 as IOCaptureTimeInMinutes from ##_IO_stats
select databasename, sum(numberofwrites) as numberofwrites, sum(numberofreads) as numberofreads, sum(totalreadwrite) as totalreadwrite,sum(readpersecond)as readpersec, sum(writepersecond)as writepersec, sum(totalreadwritepersecond)as readwritepersec from ##_IO_stats group by databasename
print ''
Print 'Average and Max connections per database'
print''
select db_name, avg(num_of_connx) as AVGConnections, max(num_of_connx) as MAXConnections from capacity_connection_info group by db_name
Print 'Wait Type information (intentional waits, like Sleep exlcuded)'
print''
exec CAPACITY_Record_wait_stat_info
select top 10
wait_type "Wait Type",
wait_time / 1000 "Wait time (s)",
Convert(Decimal(12,2), (wait_time * 100.0 )
/ (select wait_time from capacity_wait_stats where wait_type ='total')) "% Waiting"
from capacity_wait_stats
where wait_type not like '%SLEEP%'
order by wait_time desc
Print'Current Disk Space Information:'
print''
Select timecollected,driveletter,totalspace/1024 as DiskSizeGB, FreeSpace/1024 as FreeSpaceGB, label from capacity_diskspacetracking where timecollected =(select max(timecollected) from capacity_diskspacetracking)
print''
Print'Disk Usage Rate:'
print''
set @disk_space_start_date = (select min(timecollected) from capacity_diskspacetracking)
set @disk_space_end_date = (select max(timecollected) from capacity_diskspacetracking)
set @disk_space_time_period = DATEDIFF(day, @disk_space_start_date, @disk_space_end_date)
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DiskUsageRate')
DROP TABLE ##_DiskUsageRate
CREATE TABLE ##_DiskUsageRate
(
DriveLetter CHAR(1)
, StartFreeSpaceMB bigint
, EndFreeSpaceMB bigint
, TimeinDays int
, DiffInMBperDay bigint
)
insert into ##_DiskUsageRate(driveletter) (select distinct driveletter from capacity_diskspacetracking)
update ##_DiskUsageRate set timeindays = DATEDIFF(day, (select min(timecollected) from capacity_diskspacetracking), (select max(timecollected) from capacity_diskspacetracking))
update ##_DiskUsageRate set StartFreeSpaceMB = freespace from capacity_diskspacetracking where timecollected = (select min(timecollected) from capacity_diskspacetracking) and ##_DiskUsageRate.driveletter = capacity_diskspacetracking.driveletter
update ##_DiskUsageRate set EndFreeSpaceMB = freespace from capacity_diskspacetracking where timecollected = (select max(timecollected) from capacity_diskspacetracking) and ##_DiskUsageRate.driveletter = capacity_diskspacetracking.driveletter
update ##_DiskUsageRate set DiffInMBPerDay = 24*(((select freespace from capacity_diskspacetracking where timecollected = (select min(timecollected) from capacity_diskspacetracking) and ##_DiskUsageRate.driveletter = capacity_diskspacetracking.driveletter)-
(select freespace from capacity_diskspacetracking where timecollected = (select max(timecollected) from capacity_diskspacetracking) and ##_DiskUsageRate.driveletter = capacity_diskspacetracking.driveletter))/
(select DATEDIFF(hour, (select min(timecollected) from capacity_diskspacetracking), (select max(timecollected) from capacity_diskspacetracking))))
--select * from capacity_diskspacetracking
--select * from dbo.SYSFILES
select driveletter, Diffinmbperday as Growth_rate_MB_per_day, TimeinDays as TimePeriod_days from ##_DiskUsageRate
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DiskUsageRate')
DROP TABLE ##_DiskUsageRate
/**Print 'Historical Disk Space Usage'
Select timecollected as startdate, driveletter,totalspace/1024 as DiskSizeGB, freespace/1024 as initialfreespace, label from capacity_diskspacetracking where timecollected = @disk_space_start_date
**/
print 'Current datafile sizes and usage:'
print''
select distinct d.server_name, d.db_name, d.groupid,d.logicalfilename, d.OSfilename, d.filesizemb, d.filespaceusedmb,
d.filespaceavailablemb, d.filepercentused, g.growth_factor, d.time_collected
from capacity_datafile_info d, capacity_db_growth g
where d.logicalfilename = g.logical_file_name
and time_collected=(select max(time_collected) from capacity_datafile_info) order by d.server_name,d.db_name,d.groupid
/**find fastest growing files**/
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_FileGrowthRate')
DROP TABLE ##_FileGrowthRate
CREATE TABLE ##_FileGrowthRate
(
Filename varchar(50)
, StartSpaceUsedMB bigint
, EndSpaceUsedMB bigint
, TimeinDays int
, DiffInMBperDay real
, DiffInMB real
, diffinmins real
,diffinhours real
,diffindays real
)
insert into ##_FileGrowthRate(filename) (select distinct logicalfilename from capacity_datafile_info)
update ##_FileGrowthRate set timeindays = DATEDIFF(day, (select min(time_collected) from capacity_datafile_info), (select max(time_collected) from capacity_datafile_info))
update ##_FileGrowthRate set StartSpaceUsedMB = filespaceusedmb from capacity_datafile_info where time_collected = (select min(time_collected) from capacity_datafile_info) and ##_FileGrowthRate.filename = capacity_datafile_info.logicalfilename
update ##_FileGrowthRate set EndSpaceUsedMB = filespaceusedmb from capacity_datafile_info where time_collected = (select max(time_collected) from capacity_datafile_info) and ##_FileGrowthRate.filename = capacity_datafile_info.logicalfilename
update ##_FileGrowthRate set DiffInMB = EndSpaceUsedMB - StartSpaceUsedMB
update ##_FileGrowthRate set diffinmins = DATEDIFF(minute, (select min(time_collected) from capacity_datafile_info), (select max(time_collected) from capacity_datafile_info))
update ##_FileGrowthRate set diffinhours = diffinmins/60
update ##_FileGrowthRate set diffindays = diffinhours/24
update ##_FileGrowthRate set DiffInMBPerDay = diffinmb/diffindays
--select * from ##_FileGrowthRate
--select filespaceusedmb from capacity_datafile_info where time_collected = (select max(time_collected) from capacity_datafile_info)
Print 'Datafile Growth Rate (tempdb and log files exclude):'
print''
select --top(10)
filename, diffinmbperday as growthMBperDay, timeindays as TimePeriodInDays from ##_FileGrowthRate where filename not like 'temp%' and filename not like '%log%' order by diffinMBperday desc
Print 'total growth rate (excluding tempdb and logs)of data on this server in MB per day:'
select sum (diffinmbperday) from ##_FileGrowthRate where filename not like 'temp%' and filename not like '%log%'
Print 'growth rate of tempdb and logfiles in MB per day:'
select filename,sum (diffinmbperday) from ##_FileGrowthRate where filename like 'temp%' or filename like '%log%' group by filename
Print 'Oldest active transactions:'
print ''
dbcc opentran
print''
print'Current trasaction log space:'
dbcc sqlperf(logspace)
print''
-------------------------------------------------------------------------------------------
Print 'Most Recent Database Backup for Each Database'
Print''
-------------------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
ORDER BY
msdb.dbo.backupset.database_name
Print 'Databases where backup is older than 24 hours'
Print''
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]
FROM msdb.dbo.backupset
WHERE msdb.dbo.backupset.type = 'D'
GROUP BY msdb.dbo.backupset.database_name
HAVING (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))
UNION
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
master.dbo.sysdatabases.NAME AS database_name,
NULL AS [Last Data Backup Date],
9999 AS [Backup Age (Hours)]
FROM
master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
ON master.dbo.sysdatabases.name = msdb.dbo.backupset.database_name
WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb'
ORDER BY
msdb.dbo.backupset.database_name
declare @serverversion varchar (20)
set @serverversion = convert (varchar (20), serverproperty('productversion'))
if (@serverversion like '9%')
begin
Print 'Top queries in cache by last execution time:'
Print ''
USE MASTER
SELECT TOP 15 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) as QUERY,
qs.execution_count,
--qs.total_logical_reads, qs.last_logical_reads,
qs.total_elapsed_time,
qs.last_elapsed_time,
--qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time
--qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.last_elapsed_time DESC
end
else
begin
print '***Expensive query data is not available for SQL 2000*** SKIPPED EXPENSIVE QUERY ANALYSIS'
end
use dxdba
FOR 2000:
USE DXDBA
SET ANSI_PADDING OFF
set nocount on
declare @disk_space_start_date datetime
declare @disk_space_end_date datetime
declare @disk_space_time_period numeric(2,0)
Print @@servername+' Capacity Report'+' ' SELECT GETDATE()
Print''
Print 'SQL Server Version:' Select serverproperty('productversion')
Print 'SQL Server Service Pack' Select serverproperty('productlevel')
/**calculate transactions/sec**/
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_temp_Transactions_and_memory')
DROP TABLE ##_temp_Transactions_and_memory
CREATE TABLE ##_temp_Transactions_and_memory(
[Transactions] [varchar](100) NULL,
[Timecollected] [datetime])
insert into ##_temp_Transactions_and_memory
select top 2 Transactions, Timecollected from CAPACITY_Transactions_and_memory order by timecollected desc
declare @transactions_timediff bigint
declare @transactions_timestart datetime
declare @transactions_timeend datetime
declare @transaction_start bigint
declare @transaction_end bigint
declare @transaction_dif bigint
declare @transactions_persec numeric(10,2)
--select @transaction_start =min(convert(numeric(10,2),transactions)) from ##_temp_Transactions_and_memory
select @transaction_start =min(convert(decimal,transactions)) from ##_temp_Transactions_and_memory
select @transaction_end = max(convert(decimal,transactions)) from ##_temp_Transactions_and_memory
set @transaction_dif = @transaction_end - @transaction_start
--select * from ##_temp_Transactions_and_memory
select @transactions_timestart = min(timecollected) from ##_temp_Transactions_and_memory
select @transactions_timeend = max(timecollected) from ##_temp_Transactions_and_memory
set @transactions_timediff = datediff(second, @transactions_timestart,@transactions_timeend)
/**output transactions/sec and memory info**/
Print 'Memory and Load Information:'
Print ''
select @transactions_persec = convert(numeric(10,2),@transaction_dif) /convert(numeric(10,2),@transactions_timediff)
select @transactions_timestart as StartSnap, @transactions_timeend as EndSnap,@transactions_persec as TransactionsPerSecond,
buffercachehitratio as BufferCacheHitRatio, sqlservermemory as CurrentSQLMemory, SqlServerOptimalMemory as SqlServerOptimalMemory
, @transaction_dif as numberoftransactions, @transactions_timediff as numberofseconds
from CAPACITY_Transactions_and_memory where timecollected = (select max(timecollected) from CAPACITY_Transactions_and_memory)
/**end Memory section**/
exec LOG_FILESTATS
Print 'I/O Statistics'
/** to get IO sample now, "exec LOG_FILESTATS"**/
/**to clear old IO data and take a new sample now "exec LOG_FILESTATS 0"**/
--select * from TBL_FILESTATISTICS
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_IO_stats')
DROP TABLE ##_IO_stats
CREATE TABLE ##_IO_stats(
[databasename] [varchar](40) NULL,
[filename] [varchar] (40),
[durationseconds] [real],
[numberofreads] [real],
[endreads] [real],
[startreads] [real],
[endwrites] [real],
[startwrites] [real],
[numberofwrites] [real],
[totalreadwrite] [real],
readpersecond [real],
writepersecond [real],
totalreadwritepersecond [real],
endtime [datetime],
starttime [datetime])
insert into ##_IO_stats (databasename,filename,endtime, starttime)select databasename, filename,max(sampletime), min(sampletime) from TBL_FILESTATISTICS group by filename,databasename
update ##_IO_stats set durationseconds =datediff(second,starttime,endtime)
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_IO_stats2')
DROP TABLE ##_IO_stats2
create table ##_IO_stats2(
[filename] [varchar] (40),
[stat] [real])
insert into ##_IO_stats2 select filename, max(numberwrites) from TBL_FILESTATISTICS group by filename
update ##_IO_stats set endwrites= stat from ##_IO_stats2 where ##_IO_stats2.filename = ##_IO_stats.filename
truncate table ##_IO_stats2
insert into ##_IO_stats2 select filename, min(numberwrites) from TBL_FILESTATISTICS group by filename
update ##_IO_stats set startwrites= stat from ##_IO_stats2 where ##_IO_stats2.filename = ##_IO_stats.filename
truncate table ##_IO_stats2
insert into ##_IO_stats2 select filename, max(numberreads) from TBL_FILESTATISTICS group by filename
update ##_IO_stats set endreads= stat from ##_IO_stats2 where ##_IO_stats2.filename = ##_IO_stats.filename
truncate table ##_IO_stats2
insert into ##_IO_stats2 select filename, min(numberreads) from TBL_FILESTATISTICS group by filename
update ##_IO_stats set startreads= stat from ##_IO_stats2 where ##_IO_stats2.filename = ##_IO_stats.filename
truncate table ##_IO_stats2
drop table ##_IO_stats2
update ##_IO_stats set numberofreads = endreads-startreads
update ##_IO_stats set numberofwrites= endwrites-startwrites
update ##_IO_stats set totalreadwrite=numberofwrites+numberofreads
update ##_IO_stats set readpersecond =numberofreads/durationseconds
update ##_IO_stats set writepersecond =numberofwrites/durationseconds
update ##_IO_stats set totalreadwritepersecond =totalreadwrite/durationseconds
/**START SELECTS
select * from ##_IO_reads
select * from ##_IO_stats
select * from ##_IO_stats2 order by filename
select * from TBL_FILESTATISTICS
**/
select sum(totalreadwritepersecond) as Total_IO_Per_Sec_All_databases from ##_IO_stats
select top 1 durationseconds/60 as IOCaptureTimeInMinutes from ##_IO_stats
select databasename, sum(numberofwrites) as numberofwrites, sum(numberofreads) as numberofreads, sum(totalreadwrite) as totalreadwrite,sum(readpersecond)as readpersec, sum(writepersecond)as writepersec, sum(totalreadwritepersecond)as readwritepersec from ##_IO_stats group by databasename
print ''
Print 'Average and Max connections per database'
print''
select db_name, avg(num_of_connx) as AVGConnections, max(num_of_connx) as MAXConnections from capacity_connection_info group by db_name
Print 'Wait Type information (intentional waits, like Sleep exlcuded)'
print''
exec CAPACITY_Record_wait_stat_info
select top 10
wait_type "Wait Type",
wait_time / 1000 "Wait time (s)",
Convert(Decimal(12,2), (wait_time * 100.0 )
/ (select wait_time from capacity_wait_stats where wait_type ='total')) "% Waiting"
from capacity_wait_stats
where wait_type not like '%SLEEP%'
order by wait_time desc
Print'Current Disk Space Information:'
print''
Select timecollected,driveletter,totalspace/1024 as DiskSizeGB, FreeSpace/1024 as FreeSpaceGB, label from capacity_diskspacetracking where timecollected =(select max(timecollected) from capacity_diskspacetracking)
print''
Print'Disk Usage Rate:'
print''
set @disk_space_start_date = (select min(timecollected) from capacity_diskspacetracking)
set @disk_space_end_date = (select max(timecollected) from capacity_diskspacetracking)
set @disk_space_time_period = DATEDIFF(day, @disk_space_start_date, @disk_space_end_date)
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DiskUsageRate')
DROP TABLE ##_DiskUsageRate
CREATE TABLE ##_DiskUsageRate
(
DriveLetter CHAR(1)
, StartFreeSpaceMB bigint
, EndFreeSpaceMB bigint
, TimeinDays int
, DiffInMBperDay bigint
)
insert into ##_DiskUsageRate(driveletter) (select distinct driveletter from capacity_diskspacetracking)
update ##_DiskUsageRate set timeindays = DATEDIFF(day, (select min(timecollected) from capacity_diskspacetracking), (select max(timecollected) from capacity_diskspacetracking))
update ##_DiskUsageRate set StartFreeSpaceMB = freespace from capacity_diskspacetracking where timecollected = (select min(timecollected) from capacity_diskspacetracking) and ##_DiskUsageRate.driveletter = capacity_diskspacetracking.driveletter
update ##_DiskUsageRate set EndFreeSpaceMB = freespace from capacity_diskspacetracking where timecollected = (select max(timecollected) from capacity_diskspacetracking) and ##_DiskUsageRate.driveletter = capacity_diskspacetracking.driveletter
update ##_DiskUsageRate set DiffInMBPerDay = 24*(((select freespace from capacity_diskspacetracking where timecollected = (select min(timecollected) from capacity_diskspacetracking) and ##_DiskUsageRate.driveletter = capacity_diskspacetracking.driveletter)-
(select freespace from capacity_diskspacetracking where timecollected = (select max(timecollected) from capacity_diskspacetracking) and ##_DiskUsageRate.driveletter = capacity_diskspacetracking.driveletter))/
(select DATEDIFF(hour, (select min(timecollected) from capacity_diskspacetracking), (select max(timecollected) from capacity_diskspacetracking))))
--select * from capacity_diskspacetracking
--select * from dbo.SYSFILES
select driveletter, Diffinmbperday as Growth_rate_MB_per_day, TimeinDays as TimePeriod_days from ##_DiskUsageRate
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DiskUsageRate')
DROP TABLE ##_DiskUsageRate
/**Print 'Historical Disk Space Usage'
Select timecollected as startdate, driveletter,totalspace/1024 as DiskSizeGB, freespace/1024 as initialfreespace, label from capacity_diskspacetracking where timecollected = @disk_space_start_date
**/
print 'Current datafile sizes and usage:'
print''
select distinct d.server_name, d.db_name, d.groupid,d.logicalfilename, d.OSfilename, d.filesizemb, d.filespaceusedmb,
d.filespaceavailablemb, d.filepercentused, g.growth_factor, d.time_collected
from capacity_datafile_info d, capacity_db_growth g
where d.logicalfilename = g.logical_file_name
and time_collected=(select max(time_collected) from capacity_datafile_info) order by d.server_name,d.db_name,d.groupid
/**find fastest growing files**/
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_FileGrowthRate')
DROP TABLE ##_FileGrowthRate
CREATE TABLE ##_FileGrowthRate
(
Filename varchar(50)
, StartSpaceUsedMB bigint
, EndSpaceUsedMB bigint
, TimeinDays int
, DiffInMBperDay real
, DiffInMB real
, diffinmins real
,diffinhours real
,diffindays real
)
insert into ##_FileGrowthRate(filename) (select distinct logicalfilename from capacity_datafile_info)
update ##_FileGrowthRate set timeindays = DATEDIFF(day, (select min(time_collected) from capacity_datafile_info), (select max(time_collected) from capacity_datafile_info))
update ##_FileGrowthRate set StartSpaceUsedMB = filespaceusedmb from capacity_datafile_info where time_collected = (select min(time_collected) from capacity_datafile_info) and ##_FileGrowthRate.filename = capacity_datafile_info.logicalfilename
update ##_FileGrowthRate set EndSpaceUsedMB = filespaceusedmb from capacity_datafile_info where time_collected = (select max(time_collected) from capacity_datafile_info) and ##_FileGrowthRate.filename = capacity_datafile_info.logicalfilename
update ##_FileGrowthRate set DiffInMB = EndSpaceUsedMB - StartSpaceUsedMB
update ##_FileGrowthRate set diffinmins = DATEDIFF(minute, (select min(time_collected) from capacity_datafile_info), (select max(time_collected) from capacity_datafile_info))
update ##_FileGrowthRate set diffinhours = diffinmins/60
update ##_FileGrowthRate set diffindays = diffinhours/24
update ##_FileGrowthRate set DiffInMBPerDay = diffinmb/diffindays
--select * from ##_FileGrowthRate
--select filespaceusedmb from capacity_datafile_info where time_collected = (select max(time_collected) from capacity_datafile_info)
Print 'Datafile Growth Rate (tempdb and log files exclude):'
print''
select --top(10)
filename, diffinmbperday as growthMBperDay, timeindays as TimePeriodInDays from ##_FileGrowthRate where filename not like 'temp%' and filename not like '%log%' order by diffinMBperday desc
Print 'total growth rate (excluding tempdb and logs)of data on this server in MB per day:'
select sum (diffinmbperday) from ##_FileGrowthRate where filename not like 'temp%' and filename not like '%log%'
Print 'growth rate of tempdb and logfiles in MB per day:'
select filename,sum (diffinmbperday) from ##_FileGrowthRate where filename like 'temp%' or filename like '%log%' group by filename
Print 'Oldest active transactions:'
print ''
dbcc opentran
print''
print'Current trasaction log space:'
dbcc sqlperf(logspace)
print''
-------------------------------------------------------------------------------------------
Print 'Most Recent Database Backup for Each Database'
Print''
-------------------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
ORDER BY
msdb.dbo.backupset.database_name
Print 'Databases where backup is older than 24 hours'
Print''
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]
FROM msdb.dbo.backupset
WHERE msdb.dbo.backupset.type = 'D'
GROUP BY msdb.dbo.backupset.database_name
HAVING (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))
UNION
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
master.dbo.sysdatabases.NAME AS database_name,
NULL AS [Last Data Backup Date],
9999 AS [Backup Age (Hours)]
FROM
master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
ON master.dbo.sysdatabases.name = msdb.dbo.backupset.database_name
WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb'
ORDER BY
msdb.dbo.backupset.database_name
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply