list of files

Question

Post reply

list of files

bala2

SSCertifiable

Points: 5547
More actions
June 21, 2010 at 9:15 pm

#220328

stored procedure to get the list of files that were increased by more than 20% in last 24 hrs?
thanku

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

Vijay, how about trying a script posting us your effort, and then we'll see where we can assist.

There was a long thread about people just asking for scripts, instead of trying first, and then asking.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

chetanr.jain Hall of Fame Points: 3938 More actions · Answer 2

@vijay-2

That would be great if you share that script. It will help out lot of people.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

I don't believe that SQL keeps history of file sizes. So you'll have to write something that checks and logs the files sizes every day. Once you have that, it'll be trivial to query the log table and see file growth.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jncarter Ten Centuries Points: 1195 More actions · Answer 4

Gail nailed it on the head with the only surefire method of tracking the file size. However if that is not setup we could look at the default trace. Here is a little script that would meet your requirements.

declare @curr_tracefilename varchar(500), @base_tracefilename varchar(500), @indx int ;

select @curr_tracefilename = path from sys.traces where is_default = 1 ;

set @curr_tracefilename = reverse(@curr_tracefilename);

select @indx = patindex('%\%', @curr_tracefilename) ;

set @curr_tracefilename = reverse(@curr_tracefilename) ;

set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ;

WITH AutoGrow_CTE (databaseid, filename, ChangeInSize)

AS

(

select databaseid, filename, SUM(IntegerData) as ChangeInSize

from ::fn_trace_gettable( @base_tracefilename, default )

where EventClass = 92 OR EventClass = 93

AND DATEDIFF(hh,StartTime,GETDATE())<24

group by databaseid, filename

)

select DB_NAME(database_id) DatabaseName, mf.name LogicalName,

mf.size-ISNULL(ag.ChangeInSize,0) PreviousSize,

ISNULL(ag.ChangeInSize,0) AutoGrowSize,

mf.size CurrentSize,

CAST(ISNULL(ag.ChangeInSize,0)/(mf.size-ISNULL(ag.ChangeInSize,0))*100 AS VARCHAR)+'%' PercentChange

from sys.master_files mf

left outer join AutoGrow_CTE ag

on mf.database_id=ag.databaseid

and mf.name=ag.filename

WHERE ISNULL(ag.ChangeInSize,0)/(mf.size-ISNULL(ag.ChangeInSize,0))*100 >20

ORDER BY DatabaseName, LogicalName

-Jeremy