Is there a way to find growth in log (ldf) and database(mdf) ?

Hello,

I need to monitor every 15 minutes growth in data file and log file .
Since mdf and intial file sizes are set to high value,
measuring these values at 15 min interval will not provide the change
in size .

My intention is to measure the log file size growth which helps to
calculate the disk space and bandwidth required to setup log shipping .
We need to set up this infrastructure based on this calculation

Thanks

M A Srinivas

I found that on Google and added some further stuff like ID and
Reporting Date in order to keep the information stored permantly, for
permanent storage of the table create a non-temporary one.:

create table #dbinfo
(
ReportId INT IDENTITY(1,1),
dbname sysname,
dbsize decimal(10,2),
dbused decimal(10,2),
ReportDate datetime DEFAULT GETDATE()
)


exec sp_MSforeachdb 'insert into #dbinfo(dbname, dbsize, dbused) select

''?'', dbsize = sum(convert(dec(15),size))/128, (select
sum(convert(dec(15),used))/128 from [?]..sysindexes where indid in (0,
1,
255)) from [?].dbo.sysfiles where status & 0x40 = 0'

Select * from #dbinfo

I executed the query at different time intervals and found that dbsize
is not changing.

What I basically requires to know the size of the log file ( every 15
mins) without actually implementing log shipping . Is this possible ?

Thanks

Srinivas

Query gives the same database size at different intervals , even though
activity has occured on the database.

What I require is to find the size of log file ( every 15 mins )
without actually implementing Log shipping. I need to set up a script
in production server and measure the log size (every 15 mins ) and Log
shipping set up is not allowed at this point of time .

Thanks

Srinivas

Unless the file expands the size won't change
When you have autogrow of 10 percent and you fill up only 8 percent the
file size won't change until it hits that threshold

http://sqlservercode.blogspot.com/




masri999@gmail.com wrote:
> Query gives the same database size at different intervals , even though
> activity has occured on the database.
>
> What I require is to find the size of log file ( every 15 mins )
> without actually implementing Log shipping. I need to set up a script
> in production server and measure the log size (every 15 mins ) and Log
> shipping set up is not allowed at this point of time .
>
> Thanks
>
> Srinivas

(masri999@gmail.com) writes:
> Query gives the same database size at different intervals , even though
> activity has occured on the database.
>
> What I require is to find the size of log file ( every 15 mins )
> without actually implementing Log shipping. I need to set up a script
> in production server and measure the log size (every 15 mins ) and Log
> shipping set up is not allowed at this point of time .

What do you want to know? The size of the log file, or the size of
the transaction log actually in use? The log file only increases in
size when it has to.

For the current log size, DBCC SQLPERF may be an alternative.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Having just recently begun to g othrough log shipping processes myselg,
I think I understand what you're asking: you want to know how big will
the individual log files be (on average) during the log shipping
process, correct? You don't have to actually ship these files to find
out, but you will need to implement a transaction log plan that backs
up your transaction log every 15 minutes.

Use the Maintenance Plan wizard, and without implementing log shipping,
set up a plan that backs up your transaction logs. This can be safely
implemented on a production server, assuming that you don't already
have a backup plan in place that relies on transaction logs for
point-in-time restoration; if you do, then you should already have
method of answering this question.

Be sure that your database recovery mode is set to FULL.

HTH,
Stu