1

Normally when the database is online I use the following query and get how much space is free inside each file:

            SELECT CONVERT (decimal(12,2)  , 
                           (CONVERT(decimal(12,2), size)/128)/1024.00) 
                          - CONVERT(decimal(12,2),
                            ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)/1024.00)  as [FREESPACE GB],
                   * 
              FROM sys.database_files a WITH(NOLOCK)

you need to be in the database you want to look at.

enter image description here

However, how can you do that when the database is offline?

I have not found a way.

I was trying this, but it did not work out:

select object_name,counter_name,instance_name,cntr_value,
case cntr_type 
    when 65792 then 'Absolute Meaning' 
    when 65536 then 'Absolute Meaning' 
    when 272696576 then 'Per Second counter and is Cumulative in Nature'
    when 1073874176 then 'Bulk Counter. To get correct value, this value needs to be divided by Base Counter value'
    when 537003264 then 'Bulk Counter. To get correct value, this value needs to be divided by Base Counter value' 
    else 'I don"t know'   
end as counter_comments
from sys.dm_os_performance_counters with(nolock)
where cntr_type not in (1073939712)
and counter_name in ('Data File(s) Size (KB)                                                                                                          ',
                     'Database pages                                                                                                                  ')

enter image description here

Is there a way to find out free space inside files of offline databases?

3
  • Is the expectation to be able to see this information inside of SQL Server, for example via TSQL, or just be able to get this data regardless of SQL Server installed or path used to get the data, for example running a powershell command on your local laptop against the file(s)? Feb 19 at 14:53
  • a powershell command would be great Sean, more and more I see the focus moving from T-SQL to Powershell (at least where I stand) Feb 19 at 16:28
  • The server has no idea, because the files are not even open. The best you can do is query sys.master_files which at least tells you the actual file size. Feb 20 at 0:59

1 Answer 1

3

However, how can you do that when the database is offline?

There really isn't an option built in to do this as the allocation management items are in the database. The databases would have to be mounted (opened in SQL Server) in order for the engine code to process it. Could the engine get this information from a database that isn't currently online? Sure, there's no technical reason why it couldn't aside from the fact that it was never programmed to do so.

Is there a way to find out free space inside files of offline databases?

It's entirely possible, though I don't know of anything that will do this for you, though it could be a fun little project. You'd want to emulate the storage engine and do, essentially, what it would do, which is find the allocation bitmaps (in this case the GAM pages, which are at very specific locations inside of the file which is well documented) and then count the number of extents allocated via the bitmap.

If you wrote this as .net code it could be called by PowerShell or loaded into SQL Server as a CLR module (yuck) and called from T-SQL.

1
  • lovely answer @sean thanks a lot. very clear. Basically if you want that piece of info you need to dig that out, good point. Feb 20 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.