Microsoft Dynamics Forums Homepage

Forum Home Forum Home > Microsoft Dynamics GP (Great Plains) > GP - Installation and Administration
  New Posts New Posts RSS Feed - Database Capacity Exceeded!
  FAQ FAQ  Forum Search   Register Register  Login Login


Database Capacity Exceeded!

 Post Reply Post Reply
Author
Message
freelyn View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: April 01 2007
Location: Philippines
Status: Offline
Points: 1
Post Options Post Options   Thanks (0) Thanks(0)   Quote freelyn Quote  Post ReplyReply Direct Link To This Post Topic: Database Capacity Exceeded!
    Posted: April 01 2007 at 4:43am
Pls. help! One of our companies in GP-7 returned dis error: "This company's database has exceeded the 10GB size limit...."

How can i solve this problem?I've read that i need to back-up my database, and then do check links, then Reconcile. However, i don't know what to reconcile, whether System or Purchasing, or what......Pls. do HEEEELLLLP! Cry
Back to Top
Sponsored Links


Back to Top
inquirer View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: April 02 2007
Location: United States
Status: Offline
Points: 7
Post Options Post Options   Thanks (0) Thanks(0)   Quote inquirer Quote  Post ReplyReply Direct Link To This Post Posted: April 02 2007 at 9:44am
hi,

surprising.. is this (10GB) a limitation of dynamics gp-7? what about for dynamics gp 8?

thanks
Back to Top
rjavins View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: November 16 2006
Location: United States
Status: Offline
Points: 48
Post Options Post Options   Thanks (0) Thanks(0)   Quote rjavins Quote  Post ReplyReply Direct Link To This Post Posted: April 05 2007 at 8:03am
The 10gig limitation was imposed on GP Standard installations that choose to use MS SQL Standard software and not the MSDE.  There is not database size limitation if GP Professional was purchased.
 
GP Standard on MSDE is limited to the MSDE size limitations.
GP Standard on MS SQL Standard is limited to a company database size of 10gig.
GP Professional (only available for MS SQL Standard or Enterprise) - no size limitation.
 
Database can be archived and then data removed from existing database to decrease the size of the database.  OR pay to upgrade to GP Professional.
Back to Top
rico View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: September 17 2007
Location: Philippines
Status: Offline
Points: 1
Post Options Post Options   Thanks (0) Thanks(0)   Quote rico Quote  Post ReplyReply Direct Link To This Post Posted: September 18 2007 at 12:52am
Good Day!!!

Sorry for the very late reply but I hope this might help somehow...

GP 7 originally has a limitation in Storage capacity per company which is 10GB and was removed in GP Version 7.5 and up.

The same problem was also encountered before and needed immediate attention so what we did was to check the SQL structure/flow of the GP 7 from how it works in User login and company login and this is what we found out...
After the company login, GP 7 will check the company database using "sp_spaceused" stored procedure and validate the storage capacity internally in GP application. By modifying "sp_spaceused" we can tell GP 7 Application that the company which exceeded the space capacity is now being replaced by the dummy company database capacity which is less than 10GB. By using the modified stored procedure we can continue making transactions in GP 7 even though it exceeds its limited capacity. Pls. Check codes below:

ALTER procedure sp_spaceused --- 1996/08/20 17:01
@objname nvarchar(776) = null,          -- The object we want size on.
@updateusage varchar(5) = false          -- Param. for specifying that
                         -- usage info. should be updated.
as

declare @id     int               -- The object id of @objname.
declare @type     character(2) -- The object type.
declare     @pages     int               -- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage     dec(15,0)
declare @pagesperMB          dec(15,0)

/*Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
     rows          int null,
     reserved     dec(15) null,
     data          dec(15) null,
     indexp          dec(15) null,
     unused          dec(15) null
)

/*
** Check to see if user wants usages updated.
*/

if @updateusage is not null
     begin
          select @updateusage=lower(@updateusage)

          if @updateusage not in ('true','false')
               begin
                    raiserror(15143,-1,-1,@updateusage)
                    return(1)
               end
     end
/*
** Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin

     select @dbname = parsename(@objname, 3)

     if @dbname is not null and @dbname <> db_name()
          begin
               raiserror(15250,-1,-1)
               return (1)
          end

     if @dbname is null
          select @dbname = db_name()

/*88888888888888888888888888888888*/
--//NOT INCLUDED IN ORIGINAL SCRIPT
if @dbname = 'LMRKT'--//Database associated with the company being used in GP 7 that exceeds 10GB
     SET @dbname = 'LTEST' --//Dummy database in GP 7 which is less than 10GB
/*88888888888888888888888888888888*/

     /*
     ** Try to find the object.
     */
     select @id = null
     select @id = id, @type = xtype
          from sysobjects
               where id = object_id(@objname)

     /*
     ** Does the object exist?
     */
     if @id is null
          begin
               raiserror(15009,-1,-1,@objname,@dbname)
               return (1)
          end


     if not exists (select * from sysindexes
                    where @id = id and indid < 2)

          if      @type in ('P ','D ','R ','TR','C ','RF') --data stored in sysprocedures
                    begin
                         raiserror(15234,-1,-1)
                         return (1)
                    end
          else if @type = 'V ' -- View => no physical data storage.
                    begin
                         raiserror(15235,-1,-1)
                         return (1)
                    end
          else if @type in ('PK','UQ') -- no physical data storage. --?!?! too many similar messages
                    begin
                         raiserror(15064,-1,-1)
                         return (1)
                    end
          else if @type = 'F ' -- FK => no physical data storage.
                    begin
                         raiserror(15275,-1,-1)
                         return (1)
                    end
end

/*
** Update usages if user specified to do so.
*/

if @updateusage = 'true'
     begin
          if @objname is null
               dbcc updateusage(0) with no_infomsgs
          else
               dbcc updateusage(0,@objname) with no_infomsgs
          print ' '
     end


set nocount on

/*
** If @id is null, then we want summary data.
*/
/*     Space used calculated in the following way
**     @dbsize = Pages used
**     @bytesperpage = d.low (where d = master.dbo.spt_values) is
**     the # of bytes per page when d.type = 'E' and
**     d.number = 1.
**     Size = @dbsize * d.low / (1048576 (OR 1 MB))
*/
if @id is null
begin
     select @dbsize = sum(convert(dec(15),size))
          from dbo.sysfiles
          where (status & 64 = 0)

     select @logsize = sum(convert(dec(15),size))
          from dbo.sysfiles
          where (status & 64 <> 0)

     select @bytesperpage = low
          from master.dbo.spt_values
          where number = 1
               and type = 'E'
     select @pagesperMB = 1048576 / @bytesperpage

     select database_name = db_name(),
          database_size =
               ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),
          'unallocated space' =
               ltrim(str((@dbsize -
                    (select sum(convert(dec(15),reserved))
                         from sysindexes
                              where indid in (0, 1, 255)
                    )) / @pagesperMB,15,2)+ ' MB')

     print ' '
     /*
     ** Now calculate the summary data.
     ** reserved: sum(reserved) where indid in (0, 1, 255)
     */
     insert into #spt_space (reserved)
          select sum(convert(dec(15),reserved))
               from sysindexes
                    where indid in (0, 1, 255)

     /*
     ** data: sum(dpages) where indid < 2
     **     + sum(used) where indid = 255 (text)
     */
     select @pages = sum(convert(dec(15),dpages))
               from sysindexes
                    where indid < 2
     select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
          from sysindexes
               where indid = 255
     update #spt_space
          set data = @pages


     /* index: sum(used) where indid in (0, 1, 255) - data */
     update #spt_space
          set indexp = (select sum(convert(dec(15),used))
                    from sysindexes
                         where indid in (0, 1, 255))
                   - data

     /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
     update #spt_space
          set unused = reserved
                    - (select sum(convert(dec(15),used))
                         from sysindexes
                              where indid in (0, 1, 255))

     select reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
                    ' ' + 'KB'),
          data = ltrim(str(data * d.low / 1024.,15,0) +
                    ' ' + 'KB'),
          index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
                    ' ' + 'KB'),
          unused = ltrim(str(unused * d.low / 1024.,15,0) +
                    ' ' + 'KB')
          from #spt_space, master.dbo.spt_values d
          where d.number = 1
               and d.type = 'E'
end

/*
** We want a particular object.
*/
else
begin
     /*
     ** Now calculate the summary data.
     ** reserved: sum(reserved) where indid in (0, 1, 255)
     */
     insert into #spt_space (reserved)
          select sum(reserved)
               from sysindexes
                    where indid in (0, 1, 255)
                         and id = @id

     /*
     ** data: sum(dpages) where indid < 2
     **     + sum(used) where indid = 255 (text)
     */
     select @pages = sum(dpages)
               from sysindexes
                    where indid < 2
                         and id = @id
     select @pages = @pages + isnull(sum(used), 0)
          from sysindexes
               where indid = 255
                    and id = @id
     update #spt_space
          set data = @pages


     /* index: sum(used) where indid in (0, 1, 255) - data */
     update #spt_space
          set indexp = (select sum(used)
                    from sysindexes
                         where indid in (0, 1, 255)
                              and id = @id)
                   - data

     /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
     update #spt_space
          set unused = reserved
                    - (select sum(used)
                         from sysindexes
                              where indid in (0, 1, 255)
                                   and id = @id)
     update #spt_space
          set rows = i.rows
               from sysindexes i
                    where i.indid < 2
                         and i.id = @id

     select name = object_name(@id),
          rows = convert(char(11), rows),
          reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
                    ' ' + 'KB'),
          data = ltrim(str(data * d.low / 1024.,15,0) +
                    ' ' + 'KB'),
          index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
                    ' ' + 'KB'),
          unused = ltrim(str(unused * d.low / 1024.,15,0) +
                    ' ' + 'KB')
     from #spt_space, master.dbo.spt_values d
          where d.number = 1
               and d.type = 'E'
end

return (0) -- sp_spaceused

GO
Note:
    *Analyze script first!!!
    *BackUp original script before executing this script.

Code Disclaimer information

The sample code I provided is for illustrative purposes only. These examples have not
been thoroughly tested under all conditions. I, therefore, cannot guarantee or imply
reliability,serviceability, or function of these code.
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down


Copyright 2013 microsoftdynamicsforums.com. All rights reserved. MicrosoftDynamicsForums.com is an independent non-Microsoft website.
Email: contact AT microsoftdynamicsforums DOT com