如何检查数据库是否达到了大小限制？ [英] How do I check if I have hit the size limit on the Database ?

问题描述

我有这个应用程序，它将大量数据插入到SQL Server 2008 R2 Express版本的数据库中。据我所知，数据库大小的上限是10GB。现在，我需要告知用户他是否达到数据库大小的上限，同时尝试插入数据。



上限时是否抛出异常被破坏了？我如何基本上检测到这种DB大小超过的情况？我使用C＃4.5和Enterprise库进行数据库交互。



对此事的任何建议都非常感谢。



谢谢。

Hi,

I have this application which inserts huge data to a database on SQL server 2008 R2 express edition. I understand that the upper limit on the size of the database is 10GB. Now, I need to inform the user if he hits the upper limit of the DB size, while trying to insert data.

Is there an exception thrown when the upper limit is breached ? How do I basically detect such a case of DB size exceed ? I use C# 4.5 and Enterprise library for DB interactions.

Any advise on this matter is highly appreciated.

Thanks.

推荐答案

如果您尝试将数据插入数据库并且数据已满，则会引发异常。显然这并不理想，因此您希望监视数据库大小。最好的方法是查询系统视图以了解数据库的大小。



此查询：

If you try to insert data into a database and it is full, it will throw an exception. Obviously this isn't ideal, so you want to be monitoring the database size. The best way to do this is to query the system views to find out how large your database is.

This query:

SELECT *
FROM sys.database_files

将返回有关您连接的当前数据库的详细信息。有一个名为 size 的列，它具有当前分配的数据库大小（以KB为单位）。请记住，SQL Server将分配 x 数量的数据库空间，因此即使已达到最大大小，文件中也将有可用空间。



我写了这个查询来帮助显示数据库文件中有多少可用空间。您需要使用函数 FILEPROPERTY 并传递值SpaceUsed。通过此查询，我还将所有大小转换为千兆字节。

Will return the details about the current database you connected to. There is a column called size which has the current allocated size of your database in kilobytes. Remember that SQL Server will allocate x amount of database space so you will have free space within the file, even though it has hit the max size.

I wrote this query to help show how much space is available in a database file. You need to use the function FILEPROPERTY and pass the value "SpaceUsed". With this query, I also convert all of the sizes into gigabytes.

SELECT
    name
   ,[filename]
   ,CONVERT(DECIMAL(12, 2), ROUND((size / 128.000) / 1024, 2)) AS [File Size in GB]
   ,CONVERT(DECIMAL(12, 2), ROUND((FILEPROPERTY(name, 'SpaceUsed') / 128.000)
                                  / 1024, 2)) AS [Space Used in GB]
   ,CONVERT(DECIMAL(12, 2), ROUND(((size - FILEPROPERTY(name, 'SpaceUsed'))
                                   / 128.000) / 1024, 2)) AS [Free Space in GB]
   ,CONVERT(DECIMAL(4, 1), ROUND(CASE WHEN CONVERT(DECIMAL(12, 2), ROUND((size
                                                              / 128.000)
                                                              / 1024, 2)) < 1
                                      THEN 100.00
                                      ELSE CONVERT(DECIMAL(12, 2), ROUND(((size
                                                              - FILEPROPERTY(name,
                                                              'SpaceUsed'))
                                                              / 128.000)
                                                              / 1024, 2))
                                           / CONVERT(DECIMAL(12, 2), ROUND((size
                                                              / 128.000)
                                                              / 1024, 2))
                                           * 100
                                 END, 1)) AS [Percent Free]
FROM
    sys.sysfiles

这篇关于如何检查数据库是否达到了大小限制？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！