I would take a look at the size of your chglog table and see how much
you're consuming there.
If you want a comprehensive report of database table usage, you can
use the following stored procedure to report on the size of each table
in the database you specify. You can put this SP in your Vantage
database, or in any other database (I create a 'DBA' database to hold
this kind of stuff).
/****** Object: StoredProcedure [dbo].[dba_SpaceUsed] Script Date:
04/18/2011 16:54:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[dba_SpaceUsed]
@SourceDB varchar ( 128 ) = null -- Optional database name
-- If omitted, the current database is reported.
, @SortBy char(1) = 'S' -- N for name, S for Size
-- T for table name
/* Returns a table with the space used in all tables of the
* database. It's reported with the schema information unlike
* the system procedure sp_spaceuse.
*
* sp_spaceused is used to perform the calculations to ensure
* that the numbers match what SQL Server would report.
*
* Compatible with sQL Server 2000 and 2005
*
* Example:
exec dbo.dba_SpaceUsed null, 'N'
*
* (c) Copyright 2007 Andrew Novick
http://www.NovickSoftware.com
* This software is provided as is without warrentee of any kind.
* You may use this procedure in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* procedure either in print or electronically.
******************************************************************/
AS
SET NOCOUNT ON
DECLARE @sql nvarchar (4000)
IF @SourceDB IS NULL BEGIN
SET @SourceDB = DB_NAME () -- The current DB
END
--------------------------------------------------------
-- Create and fill a list of the tables in the database.
CREATE TABLE #Tables ( [schema] sysname
, TabName sysname )
SELECT @sql = 'insert #tables ([schema], [TabName])
select TABLE_SCHEMA, TABLE_NAME
from ['+ @SourceDB
+'].INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ''BASE TABLE'''
EXEC (@sql)
---------------------------------------------------------------
-- #TabSpaceTxt Holds the results of sp_spaceused.
-- It Doesn't have Schema Info!
CREATE TABLE #TabSpaceTxt (
TabName sysname
, [Rows] varchar (11)
, Reserved varchar (18)
, Data varchar (18)
, Index_Size varchar ( 18 )
, Unused varchar ( 18 )
)
---------------------------------------------------------------
-- The result table, with numeric results and Schema name.
CREATE TABLE #TabSpace ( [Schema] sysname
, TabName sysname
, [Rows] bigint
, ReservedMB numeric(18,3)
, DataMB numeric(18,3)
, Index_SizeMB numeric(18,3)
, UnusedMB numeric(18,3)
)
DECLARE @Tab sysname -- table name
, @Sch sysname -- owner,schema
DECLARE TableCursor CURSOR FOR
SELECT [SCHEMA], TabNAME
FROM #tables
OPEN TableCursor;
FETCH TableCursor into @Sch, @Tab;
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @sql = 'exec [' + @SourceDB
+ ']..sp_executesql N''insert #TabSpaceTxt exec sp_spaceused
'
+ '''''[' + @Sch + '].[' + @Tab + ']' + '''''''';
Delete from #TabSpaceTxt; -- Stores 1 result at a time
EXEC (@sql);
INSERT INTO #TabSpace
SELECT @Sch
, [TabName]
, convert(bigint, rows)
, convert(numeric(18,3), convert(numeric(18,3),
left(reserved, len(reserved)-3)) / 1024.0)
ReservedMB
, convert(numeric(18,3), convert(numeric(18,3),
left(data, len(data)-3)) / 1024.0) DataMB
, convert(numeric(18,3), convert(numeric(18,3),
left(index_size, len(index_size)-3)) / 1024.0)
Index_SizeMB
, convert(numeric(18,3), convert(numeric(18,3),
left(unused, len([Unused])-3)) / 1024.0)
[UnusedMB]
FROM #TabSpaceTxt;
FETCH TableCursor into @Sch, @Tab;
END;
CLOSE TableCursor;
DEALLOCATE TableCursor;
-----------------------------------------------------
-- Caller specifies sort, Default is size
IF @SortBy = 'N' -- Use Schema then Table Name
SELECT * FROM #TabSpace
ORDER BY [Schema] asc, [TabName] asc
ELSE IF @SortBy = 'T' -- Table name, then schema
SELECT * FROM #TabSpace
ORDER BY [TabName] asc, [Schema] asc
ELSE -- S, NULL, or whatever get's the default
SELECT * FROM #TabSpace
ORDER BY ReservedMB desc
;
DROP TABLE #Tables
DROP TABLE #TabSpaceTxt
DROP TABLE #TabSpace
-----Original Message-----
From:
vantage@yahoogroups.com [mailto:
vantage@yahoogroups.com] On Behalf
Of too_much_hg
Sent: Monday, April 18, 2011 3:00 PM
To:
vantage@yahoogroups.com
Subject: [Vantage] Rapid SQL Databse Growth - WHY ?!?
We are running 8.03.409C on SQL. - In production for about 1.5 years.
About 60 Vantage and 30 MES users.
We started out with a database size of about 3GB and it has grown on
average of about 1GB per month. I am over 20GB now.
This seems to be much more growth than one would expect for simple data
growth.
I have done a "Shrink" on the Database but it only yielded about 2GB in
the reported DB size and almost no gain in the backup size.
I suspect that there are tables filling with stuff that I don't need but
I don't know where to look or what actions to take to get this situation
under control.
Can someone give me some advice on what to do ?
Thanks,
Neil
------------------------------------
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links