January 26, 2011 at 3:04 am
Have a script that gives me the database sizes, space etc,etc but i am new to this and do not know how to enable it to export it to a csv file and the script doesnt overwrite the information in the columns, it set of data underneath the last time it was run, if someone could point out my errors i would be grateful
USE [DBSTATS]
GO
/****** Object: StoredProcedure [dbo].[usp_DB_Stats] Script Date: 01/26/2011 09:07:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:ks
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[usp_DB_Stats]
-- Add the parameters for the stored procedure here
@p1 int = 0,
@p2 int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF NOT EXISTS (SELECT 1
FROM DBSTATS.sys.tables
WHERE Name = 'DatabaseGrowth'
AND OBJECT_SCHEMA_NAME(object_id) = 'dbo'
)
BEGIN
CREATE TABLE DBSTATS.[dbo].[DatabaseGrowth]
([Database_Name] [char](128) NOT NULL,
[Database_Size_MB] DECIMAL(15,2) NOT NULL,
[Unallocated_Space_MB] DECIMAL(15,2) NOT NULL,
[Reserved_MB] DECIMAL(15,2) NOT NULL,
[Data_MB] BIGINT NOT NULL,
[Index_Size_MB] BIGINT NULL,
[Unused_MB] BIGINT NOT NULL,
[DateTimeStamp] DATETIME NOT NULL DEFAULT GETDATE(),
)
ON [PRIMARY]
END
/*
Insert the data into storage table
*/
INSERT INTO DBSTATS.dbo.DatabaseGrowth
([Database_Name]
,[Database_Size_MB]
,[Unallocated_Space_MB]
,[Reserved_MB]
,[Data_MB]
,[Index_Size_MB]
,[Unused_MB])
EXEC sp_MSforeachdb
'DECLARE
@pages BIGINT -- Working variable for size calc.
,@dbname SYSNAME
,@dbsize BIGINT
,@logsize BIGINT
,@reservedpages BIGINT
,@usedpages BIGINT
,@rowCount BIGINT
SET NOCOUNT ON
SELECT
@dbsize = SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 = 0 THEN Size ELSE 0 END))
,@logsize = SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 <> 0 THEN Size ELSE 0 END))
FROM [?].dbo.sysfiles
SELECT
@reservedpages = SUM(a.total_pages)
,@usedpages = SUM(a.used_pages)
,@pages = SUM(
CASE
/* XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size" */
WHEN it.internal_type IN (202,204,211,212,213,214,215,216) THEN 0
WHEN a.type <> 1 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages
ELSE 0
END
)
FROM [?].sys.partitions p
JOIN [?].sys.allocation_units a ON p.partition_id = a.container_id
LEFT JOIN [?].sys.internal_tables it on p.object_id = it.object_id
/* unallocated space could not be negative */
SELECT
database_name = ''?''
,database_size = (@dbsize + @logsize) * 8192 / 1048576
,''unallocated space'' = LTRIM(STR((CASE WHEN @dbsize >= @reservedpages THEN (CONVERT (dec (15,2),@dbsize) - CONVERT (DEC (15,2),@reservedpages)) * 8192 / 1048576 ELSE 0 END),15,2))
,reserved = LTRIM(STR((@reservedpages * 8192 / 1024.)/1024,15,0))
,data = LTRIM(STR((@pages * 8192 / 1024.)/1024,15,0))
,index_size = LTRIM(STR(((@usedpages - @pages) * 8192 / 1024.)/1024,15,0))
,unused = LTRIM(STR(((@reservedpages - @usedpages) * 8192 / 1024.)/1024,15,0))'
/*Show data*/
SELECT
Database_Name
,Database_Size_MB
,Unallocated_Space_MB
,Reserved_MB
,Data_MB
,Index_Size_MB
,Unused_MB
,DateTimeStamp
FROM DBSTATS.[dbo].[DatabaseGrowth]
END
January 26, 2011 at 5:23 am
The script was probably written with the intention of not writing over the data, but keeping it all for historical purposes. You can delete or truncate the data if you don't need it any more, or use a where clause to filter out older data.
You can use the export wizard to define your export to csv, then save it as an SSIS package so you can schedule it to run regularly.
January 26, 2011 at 5:43 am
Thanks for this will test it later but where exactly would i put the "where" statement?
January 26, 2011 at 7:46 am
I would think in this part. You could use GETDATE() and some date functions to return data within the last X days (or weeks etc...)
/*Show data*/
SELECT
Database_Name
,Database_Size_MB
,Unallocated_Space_MB
,Reserved_MB
,Data_MB
,Index_Size_MB
,Unused_MB
,DateTimeStamp
FROM DBSTATS.[dbo].[DatabaseGrowth]
WHERE DateTimeStamp > getdate() -7
END
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply