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...
>

logtransaction backup

Hi all,
Sql server 7.
I have been given task to schedule transaction log backup for every six hours. Pls give me the steps as how to schedule a transaction log backup.
TIA
Waiting for replyUhhh with job scheduler?

That's the easy way...

with code you'll need to use system stored procedures...look them up in BOL...

sp_Add_Job, ect|||sorry if i am not clear, i want to know how to take transaction log backup.|||Do you have client tools installed?

Do you have Books Online?

Look up BACKUP LOG...

If you want to do it with T-SQL

You could use the DB Maint Wizard in Enterprise Manager as well...

LogShipping

Hi all I have created a log shipping task in my SQL servers
This Is the server order
Ist Server (Win2003 , SQL 2000 sp3) is the data server let's say
2nd Server (win2003 , SQL 2000 sp3) Is the monitor server
3rd Server (win2000, SQL 2000 sp3) Is the server that accepts the data
(1st & 2nd server is a clucter system )
And the line Connecting 1st & 2nd Server with the 3rd server is 1MB
The problem now is that the SQL Help is awful about logshipping. It explains
nothing!!
I have Create the log shipping but i get en error that the databases are not
sync.
And the FileLoaded and file copped is stack on First_File...trn
And also my databades seemed to be in sync
What Is wrong'
What should I do'
Please HELP!!!
Thanks in advance
DimitrisHi,
I have to correct you on something...there is plenty of
documentation for log shipping!
Try looking at BOL and this link will help. You should
read before implementing.
http://support.microsoft.com/?id=323135
hth
DeeJay
>--Original Message--
>Hi all I have created a log shipping task in my SQL
servers
>This Is the server order
>Ist Server (Win2003 , SQL 2000 sp3) is the data server
let's say
>2nd Server (win2003 , SQL 2000 sp3) Is the monitor server
>3rd Server (win2000, SQL 2000 sp3) Is the server that
accepts the data
>(1st & 2nd server is a clucter system )
>And the line Connecting 1st & 2nd Server with the 3rd
server is 1MB
>The problem now is that the SQL Help is awful about
logshipping. It explains
>nothing!!
>I have Create the log shipping but i get en error that
the databases are not
>sync.
>And the FileLoaded and file copped is stack on
First_File...trn
>And also my databades seemed to be in sync
>What Is wrong'
>What should I do'
>Please HELP!!!
>Thanks in advance
>Dimitris
>
>
>.
>

Logins Successful or Failed Auditing

Hello,
I have a task to do.
I need to get all the logins (successful or failed) into a table along with
the timestamp which I can keep truncating as and when I want so that it
doesn't grow too big.
I have tried using server side traces to do this. But this has 2
disadvantages which make me NOT want to use it.
1. The output file (.trc file) can be viewed only when the rollover size is
meet or when the SQL Server is stopped <--bad mojo.
2. There is no solution for when the output files keep on growing and
rolling over. I have 75+ servers to monitor. I want something that will keep
record for say, last 1 week thats all. Output in a table can be so so better.
Apparently, using server side trace you cant have output in table.
Does anyone have any suggestions ? I figured I could do something like put a
trigger on sysprocesses but this won't give me the failed logins.
Also, server load would be incredible with a trigger shooting off each time.
Any help is appreciated. Oh, and C2 Audit option is out of the question.
Thats just too detailed.
Thanks.
Regards,
Kunal
Hello,
The only thing I can suggest is that you can import the data from the .trc
file into a table using the function fn_trace_gettable (see BOL for exact
syntax).
Hope this helps.
"kunalap" wrote:

> Hello,
> I have a task to do.
> I need to get all the logins (successful or failed) into a table along with
> the timestamp which I can keep truncating as and when I want so that it
> doesn't grow too big.
> I have tried using server side traces to do this. But this has 2
> disadvantages which make me NOT want to use it.
> 1. The output file (.trc file) can be viewed only when the rollover size is
> meet or when the SQL Server is stopped <--bad mojo.
> 2. There is no solution for when the output files keep on growing and
> rolling over. I have 75+ servers to monitor. I want something that will keep
> record for say, last 1 week thats all. Output in a table can be so so better.
> Apparently, using server side trace you cant have output in table.
> Does anyone have any suggestions ? I figured I could do something like put a
> trigger on sysprocesses but this won't give me the failed logins.
> Also, server load would be incredible with a trigger shooting off each time.
> Any help is appreciated. Oh, and C2 Audit option is out of the question.
> Thats just too detailed.
> Thanks.
> Regards,
> Kunal
|||How about writing a small VB.NET app that is scheduled frequently and reds off of the event log and
import into a table?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"kunalap" <kunalap@.discussions.microsoft.com> wrote in message
news:DE02C02D-0C1D-447B-B98C-BE4F63F5A207@.microsoft.com...
> Hello,
> I have a task to do.
> I need to get all the logins (successful or failed) into a table along with
> the timestamp which I can keep truncating as and when I want so that it
> doesn't grow too big.
> I have tried using server side traces to do this. But this has 2
> disadvantages which make me NOT want to use it.
> 1. The output file (.trc file) can be viewed only when the rollover size is
> meet or when the SQL Server is stopped <--bad mojo.
> 2. There is no solution for when the output files keep on growing and
> rolling over. I have 75+ servers to monitor. I want something that will keep
> record for say, last 1 week thats all. Output in a table can be so so better.
> Apparently, using server side trace you cant have output in table.
> Does anyone have any suggestions ? I figured I could do something like put a
> trigger on sysprocesses but this won't give me the failed logins.
> Also, server load would be incredible with a trigger shooting off each time.
> Any help is appreciated. Oh, and C2 Audit option is out of the question.
> Thats just too detailed.
> Thanks.
> Regards,
> Kunal
|||Thanks for the reply Anoop.
I was already aware of the function. But it is only for viewing the data in
query analyzer.
I guess I will have to setup another job to delete the older .trc files.
Thanks.
-Kunal.
"Anoop" wrote:
[vbcol=seagreen]
> Hello,
> The only thing I can suggest is that you can import the data from the .trc
> file into a table using the function fn_trace_gettable (see BOL for exact
> syntax).
> Hope this helps.
>
> "kunalap" wrote:

Logins Successful or Failed Auditing

Hello,
I have a task to do.
I need to get all the logins (successful or failed) into a table along with
the timestamp which I can keep truncating as and when I want so that it
doesn't grow too big.
I have tried using server side traces to do this. But this has 2
disadvantages which make me NOT want to use it.
1. The output file (.trc file) can be viewed only when the rollover size is
meet or when the SQL Server is stopped <--bad mojo.
2. There is no solution for when the output files keep on growing and
rolling over. I have 75+ servers to monitor. I want something that will keep
record for say, last 1 week thats all. Output in a table can be so so better.
Apparently, using server side trace you cant have output in table.
Does anyone have any suggestions ? I figured I could do something like put a
trigger on sysprocesses but this won't give me the failed logins.
Also, server load would be incredible with a trigger shooting off each time.
Any help is appreciated. Oh, and C2 Audit option is out of the question.
Thats just too detailed.
Thanks.
Regards,
KunalHello,
The only thing I can suggest is that you can import the data from the .trc
file into a table using the function fn_trace_gettable (see BOL for exact
syntax).
Hope this helps.
"kunalap" wrote:
> Hello,
> I have a task to do.
> I need to get all the logins (successful or failed) into a table along with
> the timestamp which I can keep truncating as and when I want so that it
> doesn't grow too big.
> I have tried using server side traces to do this. But this has 2
> disadvantages which make me NOT want to use it.
> 1. The output file (.trc file) can be viewed only when the rollover size is
> meet or when the SQL Server is stopped <--bad mojo.
> 2. There is no solution for when the output files keep on growing and
> rolling over. I have 75+ servers to monitor. I want something that will keep
> record for say, last 1 week thats all. Output in a table can be so so better.
> Apparently, using server side trace you cant have output in table.
> Does anyone have any suggestions ? I figured I could do something like put a
> trigger on sysprocesses but this won't give me the failed logins.
> Also, server load would be incredible with a trigger shooting off each time.
> Any help is appreciated. Oh, and C2 Audit option is out of the question.
> Thats just too detailed.
> Thanks.
> Regards,
> Kunal|||How about writing a small VB.NET app that is scheduled frequently and reds off of the event log and
import into a table?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"kunalap" <kunalap@.discussions.microsoft.com> wrote in message
news:DE02C02D-0C1D-447B-B98C-BE4F63F5A207@.microsoft.com...
> Hello,
> I have a task to do.
> I need to get all the logins (successful or failed) into a table along with
> the timestamp which I can keep truncating as and when I want so that it
> doesn't grow too big.
> I have tried using server side traces to do this. But this has 2
> disadvantages which make me NOT want to use it.
> 1. The output file (.trc file) can be viewed only when the rollover size is
> meet or when the SQL Server is stopped <--bad mojo.
> 2. There is no solution for when the output files keep on growing and
> rolling over. I have 75+ servers to monitor. I want something that will keep
> record for say, last 1 week thats all. Output in a table can be so so better.
> Apparently, using server side trace you cant have output in table.
> Does anyone have any suggestions ? I figured I could do something like put a
> trigger on sysprocesses but this won't give me the failed logins.
> Also, server load would be incredible with a trigger shooting off each time.
> Any help is appreciated. Oh, and C2 Audit option is out of the question.
> Thats just too detailed.
> Thanks.
> Regards,
> Kunal|||Thanks for the reply Anoop.
I was already aware of the function. But it is only for viewing the data in
query analyzer.
I guess I will have to setup another job to delete the older .trc files.
Thanks.
-Kunal.
"Anoop" wrote:
> Hello,
> The only thing I can suggest is that you can import the data from the .trc
> file into a table using the function fn_trace_gettable (see BOL for exact
> syntax).
> Hope this helps.
>
> "kunalap" wrote:
> > Hello,
> >
> > I have a task to do.
> > I need to get all the logins (successful or failed) into a table along with
> > the timestamp which I can keep truncating as and when I want so that it
> > doesn't grow too big.
> >
> > I have tried using server side traces to do this. But this has 2
> > disadvantages which make me NOT want to use it.
> >
> > 1. The output file (.trc file) can be viewed only when the rollover size is
> > meet or when the SQL Server is stopped <--bad mojo.
> > 2. There is no solution for when the output files keep on growing and
> > rolling over. I have 75+ servers to monitor. I want something that will keep
> > record for say, last 1 week thats all. Output in a table can be so so better.
> > Apparently, using server side trace you cant have output in table.
> >
> > Does anyone have any suggestions ? I figured I could do something like put a
> > trigger on sysprocesses but this won't give me the failed logins.
> > Also, server load would be incredible with a trigger shooting off each time.
> >
> > Any help is appreciated. Oh, and C2 Audit option is out of the question.
> > Thats just too detailed.
> >
> > Thanks.
> >
> > Regards,
> > Kunal

Logins Successful or Failed Auditing

Hello,
I have a task to do.
I need to get all the logins (successful or failed) into a table along with
the timestamp which I can keep truncating as and when I want so that it
doesn't grow too big.
I have tried using server side traces to do this. But this has 2
disadvantages which make me NOT want to use it.
1. The output file (.trc file) can be viewed only when the rollover size is
meet or when the SQL Server is stopped <--bad mojo.
2. There is no solution for when the output files keep on growing and
rolling over. I have 75+ servers to monitor. I want something that will keep
record for say, last 1 week thats all. Output in a table can be so so better
.
Apparently, using server side trace you cant have output in table.
Does anyone have any suggestions ? I figured I could do something like put a
trigger on sysprocesses but this won't give me the failed logins.
Also, server load would be incredible with a trigger shooting off each time.
Any help is appreciated. Oh, and C2 Audit option is out of the question.
Thats just too detailed.
Thanks.
Regards,
KunalHello,
The only thing I can suggest is that you can import the data from the .trc
file into a table using the function fn_trace_gettable (see BOL for exact
syntax).
Hope this helps.
"kunalap" wrote:

> Hello,
> I have a task to do.
> I need to get all the logins (successful or failed) into a table along wit
h
> the timestamp which I can keep truncating as and when I want so that it
> doesn't grow too big.
> I have tried using server side traces to do this. But this has 2
> disadvantages which make me NOT want to use it.
> 1. The output file (.trc file) can be viewed only when the rollover size i
s
> meet or when the SQL Server is stopped <--bad mojo.
> 2. There is no solution for when the output files keep on growing and
> rolling over. I have 75+ servers to monitor. I want something that will ke
ep
> record for say, last 1 week thats all. Output in a table can be so so bett
er.
> Apparently, using server side trace you cant have output in table.
> Does anyone have any suggestions ? I figured I could do something like put
a
> trigger on sysprocesses but this won't give me the failed logins.
> Also, server load would be incredible with a trigger shooting off each tim
e.
> Any help is appreciated. Oh, and C2 Audit option is out of the question.
> Thats just too detailed.
> Thanks.
> Regards,
> Kunal|||How about writing a small VB.NET app that is scheduled frequently and reds o
ff of the event log and
import into a table?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"kunalap" <kunalap@.discussions.microsoft.com> wrote in message
news:DE02C02D-0C1D-447B-B98C-BE4F63F5A207@.microsoft.com...
> Hello,
> I have a task to do.
> I need to get all the logins (successful or failed) into a table along wit
h
> the timestamp which I can keep truncating as and when I want so that it
> doesn't grow too big.
> I have tried using server side traces to do this. But this has 2
> disadvantages which make me NOT want to use it.
> 1. The output file (.trc file) can be viewed only when the rollover size i
s
> meet or when the SQL Server is stopped <--bad mojo.
> 2. There is no solution for when the output files keep on growing and
> rolling over. I have 75+ servers to monitor. I want something that will ke
ep
> record for say, last 1 week thats all. Output in a table can be so so bett
er.
> Apparently, using server side trace you cant have output in table.
> Does anyone have any suggestions ? I figured I could do something like put
a
> trigger on sysprocesses but this won't give me the failed logins.
> Also, server load would be incredible with a trigger shooting off each tim
e.
> Any help is appreciated. Oh, and C2 Audit option is out of the question.
> Thats just too detailed.
> Thanks.
> Regards,
> Kunal|||Thanks for the reply Anoop.
I was already aware of the function. But it is only for viewing the data in
query analyzer.
I guess I will have to setup another job to delete the older .trc files.
Thanks.
-Kunal.
"Anoop" wrote:
[vbcol=seagreen]
> Hello,
> The only thing I can suggest is that you can import the data from the .trc
> file into a table using the function fn_trace_gettable (see BOL for exact
> syntax).
> Hope this helps.
>
> "kunalap" wrote:
>