How can I tell if a SQL Server MDF file is getting full/might need to grow?

Question

I know that the transaction log/ldf file fills up and grows and that I can see how full it is by running:

DBCC SQLPERF(logspace)

Is there a corresponding command to check on the status of the data/mdf file?

---

Why I'm interested:

I'm troubleshooting a simple .NET app that uses SqlBulkCopy to import data. Normally this works fine but occassionally the app fails with due to a timeout/SqlException (included below). I've increased the BulkCopyTimeout property and that hasn't solved the problem.

Looking through the SQL Server logs I see entries like these:

Autogrow of file 'MyDatabase' in database 'MyDatabase' was cancelled by user or timed out after 29812 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

Which leads me to believe that my data file needs to grow (10% growth == a few GB) and that my bulk copy is failing while it waits for the MDF file to grow.

The .NET exception:

Unhandled Exception: System.Data.SqlClient.SqlException: Timeout expired. The t imeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea n breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj ect stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cm dHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds ParserStateObject stateObj) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount ) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowSt ate rowState)

Answer 1

You can use EXEC sp_spaceused

but keep in mind that the database_size that it returns includes both data and logs..

more info at http://msdn.microsoft.com/en-us/library/ms188776.aspx

Answer 2

select sum (total_pages) from sys.allocation_units;

sys.allocation_units keeps track of every allocated page in the database. If you want to be more specific, you're going to have to separate the allocation units per data space (sys.data_spaces) in order to identify which filegroup is running out of space.