HomeHomeOur CommunityOur CommunityGeneral Discuss...General Discuss...Database size is hugeDatabase size is huge
Previous
 
Next
New Post
7/9/2006 5:27 AM
 
I have a DNN database that is almost 3000MB (6GB total with logs). I've had databases this large before, but I'm not sure why. It could be the forums which get maybe 100 messages a day and I don't think Active Forums ever deletes old forums... we have it set-up to use filesystem for any binary data.

Jason Honingford - Web & Software Developer
www.PortVista.com
 
New Post
7/9/2006 5:42 AM
 

Take a look at the site log and run the query from this thread. It will tell you wich database is so big. I have ActiveForums also with almost 40,000 posts and the total size is less then 150 Mb.


Mariëtte Knap
 
New Post
7/9/2006 5:53 AM
 
That must be in the private forums -- I might as well get benefactored in anyway...

Jason Honingford - Web & Software Developer
www.PortVista.com
 
New Post
7/9/2006 6:25 AM
 

Here it is but promise me to get a Benefactor subscription:

SET NOCOUNT ON

CREATE TABLE #TBLSize
 (Tblname varchar(80),
 TblRows int,
 TblReserved varchar(80),
 TblData varchar(80),
 TblIndex_Size varchar(80),
 TblUnused varchar(80))

DECLARE @DBname varchar(80)
DECLARE @tablename varchar(80)

SELECT @DBname = DB_NAME(DB_ID())
PRINT 'User Table size Report for (Server / Database):   ' + @@ServerName + ' / ' + @DBName
PRINT ''
PRINT 'By Size Descending'
DECLARE TblName_cursor CURSOR FOR
SELECT NAME
FROM sysobjects
WHERE xType = 'U'

OPEN TblName_cursor

FETCH NEXT FROM TblName_cursor
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
   INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData, TblIndex_Size, TblUnused)
   EXEC Sp_SpaceUsed @tablename
     
   -- Get the next author.
   FETCH NEXT FROM TblName_cursor
   INTO @tablename
END

CLOSE TblName_cursor
DEALLOCATE TblName_cursor

SELECT  CAST(Tblname as Varchar(30)) 'Table',
 CAST(TblRows as Varchar(14)) 'Row Count',
 CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space (KB)',
        CAST(TblData as Varchar(14)) 'Data Space',
 CAST(TblIndex_Size  as Varchar(14)) 'Index Space',
        CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Total Space (KB)' Desc

PRINT ''
PRINT 'By Table Name Alphabetical'


SELECT  CAST(Tblname as Varchar(30)) 'Table',
 CAST(TblRows as Varchar(14)) 'Row Count',
 CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space (KB)',
        CAST(TblData as Varchar(14)) 'Data Space',
 CAST(TblIndex_Size  as Varchar(14)) 'Index Space',
        CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Table'

DROP TABLE #TblSize

 


Mariëtte Knap
 
New Post
7/9/2006 7:12 AM
 
TableRow CountTotal Space (KB)Data SpaceIndex SpaceUnused Space
dnn_SiteLog551607294616287312 KB7176 KB128 KB
dnn_ScheduleHistory470242146640146376 KB88 KB176 KB
dnn_NTForums_Posts278354103234120 KB32 KB6880 KB
dnn_EventLog37113258415360 KB16 KB17208 KB

Hey thanks, I just signed up. Nice script too -- I should be able to cut my database in half anyway...

Jason Honingford - Web & Software Developer
www.PortVista.com
 
Previous
 
Next
HomeHomeOur CommunityOur CommunityGeneral Discuss...General Discuss...Database size is hugeDatabase size is huge


Forum Policy

These Discussion Forums are dedicated to the discussion of the DotNetNuke Web Application Framework.

For the benefit of the community and to protect the integrity of the project, please observe the following posting guidelines:

1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DotNetNuke.
2. Discussion or promotion of DotNetNuke product releases under a different brand name are strictly prohibited.
3. No Flaming or Trolling.
4. No Profanity, Racism, or Prejudice.
5. Site Moderators have the final word on approving/removing a thread or post or comment.
6. English language posting only, please.

Attend A Webinar
Free Demo Site
Download DotNetNuke Professional Edition Trial
Have Someone Contact Me
Have Someone Contact Me
Charlotte DoDNN

Like Us on Facebook Join our Network on LinkedIn Follow DNN Corporate on Twitter Follow DNN on Twitter

Advertisers

Telerik JustCode Free
Exact Target Exec Alert
PowerDNN

DotNetNuke Scoop!

Sponsors

DotNetNuke Corporation

DotNetNuke Corp. is the steward of the DotNetNuke open source project, the most widely adopted Web Content Management Platform for building web sites and web applications on Microsoft. Organizations use DotNetNuke to quickly develop and deploy interactive and dynamic web sites, intranets, extranets and web applications. The DotNetNuke platform is available in a free Community and subscription-based Professional and Enterprise Editions with an Elite Support option. DotNetNuke Corp. also operates the DotNetNuke Store where users purchase third party apps for the platform.