一个数据库无限增大 ,程序检测到当硬盘不够时即删掉100条数据,但数据库并不会变小,于是想用数据库的可用空间大小判断, 但苦于无法得到数据库的可用空间。 于是把数据库设定成自动收缩。却不知道什么时候收缩,而且数据库很大(上百个G),收缩要很久, 请问有什么好的方法检测空间大小。 最好不是用收缩数据库
我这里想到的方法是 计算 磁盘空间盛余大小+数据库可用空间>某个值开始删除, 直到小于该值为止
我这里想到的方法是 计算 磁盘空间盛余大小+数据库可用空间>某个值开始删除, 直到小于该值为止
Lists databases that reside in an instance of Microsoft® SQL Server™ or are accessible through a database gateway.Syntax
sp_databasesReturn Code Values
NoneResult Sets
Column name Data type Description
DATABASE_NAME sysname Name of the database. In SQL Server, this column represents the database name as stored in the sysdatabases system table.
DATABASE_SIZE int Size of database, in kilobytes.
REMARKS varchar(254) For SQL Server, this field always returns NULL.
Res
In SQL Server, sp_databases returns the databases listed in the sysdatabases system table. Because some database management systems (DBMS) accessed by database gateways do not have the concept of a database, this stored procedure may return no rows if sent to a Microsoft Open Data Services-based gateway.Database names that are returned can be used as parameters in the USE statement to change the current database context.sp_databases has no equivalent in Open Database Connectivity (ODBC).
Lists databases that reside in an instance of Microsoft® SQL Server™ or are accessible through a database gateway.Syntax
sp_databasesReturn Code Values
NoneResult Sets
Column name Data type Description
DATABASE_NAME sysname Name of the database. In SQL Server, this column represents the database name as stored in the sysdatabases system table.
DATABASE_SIZE int Size of database, in kilobytes.
REMARKS varchar(254) For SQL Server, this field always returns NULL.
Res
In SQL Server, sp_databases returns the databases listed in the sysdatabases system table. Because some database management systems (DBMS) accessed by database gateways do not have the concept of a database, this stored procedure may return no rows if sent to a Microsoft Open Data Services-based gateway.Database names that are returned can be used as parameters in the USE statement to change the current database context.sp_databases has no equivalent in Open Database Connectivity (ODBC).
列出驻留在 Microsoft® SQL Server™ 实例中的数据库或可以通过数据库网关访问的数据库。
返回值
data_basename 数据库的名称
data_basesize 数据库的大小注释
在 SQL Server 中,sp_databases 返回在 sysdatabases 系统表中列出的数据库。由于数据库网关所访问的一些数据库管理系统 (DBMS) 没有数据库概念,所以如果发送到基于 Microsoft 开放式数据服务的网关,该存储过程可能不返回任何行。所返回的数据库名称可以作为 USE 语句的参数,用来更改当前数据库上下文。sp_databases 在开放式数据库连接 (ODBC) 中没有等价的过程。
'输入参数 cn 表示到服务器的连接 strDbName 表示数据库名
'输出: 0 表示失败 非0表示成功 单位:KB
'作者: 小马哥 2003-10-24
Dim rs As ADODB.Recordset
On Error GoTo ErrOut
cn.Execute "if object_id('tempdb..#a') is not null drop table #DbSize"
cn.Execute "create table #DbSize(dbname varchar(50),dbsize int,res int)"
cn.Execute "insert into #DbSize exec ('master..sp_databases')"
Set rs = New ADODB.Recordset
rs.Open "select dbsize from #DbSize where dbname='" & strDbName & "'", cn
GetDbSize = IIf(rs.EOF, 0, rs!dbsize)
cn.Execute "if object_id('tempdb..#a') is not null drop table #DbSize"
rs.Close
Set rs = Nothing
Exit Function
ErrOut:
Set rs = Nothing
GetDbSize = 0
End Function
对我有兴趣? 把我所有问题都up了。
不过还是谢谢你
该问题早解决了