huge database

JDEboy

Active Member
gents,
i am not a DBA guy, and one of my client has XE runinig.
the business data database for production environment becoming sooo huge, this database size is 95 GB.
does any body know how to solve such issue to reduce the size, where to check exactly?, which tables should be purged? is there is any thing could be done on the SQL server level?
thanks in advance.
 
check if the log file size is ok you will find this file under z:\Microsoft SQL Server\MSSQL\Data
you will find at least 2 files for production database MDF and LDF file
check wich one is larger.
 
I hate to sound like this but 95GB is definitely not huge. Sounds kinda small to me actually.

Run this code. It will create a stored procedure that you can then run and find the size of tables in the database. This may help start you on your way. If you see any tables other than the usual suspects (F0911, F1511B, F03B11, F0902, etc.) in the list of largest tables you may need to do some cleanup.

USE master
GO

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'sp_show_huge_tables' AND type = 'P')
DROP PROC sp_show_huge_tables
GO

CREATE PROC sp_show_huge_tables
(
@top int = NULL,
@include_system_tables bit = 0
)
AS
/*************************************************************************************************
Copyright © 1997 - 2002 Narayana Vyas Kondreddi. All rights reserved.

Purpose: To list the size of all tables in the database in descending order (that is biggere tables first).
Output can be restricted to top n tables. That is you can pass the value 3 to @top parameter to
see the top 3 biggest tables in your database. Optionally, you can use @include_system_tables
parameter, to include systemt tables in the output.
NOTE: Always create this procedure in the master database and call it from the required databases,
as if the stored procedure is created in that database (That is, don't prefix the stored procedure
with the database name).

Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com

Tested on: SQL Server 7.0

Date created: January-22-2002 21:37 GMT

Date modified: February-18-2002 11:31 GMT

Email: [email protected]

Examples:

To list all the user tables in the database along with their sizes:
EXEC sp_show_huge_tables

To see the top three biggest tables in your database:
EXEC sp_show_huge_tables 3

To list all the user AND system tables in the database along with their sizes:
EXEC sp_show_huge_tables @include_system_tables = 1


To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
*************************************************************************************************/

BEGIN
IF @top > 0
SET ROWCOUNT @top

SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)] FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS [Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U') AND
((@include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))

) as a
ORDER BY [Total space used (MB)] DESC


SET ROWCOUNT 0
END

GO

GRANT EXEC ON sp_show_huge_tables TO Public



[ QUOTE ]
gents,
i am not a DBA guy, and one of my client has XE runinig.
the business data database for production environment becoming sooo huge, this database size is 95 GB.
does any body know how to solve such issue to reduce the size, where to check exactly?, which tables should be purged? is there is any thing could be done on the SQL server level?
thanks in advance.

[/ QUOTE ]
 
Another thought. Not sure if your client is running database maintenance but you can run DBCC SHRINKDATABASE ('JDE_PRODUCTION') to reclaim space. They should be running this in a maintenance plan but you can run it manually.



[ QUOTE ]
gents,
i am not a DBA guy, and one of my client has XE runinig.
the business data database for production environment becoming sooo huge, this database size is 95 GB.
does any body know how to solve such issue to reduce the size, where to check exactly?, which tables should be purged? is there is any thing could be done on the SQL server level?
thanks in advance.

[/ QUOTE ]
 
Suggest to your client that maybe they should look at archive/purge software.
 
[ QUOTE ]
Suggest to your client that maybe they should look at archive/purge software.

[/ QUOTE ]

We are fairly close to purchasing Princeton Softec's purge and archive solution. I haven't seen it yet, but my boss is pretty impressed with the product.
 
Definitely not so huge. We have *individual* tables that have approached that size due to a lack of purging and regular maintenance routines. I'm going to back up the others who've recommended archiving/purging.

We are also looking at Princeton; they seem to have the best canned archiving solution for our needs. Others will likely want to send in consultants to custom tailor a solution...many of whom have no real JDE experience. I know this because I've talked to the others face to face.
 
Charlie "Bubba" Anderson,

We're gonna miss your insights when you go off shrimping, bud! I agree that size is in the eye of the beholder. We have a *Terrabyte* of disk space set aside for JDE business and test databases.

Gregg Larkin
JDE System Administrator (CNC) / North America
Praxair, Inc.
 
Back
Top