February 18, 2010 at 11:09 am
Hi there,
I am looking for a sql-statement which shows me how much data/freespace I have in my single datafiles or at least filegroups from my database.
Background: As many of you know, the recommendation for database-files is to NOT use the auto-grow option which can lead to a decline of performance.
So I calculated the estimated filesizes an builded the database with nogrow-option.
As the database is partitioned and splitted up into different filegroups I cannot just take the values from the sp_spaceused which can (imo) only be used for dbs and tables/indexs not files or filegroups.
Cheers,
Mitch
February 18, 2010 at 11:27 am
The simple answer is DBCC showfilestats. However, you will likely want this information to be in a more consumable format. I have tables and a procedure to help with that - let me know if you are interested.
Regards,
Toby
February 18, 2010 at 12:29 pm
mitch.fh (2/18/2010)
Background: As many of you know, the recommendation for database-files is to NOT use the auto-grow option which can lead to a decline of performance.
I would like to know, where have you seen that recommendation of disabling auto growth for database file.
It is suggested to have the files large enough that they do not autogrow too frequently, but I have not seen that autogrowth for files should be disabled.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 18, 2010 at 12:43 pm
Use this script.
Change the @PCTFULL value to filter the files with more than X % fullness
DECLARE @PCTFULL INT
SET @PCTFULL = 80
DECLARE @sql VARCHAR(4000),@sql2 VARCHAR(4000),@body varchar(8000), @SUBJ VARCHAR(1000)
DECLARE @dbName varchar(255),@LFNAME varchar(255),@PFNAME varchar(255), @PCTUSD varchar(255)
CREATE TABLE #MainTemp (DBNAME varchar(128), LFName Varchar(500) , PhysLoc Varchar(500),
TotalExts DECIMAL(10,2), UsedExtents DECIMAL(10,2) )
CREATE TABLE #Temp1 ( FileID INT, FileGroup INT, TotalExts INT, UsedExtents INT,
LFName Varchar(500) , PhysLoc Varchar(500))
DECLARE DataBaseNamesCursor CURSOR FOR SELECT name FROM master..sysdatabases
WHERE name NOT IN ('tempdb','master','model','msdb')
OPEN DataBaseNamesCursor
FETCH next FROM DataBaseNamesCursor INTO @dbName
WHILE @@fetch_status=0
BEGIN
SET @sql = ' USE ' + @dbname + CHAR(13)
+ 'Insert into #Temp1 (FileID,FileGroup,TotalExts,UsedExtents,LFName,PhysLoc )' + CHAR(13)
+ 'EXEC (''DBCC showfilestats with no_infomsgs'')' + CHAR(13) +
'Insert into #MainTemp' + CHAR(13) + 'Select ''' + @dbname + ''', Temp.LFName,Temp.PhysLoc,Temp.TotalExts,Temp.UsedExtents
from #Temp1 Temp INNER JOIN ' + @dbname + '.dbo.sysfiles SF ON Temp.FileID = SF.FileID
AND Temp.LFName = SF.Name AND Temp.PhysLoc = SF.FILEName '
EXEC(@sql)
FETCH next FROM DataBaseNamesCursor INTO @dbName
END
CLOSE DataBaseNamesCursor
DEALLOCATE DataBaseNamesCursor
SELECT DBNAME, LFName , PhysLoc, CEILING((UsedExtents / TotalExts) * 100) AS PERCENTAGE_USED FROM #MainTemp
WHERE CEILING((UsedExtents / TotalExts) * 100) > @PCTFULL
ORDER BY PERCENTAGE_USED DESC
DROP TABLE #Temp1
DROP TABLE #MainTemp
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 18, 2010 at 3:50 pm
Bru Medishetty (2/18/2010)
I would like to know, where have you seen that recommendation of disabling auto growth for database file.It is suggested to have the files large enough that they do not autogrow too frequently, but I have not seen that autogrowth for files should be disabled.
Actually I am not finding this recommendation, maybe it was one of the books in my office. I will check tomorrow. However I admit, that most of the sources I read while looking for my statement today said that the files should be set to a reasonable size but the autogrow should stay on :w00t:
Thanks for your skript Bru.
February 18, 2010 at 6:05 pm
You are welcome !
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 19, 2010 at 1:16 am
sp_helpfile
----------
Ashish
February 19, 2010 at 3:32 am
ashish.kuriyal (2/19/2010)
sp_helpfile
"Returns the physical names and attributes of files associated with the current database. Use this stored procedure to determine the names of files to attach to or detach from the server."
Useful occasionally, but doesn't answer the question in this thread.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 19, 2010 at 8:12 am
As Paul indicated, that would not help solve the OP required task.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply