SQL server questions: third

Showing posts with label third. Show all posts

Tuesday, March 20, 2012

A tool...how long will this take...

Is there a tool, in SQL 2K or third party, that can look
at an UPDATE, or even DELETE, I'm about to run,
and "estimate" how long it'll run. I started a massive
UPDATE on 37 million rows on one column. I stared the
UPDATE 14 hours ago, and it's still running. I can't run
a query to determine how many records have already been
updated, because the update has a lock on the table.
I was just wondering was there something I could've done
beforehand, that could've told me how long this UPDATE
would take. I've played with the Execution Plan feature,
and it does give some useful information, but I'm
specifically looking for costs in terms of "time".
Thanks
RozRoz
I would divide a long transaction into small. Have you checked transaction
log file? Did it grow?
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
UPDATE command
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
> Is there a tool, in SQL 2K or third party, that can look
> at an UPDATE, or even DELETE, I'm about to run,
> and "estimate" how long it'll run. I started a massive
> UPDATE on 37 million rows on one column. I stared the
> UPDATE 14 hours ago, and it's still running. I can't run
> a query to determine how many records have already been
> updated, because the update has a lock on the table.
> I was just wondering was there something I could've done
> beforehand, that could've told me how long this UPDATE
> would take. I've played with the Execution Plan feature,
> and it does give some useful information, but I'm
> specifically looking for costs in terms of "time".
> Thanks
> Roz
>|||run a query with a nolock hint to see where you are in
the process.
Mark Baekdal
www.dbghost.com
>--Original Message--
>Is there a tool, in SQL 2K or third party, that can look
>at an UPDATE, or even DELETE, I'm about to run,
>and "estimate" how long it'll run. I started a massive
>UPDATE on 37 million rows on one column. I stared the
>UPDATE 14 hours ago, and it's still running. I can't
run
>a query to determine how many records have already been
>updated, because the update has a lock on the table.
>I was just wondering was there something I could've done
>beforehand, that could've told me how long this UPDATE
>would take. I've played with the Execution Plan
feature,
>and it does give some useful information, but I'm
>specifically looking for costs in terms of "time".
>Thanks
>Roz
>.
>|||Uri,
Yep, that's exactly what I did. I broke the Update into
5000 records at a time. The Tlog is small, as it should
be since I'm Checkpointing quite frequently. But the
Update is still running. I guess it just takes this
long...
Roz
>--Original Message--
>Roz
>I would divide a long transaction into small. Have you
checked transaction
>log file? Did it grow?
>SET ROWCOUNT 1000
>WHILE 1 = 1
>BEGIN
> UPDATE command
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT
> END
>END
>SET ROWCOUNT 0
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
>> Is there a tool, in SQL 2K or third party, that can look
>> at an UPDATE, or even DELETE, I'm about to run,
>> and "estimate" how long it'll run. I started a massive
>> UPDATE on 37 million rows on one column. I stared the
>> UPDATE 14 hours ago, and it's still running. I can't
run
>> a query to determine how many records have already been
>> updated, because the update has a lock on the table.
>> I was just wondering was there something I could've done
>> beforehand, that could've told me how long this UPDATE
>> would take. I've played with the Execution Plan
feature,
>> and it does give some useful information, but I'm
>> specifically looking for costs in terms of "time".
>> Thanks
>> Roz
>
>.
>|||That will give you 7400 separate transactions. It could still take a while,
but you will not cause your transaction log to grow uncontrollably. Using
smaller chunks gives you more options. You could insert into (or update) a
"logging" table after each pass through the while loop. That would give you
the ability to know how many you have done and how many more rows are left
to process.
--
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...
> Uri,
> Yep, that's exactly what I did. I broke the Update into
> 5000 records at a time. The Tlog is small, as it should
> be since I'm Checkpointing quite frequently. But the
> Update is still running. I guess it just takes this
> long...
> Roz
>
> >--Original Message--
> >Roz
> >I would divide a long transaction into small. Have you
> checked transaction
> >log file? Did it grow?
> >SET ROWCOUNT 1000
> >WHILE 1 = 1
> >BEGIN
> >
> > UPDATE command
> >
> > IF @.@.ROWCOUNT = 0
> > BEGIN
> > BREAK
> > END
> > ELSE
> > BEGIN
> >
> > CHECKPOINT
> > END
> >END
> >
> >SET ROWCOUNT 0
> >
> >"Roz" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
> >> Is there a tool, in SQL 2K or third party, that can look
> >> at an UPDATE, or even DELETE, I'm about to run,
> >> and "estimate" how long it'll run. I started a massive
> >> UPDATE on 37 million rows on one column. I stared the
> >> UPDATE 14 hours ago, and it's still running. I can't
> run
> >> a query to determine how many records have already been
> >> updated, because the update has a lock on the table.
> >>
> >> I was just wondering was there something I could've done
> >> beforehand, that could've told me how long this UPDATE
> >> would take. I've played with the Execution Plan
> feature,
> >> and it does give some useful information, but I'm
> >> specifically looking for costs in terms of "time".
> >>
> >> Thanks
> >> Roz
> >>
> >
> >
> >.
> >|||Beautiful. Very excellent ideas to try. I'll keep these
in mind next time I need to do such a massive update.
Thanks very, very much to all.
Roz
>--Original Message--
>That will give you 7400 separate transactions. It could
still take a while,
>but you will not cause your transaction log to grow
uncontrollably. Using
>smaller chunks gives you more options. You could insert
into (or update) a
>"logging" table after each pass through the while loop.
That would give you
>the ability to know how many you have done and how many
more rows are left
>to process.
>--
>Keith
>
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...
>> Uri,
>> Yep, that's exactly what I did. I broke the Update into
>> 5000 records at a time. The Tlog is small, as it should
>> be since I'm Checkpointing quite frequently. But the
>> Update is still running. I guess it just takes this
>> long...
>> Roz
>>
>> >--Original Message--
>> >Roz
>> >I would divide a long transaction into small. Have you
>> checked transaction
>> >log file? Did it grow?
>> >SET ROWCOUNT 1000
>> >WHILE 1 = 1
>> >BEGIN
>> >
>> > UPDATE command
>> >
>> > IF @.@.ROWCOUNT = 0
>> > BEGIN
>> > BREAK
>> > END
>> > ELSE
>> > BEGIN
>> >
>> > CHECKPOINT
>> > END
>> >END
>> >
>> >SET ROWCOUNT 0
>> >
>> >"Roz" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
>> >> Is there a tool, in SQL 2K or third party, that can
look
>> >> at an UPDATE, or even DELETE, I'm about to run,
>> >> and "estimate" how long it'll run. I started a
massive
>> >> UPDATE on 37 million rows on one column. I stared
the
>> >> UPDATE 14 hours ago, and it's still running. I can't
>> run
>> >> a query to determine how many records have already
been
>> >> updated, because the update has a lock on the table.
>> >>
>> >> I was just wondering was there something I could've
done
>> >> beforehand, that could've told me how long this
UPDATE
>> >> would take. I've played with the Execution Plan
>> feature,
>> >> and it does give some useful information, but I'm
>> >> specifically looking for costs in terms of "time".
>> >>
>> >> Thanks
>> >> Roz
>> >>
>> >
>> >
>> >.
>> >
>.
>|||By the way, limiting the rowcount to 5000 updates seems a little light. I
would probably try with 50,000 or even 100,000. Heck, you could set it to
10 if you wanted to...it is probably a balancing act of time vs resource
usage.
One more idea for inside the WHILE loop: you could perform a BACKUP LOG
<database> WITH NO_LOG within the while loop to clear the transaction log.
--
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:226eb01c45d23$f44297e0$a501280a@.phx.gbl...
> Beautiful. Very excellent ideas to try. I'll keep these
> in mind next time I need to do such a massive update.
> Thanks very, very much to all.
> Roz
> >--Original Message--
> >That will give you 7400 separate transactions. It could
> still take a while,
> >but you will not cause your transaction log to grow
> uncontrollably. Using
> >smaller chunks gives you more options. You could insert
> into (or update) a
> >"logging" table after each pass through the while loop.
> That would give you
> >the ability to know how many you have done and how many
> more rows are left
> >to process.
> >
> >--
> >Keith
> >
> >
> >"Roz" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...
> >> Uri,
> >>
> >> Yep, that's exactly what I did. I broke the Update into
> >> 5000 records at a time. The Tlog is small, as it should
> >> be since I'm Checkpointing quite frequently. But the
> >> Update is still running. I guess it just takes this
> >> long...
> >>
> >> Roz
> >>
> >>
> >> >--Original Message--
> >> >Roz
> >> >I would divide a long transaction into small. Have you
> >> checked transaction
> >> >log file? Did it grow?
> >> >SET ROWCOUNT 1000
> >> >WHILE 1 = 1
> >> >BEGIN
> >> >
> >> > UPDATE command
> >> >
> >> > IF @.@.ROWCOUNT = 0
> >> > BEGIN
> >> > BREAK
> >> > END
> >> > ELSE
> >> > BEGIN
> >> >
> >> > CHECKPOINT
> >> > END
> >> >END
> >> >
> >> >SET ROWCOUNT 0
> >> >
> >> >"Roz" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
> >> >> Is there a tool, in SQL 2K or third party, that can
> look
> >> >> at an UPDATE, or even DELETE, I'm about to run,
> >> >> and "estimate" how long it'll run. I started a
> massive
> >> >> UPDATE on 37 million rows on one column. I stared
> the
> >> >> UPDATE 14 hours ago, and it's still running. I can't
> >> run
> >> >> a query to determine how many records have already
> been
> >> >> updated, because the update has a lock on the table.
> >> >>
> >> >> I was just wondering was there something I could've
> done
> >> >> beforehand, that could've told me how long this
> UPDATE
> >> >> would take. I've played with the Execution Plan
> >> feature,
> >> >> and it does give some useful information, but I'm
> >> >> specifically looking for costs in terms of "time".
> >> >>
> >> >> Thanks
> >> >> Roz
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >.
> >sql

A tool...how long will this take...

Monday, March 19, 2012

A third dynamic assembly loading problem ;)

Hi!

From the dll I've installed in SQL Server I load a dll via reflection.assembly.load from the GAC . I know the dll can load the dll when I test it outside SQL Server but when I run it via a store procedure from within the SQL Server I keep getting a System.IO.FileNotFoundException.

How does this work? Isn't all dll in the GAC loaded by SQL Server? Is this impossible to achieve?

Thanks in advance

Richard Hallgren

There are only a sub-set (13 or something like that, all of them system assemblies) of assemblies that are allowed to leaded from the GAC inside of SQL Server. No user assemblies are allowed to be loaded from the GAC.

So what you need to do is to catalog the assemblies you want to load dynamically in the database, and it should work.

Niels
|||Thanks Niels!

But the dependency tree for the assemblies I like to reference is kind of big and guess I have to catalog all of those as well (that is the assemblies that the assembly I like to reference depend on). Some of them contains unmanaged code as well but I guess as long as I set the unsafe permission mode on those I should be able to catalog those as well. Right?|||

Richard Hallgren wrote:

But the dependency tree for the assemblies I like to reference is kind of big and guess I have to catalog all of those as well (that is the assemblies that the assembly I like to reference depend on). Some of them contains unmanaged code as well but I guess as long as I set the unsafe permission mode on those I should be able to catalog those as well. Right?

You basically need to catalog all assemblies that will be loaded, either directly or indirectly.

Niels

Thursday, February 16, 2012

A probably simple question

A third party vendor has a table with a field name of "desc" in it. Since "desc" is a reserved term in SQL Server 2005 how does one query Table.Desc ?

when you try it with table.desc it errors since it turned blue being a reserved word.

Jeff

try table.[desc]|||

I did this and it would not create the column. I have tried:

table.[desc]

[table].[desc]

Neither of which worked.

Jeff

|||

I just ran this code in sql 2005:

create table #tmp( [desc] varchar(10))

insert into #tmp values( 'one')

select #tmp.[desc] from #tmp

Is your table actually named "Table"?

|||

Even if your table is called “table” you should be able to query it.

For example,

create table [table]([desc] char(10))

insert into [table] values ('test')

select [desc] from [table]

When you said it did not work, what was the error message? What is the version of SQL Server are you using?

Consult this Books Online topic http://msdn2.microsoft.com/en-US/library/ms176027(SQL.90).aspx for more information.

HTH,

Boris.

|||

There wasn't an error, it just didn't show the desc column in the resultset grid.

ALTER PROCEDURE [dbo].[SUR_GiftsDetail]

@.StartDate DateTime,

@.EndDate DateTime

BEGIN

SET NOCOUNT ON;

SELECT

ItemID,

Quantity,

TourID,

TourNumber,

MasterID,

Description

MasterCatID,

CategoryID,

CategoryDescription,

Arrival,

Depart,

BookingID,

it_arrival_date

FROM

(SELECT

Booking.bk_id AS BookingID,

i.it_arrival_date AS Arrival,

i.it_arrival_date,

i.it_id AS TourNumber,

i.it_arrival_date + i.it_nights AS Depart,

pt.fk_itemid AS ItemID,

pt.Qty AS Quantity,

pt.fk_tourid AS TourID,

pm.ItemID AS MasterID,

pm.[desc] AS Description,

pm.fk_categoryID AS MasterCatID,

pc.categoryID AS CategoryID,

pc.[Desc] AS CategoryDescription

FROM

Booking

LEFT JOIN ITINERARY i ON Booking.bk_id = i.fk_bk_id

LEFT JOIN pi_transactions pt ON pt.fk_tourid = Booking.bk_id

LEFT JOIN pi_master pm ON pm.itemid = pt.fk_itemid

LEFT JOIN pi_category pc ON pc.categoryid = pm.fk_categoryid

LEFT JOIN pi_transactions ptt ON ptt.fk_itemid = pm.itemid

WHERE

i.it_arrival_date BETWEEN @.StartDate AND @.EndDate AND i.fk_et_entity_type LIKE 'Hotel') AS derivedTour

WHERE

it_arrival_date BETWEEN @.StartDate AND @.EndDate

ORDER BY

TourID

END

All the columns show except for the two desc fields.

SQL Server 2005

Jeff

|||

You are missing a comma after Description in the outer SELECT list.

Tuesday, March 20, 2012

A tool...how long will this take...

A tool...how long will this take...

A tool...how long will this take...

Monday, March 19, 2012

A third dynamic assembly loading problem ;)

Thursday, February 16, 2012

A probably simple question

SQL server questions

Blog Archive

About Me