CODEWITHSUNDEEP
c#databasesql-server-express
Santhosh
Santhosh

Reputation: 6707

How to retrieve the used size of a Database?

What is the best way to retrieve the used size of a (sql) Database in c#? I have access to the connection string of the database and assume I also have dbadmin access to the database.

This place suggests that I use Microsoft.SqlServer.Smo.dll, however I would like to know if it is possible to do this using the Sql* namespaces in System.Data itself? (like SqlConnection, DbConnection, etc)

Upvotes: 1

Views: 4991

Answers (4)

Sascha Hennig
Sascha Hennig

Reputation: 2572

You could just query the SQL-Server to get that information:

USE <databasename>;
GO

/* get the database size, including the log file */
exec sp_spaceused;

/* get the size of the table */
exec sp_spaceused <tablename>;

/* the actual database file sizes */
SELECT * FROM sys.database_files;

Upvotes: 4

John Weldon
John Weldon

Reputation: 40739

sp_spaceused returns allocated and unused space. ( http://msdn.microsoft.com/en-us/library/ms188776.aspx ), subtract the latter from the former to get the actual used space.

EXEC sp_spaceused

For sql server variants.

Just create a DbConnection and execute this sproc

how to query sql server database size

Upvotes: 5

Tim
Tim

Reputation: 28520

I don't think there are any direct ways to do that through the Sql.* namespaces, but you could use the ADO.NET framework to run the sp_spaceused (Transact-SQL) stored procedure.

See this SO question: how to query sql server database size

Upvotes: 2

Henk Holterman
Henk Holterman

Reputation: 273169

For an attached database you can retrieve the filename from the connection. With that you can construct the .ldf filename as well and use System.IO.File to get the filesizes.

For 'normal' server-registered dbs I wouldn't know.

Upvotes: 4

Related Questions