我需要提供有关数据库升级和迁移的预期文件大小的信息。
我有一个正在多个站点使用的数据库。每个站点都有不同数量的用户和记录。 SQL Server的当前版本是2008 R2。我们的新版本将部署在SQL Server 2017服务器上,其中包括对业务规则的更新,其中包括新列,表,函数和存储过程等。我被要求提供一些数据以帮助站点计划资源。我需要知道新版本中数据库的大小如何变化。
USE Old_Database;
EXEC sp_spaceused
USE New_Database
EXEC sp_spaceused
EXEC sp_helpdb N'Old_Database'
EXEC sp_helpdb N'New_Database'
USE Old_Database;
SELECT
t.NAME AS TableName,i.name AS indexName,SUM(p.rows) AS RowCounts,SUM(a.total_pages) AS TotalPages,SUM(a.used_pages) AS UsedPages,SUM(a.data_pages) AS DataPages,(SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB,(SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB,(SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%' AND
i.OBJECT_ID > 255 AND
i.index_id <= 1
GROUP BY
t.NAME,i.object_id,i.index_id,i.name
ORDER BY
OBJECT_NAME(i.object_id)
USE New_Database;
SELECT
t.NAME AS TableName,i.name
ORDER BY
OBJECT_NAME(i.object_id)
我从这些结果中得出了我认为是有效的结果,但我只是想确保没有我可能会错过的东西。