CODEWITHSUNDEEP
sql-serversql-server-2008
nick
nick

Reputation: 3458

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

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)

Upvotes: 2

Views: 3554

Answers (3)

user2916014
user2916014

Reputation: 1

This will give the file, the size , amount of storage used . and amount available;

SELECT    
[name] AS [File Name],    
physical_name AS [Physical Name],    
[Total Size_Mb] = ceiling([size]/128), 
[Space used_Mb] =    CAST(FILEPROPERTY([name], 'SpaceUsed') as int) /128,
[Available Space_Mb] = (([size]/128) - CAST(FILEPROPERTY([name], 'SpaceUsed') as int) /128) ,file_id
FROM sys.database_files   
ORDER BY [file_id]

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294277

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.

Upvotes: 3

Gabriele Petrioli
Gabriele Petrioli

Reputation: 195992

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

Upvotes: 3

Related Questions