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

long html string

Hi All
I'm trying to display a html report from a store procedure but the string is
to long. I try to cut the sting in parts but no luck.
CREATE PROCEDURE createReport
@.orderNumber int
AS
declare @.lineDesc01 varchar(8000)
declare @.lineDesc02 varchar(8000)
declare @.lineDesc03 varchar(8000)
set @.lineDesc01 = 'select lineDesc = ''<html><head><title>blabla...
<td > ''+ cast(b.orderID as varchar) +''</td> bla...'
set @.lineDesc02 = '</td>
<td>''+ isnull(b.myDesc, '') +''</td>
</tr>
</table><br> bla.bla...
set @.lineDesc03 = '</td>
<td>como esta</td>
</tr>
</table></body></html>''
FROM orders b
WHERE b.orderID = @.orderNumber'
exec( @.lineDesc01 + @.lineDesc02 + @.lineDesc03)
How can i fix this problem?
Is other way to do this?
Tks in advance.
JFBWhy are you creating the presentation (HTML) in the stored procedure? You
should always try to minimize the amount of data you pass server <-> client
and you should leave as much of the presenation as you can where it belongs,
at the presentation layer.
But aside from that, why are you using variables at all?
SELECT '<html>... '+RTRIM(OrderID)+'...up to about 8000',
'</td><td>'+COALESCE(myDesc,'')+'... up to about 8000',
'</td><td>como esta...up to about 8000'
FROM orders
WHERE orderID = @.orderNumber
Now the presentation tier just has to write out however many columns you end
up with (and I only suggest using columns because you need to concatenate).
There are much, much, much better was to do this, even if you are going to
insist on the bad practice of missing data operations with presentation
details...
"JFB" <help@.jfb.com> wrote in message
news:uAKXgsfkFHA.2792@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I'm trying to display a html report from a store procedure but the string
> is to long. I try to cut the sting in parts but no luck.
> CREATE PROCEDURE createReport
> @.orderNumber int
> AS
> declare @.lineDesc01 varchar(8000)
> declare @.lineDesc02 varchar(8000)
> declare @.lineDesc03 varchar(8000)
> set @.lineDesc01 = 'select lineDesc = ''<html><head><title>blabla...
> <td > ''+ cast(b.orderID as varchar) +''</td> bla...'
> set @.lineDesc02 = '</td>
> <td>''+ isnull(b.myDesc, '') +''</td>
> </tr>
> </table><br> bla.bla...
> set @.lineDesc03 = '</td>
> <td>como esta</td>
> </tr>
> </table></body></html>''
> FROM orders b
> WHERE b.orderID = @.orderNumber'
> exec( @.lineDesc01 + @.lineDesc02 + @.lineDesc03)
> How can i fix this problem?
> Is other way to do this?
> Tks in advance.
> JFB
>|||How about learning something new and useful?
Keywords: SQLXML, XSL.
If it really needs to be done straight from the server.
ML|||Tks...You right I will try to use my normal way.
Can you tell me about other ways?
Rgds
JFB
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uMABvxfkFHA.320@.TK2MSFTNGP09.phx.gbl...
> Why are you creating the presentation (HTML) in the stored procedure? You
> should always try to minimize the amount of data you pass server <->
> client and you should leave as much of the presenation as you can where it
> belongs, at the presentation layer.
> But aside from that, why are you using variables at all?
> SELECT '<html>... '+RTRIM(OrderID)+'...up to about 8000',
> '</td><td>'+COALESCE(myDesc,'')+'... up to about 8000',
> '</td><td>como esta...up to about 8000'
> FROM orders
> WHERE orderID = @.orderNumber
> Now the presentation tier just has to write out however many columns you
> end up with (and I only suggest using columns because you need to
> concatenate).
> There are much, much, much better was to do this, even if you are going to
> insist on the bad practice of missing data operations with presentation
> details...
>
> "JFB" <help@.jfb.com> wrote in message
> news:uAKXgsfkFHA.2792@.TK2MSFTNGP10.phx.gbl...
>|||JFB,
Generally you would return only data from the database, usually in the form
of a recordset. For example, your stored proc would be like this (contents
only):
SELECT OrderID, [Description]
FROM dbo.Orders
WHERE OrderID = @.OrderId
--
Then say you were presenting the report via an ASP web page, it would look
something like this:
// This code would be in your ASP file
// Assume command has been executed and returned a recordset "rs"
for (; !rs.EOF; rs.MoveNext())
{
Response.Write( "<tr><td>" );
Response.Write( rs.Fields("OrderID").Value );
Response.Write( "</td><td>" );
Response.Write( rs.Fields("Description").Value );
Response.Write( "</td></tr>" );
}
"JFB" wrote:

> Tks...You right I will try to use my normal way.
> Can you tell me about other ways?
> Rgds
> JFB
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:uMABvxfkFHA.320@.TK2MSFTNGP09.phx.gbl...
>
>|||mmm... do you have any exmples? or any web sites to check it out?
Tks for you reply
JFB
"ML" <ML@.discussions.microsoft.com> wrote in message
news:44494029-B841-4A9E-8005-9CDDA3557DDD@.microsoft.com...
> How about learning something new and useful?
> Keywords: SQLXML, XSL.
> If it really needs to be done straight from the server.
>
> ML|||> any web sites to check it out?
www.sqlxml.org

login names are deleted after restoring the backups

hi all
i have taken backup of a database 'XYZ' and restored it to a different
server with the same database name 'XYZ', it restored all the items/objects
successfully but the users of that database had blank loginnames in the
restored database. now it makes the objects of that user unaccessable
(without the login name the user cannot access its tables in the database).
i have also tried to make the users before database restoration but when the
database backup is restored it eliminates the user loginname
i'll be thankyou if anyone has the solution
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200508/1
HI,
Tranfer all the logins from the mainserver to ur restored database from
dts.
or restore the master database backup
from
sufian
|||You need to remap the user in the database to the logins on the other server
(assuming they exist). I tend to prefer to use sp_SidMap for this task which
can be found in the MapSids link in the following KB article
HOW TO: Resolve Permission Issues When You Move a Database Between Servers
That Are Running SQL Server
http://support.microsoft.com/Default.aspx?id=240872
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Daniyal khan via droptable.com" <forum@.droptable.com> wrote in message
news:526E5BAE50074@.droptable.com...
> hi all
> i have taken backup of a database 'XYZ' and restored it to a different
> server with the same database name 'XYZ', it restored all the
> items/objects
> successfully but the users of that database had blank loginnames in the
> restored database. now it makes the objects of that user unaccessable
> (without the login name the user cannot access its tables in the
> database).
> i have also tried to make the users before database restoration but when
> the
> database backup is restored it eliminates the user loginname
> i'll be thankyou if anyone has the solution
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200508/1

login names are deleted after restoring the backups

hi all
i have taken backup of a database 'XYZ' and restored it to a different
server with the same database name 'XYZ', it restored all the items/objects
successfully but the users of that database had blank loginnames in the
restored database. now it makes the objects of that user unaccessable
(without the login name the user cannot access its tables in the database).
i have also tried to make the users before database restoration but when the
database backup is restored it eliminates the user loginname
i'll be thankyou if anyone has the solution
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200508/1HI,
Tranfer all the logins from the mainserver to ur restored database from
dts.
or restore the master database backup
from
sufian|||You need to remap the user in the database to the logins on the other server
(assuming they exist). I tend to prefer to use sp_SidMap for this task which
can be found in the MapSids link in the following KB article
HOW TO: Resolve Permission Issues When You Move a Database Between Servers
That Are Running SQL Server
http://support.microsoft.com/Default.aspx?id=240872
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Daniyal khan via droptable.com" <forum@.droptable.com> wrote in message
news:526E5BAE50074@.droptable.com...
> hi all
> i have taken backup of a database 'XYZ' and restored it to a different
> server with the same database name 'XYZ', it restored all the
> items/objects
> successfully but the users of that database had blank loginnames in the
> restored database. now it makes the objects of that user unaccessable
> (without the login name the user cannot access its tables in the
> database).
> i have also tried to make the users before database restoration but when
> the
> database backup is restored it eliminates the user loginname
> i'll be thankyou if anyone has the solution
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200508/1