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 ]