Products

Solutions

Resources

Partners

Community

Blog

About

QA

Ideas Test

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeOur CommunityOur CommunityGeneral Discuss...General Discuss...Database size is hugeDatabase size is huge
Previous
 
Next
New Post
7/9/2006 8: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 8: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.

 
New Post
7/9/2006 8: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 9: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

 

 
New Post
7/9/2006 10: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


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, 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 DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out