Long Running Re-Index Task

Hi All
I have a SQL Server 2005 Standard Edition on SP1 where the Re-Index
Maintenance task normally takes 5 minutes however every now and then the job
will take over 8 hours to complete.
Can anyone point me in the right direction to identify how to identify why
the re-index takes so long?
I have scripted the maintenance plan and listed below is a sample of the
re-index
ALTER INDEX [pk_Account] ON [dbo].[Account] REBUILD WITH ( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
SORT_IN_TEMPDB = OFF, ONLINE = OFF )
Thanks
David
How big is database? Any activities on the server while you are running MP?
"David" <David@.discussions.microsoft.com> wrote in message
news:44134DDF-BCC0-4130-BE08-B053231876BA@.microsoft.com...
> Hi All
> I have a SQL Server 2005 Standard Edition on SP1 where the Re-Index
> Maintenance task normally takes 5 minutes however every now and then the
> job
> will take over 8 hours to complete.
> Can anyone point me in the right direction to identify how to identify why
> the re-index takes so long?
> I have scripted the maintenance plan and listed below is a sample of the
> re-index
> ALTER INDEX [pk_Account] ON [dbo].[Account] REBUILD WITH ( PAD_INDEX =
> OFF,
> STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =
> ON,
> SORT_IN_TEMPDB = OFF, ONLINE = OFF )
> Thanks
|||Is there a long-running transaction that's holding a lock on the table? That
may prevent the index rebuild acquiring the locks it requires.
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ycFxx7tHHA.4800@.TK2MSFTNGP05.phx.gbl...
> David
> How big is database? Any activities on the server while you are running
> MP?
>
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:44134DDF-BCC0-4130-BE08-B053231876BA@.microsoft.com...
>

Long Running Re-Index Task

Hi All
I have a SQL Server 2005 Standard Edition on SP1 where the Re-Index
Maintenance task normally takes 5 minutes however every now and then the job
will take over 8 hours to complete.
Can anyone point me in the right direction to identify how to identify why
the re-index takes so long?
I have scripted the maintenance plan and listed below is a sample of the
re-index
ALTER INDEX [pk_Account] ON [dbo].[Account] REBUILD WITH ( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
SORT_IN_TEMPDB = OFF, ONLINE = OFF )
ThanksDavid
How big is database? Any activities on the server while you are running MP?
"David" <David@.discussions.microsoft.com> wrote in message
news:44134DDF-BCC0-4130-BE08-B053231876BA@.microsoft.com...
> Hi All
> I have a SQL Server 2005 Standard Edition on SP1 where the Re-Index
> Maintenance task normally takes 5 minutes however every now and then the
> job
> will take over 8 hours to complete.
> Can anyone point me in the right direction to identify how to identify why
> the re-index takes so long?
> I have scripted the maintenance plan and listed below is a sample of the
> re-index
> ALTER INDEX [pk_Account] ON [dbo].[Account] REBUILD WITH ( PAD_INDEX => OFF,
> STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS => ON,
> SORT_IN_TEMPDB = OFF, ONLINE = OFF )
> Thanks|||Is there a long-running transaction that's holding a lock on the table? That
may prevent the index rebuild acquiring the locks it requires.
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ycFxx7tHHA.4800@.TK2MSFTNGP05.phx.gbl...
> David
> How big is database? Any activities on the server while you are running
> MP?
>
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:44134DDF-BCC0-4130-BE08-B053231876BA@.microsoft.com...
>> Hi All
>> I have a SQL Server 2005 Standard Edition on SP1 where the Re-Index
>> Maintenance task normally takes 5 minutes however every now and then the
>> job
>> will take over 8 hours to complete.
>> Can anyone point me in the right direction to identify how to identify
>> why
>> the re-index takes so long?
>> I have scripted the maintenance plan and listed below is a sample of the
>> re-index
>> ALTER INDEX [pk_Account] ON [dbo].[Account] REBUILD WITH ( PAD_INDEX =>> OFF,
>> STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =>> ON,
>> SORT_IN_TEMPDB = OFF, ONLINE = OFF )
>> Thanks
>

Long Running Re-Index Task

Hi All
I have a SQL Server 2005 Standard Edition on SP1 where the Re-Index
Maintenance task normally takes 5 minutes however every now and then the job
will take over 8 hours to complete.
Can anyone point me in the right direction to identify how to identify why
the re-index takes so long?
I have scripted the maintenance plan and listed below is a sample of the
re-index
ALTER INDEX [pk_Account] ON [dbo].[Account] REBUILD WITH ( PAD_I
NDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
,
SORT_IN_TEMPDB = OFF, ONLINE = OFF )
ThanksDavid
How big is database? Any activities on the server while you are running MP?
"David" <David@.discussions.microsoft.com> wrote in message
news:44134DDF-BCC0-4130-BE08-B053231876BA@.microsoft.com...
> Hi All
> I have a SQL Server 2005 Standard Edition on SP1 where the Re-Index
> Maintenance task normally takes 5 minutes however every now and then the
> job
> will take over 8 hours to complete.
> Can anyone point me in the right direction to identify how to identify why
> the re-index takes so long?
> I have scripted the maintenance plan and listed below is a sample of the
> re-index
> ALTER INDEX [pk_Account] ON [dbo].[Account] REBUILD WITH ( PAD
_INDEX =
> OFF,
> STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =
> ON,
> SORT_IN_TEMPDB = OFF, ONLINE = OFF )
> Thanks|||Is there a long-running transaction that's holding a lock on the table? That
may prevent the index rebuild acquiring the locks it requires.
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ycFxx7tHHA.4800@.TK2MSFTNGP05.phx.gbl...
> David
> How big is database? Any activities on the server while you are running
> MP?
>
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:44134DDF-BCC0-4130-BE08-B053231876BA@.microsoft.com...
>

Logins & password

I need to rebuild SQL Server from Enterprise Edition to Standard Edition.
Is there a way to save the passwords for all SQL logins (SQL
authentication)? I know that restoring master will get the password back
but does it defeat the purpose of changing the SQL Server edition? Thanks!This occur on the same server.
"Flicker" <hthan@.superioraccess.com> wrote in message
news:eI2znzXcDHA.2392@.TK2MSFTNGP10.phx.gbl...
> if you already have the std SQL box you can DTS to transfer logins.
> "Kevin" <kevin@.noemail.com> wrote in message
> news:eom3QuXcDHA.2820@.tk2msftngp13.phx.gbl...
> > I need to rebuild SQL Server from Enterprise Edition to Standard
Edition.
> > Is there a way to save the passwords for all SQL logins (SQL
> > authentication)? I know that restoring master will get the password
back
> > but does it defeat the purpose of changing the SQL Server edition?
> Thanks!
> >
> >
>|||HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246133#4
Create the stored procedures in the KB article then run sp_help_revlogin in
Query Analyzer with results as text. You can then copy and paste the results
into Notepad and save it. Once you've rebuilt you can run the script to add
your logins with previous SID and passwords
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Kevin" <kevin@.noemail.com> wrote in message
news:eom3QuXcDHA.2820@.tk2msftngp13.phx.gbl...
I need to rebuild SQL Server from Enterprise Edition to Standard Edition.
Is there a way to save the passwords for all SQL logins (SQL
authentication)? I know that restoring master will get the password back
but does it defeat the purpose of changing the SQL Server edition? Thanks!

Loging perfmon to SQL

Hi Guys,
I have tried the standard MS lists for this question and they didnt know
that it was possible. So this might be a DBA type question.
I am looking to have 50 ish servers loging through perfmon to a sql DB. It
seams that you can do this under counter logs then logs you can select a
system DSN. Seams easy enough but I cant find any info/script to create the
DB required for the logging to work. Thought that MS may have been smart
enough if I just pointed it at a blank DB but know such luck.
If you have any ideas that would be great.
Thanks in advance
CliffHi Cliff.
It actually is smart enough to just point at a blank db - it will create the
schema itself as long as you can get it to connect. The trick is to get it
connected at all usually.
What is actually going wrong? Have you checked the Event Log via the Event
Viewer? If the problem is a login issue, you probably need to add the
Windows account group that the "Performance Logs & Alerts" service is
running under as a trusted login under SQL Security / Logins.
Follow these steps:
(a) Open the Services applet, under: Start / Control Panel / Services /
Administrative Tools / Services
(b) Double click on the "Performance Logs & Alerts" service
(c) Click on the "Log On" tab
(d) Identify the account / group (or LocalSystem) that the "Performance Logs
& Alerts" service is logging on as
(e) Open SQL Enterprise Manager
(f) Click on Security
(g) Right Click on Logins - select "Add New" Menu
(h) Use the name ([Domain]\[Account / Group name]) identified in step (d)
(i) Assign whatever permissions it needs to access the database named in
your DSN.
(j) Configure the DSN as a System DSN & ensure that the DSN uses Windows NT
Authentication to Log On.
Then start your trace & you should see the tables created as needed by
PerfMon.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Cliff" <cliff.bree@.bsl.com.au> wrote in message
news:eoxLfvbVDHA.2368@.TK2MSFTNGP09.phx.gbl...
> Hi Guys,
> I have tried the standard MS lists for this question and they didnt know
> that it was possible. So this might be a DBA type question.
> I am looking to have 50 ish servers loging through perfmon to a sql DB.
It
> seams that you can do this under counter logs then logs you can select a
> system DSN. Seams easy enough but I cant find any info/script to create
the
> DB required for the logging to work. Thought that MS may have been smart
> enough if I just pointed it at a blank DB but know such luck.
> If you have any ideas that would be great.
> Thanks in advance
> Cliff
>|||Only in XP, right?
----
The views expressed here are my own
and not of my employer.
----
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:ONWOv8cVDHA.2268@.TK2MSFTNGP11.phx.gbl...
> Hi Cliff.
> It actually is smart enough to just point at a blank db - it will create
the
> schema itself as long as you can get it to connect. The trick is to get it
> connected at all usually.
> What is actually going wrong? Have you checked the Event Log via the Event
> Viewer? If the problem is a login issue, you probably need to add the
> Windows account group that the "Performance Logs & Alerts" service is
> running under as a trusted login under SQL Security / Logins.
> Follow these steps:
> (a) Open the Services applet, under: Start / Control Panel / Services /
> Administrative Tools / Services
> (b) Double click on the "Performance Logs & Alerts" service
> (c) Click on the "Log On" tab
> (d) Identify the account / group (or LocalSystem) that the "Performance
Logs
> & Alerts" service is logging on as
> (e) Open SQL Enterprise Manager
> (f) Click on Security
> (g) Right Click on Logins - select "Add New" Menu
> (h) Use the name ([Domain]\[Account / Group name]) identified in step (d)
> (i) Assign whatever permissions it needs to access the database named in
> your DSN.
> (j) Configure the DSN as a System DSN & ensure that the DSN uses Windows
NT
> Authentication to Log On.
> Then start your trace & you should see the tables created as needed by
> PerfMon.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Cliff" <cliff.bree@.bsl.com.au> wrote in message
> news:eoxLfvbVDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > Hi Guys,
> >
> > I have tried the standard MS lists for this question and they didnt know
> > that it was possible. So this might be a DBA type question.
> >
> > I am looking to have 50 ish servers loging through perfmon to a sql DB.
> It
> > seams that you can do this under counter logs then logs you can select a
> > system DSN. Seams easy enough but I cant find any info/script to create
> the
> > DB required for the logging to work. Thought that MS may have been
smart
> > enough if I just pointed it at a blank DB but know such luck.
> >
> > If you have any ideas that would be great.
> >
> > Thanks in advance
> >
> > Cliff
> >
> >
>|||oops, that's what I meant, 2003 or XP. It's also quite easy to get to the
perfmon counters from WMI, and even easier from within .NET.
----
The views expressed here are my own
and not of my employer.
----
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OhJWx#dVDHA.3332@.tk2msftngp13.phx.gbl...
> Win 2003 does have this feature too.
> It's actually not too hard to write a PDH library wrapper to do this on
> Win2k or earlier if you really need to do this though.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:ugewv6dVDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > Only in XP, right?
> >
> >
> > --
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > news:ONWOv8cVDHA.2268@.TK2MSFTNGP11.phx.gbl...
> > > Hi Cliff.
> > >
> > > It actually is smart enough to just point at a blank db - it will
create
> > the
> > > schema itself as long as you can get it to connect. The trick is to
get
> it
> > > connected at all usually.
> > >
> > > What is actually going wrong? Have you checked the Event Log via the
> Event
> > > Viewer? If the problem is a login issue, you probably need to add the
> > > Windows account group that the "Performance Logs & Alerts" service is
> > > running under as a trusted login under SQL Security / Logins.
> > >
> > > Follow these steps:
> > > (a) Open the Services applet, under: Start / Control Panel / Services
/
> > > Administrative Tools / Services
> > > (b) Double click on the "Performance Logs & Alerts" service
> > > (c) Click on the "Log On" tab
> > > (d) Identify the account / group (or LocalSystem) that the
"Performance
> > Logs
> > > & Alerts" service is logging on as
> > > (e) Open SQL Enterprise Manager
> > > (f) Click on Security
> > > (g) Right Click on Logins - select "Add New" Menu
> > > (h) Use the name ([Domain]\[Account / Group name]) identified in step
> (d)
> > > (i) Assign whatever permissions it needs to access the database named
in
> > > your DSN.
> > > (j) Configure the DSN as a System DSN & ensure that the DSN uses
Windows
> > NT
> > > Authentication to Log On.
> > >
> > > Then start your trace & you should see the tables created as needed by
> > > PerfMon.
> > >
> > > HTH
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "Cliff" <cliff.bree@.bsl.com.au> wrote in message
> > > news:eoxLfvbVDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > > > Hi Guys,
> > > >
> > > > I have tried the standard MS lists for this question and they didnt
> know
> > > > that it was possible. So this might be a DBA type question.
> > > >
> > > > I am looking to have 50 ish servers loging through perfmon to a sql
> DB.
> > > It
> > > > seams that you can do this under counter logs then logs you can
select
> a
> > > > system DSN. Seams easy enough but I cant find any info/script to
> create
> > > the
> > > > DB required for the logging to work. Thought that MS may have been
> > smart
> > > > enough if I just pointed it at a blank DB but know such luck.
> > > >
> > > > If you have any ideas that would be great.
> > > >
> > > > Thanks in advance
> > > >
> > > > Cliff
> > > >
> > > >
> > >
> > >
> >
> >
>|||Yep - WMI & .Net are good general approaches. I don't want to sound like a
dinosaur, but imho when you're talking about performance monitoring,
efficiency (usually) REALLY matters. Using a .Net wrapper (or even WMI) are
just never going to perform as well as a solution written directly against
the Win32 API (or PDH lib) with an efficient language like C.
Regards,
Greg Linwood
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:#kE0pAeVDHA.1832@.TK2MSFTNGP09.phx.gbl...
> oops, that's what I meant, 2003 or XP. It's also quite easy to get to the
> perfmon counters from WMI, and even easier from within .NET.
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OhJWx#dVDHA.3332@.tk2msftngp13.phx.gbl...
> > Win 2003 does have this feature too.
> >
> > It's actually not too hard to write a PDH library wrapper to do this on
> > Win2k or earlier if you really need to do this though.
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > news:ugewv6dVDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > > Only in XP, right?
> > >
> > >
> > > --
> > > ----
> > > The views expressed here are my own
> > > and not of my employer.
> > > ----
> > > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > > news:ONWOv8cVDHA.2268@.TK2MSFTNGP11.phx.gbl...
> > > > Hi Cliff.
> > > >
> > > > It actually is smart enough to just point at a blank db - it will
> create
> > > the
> > > > schema itself as long as you can get it to connect. The trick is to
> get
> > it
> > > > connected at all usually.
> > > >
> > > > What is actually going wrong? Have you checked the Event Log via the
> > Event
> > > > Viewer? If the problem is a login issue, you probably need to add
the
> > > > Windows account group that the "Performance Logs & Alerts" service
is
> > > > running under as a trusted login under SQL Security / Logins.
> > > >
> > > > Follow these steps:
> > > > (a) Open the Services applet, under: Start / Control Panel /
Services
> /
> > > > Administrative Tools / Services
> > > > (b) Double click on the "Performance Logs & Alerts" service
> > > > (c) Click on the "Log On" tab
> > > > (d) Identify the account / group (or LocalSystem) that the
> "Performance
> > > Logs
> > > > & Alerts" service is logging on as
> > > > (e) Open SQL Enterprise Manager
> > > > (f) Click on Security
> > > > (g) Right Click on Logins - select "Add New" Menu
> > > > (h) Use the name ([Domain]\[Account / Group name]) identified in
step
> > (d)
> > > > (i) Assign whatever permissions it needs to access the database
named
> in
> > > > your DSN.
> > > > (j) Configure the DSN as a System DSN & ensure that the DSN uses
> Windows
> > > NT
> > > > Authentication to Log On.
> > > >
> > > > Then start your trace & you should see the tables created as needed
by
> > > > PerfMon.
> > > >
> > > > HTH
> > > >
> > > > Regards,
> > > > Greg Linwood
> > > > SQL Server MVP
> > > >
> > > > "Cliff" <cliff.bree@.bsl.com.au> wrote in message
> > > > news:eoxLfvbVDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > > > > Hi Guys,
> > > > >
> > > > > I have tried the standard MS lists for this question and they
didnt
> > know
> > > > > that it was possible. So this might be a DBA type question.
> > > > >
> > > > > I am looking to have 50 ish servers loging through perfmon to a
sql
> > DB.
> > > > It
> > > > > seams that you can do this under counter logs then logs you can
> select
> > a
> > > > > system DSN. Seams easy enough but I cant find any info/script to
> > create
> > > > the
> > > > > DB required for the logging to work. Thought that MS may have
been
> > > smart
> > > > > enough if I just pointed it at a blank DB but know such luck.
> > > > >
> > > > > If you have any ideas that would be great.
> > > > >
> > > > > Thanks in advance
> > > > >
> > > > > Cliff
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||You may want to read this article:
http://www.sqlservercentral.com/columnists/losmith/automatedtracefilecollect
ion.asp
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OYjPCIeVDHA.2268@.TK2MSFTNGP11.phx.gbl...
> Yep - WMI & .Net are good general approaches. I don't want to sound like a
> dinosaur, but imho when you're talking about performance monitoring,
> efficiency (usually) REALLY matters. Using a .Net wrapper (or even WMI)
are
> just never going to perform as well as a solution written directly against
> the Win32 API (or PDH lib) with an efficient language like C.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:#kE0pAeVDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > oops, that's what I meant, 2003 or XP. It's also quite easy to get to
the
> > perfmon counters from WMI, and even easier from within .NET.
> >
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > news:OhJWx#dVDHA.3332@.tk2msftngp13.phx.gbl...
> > > Win 2003 does have this feature too.
> > >
> > > It's actually not too hard to write a PDH library wrapper to do this
on
> > > Win2k or earlier if you really need to do this though.
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > news:ugewv6dVDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > > > Only in XP, right?
> > > >
> > > >
> > > > --
> > > > ----
> > > > The views expressed here are my own
> > > > and not of my employer.
> > > > ----
> > > > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > > > news:ONWOv8cVDHA.2268@.TK2MSFTNGP11.phx.gbl...
> > > > > Hi Cliff.
> > > > >
> > > > > It actually is smart enough to just point at a blank db - it will
> > create
> > > > the
> > > > > schema itself as long as you can get it to connect. The trick is
to
> > get
> > > it
> > > > > connected at all usually.
> > > > >
> > > > > What is actually going wrong? Have you checked the Event Log via
the
> > > Event
> > > > > Viewer? If the problem is a login issue, you probably need to add
> the
> > > > > Windows account group that the "Performance Logs & Alerts" service
> is
> > > > > running under as a trusted login under SQL Security / Logins.
> > > > >
> > > > > Follow these steps:
> > > > > (a) Open the Services applet, under: Start / Control Panel /
> Services
> > /
> > > > > Administrative Tools / Services
> > > > > (b) Double click on the "Performance Logs & Alerts" service
> > > > > (c) Click on the "Log On" tab
> > > > > (d) Identify the account / group (or LocalSystem) that the
> > "Performance
> > > > Logs
> > > > > & Alerts" service is logging on as
> > > > > (e) Open SQL Enterprise Manager
> > > > > (f) Click on Security
> > > > > (g) Right Click on Logins - select "Add New" Menu
> > > > > (h) Use the name ([Domain]\[Account / Group name]) identified in
> step
> > > (d)
> > > > > (i) Assign whatever permissions it needs to access the database
> named
> > in
> > > > > your DSN.
> > > > > (j) Configure the DSN as a System DSN & ensure that the DSN uses
> > Windows
> > > > NT
> > > > > Authentication to Log On.
> > > > >
> > > > > Then start your trace & you should see the tables created as
needed
> by
> > > > > PerfMon.
> > > > >
> > > > > HTH
> > > > >
> > > > > Regards,
> > > > > Greg Linwood
> > > > > SQL Server MVP
> > > > >
> > > > > "Cliff" <cliff.bree@.bsl.com.au> wrote in message
> > > > > news:eoxLfvbVDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > > > > > Hi Guys,
> > > > > >
> > > > > > I have tried the standard MS lists for this question and they
> didnt
> > > know
> > > > > > that it was possible. So this might be a DBA type question.
> > > > > >
> > > > > > I am looking to have 50 ish servers loging through perfmon to a
> sql
> > > DB.
> > > > > It
> > > > > > seams that you can do this under counter logs then logs you can
> > select
> > > a
> > > > > > system DSN. Seams easy enough but I cant find any info/script
to
> > > create
> > > > > the
> > > > > > DB required for the logging to work. Thought that MS may have
> been
> > > > smart
> > > > > > enough if I just pointed it at a blank DB but know such luck.
> > > > > >
> > > > > > If you have any ideas that would be great.
> > > > > >
> > > > > > Thanks in advance
> > > > > >
> > > > > > Cliff
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Ah, yes, but monitoring should not be a signicant part of your system
workload anyway, can be done remotely, and can scale out!
--
----
The views expressed here are my own
and not of my employer.
----
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OYjPCIeVDHA.2268@.TK2MSFTNGP11.phx.gbl...
> Yep - WMI & .Net are good general approaches. I don't want to sound like a
> dinosaur, but imho when you're talking about performance monitoring,
> efficiency (usually) REALLY matters. Using a .Net wrapper (or even WMI)
are
> just never going to perform as well as a solution written directly against
> the Win32 API (or PDH lib) with an efficient language like C.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:#kE0pAeVDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > oops, that's what I meant, 2003 or XP. It's also quite easy to get to
the
> > perfmon counters from WMI, and even easier from within .NET.
> >
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > news:OhJWx#dVDHA.3332@.tk2msftngp13.phx.gbl...
> > > Win 2003 does have this feature too.
> > >
> > > It's actually not too hard to write a PDH library wrapper to do this
on
> > > Win2k or earlier if you really need to do this though.
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > news:ugewv6dVDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > > > Only in XP, right?
> > > >
> > > >
> > > > --
> > > > ----
> > > > The views expressed here are my own
> > > > and not of my employer.
> > > > ----
> > > > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > > > news:ONWOv8cVDHA.2268@.TK2MSFTNGP11.phx.gbl...
> > > > > Hi Cliff.
> > > > >
> > > > > It actually is smart enough to just point at a blank db - it will
> > create
> > > > the
> > > > > schema itself as long as you can get it to connect. The trick is
to
> > get
> > > it
> > > > > connected at all usually.
> > > > >
> > > > > What is actually going wrong? Have you checked the Event Log via
the
> > > Event
> > > > > Viewer? If the problem is a login issue, you probably need to add
> the
> > > > > Windows account group that the "Performance Logs & Alerts" service
> is
> > > > > running under as a trusted login under SQL Security / Logins.
> > > > >
> > > > > Follow these steps:
> > > > > (a) Open the Services applet, under: Start / Control Panel /
> Services
> > /
> > > > > Administrative Tools / Services
> > > > > (b) Double click on the "Performance Logs & Alerts" service
> > > > > (c) Click on the "Log On" tab
> > > > > (d) Identify the account / group (or LocalSystem) that the
> > "Performance
> > > > Logs
> > > > > & Alerts" service is logging on as
> > > > > (e) Open SQL Enterprise Manager
> > > > > (f) Click on Security
> > > > > (g) Right Click on Logins - select "Add New" Menu
> > > > > (h) Use the name ([Domain]\[Account / Group name]) identified in
> step
> > > (d)
> > > > > (i) Assign whatever permissions it needs to access the database
> named
> > in
> > > > > your DSN.
> > > > > (j) Configure the DSN as a System DSN & ensure that the DSN uses
> > Windows
> > > > NT
> > > > > Authentication to Log On.
> > > > >
> > > > > Then start your trace & you should see the tables created as
needed
> by
> > > > > PerfMon.
> > > > >
> > > > > HTH
> > > > >
> > > > > Regards,
> > > > > Greg Linwood
> > > > > SQL Server MVP
> > > > >
> > > > > "Cliff" <cliff.bree@.bsl.com.au> wrote in message
> > > > > news:eoxLfvbVDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > > > > > Hi Guys,
> > > > > >
> > > > > > I have tried the standard MS lists for this question and they
> didnt
> > > know
> > > > > > that it was possible. So this might be a DBA type question.
> > > > > >
> > > > > > I am looking to have 50 ish servers loging through perfmon to a
> sql
> > > DB.
> > > > > It
> > > > > > seams that you can do this under counter logs then logs you can
> > select
> > > a
> > > > > > system DSN. Seams easy enough but I cant find any info/script
to
> > > create
> > > > > the
> > > > > > DB required for the logging to work. Thought that MS may have
> been
> > > > smart
> > > > > > enough if I just pointed it at a blank DB but know such luck.
> > > > > >
> > > > > > If you have any ideas that would be great.
> > > > > >
> > > > > > Thanks in advance
> > > > > >
> > > > > > Cliff
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Login.Language property for sa is not set.

I installed SQL 2000 standard edition.

Then I called server.Logins["sa"].Language

For some reason the value of this property was "" (empty string).

Can anyone explain, why?

P.S. After changing Default language for 'sa' login through Management Studio Language property became accessible..

Alexander Sychev.

The language property is tied to the property set in SQL for the login. By default it is just <default> and that is an empty string. When you set it like you did, the property is explicitly set.

Login.Language property for sa is not set.

I installed SQL 2000 standard edition.

Then I called server.Logins["sa"].Language

For some reason the value of this property was "" (empty string).

Can anyone explain, why?

P.S. After changing Default language for 'sa' login through Management Studio Language property became accessible..

Alexander Sychev.

The language property is tied to the property set in SQL for the login. By default it is just <default> and that is an empty string. When you set it like you did, the property is explicitly set.

Login problem by not being in sysadmin group

Hi All

I experience a very strange login problem:

I create standard security login, let say test1/test1 with a default db test and assign it sysadmin group.

All is working well.

The moment remove sysadmin group from this login, i start getting errors:

Login failed for user 'test1'

... when I login from remote server. If I login from the same host - it continues with no problem.

When I go to sql server configuration manager, I see next:

sql native client configuration(32bit):

shared memeory enabled

tcp/ip enabled

named pipes enabled

VIA disabled

The same settings from sql server 2005 network configuration / protocols for mssqlserver

sql native client configuration / client protocols

sql 2005 surface area configuration / remote connections is configured:

local and remote connections (checked), using both tcp/ip and named pipes.

Does anybody have a clue?

What is the default database for your login?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Hi Wesley

Default db is named 'test'. I made sure login has default db and can browse it.

|||

Try changing the default database to master. If you can then connect with that login, there is a problem with the users access to what you were using for the default database.

-Sue

Login problem by not being in sysadmin group

Hi All

I experience a very strange login problem:

I create standard security login, let say test1/test1 with a default db test and assign it sysadmin group.

All is working well.

The moment remove sysadmin group from this login, i start getting errors:

Login failed for user 'test1'

... when I login from remote server. If I login from the same host - it continues with no problem.

When I go to sql server configuration manager, I see next:

sql native client configuration(32bit):

shared memeory enabled

tcp/ip enabled

named pipes enabled

VIA disabled

The same settings from sql server 2005 network configuration / protocols for mssqlserver

sql native client configuration / client protocols

sql 2005 surface area configuration / remote connections is configured:

local and remote connections (checked), using both tcp/ip and named pipes.

Does anybody have a clue?

What is the default database for your login?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Hi Wesley

Default db is named 'test'. I made sure login has default db and can browse it.

|||

Try changing the default database to master. If you can then connect with that login, there is a problem with the users access to what you were using for the default database.

-Sue

Login Page just refreshes with ASP.NET 2.0 Login Control

I am try to login using the ASP.NET 2.0 Standard Login Control. When I enter the correct userId/Password and hit the Login button, the page just refreshes, no error is displayed. But if I enter wrong userid/password..it give me an error.

Also I check the Membership.ValidateUser() returns true, that means the user is validated, but still I am able to redirect to ReturnURL.?

Is there something wrong with the Authentication, or Creating a cookie. Can somebody shed some light on this,

I have the following machine configuration

Windows XP Pro Service Pack2
IIS 5.1
SQL Server 2000
Visual Studio 2005
DISABLE ANONMYOUS ACCESS IN IIS
ENABLE WINDOWS AUTHENTICATION

I am getting the following output :

Membership.ValidateUser(Login1.UserName, Login1.Password) : - True
HttpContext.Current.User.Identity.Name : - // is blank
User.Identity.IsAuthenticated :- False
User.Identity.AuthenticationType :- // is blank

NOTE : The applicationName is same in web.config as well as in the aspnet_Applications table in SQL Server 2000 membership database.

Do you have an error logged in the SQL Server error log file (name starts with ERRORLOG)?

If not, then you should post the question on an ASP forum.

Thanks
Laurentiu

Login Page just refreshes with ASP.NET 2.0 Login Control

I am try to login using the ASP.NET 2.0 Standard Login Control. When I enter the correct userId/Password and hit the Login button, the page just refreshes, no error is displayed. But if I enter wrong userid/password..it give me an error.

Also I check the Membership.ValidateUser() returns true, that means the user is validated, but still I am able to redirect to ReturnURL.?

Is there something wrong with the Authentication, or Creating a cookie. Can somebody shed some light on this,

I have the following machine configuration

Windows XP Pro Service Pack2
IIS 5.1
SQL Server 2000
Visual Studio 2005
DISABLE ANONMYOUS ACCESS IN IIS
ENABLE WINDOWS AUTHENTICATION

I am getting the following output :

Membership.ValidateUser(Login1.UserName, Login1.Password) : - True
HttpContext.Current.User.Identity.Name : - // is blank
User.Identity.IsAuthenticated :- False
User.Identity.AuthenticationType :- // is blank

NOTE : The applicationName is same in web.config as well as in the aspnet_Applications table in SQL Server 2000 membership database.

Do you have an error logged in the SQL Server error log file (name starts with ERRORLOG)?

If not, then you should post the question on an ASP forum.

Thanks
Laurentiu