phpBB3

SQL Azure does not allow SELECT FROM sysfiles

Details

Description

The ACP statistics try to list the database size retrieved with 'SELECT ((SUM(size) * 8.0) * 1024.0) as dbsize FROM sysfiles' which does not work on Azure.

Relevant code from phpBB/includes/functions_admin.php:

		case 'mssql':
		case 'mssql_odbc':
		case 'mssqlnative':
			$sql = 'SELECT ((SUM(size) * 8.0) * 1024.0) as dbsize
				FROM sysfiles';
			$result = $db->sql_query($sql, 7200);
			$database_size = ($row = $db->sql_fetchrow($result)) ? $row['dbsize'] : false;
			$db->sql_freeresult($result);
		break;

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
bibin added a comment -

sysfiles is not available in SQL Azure, but we can calculate the database size in SQL Azure using the following SQL query

SELECT ((SUM(reserved_page_count) * 8.0) * 1024.0) as dbsize FROM sys.dm_db_partition_stats;

So the fix is to check the particular SQL Server version and use the appropriate SQL Query.

Replace line no: 3079 - 3080 of includes/functions_admin.php with the below code:

$sql_version = ($row = $db->sql_fetchrow($db->sql_query('SELECT @@version as version'))) ? $row['version'] : false;
$sql = (strpos($sql_version, 'SQL Azure') !== false) ? 'SELECT ((SUM(reserved_page_count) * 8.0) * 1024.0) as dbsize FROM sys.dm_db_partition_stats' : 'SELECT ((SUM(size) * 8.0) * 1024.0) as dbsize FROM sysfiles';

Show
bibin added a comment - sysfiles is not available in SQL Azure, but we can calculate the database size in SQL Azure using the following SQL query SELECT ((SUM(reserved_page_count) * 8.0) * 1024.0) as dbsize FROM sys.dm_db_partition_stats; So the fix is to check the particular SQL Server version and use the appropriate SQL Query. Replace line no: 3079 - 3080 of includes/functions_admin.php with the below code: $sql_version = ($row = $db->sql_fetchrow($db->sql_query('SELECT @@version as version'))) ? $row ['version'] : false; $sql = (strpos($sql_version, 'SQL Azure') !== false) ? 'SELECT ((SUM(reserved_page_count) * 8.0) * 1024.0) as dbsize FROM sys.dm_db_partition_stats' : 'SELECT ((SUM(size) * 8.0) * 1024.0) as dbsize FROM sysfiles';
Hide
Permalink
Patrick Webster added a comment -

Is there some reason not to just use the partition statistics on all SQL Server types? The main difference only seems to be that sysfiles also includes the log files.

Show
Patrick Webster added a comment - Is there some reason not to just use the partition statistics on all SQL Server types? The main difference only seems to be that sysfiles also includes the log files.
Hide
Permalink
bibin added a comment -

sys.dm_db_partition_stats will work for both SQL Server and SQL Azure, but as you said, the database size never includes the log file size. If you dont want to change the existing phpBB SQL Server db size calculation logic it's better to check the SQL Server version and use the proper SQL query.

Show
bibin added a comment - sys.dm_db_partition_stats will work for both SQL Server and SQL Azure, but as you said, the database size never includes the log file size. If you dont want to change the existing phpBB SQL Server db size calculation logic it's better to check the SQL Server version and use the proper SQL query.

People

  • Assignee:
    Nils Adermann
    Reporter:
    Nils Adermann
Vote (0)
Watch (0)

Dates

  • Created:
    Updated: