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 cluster memory requirement

Hi,
I am aggregating 6-8 SQL Server 2000 databases to a cluster of SQL
Server consisting of two nodes, each with four 3.1GHz Xeon CPUs. How do
I estimate the memory requirement for each node? Do I simply add the
memory utilization of each SQL Server instance on each individual SQL
server? Or, is there some other formula to arrive at this figure?
Thanks,
- Siddhartha
It actually works the other way around. Put as much memory as you can in
each node. SQL will generally run better the more memory you give it. Then
divide the memory amongst the instances. Remember, during a failover, all
your instances will "stack" onto one server so you must plan for that
situation. Also you will need to leave 1-2GB for the OS and support
applications. Use the Performance Monitor tool to watchoverall memory use.
I like to use Page Life Expectancy as a measurement for memory pressure
within SQL Server.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Siddhartha Jain" <sid@.netmagicsolutions.com> wrote in message
news:ufRmfc17FHA.2616@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am aggregating 6-8 SQL Server 2000 databases to a cluster of SQL
> Server consisting of two nodes, each with four 3.1GHz Xeon CPUs. How do
> I estimate the memory requirement for each node? Do I simply add the
> memory utilization of each SQL Server instance on each individual SQL
> server? Or, is there some other formula to arrive at this figure?
> Thanks,
> - Siddhartha
|||Thanks for the reply Geoff. I don't intend to run multiple instances per
node but only one instance per node. So lets say I have six servers now,
each using 2GB currently for the SQL server instance, then do I size 6GB
for the SQL instance per cluster node (in a cluster of two nodes).
- Siddhartha
Geoff N. Hiten wrote:
> It actually works the other way around. Put as much memory as you can in
> each node. SQL will generally run better the more memory you give it. Then
> divide the memory amongst the instances. Remember, during a failover, all
> your instances will "stack" onto one server so you must plan for that
> situation. Also you will need to leave 1-2GB for the OS and support
> applications. Use the Performance Monitor tool to watchoverall memory use.
> I like to use Page Life Expectancy as a measurement for memory pressure
> within SQL Server.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Siddhartha Jain" <sid@.netmagicsolutions.com> wrote in message
> news:ufRmfc17FHA.2616@.TK2MSFTNGP15.phx.gbl...
>
>
|||During a cluster node failure event, the instances will all migrate to a
single node. That is how it wirks. You will need to divide the memory so
that both instances can live on a single node. As for total memory
required, there is no exact rule. SQL likes more memory so adding memory
won't hurt. Watch the performance counters to check memory pressure.
GNH
"Siddhartha Jain" <sid@.netmagicsolutions.com> wrote in message
news:%23l$s1u27FHA.744@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Thanks for the reply Geoff. I don't intend to run multiple instances per
> node but only one instance per node. So lets say I have six servers now,
> each using 2GB currently for the SQL server instance, then do I size 6GB
> for the SQL instance per cluster node (in a cluster of two nodes).
> - Siddhartha
>
> Geoff N. Hiten wrote:
sql

Errors with SQL Cluster running .DTSX packages

I'm experiencing some frustration with my active/passive SQL cluster not running my .DTSX packages. I am hoping someone can shed some light on what I need to do.

I've created some .DTSX packages with the SQL Server Business Intel Dev Studio. I initially built & tested these packages in a non-SQL cluster environment without any problems. I'm now re-creating them to work on our SQL cluster. If I run the package through the Dev Studio it works great.

The packages basically grab .txt files from one of the shared drives (which is a resource of the sql cluster group) and imports the data into the one of the databases. The database does not have any special settings (right-click -> new database... -> enter name -> click ok).

I've setup a SQL Server Agent Job with 1 step with the following properties.

Step name: I Offices
Type: SQL Server Integration Services Package
Run as: SQL Agent Service Account
Package source: File system
Package: G:\ImportRAGFLOffices.dtsx

When I run the SQL Server Agent Job through MSSQL Server Management Studio (right-click -> start job) I get an error on "Execute job 'RAGFL TestJob'".

These are the 2 messages that show up when I view the history of the SQL Server Agent job.

***********************************************************************************************
Date 9/25/2007 1:16:13 PM
Log Job History (RAGFL TestJob)

Step ID 0
Server BADBOYS
Job Name RAGFL TestJob
Step Name (Job outcome)
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. The Job was invoked by User sa. The last step to run was step 1 (I Offices).
***********************************************************************************************

***********************************************************************************************
Date 9/25/2007 1:16:13 PM
Log Job History (RAGFL TestJob)

Step ID 1
Server BADBOYS
Job Name RAGFL TestJob
Step Name I Offices
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: THEISLAND\Administrator. The package execution failed. The step failed.
***********************************************************************************************

THEISLAND is our internal domain.

There is a decent article about clustering SSIS in Expert SQL Server 2005 Integration Services from Wrox...

The following are the high level steps:

1. Install SSIS on all nodes that can use the service

2. Create a new cluster group (optional)

3. If you created a new group, create a virtual IP, name, drive as clustered resources.

4. Copy the MsDtsSrvr.ini.xml to the clustered drive

5. Modify the MsDtsSrvr.ini.xml file to change the location of hte packages

6. Change the Registry setting to point to the MsDtsSrvr.ini.xml file

7. Cluster the MsDtsServer service as a generic service

Also, see the following for more information on Clustering SSIS:

http://wiki.sqlis.com/default.aspx/SQLISWiki/ClusterSSIS.html

http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx

|||

What a great name for a server!

Errorlog File The system cannot find the path specified

I have a cluster SQL Server 2000 database with Windows 2003 server operating
system. Periodically I receive the error listed below. The database is
functioning properly when I received this error message.
Please help me determine why this error occurs periodically.
Thanks,
initerrlog: Could not open error log file 'F=:\LOG\ERRORLOG'. Operating
system error = 3(The system cannot find the path specified.).
Hi,
just go that path and see is there any error log file or not
if not then create a error logfile.
U did not mentioned from where does this error occurs.
thanx
from
killer

Errorlog File The system cannot find the path specified

I have a cluster SQL Server 2000 database with Windows 2003 server operating
system. Periodically I receive the error listed below. The database is
functioning properly when I received this error message.
Please help me determine why this error occurs periodically.
Thanks,
initerrlog: Could not open error log file 'F=:\LOG\ERRORLOG'. Operating
system error = 3(The system cannot find the path specified.).Hi,
just go that path and see is there any error log file or not
if not then create a error logfile.
U did not mentioned from where does this error occurs.
thanx
from
killer

Errorlog File The system cannot find the path specified

I have a cluster SQL Server 2000 database with Windows 2003 server operating
system. Periodically I receive the error listed below. The database is
functioning properly when I received this error message.
Please help me determine why this error occurs periodically.
Thanks,
initerrlog: Could not open error log file 'F=:\LOG\ERRORLOG'. Operating
system error = 3(The system cannot find the path specified.).Hi,
just go that path and see is there any error log file or not
if not then create a error logfile.
U did not mentioned from where does this error occurs.
thanx
from
killer

ERROR: SQLSERVERAGENT COULD NOT BE STARTED(ERROR CREATING A NEW SESSION)

I keep on getting this msg on a active/passive win2003 cluster environment
with SQL 2005
ERROR: SQLSERVERAGENT COULD NOT BE STARTED (ERROR CREATING A NEW SESSION)
(sqagtres) online thread: error 435 brining Resource online.
(sqagtres) online Thread: ResUtilsStartResouceService failed (status 435)
Thank you in avance
>>> On 12/4/2006 at 10:16 AM, in message
<esNUSh8FHHA.3616@.TK2MSFTNGP06.phx.gbl>, Peter<Vik> wrote:
> ERROR: SQLSERVERAGENT COULD NOT BE STARTED (ERROR CREATING A NEW
> SESSION)
> (sqagtres) online thread: error 435 brining Resource online.
> (sqagtres) online Thread: ResUtilsStartResouceService failed (status
> 435)
If you are using AWE, you need to give the agent user (in Windows) the
lock pages in memory permission.
|||Thank you for your quick response, but what's AWE and which lock pages?
"Joel Maslak" <jmasla@.state.wy.us> wrote in message
news:4573F823.BB84.007F.0@.state.wy.us...
> <esNUSh8FHHA.3616@.TK2MSFTNGP06.phx.gbl>, Peter<Vik> wrote:
> If you are using AWE, you need to give the agent user (in Windows) the
> lock pages in memory permission.
|||>>> On 12/4/2006 at 10:41 AM, in message
<OtdTgv8FHHA.3468@.TK2MSFTNGP04.phx.gbl>, Peter<Vik> wrote:
> Thank you for your quick response, but what's AWE and which lock
> pages?
You probably aren't using AWE if you don't know about it. You have to
provide /PAE as a boot command-line option, and it only applies to 32
bit OS, 32 bit SQL, and > 2GB of memory allocated to SQL.