Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: 2 Questions

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    4,186

    Default 2 Questions

    I have a few nagging questions that I hope someone can provide some clarity on:

    1. If running in 32-bit (ie DF19.1 or DF20.0-32bit) and connecting to MS SQL which is 64-bit, are you constrained by the 2GB file size limit per table, or is this negated by using SQL and thereby file size is determined by SQL itself?


    2. If running on SQL Express (which has a max file size of 10GB), what happens when you reach that maximum?

  2. #2
    Join Date
    Feb 2009
    Location
    South Florida
    Posts
    4,727

    Default Re: 2 Questions

    SQL file size is not limited by the client app running 32bit

    as far as i know (not from actual experience but from research) if you run into the size limit in SQL Express you will get errors from sql
    Michael Salzlechner
    StarZen Technologies, Inc
    http.://www.starzen.com

    Development Blog
    http://www.salzlechner.com/dev

    DataFlex Package Manager (aka Nuget for DataFlex)
    http://windowsdeveloper.com/dfPackage

  3. #3
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    4,186

    Default Re: 2 Questions

    Thanks Michael

    I was assuming the file size was dictated by SQL so thanks for helping to set my mind at rest there.

    It seems like only a matter of time before some of my customers hit the 10gb limit, so I am going to look into figuring out how I can warn them but ONLY if they are running SQL Express.... Likely there is an SQL function I can call to determine if the version of SQL the customer is running has a limit. I'll post back here if/when I find something useful.

    Mike

  4. #4
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    4,186

    Default Re: 2 Questions

    So far, I found this:

    I just look little deep into your questions, here i found some interesting stuff; both the queries mentioned below will return the same output;
    --1)
    SELECT DB_NAME(database_id) AS DatabaseName,Name AS Logical_Name,
    Physical_Name, (size*8)/1024 AS SizeMB,max_size
    FROM sys.master_files
    WHERE DB_NAME(database_id) = 'AdventureWorks'

    --2)
    SELECT DB_NAME() AS DataBaseName,Name AS LogicalName
    ,Physical_Name,(size*8)/1024 AS SizeMB,Max_Size
    FROM sys.database_files


    This first query method was used internally by 'sp_databases' to return DatabseName , DatabaseSize and Remarks if any.
    Now moving to your questions, both of above mentioned tables contained column Max_Size.
    Maximum file size, in 8-KB pages:
    0 = No growth is allowed.
    -1 = File will grow until the disk is full.
    268435456 = Log file will grow to a maximum size of 2 TB.
    You can also re-verify this from below MSDN link;
    http://msdn.microsoft.com/en-us/library/ms186782.aspx
    So, appearently it didn't seems that MS SQL Server uses some table to store this size limit. We can only verify it by attaching a DB File '.mdf' which must be greater than 10 GB to verify how SQL Server responds to that.

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks,
    I ran this on my one production server running SQL Express, and it too indicates that Max_Size will grow until the disk is full... which isn't what I would expect when running SQL Express... Returning to the drawing board.

  5. #5
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    4,186

    Default Re: 2 Questions

    I found this:

    SELECT SERVERPROPERTY('Edition');
    It seems that I can use this to determine if they are running Express and then the prior call to figure out is the size is getting close to the max

  6. #6
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    4,186

    Default Re: 2 Questions

    OK... I think I can build this in when my product starts:

    Code:
    SELECT SERVERPROPERTY('Edition');
    SELECT SERVERPROPERTY('EngineEdition');
    SELECT SERVERPROPERTY('ProductVersion');
    
    
    --SELECT DB_NAME(database_id) AS DatabaseName,Name AS Logical_Name,
    --Physical_Name, (size*8)/1024 AS SizeMB,max_size
    --FROM sys.master_files
    --WHERE DB_NAME(database_id) = 'WISH_WCS-WR'
    
    
    SELECT DB_NAME() AS DataBaseName,Name AS LogicalName
    ,Physical_Name,(size*8)/1024 AS SizeMB,Max_Size
    FROM sys.database_files
    If EngineEdition returns "4", then it is Express....

    If it is Express, I can retrieve the "ProductVersion" to determine if it will have a 2GB or 10GB limit

    Then I can compare the limit with the SizeMB returned for the LogicalName

    If the user is running Express and close the the limitation, I can warn them before the Sh*t hits the fan.

  7. #7
    Join Date
    Oct 2019
    Posts
    23

    Default Re: 2 Questions

    Hi Mike,

    Code:
    SELECT
    CASE
    WHEN SERVERPROPERTY ('EngineEdition') = 4 -- 4 = Express Edition
    THEN
    CASE
    WHEN (SELECT max_size FROM sys.database_files WHERE file_id =1)=-1
    THEN
    CASE
    WHEN CONVERT(REAL,CONVERT(VARCHAR(5),SUBSTRING(CONVERT(VARCHAR(5),SERVERPROPERTY('ProductVersion')), 1, CHARINDEX('.', CONVERT(VARCHAR(5),SERVERPROPERTY('ProductVersion')))-1))) < 10.5 -- Not SQL Server R2
    THEN
    'You have got '
    + CONVERT(VARCHAR(38), (SELECT 4096-size/128 FROM sys.database_files WHERE file_id =1))
    +' Mb '
    +'('
    + CONVERT(VARCHAR(38), CONVERT(int, 100*CONVERT(float, (SELECT 4096 - size/128 FROM sys.database_files WHERE file_id =1))
    
    
    /
    4096))
    + '%) available.'
    + ' You may increase your database size up to 4Gb.'
    ELSE -- You have got SQL Server R2
    'You have got '
    + CONVERT(VARCHAR(38), (SELECT 10240-size/128 FROM sys.database_files WHERE file_id =1))
    +' Mb'
    +'('
    + CONVERT(VARCHAR(38), CONVERT(int, 100*CONVERT(float, (SELECT 10240 - size/128 FROM sys.database_files WHERE file_id =1))
    /
    10240))
    + '%) available.'
    + ' You may increase your database size up to 10Gb.'
    END
    ELSE
    CASE
    WHEN CONVERT(REAL,CONVERT(VARCHAR(5),SUBSTRING(CONVERT(VARCHAR(5),SERVERPROPERTY('ProductVersion')), 1, CHARINDEX('.', CONVERT(VARCHAR(5),SERVERPROPERTY('ProductVersion')))-1))) < 10.5 -- Not SQL Server R2
    THEN
    'You have got '
    + CONVERT(VARCHAR(38), (SELECT max_size - size FROM sys.database_files WHERE file_id =1)/128)
    +' Mb left out of '
    +' Mb ('
    + CONVERT(VARCHAR(38), CONVERT(int, 100*CONVERT(float, (SELECT max_size - size FROM sys.database_files WHERE file_id =1))
    /
    CONVERT(float,(SELECT max_size FROM sys.database_files WHERE file_id =1))))
    + '%).'
    + ' You may increase your database size up to 4Gb.'
    ELSE -- You have got SQL Server R2
    'You have got '
    + CONVERT(VARCHAR(38), (SELECT max_size - size FROM sys.database_files WHERE file_id =1)/128)
    +' Mb left out of '
    + CONVERT(VARCHAR(38), (SELECT max_size FROM sys.database_files WHERE file_id =1)/128)
    +' Mb ('
    + CONVERT(VARCHAR(38), CONVERT(int, 100*CONVERT(float, (SELECT max_size - size FROM sys.database_files WHERE file_id =1))
    /
    CONVERT(float,(SELECT max_size FROM sys.database_files WHERE file_id =1))))
    + '%).'
    + ' You may increase your database size up to 10Gb.'
    END
    END
    ELSE -- Congratulations! You have got something better than Express Edition!
    CASE
    WHEN (SELECT max_size FROM sys.database_files WHERE file_id =1)=-1
    THEN
    'Main file will grow until the disk is full.'
    ELSE
    'You have got '
    + CONVERT(VARCHAR(38), (SELECT max_size - size FROM sys.database_files WHERE file_id =1)/128)
    +' Mb left out of '
    + CONVERT(VARCHAR(38), (SELECT max_size FROM sys.database_files WHERE file_id =1)/128)
    +' Mb ('
    + CONVERT(VARCHAR(38), CONVERT(int, 100*CONVERT(float, (SELECT max_size - size FROM sys.database_files WHERE file_id =1))
    /
    CONVERT(float,(SELECT max_size FROM sys.database_files WHERE file_id =1))))
    + '%)'
    END
    END
    
    
    AS
    Database_Info

  8. #8
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    4,186

    Default Re: 2 Questions

    Nice! Thank you.

  9. #9
    Join Date
    Oct 2019
    Posts
    23

    Default Re: 2 Questions

    Hi Mike,
    i've found this on the web
    "Microsoft SQL Server Express has a 10 GB maximum database size and other important limits. High load scenarios are not supported by Express. Symptoms can include database connection errors."
    HTH
    AnnaM

  10. #10
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    4,186

    Default Re: 2 Questions

    Thanks Anna

    I was hoping to find a way to jump in before any of my customers who happen to be running Express hit the max.

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •