Estimation of Backup Time

Any idea, how to calculate estimated time for a DB Backup?
Kind regards
Khurram ShahzadHi
It is very dependent on your hardware and Server Load.
You are going to have to test it yourself.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Khurram Shahzad" wrote:

> Any idea, how to calculate estimated time for a DB Backup?
> Kind regards
> Khurram Shahzad
>
>
>sql

Estimation of Backup Time

Any idea, how to calculate estimated time for a DB Backup?
Kind regards
Khurram Shahzad
Hi
It is very dependent on your Hardware and Server Load.
You are going to have to test it yourself.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Khurram Shahzad" wrote:

> Any idea, how to calculate estimated time for a DB Backup?
> Kind regards
> Khurram Shahzad
>
>
>

Estimation of Backup Time

Any idea, how to calculate estimated time for a DB Backup?
Kind regards
Khurram ShahzadHi
It is very dependent on your Hardware and Server Load.
You are going to have to test it yourself.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Khurram Shahzad" wrote:
> Any idea, how to calculate estimated time for a DB Backup?
> Kind regards
> Khurram Shahzad
>
>
>

Estimating the Size of a Clustered Index

Dear All,
I am trying to calculate the size of a cluster index using the "Estimating
the Size of a Clustered Index" section of Books online. Can some body tell me
what the below formula means
Calculate the number of pages in the index:
Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1
where 1 <= Level <= LevelsShri.DBA wrote:
> Dear All,
> I am trying to calculate the size of a cluster index using the "Estimating
> the Size of a Clustered Index" section of Books online. Can some body tell me
> what the below formula means
> Calculate the number of pages in the index:
> Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1
> where 1 <= Level <= Levels
>
for each value of variable 'level' between 1 and Levels (levels is
variable calculated earlier):
calculate (Index_Rows_Per_Page)^(Level â' 1)
sum all (Index_Rows_Per_Page)^(Level â' 1)
I used ^ to mark exponent
that means:
let's say we have 3 levels, Levels=3:
calculate:
- for level = 1: (Index_Rows_Per_Page)^(1 â' 1) =(Index_Rows_Per_Page)^0=1
- for level = 2: (Index_Rows_Per_Page)^(2 â' 1) =(Index_Rows_Per_Page)^1=Index_Rows_Per_Page
- for level = 3: (Index_Rows_Per_Page)^(3 â' 1) =(Index_Rows_Per_Page)^2=Index_Rows_Per_Page*Index_Rows_Per_Page
now, sum all these: 1 + Index_Rows_Per_Page +
Index_Rows_Per_Page*Index_Rows_Per_Page
OK?|||> Can some body tell me
> what the below formula means
The calculation means that the number of non-leaf node index pages required
for a clustered index is the sum of the number pages need at all levels.
The number of required pages at a given level is Index_Rows_Per_Page to the
power of level - 1. Below is the Transact-SQL equivalent.
DECLARE
@.Num_Index_Pages int,
@.Index_Rows_Per_Page int,
@.Level int
SELECT
@.Num_Index_Pages = 0,
@.Index_Rows_Per_Page = 10,
@.Level = 3 --init at number of levels
WHILE @.Level > 0
BEGIN
SET @.Level = @.Level - 1
SET @.Num_Index_Pages = @.Num_Index_Pages + POWER(@.Index_Rows_Per_Page,
@.Level)
END
SELECT @.Num_Index_Pages AS Num_Index_Pages
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message
news:5634D0D1-7B1F-4D3A-8878-DE30960D7255@.microsoft.com...
> Dear All,
> I am trying to calculate the size of a cluster index using the "Estimating
> the Size of a Clustered Index" section of Books online. Can some body tell
> me
> what the below formula means
> Calculate the number of pages in the index:
> Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1
> where 1 <= Level <= Levels
>|||"Zarko Jovanovic" wrote:
> Shri.DBA wrote:
> > Dear All,
> >
> > I am trying to calculate the size of a cluster index using the "Estimating
> > the Size of a Clustered Index" section of Books online. Can some body tell me
> > what the below formula means
> >
> > Calculate the number of pages in the index:
> >
> > Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1
> >
> > where 1 <= Level <= Levels
> >
> >
> for each value of variable 'level' between 1 and Levels (levels is
> variable calculated earlier):
> calculate (Index_Rows_Per_Page)^(Level â' 1)
> sum all (Index_Rows_Per_Page)^(Level â' 1)
> I used ^ to mark exponent
> that means:
> let's say we have 3 levels, Levels=3:
> calculate:
> - for level = 1: (Index_Rows_Per_Page)^(1 â' 1) => (Index_Rows_Per_Page)^0=1
> - for level = 2: (Index_Rows_Per_Page)^(2 â' 1) => (Index_Rows_Per_Page)^1=Index_Rows_Per_Page
> - for level = 3: (Index_Rows_Per_Page)^(3 â' 1) => (Index_Rows_Per_Page)^2=Index_Rows_Per_Page*Index_Rows_Per_Page
> now, sum all these: 1 + Index_Rows_Per_Page +
> Index_Rows_Per_Page*Index_Rows_Per_Page
> OK?
>
----
Some how I am not conveniced with this formula for calculating the number of
index pages.
I have created below table and loaded 1000000 records. When I calculate the
number of index pages it gives me 387507 pages. I don't understand why so
many index pages are required to store clustered index key size of 4 bytes.
Regards
Balaji
USE [MyDB]
GO
/****** Object: Table [dbo].[Log] Script Date: 10/30/2007 17:39:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Log](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[LogMessage] [varchar](50) NOT NULL,
[LogDateTime] [datetime] NOT NULL,
CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF|||Balaji wrote:
> "Zarko Jovanovic" wrote:
>> Shri.DBA wrote:
>> Dear All,
>> I am trying to calculate the size of a cluster index using the "Estimating
>> the Size of a Clustered Index" section of Books online. Can some body tell me
>> what the below formula means
>> Calculate the number of pages in the index:
>> Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1
>> where 1 <= Level <= Levels
>>
>> for each value of variable 'level' between 1 and Levels (levels is
>> variable calculated earlier):
>> calculate (Index_Rows_Per_Page)^(Level â' 1)
>> sum all (Index_Rows_Per_Page)^(Level â' 1)
>> I used ^ to mark exponent
>> that means:
>> let's say we have 3 levels, Levels=3:
>> calculate:
>> - for level = 1: (Index_Rows_Per_Page)^(1 â' 1) =>> (Index_Rows_Per_Page)^0=1
>> - for level = 2: (Index_Rows_Per_Page)^(2 â' 1) =>> (Index_Rows_Per_Page)^1=Index_Rows_Per_Page
>> - for level = 3: (Index_Rows_Per_Page)^(3 â' 1) =>> (Index_Rows_Per_Page)^2=Index_Rows_Per_Page*Index_Rows_Per_Page
>> now, sum all these: 1 + Index_Rows_Per_Page +
>> Index_Rows_Per_Page*Index_Rows_Per_Page
>> OK?
> ----
> Some how I am not conveniced with this formula for calculating the number of
> index pages.
> I have created below table and loaded 1000000 records. When I calculate the
> number of index pages it gives me 387507 pages. I don't understand why so
> many index pages are required to store clustered index key size of 4 bytes.
> Regards
> Balaji
> USE [MyDB]
> GO
> /****** Object: Table [dbo].[Log] Script Date: 10/30/2007 17:39:04 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[Log](
> [LogID] [int] IDENTITY(1,1) NOT NULL,
> [LogMessage] [varchar](50) NOT NULL,
> [LogDateTime] [datetime] NOT NULL,
> CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
> (
> [LogID] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> SET ANSI_PADDING OFF
did you read whole article in BOL?|||Hi Dan Guzman,
Thanks very much for your help. I see there is a difference of 10-15% from
the calculated value and original value. Any thoughts.
Regards
Balaji
"Dan Guzman" wrote:
> > Can some body tell me
> > what the below formula means
> The calculation means that the number of non-leaf node index pages required
> for a clustered index is the sum of the number pages need at all levels.
> The number of required pages at a given level is Index_Rows_Per_Page to the
> power of level - 1. Below is the Transact-SQL equivalent.
>
> DECLARE
> @.Num_Index_Pages int,
> @.Index_Rows_Per_Page int,
> @.Level int
> SELECT
> @.Num_Index_Pages = 0,
> @.Index_Rows_Per_Page = 10,
> @.Level = 3 --init at number of levels
> WHILE @.Level > 0
> BEGIN
> SET @.Level = @.Level - 1
> SET @.Num_Index_Pages = @.Num_Index_Pages + POWER(@.Index_Rows_Per_Page,
> @.Level)
> END
> SELECT @.Num_Index_Pages AS Num_Index_Pages
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message
> news:5634D0D1-7B1F-4D3A-8878-DE30960D7255@.microsoft.com...
> > Dear All,
> >
> > I am trying to calculate the size of a cluster index using the "Estimating
> > the Size of a Clustered Index" section of Books online. Can some body tell
> > me
> > what the below formula means
> >
> > Calculate the number of pages in the index:
> >
> > Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1
> >
> > where 1 <= Level <= Levels
> >
> >
>|||> Thanks very much for your help. I see there is a difference of 10-15% from
> the calculated value and original value. Any thoughts.
What original value are you referring to?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:AA2312C8-5C72-43F3-A283-E4D7AE2EFE3A@.microsoft.com...
> Hi Dan Guzman,
> Thanks very much for your help. I see there is a difference of 10-15% from
> the calculated value and original value. Any thoughts.
> Regards
> Balaji
> "Dan Guzman" wrote:
>> > Can some body tell me
>> > what the below formula means
>> The calculation means that the number of non-leaf node index pages
>> required
>> for a clustered index is the sum of the number pages need at all levels.
>> The number of required pages at a given level is Index_Rows_Per_Page to
>> the
>> power of level - 1. Below is the Transact-SQL equivalent.
>>
>> DECLARE
>> @.Num_Index_Pages int,
>> @.Index_Rows_Per_Page int,
>> @.Level int
>> SELECT
>> @.Num_Index_Pages = 0,
>> @.Index_Rows_Per_Page = 10,
>> @.Level = 3 --init at number of levels
>> WHILE @.Level > 0
>> BEGIN
>> SET @.Level = @.Level - 1
>> SET @.Num_Index_Pages = @.Num_Index_Pages + POWER(@.Index_Rows_Per_Page,
>> @.Level)
>> END
>> SELECT @.Num_Index_Pages AS Num_Index_Pages
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message
>> news:5634D0D1-7B1F-4D3A-8878-DE30960D7255@.microsoft.com...
>> > Dear All,
>> >
>> > I am trying to calculate the size of a cluster index using the
>> > "Estimating
>> > the Size of a Clustered Index" section of Books online. Can some body
>> > tell
>> > me
>> > what the below formula means
>> >
>> > Calculate the number of pages in the index:
>> >
>> > Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1
>> >
>> > where 1 <= Level <= Levels
>> >
>> >|||Hi Dan,
The original value I am referring to here is the result of sp_spaceused.
Regards
Balaji.T
"Dan Guzman" wrote:
> > Thanks very much for your help. I see there is a difference of 10-15% from
> > the calculated value and original value. Any thoughts.
> What original value are you referring to?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:AA2312C8-5C72-43F3-A283-E4D7AE2EFE3A@.microsoft.com...
> > Hi Dan Guzman,
> >
> > Thanks very much for your help. I see there is a difference of 10-15% from
> > the calculated value and original value. Any thoughts.
> >
> > Regards
> > Balaji
> >
> > "Dan Guzman" wrote:
> >
> >> > Can some body tell me
> >> > what the below formula means
> >>
> >> The calculation means that the number of non-leaf node index pages
> >> required
> >> for a clustered index is the sum of the number pages need at all levels.
> >> The number of required pages at a given level is Index_Rows_Per_Page to
> >> the
> >> power of level - 1. Below is the Transact-SQL equivalent.
> >>
> >>
> >> DECLARE
> >> @.Num_Index_Pages int,
> >> @.Index_Rows_Per_Page int,
> >> @.Level int
> >> SELECT
> >> @.Num_Index_Pages = 0,
> >> @.Index_Rows_Per_Page = 10,
> >> @.Level = 3 --init at number of levels
> >> WHILE @.Level > 0
> >> BEGIN
> >> SET @.Level = @.Level - 1
> >> SET @.Num_Index_Pages = @.Num_Index_Pages + POWER(@.Index_Rows_Per_Page,
> >> @.Level)
> >> END
> >> SELECT @.Num_Index_Pages AS Num_Index_Pages
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message
> >> news:5634D0D1-7B1F-4D3A-8878-DE30960D7255@.microsoft.com...
> >> > Dear All,
> >> >
> >> > I am trying to calculate the size of a cluster index using the
> >> > "Estimating
> >> > the Size of a Clustered Index" section of Books online. Can some body
> >> > tell
> >> > me
> >> > what the below formula means
> >> >
> >> > Calculate the number of pages in the index:
> >> >
> >> > Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1
> >> >
> >> > where 1 <= Level <= Levels
> >> >
> >> >
> >>
>|||> The original value I am referring to here is the result of sp_spaceused.
Perhaps extra space is required due to fragmentation.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:CE6A90B3-E3D1-4CD1-A67F-B6DA9F78D754@.microsoft.com...
> Hi Dan,
> The original value I am referring to here is the result of sp_spaceused.
> Regards
> Balaji.T
> "Dan Guzman" wrote:
>> > Thanks very much for your help. I see there is a difference of 10-15%
>> > from
>> > the calculated value and original value. Any thoughts.
>> What original value are you referring to?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
>> news:AA2312C8-5C72-43F3-A283-E4D7AE2EFE3A@.microsoft.com...
>> > Hi Dan Guzman,
>> >
>> > Thanks very much for your help. I see there is a difference of 10-15%
>> > from
>> > the calculated value and original value. Any thoughts.
>> >
>> > Regards
>> > Balaji
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> > Can some body tell me
>> >> > what the below formula means
>> >>
>> >> The calculation means that the number of non-leaf node index pages
>> >> required
>> >> for a clustered index is the sum of the number pages need at all
>> >> levels.
>> >> The number of required pages at a given level is Index_Rows_Per_Page
>> >> to
>> >> the
>> >> power of level - 1. Below is the Transact-SQL equivalent.
>> >>
>> >>
>> >> DECLARE
>> >> @.Num_Index_Pages int,
>> >> @.Index_Rows_Per_Page int,
>> >> @.Level int
>> >> SELECT
>> >> @.Num_Index_Pages = 0,
>> >> @.Index_Rows_Per_Page = 10,
>> >> @.Level = 3 --init at number of levels
>> >> WHILE @.Level > 0
>> >> BEGIN
>> >> SET @.Level = @.Level - 1
>> >> SET @.Num_Index_Pages = @.Num_Index_Pages +
>> >> POWER(@.Index_Rows_Per_Page,
>> >> @.Level)
>> >> END
>> >> SELECT @.Num_Index_Pages AS Num_Index_Pages
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message
>> >> news:5634D0D1-7B1F-4D3A-8878-DE30960D7255@.microsoft.com...
>> >> > Dear All,
>> >> >
>> >> > I am trying to calculate the size of a cluster index using the
>> >> > "Estimating
>> >> > the Size of a Clustered Index" section of Books online. Can some
>> >> > body
>> >> > tell
>> >> > me
>> >> > what the below formula means
>> >> >
>> >> > Calculate the number of pages in the index:
>> >> >
>> >> > Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1
>> >> >
>> >> > where 1 <= Level <= Levels
>> >> >
>> >> >
>> >>|||Ok...thank you very much for the reply.
"Dan Guzman" wrote:
> > The original value I am referring to here is the result of sp_spaceused.
> Perhaps extra space is required due to fragmentation.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:CE6A90B3-E3D1-4CD1-A67F-B6DA9F78D754@.microsoft.com...
> > Hi Dan,
> >
> > The original value I am referring to here is the result of sp_spaceused.
> >
> > Regards
> > Balaji.T
> >
> > "Dan Guzman" wrote:
> >
> >> > Thanks very much for your help. I see there is a difference of 10-15%
> >> > from
> >> > the calculated value and original value. Any thoughts.
> >>
> >> What original value are you referring to?
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> >> news:AA2312C8-5C72-43F3-A283-E4D7AE2EFE3A@.microsoft.com...
> >> > Hi Dan Guzman,
> >> >
> >> > Thanks very much for your help. I see there is a difference of 10-15%
> >> > from
> >> > the calculated value and original value. Any thoughts.
> >> >
> >> > Regards
> >> > Balaji
> >> >
> >> > "Dan Guzman" wrote:
> >> >
> >> >> > Can some body tell me
> >> >> > what the below formula means
> >> >>
> >> >> The calculation means that the number of non-leaf node index pages
> >> >> required
> >> >> for a clustered index is the sum of the number pages need at all
> >> >> levels.
> >> >> The number of required pages at a given level is Index_Rows_Per_Page
> >> >> to
> >> >> the
> >> >> power of level - 1. Below is the Transact-SQL equivalent.
> >> >>
> >> >>
> >> >> DECLARE
> >> >> @.Num_Index_Pages int,
> >> >> @.Index_Rows_Per_Page int,
> >> >> @.Level int
> >> >> SELECT
> >> >> @.Num_Index_Pages = 0,
> >> >> @.Index_Rows_Per_Page = 10,
> >> >> @.Level = 3 --init at number of levels
> >> >> WHILE @.Level > 0
> >> >> BEGIN
> >> >> SET @.Level = @.Level - 1
> >> >> SET @.Num_Index_Pages = @.Num_Index_Pages +
> >> >> POWER(@.Index_Rows_Per_Page,
> >> >> @.Level)
> >> >> END
> >> >> SELECT @.Num_Index_Pages AS Num_Index_Pages
> >> >>
> >> >> --
> >> >> Hope this helps.
> >> >>
> >> >> Dan Guzman
> >> >> SQL Server MVP
> >> >>
> >> >> "Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message
> >> >> news:5634D0D1-7B1F-4D3A-8878-DE30960D7255@.microsoft.com...
> >> >> > Dear All,
> >> >> >
> >> >> > I am trying to calculate the size of a cluster index using the
> >> >> > "Estimating
> >> >> > the Size of a Clustered Index" section of Books online. Can some
> >> >> > body
> >> >> > tell
> >> >> > me
> >> >> > what the below formula means
> >> >> >
> >> >> > Calculate the number of pages in the index:
> >> >> >
> >> >> > Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1
> >> >> >
> >> >> > where 1 <= Level <= Levels
> >> >> >
> >> >> >
> >> >>
> >>
>

Estimating Log File Size

Dear All,
Yesterday I was given a sp to calculate the size of tables
and the overall size of data files.
Is there something similar for log files, i.e. an
algorithm to calculate the size now and say 2 years time
of a log file ?
Thanks
Jim
Jimbo
Look at sp_helpfile as well as sysfiles system table
It is hard to estimate what is your log file will be in the next two years.
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
> Dear All,
> Yesterday I was given a sp to calculate the size of tables
> and the overall size of data files.
> Is there something similar for log files, i.e. an
> algorithm to calculate the size now and say 2 years time
> of a log file ?
> Thanks
> Jim
|||Probably the best way to estimate future sizes is to capture growth over
time and predict from that...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
> Dear All,
> Yesterday I was given a sp to calculate the size of tables
> and the overall size of data files.
> Is there something similar for log files, i.e. an
> algorithm to calculate the size now and say 2 years time
> of a log file ?
> Thanks
> Jim
|||Hi,
You could also use the below command to get current log size and usage
dbcc sqlperf(logspace)
Estimating the log size for next 2 years will be hard.. That depends up on
the amount of batch operation and frequency in which
you perform the transaction log backup. Normally it is not required to
project Log size because the log file will be cleared once
you perform the transaction log backup.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eFgFomsfEHA.3932@.TK2MSFTNGP09.phx.gbl...
> Jimbo
> Look at sp_helpfile as well as sysfiles system table
> It is hard to estimate what is your log file will be in the next two
years.
>
> "Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
> news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
>

Estimating Log File Size

Dear All,
Yesterday I was given a sp to calculate the size of tables
and the overall size of data files.
Is there something similar for log files, i.e. an
algorithm to calculate the size now and say 2 years time
of a log file ?
Thanks
JimJimbo
Look at sp_helpfile as well as sysfiles system table
It is hard to estimate what is your log file will be in the next two years.
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
> Dear All,
> Yesterday I was given a sp to calculate the size of tables
> and the overall size of data files.
> Is there something similar for log files, i.e. an
> algorithm to calculate the size now and say 2 years time
> of a log file ?
> Thanks
> Jim|||Probably the best way to estimate future sizes is to capture growth over
time and predict from that...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
> Dear All,
> Yesterday I was given a sp to calculate the size of tables
> and the overall size of data files.
> Is there something similar for log files, i.e. an
> algorithm to calculate the size now and say 2 years time
> of a log file ?
> Thanks
> Jim|||Hi,
You could also use the below command to get current log size and usage
dbcc sqlperf(logspace)
Estimating the log size for next 2 years will be hard.. That depends up on
the amount of batch operation and frequency in which
you perform the transaction log backup. Normally it is not required to
project Log size because the log file will be cleared once
you perform the transaction log backup.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eFgFomsfEHA.3932@.TK2MSFTNGP09.phx.gbl...
> Jimbo
> Look at sp_helpfile as well as sysfiles system table
> It is hard to estimate what is your log file will be in the next two
years.
>
> "Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
> news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
>

Estimating Log File Size

Dear All,
Yesterday I was given a sp to calculate the size of tables
and the overall size of data files.
Is there something similar for log files, i.e. an
algorithm to calculate the size now and say 2 years time
of a log file ?
Thanks
JimJimbo
Look at sp_helpfile as well as sysfiles system table
It is hard to estimate what is your log file will be in the next two years.
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
> Dear All,
> Yesterday I was given a sp to calculate the size of tables
> and the overall size of data files.
> Is there something similar for log files, i.e. an
> algorithm to calculate the size now and say 2 years time
> of a log file ?
> Thanks
> Jim|||Probably the best way to estimate future sizes is to capture growth over
time and predict from that...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
> Dear All,
> Yesterday I was given a sp to calculate the size of tables
> and the overall size of data files.
> Is there something similar for log files, i.e. an
> algorithm to calculate the size now and say 2 years time
> of a log file ?
> Thanks
> Jim|||Hi,
You could also use the below command to get current log size and usage
dbcc sqlperf(logspace)
Estimating the log size for next 2 years will be hard.. That depends up on
the amount of batch operation and frequency in which
you perform the transaction log backup. Normally it is not required to
project Log size because the log file will be cleared once
you perform the transaction log backup.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eFgFomsfEHA.3932@.TK2MSFTNGP09.phx.gbl...
> Jimbo
> Look at sp_helpfile as well as sysfiles system table
> It is hard to estimate what is your log file will be in the next two
years.
>
> "Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
> news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
> > Dear All,
> >
> > Yesterday I was given a sp to calculate the size of tables
> > and the overall size of data files.
> >
> > Is there something similar for log files, i.e. an
> > algorithm to calculate the size now and say 2 years time
> > of a log file ?
> >
> > Thanks
> > Jim
>

Estimating Database Size Issue

All,
I am following the guidelines laid out on the following page to
calculate an estimated size for a database.
http://msdn2.microsoft.com/en-us/library/ms178085.aspx.
However, I am noticing something weird. Because
Index_Rows_Per_Page is calculated by dividing the (Index_Row_Size +
2), the MORE variable and non-variable keys you add to your table, the
SMALLER the size of the database gets. Does this make sense to anyone
because it doesn't make sense to me.
Here's my database:
Num_Rows = 75,000,000
Num_Cols = 3
Fixed_Data_Size = 8
Num_Variable_Cols = 0
Max_Var_Size = 0
Num_Key_Cols = 2
Fixed_Key_Size = 4
Num_Variable_Key_Cols = 0
Max_Var_Key_Size = 0
Num_Nullable_Key_Cols = 0
Using this information, I get 438.07GB as the size of my
database.
If I change the number of Fixed_Key_Size to 6, the size of the
database drops to 335.25GB.
Again, this doesn't make sense to me. If quadruple checked my
math against what's in the URL above, and it appears to match what is
in the document. Is Microsoft's calculation wrong? Surely not. Maybe I
missed something? Are you guys getting the same results based off of
my info above?
Thanks,
JeremyI remember when I first read this article I felt pretty overwhelmed.
I've found the many scripts and articles from www.sqlservercentral.com
(and www.sql-server-perfromance.com I think) to be much more
intuitive. I know this doesn't really answer your question, its more
of a suggestion.
Erik|||Thanks Erik. I was looking further through the code I wrote, value by
value, and it turns out that it's either the LOG function or the
Summation function (or a combination of both) that is making the size
decrease when the byte size of indexed keys increases. But it still
doesn't make sense.
I'll take a look at those links though, thanks!
Jeremysql

Estimate DB Size: how to calculate clustered and non-clustered ind

I am doing an estimation on db size based on this link:
http://msdn.microsoft.com/library/en...asp?frame=true
How can I check which tables have clustered index and non-clustered index
fastly?
Thank you!
In addition, noting the sentence "Total Table Size = Data Space Used +
Nonclustered Index + Clustered Index + ...n"
What are the other "...n" factors and how significant (can measure to %?)
are these factors affecting the result?
Many thanks!
|||Get the script I posted at the following link.
http://www.sqlservercentral.com/colu...xdetection.asp
"Jackie Chow" wrote:

> I am doing an estimation on db size based on this link:
> http://msdn.microsoft.com/library/en...asp?frame=true
> How can I check which tables have clustered index and non-clustered index
> fastly?
> Thank you!
|||Check for fragmentation using DBCC SHOWCONTIG.
I will send you a document explaining the use and the Excel Document.
Regards
Andre
"Jackie Chow" wrote:

> I am doing an estimation on db size based on this link:
> http://msdn.microsoft.com/library/en...asp?frame=true
> How can I check which tables have clustered index and non-clustered index
> fastly?
> Thank you!