How to identify the amount of space currently in use for a given data file when multiple data files exist for a file group
-
Is there a table I could query to obtain the actual amount of space in use by individual data files if multiple data files comprise a file group?
I am writing a stored procedure that takes a database name as a parameter. The stored procedure attempts to perform a dbcc shrinkfile on all the data files for the database.
I have a driving cursor consisting of a join on sysfiles and sysfilegroups to identify the fileid's.
I am using the sysfiles.size column to determine the initial allocation for each data file.
driving cursor declaration:
set @statement = N' declare DataFileCursor CURSOR FOR select sf.fileid,rtrim(sf.name),sf.groupid,sfg.groupname from ' +@dbname+'.dbo.sysfiles sf (nolock) INNER JOIN sysfilegroups sfg WITH (NOLOCK) ON sf.groupId = sfg.groupId where fileid <> 2 order by fileid asc'
exec sp_executesql @statement
FETCH NEXT FROM DataFileCursor INTO @datafileid,@datafile,@datafilegroupid,@datafilegroupname
I am trying to obtain the current size of the objects for the data file using :
set @statement = N'select @data_used = sum(convert(dec(15),reserved)) from '+@dbname+'.dbo.sysindexes (nolock) where indid in (0, 1, 255) and fileid ='+cast(@datafileid as varchar(20))
exec sp_executesql @statement ,N' @data_used int out',@data_used OUTPUT
I calculate unused space as :
allocated space (driving cursor) - sum(from dbo.sysindexes) --> which contain information for multiple data files as there are multiple data files per file group.
I want to perform a dbcc shrinkfile on each data file using the allocated space - unused space as the size to shrink the data file to.
-
You could use DBCC SHRINKFILE with TRUNCATEONLY, or specify the current file size, to have it execute quickly and return the UsedPages and EstimatedPages values for the file.
The only other option that comes to mind is using undocumented DBCC functions to try to read the page allocation map, but that sounds like a lot of work.
-
I don't know how to do it thru query.....I'm still trying though
In EM, if you right click on a database --> view-->taskpad, it shows you the size of your log - how much is used and how much is free.
-
You can use DBCC SHRINKFILE (<filename or fileid>, <current size in MB>, NOTRUNCATE) to get the EstimatedPages value without tying up the server. If I read BOL correctly this command should not cause it to start moving log pages around, but it will reset the minimum size for the log file to the <current size> value used. If you use a smaller number (such as the existing minumum size value) to avoid making minimum size too big, it won't shrink the file (because of NOTRUNCATE) but it still will try to move used log pages around to get below the specified size. Hint: MB = Pages / 128, or if you want to round up it could be MB = CEILING( Pages / 128.0 ).
Unfortunatly my idea won't work in a stored procedure. There are a number of uses for captuing DBCC output in a temp table like this:
create table #info (...)
insert into #info
exec('DBCC command')
For a good example look up DBCC SHOWCONTIG in BOL. But this doesn't work with DBCC SHRINKFILE because you get the error "Cannot perform a ShrinkFile operation inside a user transaction." (you can't run SHRINKFILE while the INSERT is active). Maybe someone has a workaround?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply