kill long running query

I am changing some field types in a large table
by creating the new table and then selecting the
data into it. So no changes are being made to the
existing data.
I started this at 5:00 last night, and by 11:00
it became apparent that it wasn't going to finish
until this weekend.
I can run queries in UNCOMMITTED READ isolation level
and see how many images have been copied. By 11:00
it was only 12% done.
So I cancelled the query at 11:00. 12 hours later,
it is about half way done rolling back. As a result
we can't access the table and get any work done today.
I don't need this transaction rolled back. I could
just drop the temporary table and be done with it.
I've looked at the KILL copmmand, but that just causes
it to rollback, so it doesn't help.
I could reboot the server, but as far as I know, it might
pick up where it left off and continue rolling back the
transaction.
Is there any way out of this hole that I've dug myself into?
Thanks,
Brad.> So I cancelled the query at 11:00. 12 hours later,
> it is about half way done rolling back. As a result
> we can't access the table and get any work done today.
If it is rolling back a SELECT INTO a new table, why would this prevent you
from accessing the existing table? Or did you mean to explain that the
rollback is consuming the server in terms of resources?|||Aaron Bertrand [SQL Server MVP] wrote :
>> So I cancelled the query at 11:00. 12 hours later,
>> it is about half way done rolling back. As a result
>> we can't access the table and get any work done today.
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
I don't know the answer to that.
I assume that it has affected so many rows
(I was copying the entire table to a new table)
that the locks have been promoted to table level.
2G rows.
The script I was using use ISOLATION LEVEL SERIALIZABLE.
The question has been rendered moot, as the server crashed.
Thanks,
Brad.|||Aaron Bertrand [SQL Server MVP] wrote on 4/4/2008 :
>> So I cancelled the query at 11:00. 12 hours later,
>> it is about half way done rolling back. As a result
>> we can't access the table and get any work done today.
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
No, I don't know why it would be adding locks
on the SOURCE table.
What led me to think that was that queries against
any other table would return reasonably, while
queries against either of those tables would run
up to 30 minutes without returning before I killed them.
Thanks,
Brad.|||2 billion rows, yeah that's going to be tough. If you have the space, I
would suggest making a backup of the database, restoring it as a different
database, and then copy the rows from the restored copy. Then you can use a
much lower isolation level because you don't have to worry about people
coming in and changing the source rows while you are copying...
"Brad White" <bwhite_at_inebraska_dot_com@.remove.nul> wrote in message
news:mn.24b57d840b74b3fd.81110@.remove.nul...
> Aaron Bertrand [SQL Server MVP] wrote :
>> So I cancelled the query at 11:00. 12 hours later,
>> it is about half way done rolling back. As a result
>> we can't access the table and get any work done today.
>> If it is rolling back a SELECT INTO a new table, why would this prevent
>> you from accessing the existing table? Or did you mean to explain that
>> the rollback is consuming the server in terms of resources?
> I don't know the answer to that.
> I assume that it has affected so many rows
> (I was copying the entire table to a new table)
> that the locks have been promoted to table level.
> 2G rows.
> The script I was using use ISOLATION LEVEL SERIALIZABLE.
> The question has been rendered moot, as the server crashed.
> Thanks,
> Brad.
>

kill long running query

I am changing some field types in a large table
by creating the new table and then selecting the
data into it. So no changes are being made to the
existing data.
I started this at 5:00 last night, and by 11:00
it became apparent that it wasn't going to finish
until this weekend.
I can run queries in UNCOMMITTED READ isolation level
and see how many images have been copied. By 11:00
it was only 12% done.
So I cancelled the query at 11:00. 12 hours later,
it is about half way done rolling back. As a result
we can't access the table and get any work done today.
I don't need this transaction rolled back. I could
just drop the temporary table and be done with it.
I've looked at the KILL copmmand, but that just causes
it to rollback, so it doesn't help.
I could reboot the server, but as far as I know, it might
pick up where it left off and continue rolling back the
transaction.
Is there any way out of this hole that I've dug myself into?
Thanks,
Brad.
> So I cancelled the query at 11:00. 12 hours later,
> it is about half way done rolling back. As a result
> we can't access the table and get any work done today.
If it is rolling back a SELECT INTO a new table, why would this prevent you
from accessing the existing table? Or did you mean to explain that the
rollback is consuming the server in terms of resources?
|||Aaron Bertrand [SQL Server MVP] wrote :
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
I don't know the answer to that.
I assume that it has affected so many rows
(I was copying the entire table to a new table)
that the locks have been promoted to table level.
2G rows.
The script I was using use ISOLATION LEVEL SERIALIZABLE.
The question has been rendered moot, as the server crashed.
Thanks,
Brad.
|||Aaron Bertrand [SQL Server MVP] wrote on 4/4/2008 :
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
No, I don't know why it would be adding locks
on the SOURCE table.
What led me to think that was that queries against
any other table would return reasonably, while
queries against either of those tables would run
up to 30 minutes without returning before I killed them.
Thanks,
Brad.
|||2 billion rows, yeah that's going to be tough. If you have the space, I
would suggest making a backup of the database, restoring it as a different
database, and then copy the rows from the restored copy. Then you can use a
much lower isolation level because you don't have to worry about people
coming in and changing the source rows while you are copying...
"Brad White" <bwhite_at_inebraska_dot_com@.remove.nul> wrote in message
news:mn.24b57d840b74b3fd.81110@.remove.nul...
> Aaron Bertrand [SQL Server MVP] wrote :
> I don't know the answer to that.
> I assume that it has affected so many rows
> (I was copying the entire table to a new table)
> that the locks have been promoted to table level.
> 2G rows.
> The script I was using use ISOLATION LEVEL SERIALIZABLE.
> The question has been rendered moot, as the server crashed.
> Thanks,
> Brad.
>

Keeping DB schemas synched

Hi - what's the best (preferably open source or inexpensive) tool for
identifying differences (tables, columns, types, etc) between a development
database and a production one, and applying dev changes to production
automatically?
Thanks,
ChrisI don't think it is ever a good idea to make changes to a production system
"automatically". But the compare tool from www.red-gate.com is inexpensive
and should do what you are after.
Andrew J. Kelly SQL MVP
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:9E38D2DB-B107-45D1-A5D4-2E0970067AE8@.microsoft.com...
> Hi - what's the best (preferably open source or inexpensive) tool for
> identifying differences (tables, columns, types, etc) between a
> development
> database and a production one, and applying dev changes to production
> automatically?
> Thanks,
> Chris|||2nd the vote for red gate...I've used SQL COmpare and Data Compare for
several years to keep my schems and lookup data intact
Free 14 day trial, less than $300 per tool. VERY hard to beat
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.expertsrt.com - not your average tech Q&A site
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uxykvJONGHA.2472@.TK2MSFTNGP11.phx.gbl...
>I don't think it is ever a good idea to make changes to a production system
>"automatically". But the compare tool from www.red-gate.com is inexpensive
>and should do what you are after.
> --
> Andrew J. Kelly SQL MVP
>
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:9E38D2DB-B107-45D1-A5D4-2E0970067AE8@.microsoft.com...
>|||Thanks Andrew- I fear I do a lot of things that aren't such good ideas when
developing! With luck, the right tool will have options to select the change
s
I'd like to apply, and then apply them.
Thanks for the rec,
Chris|||Developing is one thing, production is quite another. Yes this tool will
generate scripts for the changes but no matter how good the tool all scripts
should be tested before being applied to production.
Andrew J. Kelly SQL MVP
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:A653C08A-D917-4E85-84DE-D8B4B6A2E015@.microsoft.com...
> Thanks Andrew- I fear I do a lot of things that aren't such good ideas
> when
> developing! With luck, the right tool will have options to select the
> changes
> I'd like to apply, and then apply them.
> Thanks for the rec,
> Chris|||You should be checking your development and production scripts into some
type of source version control system. For example, Visual Source Safe has
an option to compare two projects and list files that are different, and it
has a feature for comparing two versions of a script side by side with
differences highlighted.
Also, you can script the databases to seperate folders and use a tool like
WinMerge to perform the comparisons:
http://groups.google.com/group/micr...br />
46abfa76
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:9E38D2DB-B107-45D1-A5D4-2E0970067AE8@.microsoft.com...
> Hi - what's the best (preferably open source or inexpensive) tool for
> identifying differences (tables, columns, types, etc) between a
> development
> database and a production one, and applying dev changes to production
> automatically?
> Thanks,
> Chris|||Thanks JT- unfortunately, I currently do all admin via the admin console, no
t
with scripts. Bad practice?
Chris
"JT" wrote:

> You should be checking your development and production scripts into some
> type of source version control system. For example, Visual Source Safe has
> an option to compare two projects and list files that are different, and i
t
> has a feature for comparing two versions of a script side by side with
> differences highlighted.
> Also, you can script the databases to seperate folders and use a tool like
> WinMerge to perform the comparisons:
> http://groups.google.com/group/micr... />
0c46abfa76
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:9E38D2DB-B107-45D1-A5D4-2E0970067AE8@.microsoft.com...
>
>|||In most SQL Server environments, changes to the database schema are
implemented as scripts; which are first tested against a DEV or QA server
and then promoted against the production server. These scripts are archived
using a source control system just like C# or Visual Basic projects.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:86627853-B428-44F0-B94E-282A69894DF7@.microsoft.com...
> Thanks JT- unfortunately, I currently do all admin via the admin console,
> not
> with scripts. Bad practice?
> Chris
> "JT" wrote:
>

Keep Together Possible?

I have a report which contains multiple sub-reports to display different data types that do not relate, but are needed on the same report (the reason for the various sub-reports).

Everything was working fine, until....

The first sub-report is very small, prints maybe 4 inches down the first page. The second sub-report is very large, and now has grown larger than the remainder of the page.

So the second sub-report pushes down and prints of on the 2nd page, leaving most of the first page blank (looks HORRIBLE). I have placed all the sub-reports into a rectangle property, but still do not see anyway to keep the sub-reports together (so they look continuous).

Does anyone know of a work around for this?

Thank you for you help.

T.J.

I'm sorry, there is not a way to override this implicit keep-together on the subreport. The closest you'll get is if you combine the two reports into one.

|||

Thank you for your help.

What is really weird is on the 2nd page of the report, where the 2nd sub-report starts, a 3rd sub-report prints as well.

The 3rd sub-report does print on two pages (where the 2nd sub-report will not do this). And I can find nothing different in the properties of the sub-reports, the main report, or the tables used on the sub-reports.

Very weird. It is like SQL reporting services just picks and chooses when it wants the sub-report to behave correctly.

Hate to have to go back to Crystal, but that might be the only option.

Let me know if you have any other ideas.

Thank you,

T.J.

|||Was the third subreport too large to fit on a single page? If so, it means we can't honor the keep-together and will not try and push it to the next page.|||

Yes, the 3rd sub-report is too large to fit on one page (like the 2nd sub-report), but it breaks out correctly and prints on the 2nd and 3rd page (as expected, the third sub-report starts on the 2nd page, and continues to the 3rd page).

Only the 2nd sub-report forces down to a new page. And I cannot find anything different in these sub-reports at all.

Very strange deal.

Thank you for your help. If worse comes to worse, I can build it in Crystal and run it for the users and send them a .pdf.

|||

Printing it out and looking closely, it appears the report footer in the 2nd sub-form just barely does not fit on the first page.

I cannot find any properties to control this, but maybe the glitch is caused by the report forcing the page footer to print on the same page as the rest of the sub-report.

I then expanded the vertical white space in my 2nd sub-report, and the overall report printed as I had hoped (the 2nd sub-report correctly starts on the first page). The bad thing is now my 2nd sub-report has vertical white space on each record that I did not need added.

A start though.

Any thoughts?

I

|||

Just wanted to follow up, for other report developer's sake.

Maybe I caught a glitch with SQL reporting services, in which the footer forces the entire sub-form to move to the next page? Maybe this is already known?

Any thoughts?

Thank you!

T.J.