Hi All,
I am new in SQL SERVER 2000.I have few questions -
1) WHAT WILL I DO TO TRUNCATE THE SIZE OF A TRANSACTION LOG?
2) WHAT WILL BE THE STEPS OF BUILDING THE MASTER DATABASE?
3) WHAT WOULD BE THE PLAN OF ACTION WHEN SQL DOES NOT STARTS UP?
4) WHAT WOULD BE MY PLAN OF ACTION WHEN SQL DB GETS CORUPTED OR STARTS IN A SUSPECT MODE?1) See BACKUP
2) Install SQL 2000
3) Start MS-SQL, then examine the log file (whether it actually starts or not).
4) Determine what caused the problem(s), and fix it.
If these answers seem terse, it is because the questions sound like homework to me.
-PatP
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Tuesday, March 27, 2012
Wednesday, March 7, 2012
fast kill/rollback
I have just killed a transaction and it says it's going to take about 20
hours to roll the change back. Is there such a thing as a "fast kill"? I
don't care about rolling this back - I just want it to stop. The process is
blocking my users and I need this process killed sooner than 20 hours from
now. I also don't understand how/why a process that had run for 9 hours
could take 20 hours to rollback. It doesn't really make sense to me.
Thanks in advance.
AndreHi
Generally, if a transaction took 9 hours, you can expect a rollback to take
12-24 hours (1.5-3 times the forward time).
It has to read the original row from the transaction log, and apply it back
to the database. This is very IO intensive. I hope your transaction log is
on a separate physical drive/LUN.
It does not help to stop the SQL Server service as on startup, it will do
the rollback and it will take the same time.
You should never have very long transactions, as it can take a long time to
rollback as anything can happen over such a long period (power, network
server problems)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>|||> Is there such a thing as a "fast kill"? I don't care about rolling this
> back - I just want it to stop.
The only way to avoid the rollback is to restore from backup.
--
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>|||bummer.
In that case, maybe I should ask for advice on how to do this. I'm trying
to add a new column to a table that has 9 cols and 34 million records. I'm
trying to add an identity col called RowID, using alter table. Is there a
better way to do this? Should I drop all indexes before running this
command?
Thanks, Andre|||Even on the worst hardware I can think of this should not take 9 hours to
do. Most likely you were blocked and it wasn't doing anything. But in any
case you might want to BCP out the data, create a new table just the way you
need with no indexes. BCP the data back in (you will probably need a format
file since the structure will be different) and recreate the indexes. That
way you can get a minimally logged load if you do the BCP or Bulk Insert
properly.
--
Andrew J. Kelly SQL MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>|||Another method besides the BCP method Andrew suggested is to create a new
table using SELECT INTO, creating the new column using the IDENTITY
function. This will also be minimally logged in the SIMPLE recovery model.
You can then drop the old table and recreate constraints and indexes.
--
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>|||I thought about that one too but wondered about the logging. I'll give that
a try tonight and see how it goes.
I also do think there was some blocking going on. I'm going to stop SQL
Agent tonight before starting.
Thanks again,
Andre
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OO%23OJIYDGHA.3992@.TK2MSFTNGP12.phx.gbl...
> Another method besides the BCP method Andrew suggested is to create a new
> table using SELECT INTO, creating the new column using the IDENTITY
> function. This will also be minimally logged in the SIMPLE recovery
> model. You can then drop the old table and recreate constraints and
> indexes.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Andre" <no@.spam.com> wrote in message
> news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
>> bummer.
>> In that case, maybe I should ask for advice on how to do this. I'm
>> trying to add a new column to a table that has 9 cols and 34 million
>> records. I'm trying to add an identity col called RowID, using alter
>> table. Is there a better way to do this? Should I drop all indexes
>> before running this command?
>> Thanks, Andre
>
hours to roll the change back. Is there such a thing as a "fast kill"? I
don't care about rolling this back - I just want it to stop. The process is
blocking my users and I need this process killed sooner than 20 hours from
now. I also don't understand how/why a process that had run for 9 hours
could take 20 hours to rollback. It doesn't really make sense to me.
Thanks in advance.
AndreHi
Generally, if a transaction took 9 hours, you can expect a rollback to take
12-24 hours (1.5-3 times the forward time).
It has to read the original row from the transaction log, and apply it back
to the database. This is very IO intensive. I hope your transaction log is
on a separate physical drive/LUN.
It does not help to stop the SQL Server service as on startup, it will do
the rollback and it will take the same time.
You should never have very long transactions, as it can take a long time to
rollback as anything can happen over such a long period (power, network
server problems)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>|||> Is there such a thing as a "fast kill"? I don't care about rolling this
> back - I just want it to stop.
The only way to avoid the rollback is to restore from backup.
--
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>|||bummer.
In that case, maybe I should ask for advice on how to do this. I'm trying
to add a new column to a table that has 9 cols and 34 million records. I'm
trying to add an identity col called RowID, using alter table. Is there a
better way to do this? Should I drop all indexes before running this
command?
Thanks, Andre|||Even on the worst hardware I can think of this should not take 9 hours to
do. Most likely you were blocked and it wasn't doing anything. But in any
case you might want to BCP out the data, create a new table just the way you
need with no indexes. BCP the data back in (you will probably need a format
file since the structure will be different) and recreate the indexes. That
way you can get a minimally logged load if you do the BCP or Bulk Insert
properly.
--
Andrew J. Kelly SQL MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>|||Another method besides the BCP method Andrew suggested is to create a new
table using SELECT INTO, creating the new column using the IDENTITY
function. This will also be minimally logged in the SIMPLE recovery model.
You can then drop the old table and recreate constraints and indexes.
--
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>|||I thought about that one too but wondered about the logging. I'll give that
a try tonight and see how it goes.
I also do think there was some blocking going on. I'm going to stop SQL
Agent tonight before starting.
Thanks again,
Andre
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OO%23OJIYDGHA.3992@.TK2MSFTNGP12.phx.gbl...
> Another method besides the BCP method Andrew suggested is to create a new
> table using SELECT INTO, creating the new column using the IDENTITY
> function. This will also be minimally logged in the SIMPLE recovery
> model. You can then drop the old table and recreate constraints and
> indexes.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Andre" <no@.spam.com> wrote in message
> news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
>> bummer.
>> In that case, maybe I should ask for advice on how to do this. I'm
>> trying to add a new column to a table that has 9 cols and 34 million
>> records. I'm trying to add an identity col called RowID, using alter
>> table. Is there a better way to do this? Should I drop all indexes
>> before running this command?
>> Thanks, Andre
>
fast kill/rollback
I have just killed a transaction and it says it's going to take about 20
hours to roll the change back. Is there such a thing as a "fast kill"? I
don't care about rolling this back - I just want it to stop. The process is
blocking my users and I need this process killed sooner than 20 hours from
now. I also don't understand how/why a process that had run for 9 hours
could take 20 hours to rollback. It doesn't really make sense to me.
Thanks in advance.
Andre
> Is there such a thing as a "fast kill"? I don't care about rolling this
> back - I just want it to stop.
The only way to avoid the rollback is to restore from backup.
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>
|||bummer.
In that case, maybe I should ask for advice on how to do this. I'm trying
to add a new column to a table that has 9 cols and 34 million records. I'm
trying to add an identity col called RowID, using alter table. Is there a
better way to do this? Should I drop all indexes before running this
command?
Thanks, Andre
|||Even on the worst hardware I can think of this should not take 9 hours to
do. Most likely you were blocked and it wasn't doing anything. But in any
case you might want to BCP out the data, create a new table just the way you
need with no indexes. BCP the data back in (you will probably need a format
file since the structure will be different) and recreate the indexes. That
way you can get a minimally logged load if you do the BCP or Bulk Insert
properly.
Andrew J. Kelly SQL MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>
|||Another method besides the BCP method Andrew suggested is to create a new
table using SELECT INTO, creating the new column using the IDENTITY
function. This will also be minimally logged in the SIMPLE recovery model.
You can then drop the old table and recreate constraints and indexes.
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>
|||I thought about that one too but wondered about the logging. I'll give that
a try tonight and see how it goes.
I also do think there was some blocking going on. I'm going to stop SQL
Agent tonight before starting.
Thanks again,
Andre
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OO%23OJIYDGHA.3992@.TK2MSFTNGP12.phx.gbl...
> Another method besides the BCP method Andrew suggested is to create a new
> table using SELECT INTO, creating the new column using the IDENTITY
> function. This will also be minimally logged in the SIMPLE recovery
> model. You can then drop the old table and recreate constraints and
> indexes.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Andre" <no@.spam.com> wrote in message
> news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
>
|||Hi
Generally, if a transaction took 9 hours, you can expect a rollback to take
12-24 hours (1.5-3 times the forward time).
It has to read the original row from the transaction log, and apply it back
to the database. This is very IO intensive. I hope your transaction log is
on a separate physical drive/LUN.
It does not help to stop the SQL Server service as on startup, it will do
the rollback and it will take the same time.
You should never have very long transactions, as it can take a long time to
rollback as anything can happen over such a long period (power, network
server problems)
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>
hours to roll the change back. Is there such a thing as a "fast kill"? I
don't care about rolling this back - I just want it to stop. The process is
blocking my users and I need this process killed sooner than 20 hours from
now. I also don't understand how/why a process that had run for 9 hours
could take 20 hours to rollback. It doesn't really make sense to me.
Thanks in advance.
Andre
> Is there such a thing as a "fast kill"? I don't care about rolling this
> back - I just want it to stop.
The only way to avoid the rollback is to restore from backup.
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>
|||bummer.
In that case, maybe I should ask for advice on how to do this. I'm trying
to add a new column to a table that has 9 cols and 34 million records. I'm
trying to add an identity col called RowID, using alter table. Is there a
better way to do this? Should I drop all indexes before running this
command?
Thanks, Andre
|||Even on the worst hardware I can think of this should not take 9 hours to
do. Most likely you were blocked and it wasn't doing anything. But in any
case you might want to BCP out the data, create a new table just the way you
need with no indexes. BCP the data back in (you will probably need a format
file since the structure will be different) and recreate the indexes. That
way you can get a minimally logged load if you do the BCP or Bulk Insert
properly.
Andrew J. Kelly SQL MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>
|||Another method besides the BCP method Andrew suggested is to create a new
table using SELECT INTO, creating the new column using the IDENTITY
function. This will also be minimally logged in the SIMPLE recovery model.
You can then drop the old table and recreate constraints and indexes.
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>
|||I thought about that one too but wondered about the logging. I'll give that
a try tonight and see how it goes.
I also do think there was some blocking going on. I'm going to stop SQL
Agent tonight before starting.
Thanks again,
Andre
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OO%23OJIYDGHA.3992@.TK2MSFTNGP12.phx.gbl...
> Another method besides the BCP method Andrew suggested is to create a new
> table using SELECT INTO, creating the new column using the IDENTITY
> function. This will also be minimally logged in the SIMPLE recovery
> model. You can then drop the old table and recreate constraints and
> indexes.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Andre" <no@.spam.com> wrote in message
> news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
>
|||Hi
Generally, if a transaction took 9 hours, you can expect a rollback to take
12-24 hours (1.5-3 times the forward time).
It has to read the original row from the transaction log, and apply it back
to the database. This is very IO intensive. I hope your transaction log is
on a separate physical drive/LUN.
It does not help to stop the SQL Server service as on startup, it will do
the rollback and it will take the same time.
You should never have very long transactions, as it can take a long time to
rollback as anything can happen over such a long period (power, network
server problems)
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>
fast kill/rollback
I have just killed a transaction and it says it's going to take about 20
hours to roll the change back. Is there such a thing as a "fast kill"? I
don't care about rolling this back - I just want it to stop. The process is
blocking my users and I need this process killed sooner than 20 hours from
now. I also don't understand how/why a process that had run for 9 hours
could take 20 hours to rollback. It doesn't really make sense to me.
Thanks in advance.
AndreHi
Generally, if a transaction took 9 hours, you can expect a rollback to take
12-24 hours (1.5-3 times the forward time).
It has to read the original row from the transaction log, and apply it back
to the database. This is very IO intensive. I hope your transaction log is
on a separate physical drive/LUN.
It does not help to stop the SQL Server service as on startup, it will do
the rollback and it will take the same time.
You should never have very long transactions, as it can take a long time to
rollback as anything can happen over such a long period (power, network
server problems)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>|||> Is there such a thing as a "fast kill"? I don't care about rolling this
> back - I just want it to stop.
The only way to avoid the rollback is to restore from backup.
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>|||bummer.
In that case, maybe I should ask for advice on how to do this. I'm trying
to add a new column to a table that has 9 cols and 34 million records. I'm
trying to add an identity col called RowID, using alter table. Is there a
better way to do this? Should I drop all indexes before running this
command?
Thanks, Andre|||Even on the worst hardware I can think of this should not take 9 hours to
do. Most likely you were blocked and it wasn't doing anything. But in any
case you might want to BCP out the data, create a new table just the way you
need with no indexes. BCP the data back in (you will probably need a format
file since the structure will be different) and recreate the indexes. That
way you can get a minimally logged load if you do the BCP or Bulk Insert
properly.
Andrew J. Kelly SQL MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>|||Another method besides the BCP method Andrew suggested is to create a new
table using SELECT INTO, creating the new column using the IDENTITY
function. This will also be minimally logged in the SIMPLE recovery model.
You can then drop the old table and recreate constraints and indexes.
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>|||I thought about that one too but wondered about the logging. I'll give that
a try tonight and see how it goes.
I also do think there was some blocking going on. I'm going to stop SQL
Agent tonight before starting.
Thanks again,
Andre
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OO%23OJIYDGHA.3992@.TK2MSFTNGP12.phx.gbl...
> Another method besides the BCP method Andrew suggested is to create a new
> table using SELECT INTO, creating the new column using the IDENTITY
> function. This will also be minimally logged in the SIMPLE recovery
> model. You can then drop the old table and recreate constraints and
> indexes.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Andre" <no@.spam.com> wrote in message
> news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
>
hours to roll the change back. Is there such a thing as a "fast kill"? I
don't care about rolling this back - I just want it to stop. The process is
blocking my users and I need this process killed sooner than 20 hours from
now. I also don't understand how/why a process that had run for 9 hours
could take 20 hours to rollback. It doesn't really make sense to me.
Thanks in advance.
AndreHi
Generally, if a transaction took 9 hours, you can expect a rollback to take
12-24 hours (1.5-3 times the forward time).
It has to read the original row from the transaction log, and apply it back
to the database. This is very IO intensive. I hope your transaction log is
on a separate physical drive/LUN.
It does not help to stop the SQL Server service as on startup, it will do
the rollback and it will take the same time.
You should never have very long transactions, as it can take a long time to
rollback as anything can happen over such a long period (power, network
server problems)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>|||> Is there such a thing as a "fast kill"? I don't care about rolling this
> back - I just want it to stop.
The only way to avoid the rollback is to restore from backup.
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>|||bummer.
In that case, maybe I should ask for advice on how to do this. I'm trying
to add a new column to a table that has 9 cols and 34 million records. I'm
trying to add an identity col called RowID, using alter table. Is there a
better way to do this? Should I drop all indexes before running this
command?
Thanks, Andre|||Even on the worst hardware I can think of this should not take 9 hours to
do. Most likely you were blocked and it wasn't doing anything. But in any
case you might want to BCP out the data, create a new table just the way you
need with no indexes. BCP the data back in (you will probably need a format
file since the structure will be different) and recreate the indexes. That
way you can get a minimally logged load if you do the BCP or Bulk Insert
properly.
Andrew J. Kelly SQL MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>|||Another method besides the BCP method Andrew suggested is to create a new
table using SELECT INTO, creating the new column using the IDENTITY
function. This will also be minimally logged in the SIMPLE recovery model.
You can then drop the old table and recreate constraints and indexes.
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>|||I thought about that one too but wondered about the logging. I'll give that
a try tonight and see how it goes.
I also do think there was some blocking going on. I'm going to stop SQL
Agent tonight before starting.
Thanks again,
Andre
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OO%23OJIYDGHA.3992@.TK2MSFTNGP12.phx.gbl...
> Another method besides the BCP method Andrew suggested is to create a new
> table using SELECT INTO, creating the new column using the IDENTITY
> function. This will also be minimally logged in the SIMPLE recovery
> model. You can then drop the old table and recreate constraints and
> indexes.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Andre" <no@.spam.com> wrote in message
> news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
>
Sunday, February 26, 2012
Fast "bulk" inserts...
I have an app that must insert batches of transaction records every 30 min.
Typically only a few hundred each time. We found that just appending a
hundred or so INSERT commands together in a single query sent to the server
was significantly faster than our first approach of preparing a paraemterize
d
query and executing once for each record. So now we send only a few commands
to SQL instead of hundreds.
Someone suggested to me that we put all of the records into an XML
"document" and use OPENXML. We will test this approach.
Any suggestions, guesses on how the new approach and other possible
approaches to try?
Also, would a different approach be warranted if the number of records in a
batch was around 10K, instead of just 200-300?
Thanks,
LMcPhee"lmcphee" <lmcphee@.discussions.microsoft.com> wrote in message
news:E2D39232-D7B3-450C-B76F-5536DC10FEF5@.microsoft.com...
>I have an app that must insert batches of transaction records every 30 min.
> Typically only a few hundred each time. We found that just appending a
> hundred or so INSERT commands together in a single query sent to the
> server
> was significantly faster than our first approach of preparing a
> paraemterized
> query and executing once for each record. So now we send only a few
> commands
> to SQL instead of hundreds.
> Someone suggested to me that we put all of the records into an XML
> "document" and use OPENXML. We will test this approach.
If you can get by with your current approach, do. In SQL 2005/ADO.NET 2.0
you will have the SQLBulkInsert, which will be the perfered method.
> Any suggestions, guesses on how the new approach and other possible
> approaches to try?
> Also, would a different approach be warranted if the number of records in
> a
> batch was around 10K, instead of just 200-300?
>
The batching approach will still work, but you wouldn't want to batch 10k
rows. That's just too much SQL. Keep your batch size constant, and send
multiple batches if necessary.
Another critically important thing for running multiple inserts is to use an
explicit transaction. Without an explicit transaction SQL Server must flush
the log to disk (aka commit) after every insert. The commit takes much
longer than just doing the insert, so you want to do all the inserts, and
just force a single commit at the end.
David
Typically only a few hundred each time. We found that just appending a
hundred or so INSERT commands together in a single query sent to the server
was significantly faster than our first approach of preparing a paraemterize
d
query and executing once for each record. So now we send only a few commands
to SQL instead of hundreds.
Someone suggested to me that we put all of the records into an XML
"document" and use OPENXML. We will test this approach.
Any suggestions, guesses on how the new approach and other possible
approaches to try?
Also, would a different approach be warranted if the number of records in a
batch was around 10K, instead of just 200-300?
Thanks,
LMcPhee"lmcphee" <lmcphee@.discussions.microsoft.com> wrote in message
news:E2D39232-D7B3-450C-B76F-5536DC10FEF5@.microsoft.com...
>I have an app that must insert batches of transaction records every 30 min.
> Typically only a few hundred each time. We found that just appending a
> hundred or so INSERT commands together in a single query sent to the
> server
> was significantly faster than our first approach of preparing a
> paraemterized
> query and executing once for each record. So now we send only a few
> commands
> to SQL instead of hundreds.
> Someone suggested to me that we put all of the records into an XML
> "document" and use OPENXML. We will test this approach.
If you can get by with your current approach, do. In SQL 2005/ADO.NET 2.0
you will have the SQLBulkInsert, which will be the perfered method.
> Any suggestions, guesses on how the new approach and other possible
> approaches to try?
> Also, would a different approach be warranted if the number of records in
> a
> batch was around 10K, instead of just 200-300?
>
The batching approach will still work, but you wouldn't want to batch 10k
rows. That's just too much SQL. Keep your batch size constant, and send
multiple batches if necessary.
Another critically important thing for running multiple inserts is to use an
explicit transaction. Without an explicit transaction SQL Server must flush
the log to disk (aka commit) after every insert. The commit takes much
longer than just doing the insert, so you want to do all the inserts, and
just force a single commit at the end.
David
Friday, February 24, 2012
Failure to Remove Old Transaction Log Backups
I've just become the DBA. The origianl Maintenance Plan was setup to remove
old TRasact Log Backups. This has not worked in several months. Does anyone
have an idea why a working plan would fail? Everything appears to be set
correctly. Is there any advantage to placing optimimization in a seperate
plan from the backups?
Here is a post from Bill at MS that outlines the most common reasons for
this:
-- Log files don't delete --
http://support.microsoft.com/default...;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Andrew J. Kelly SQL MVP
"mark_at_msb" <mark_at_msb@.discussions.microsoft.com> wrote in message
news:486897A4-7C19-4924-845D-0A724D4E4899@.microsoft.com...
> I've just become the DBA. The origianl Maintenance Plan was setup to
remove
> old TRasact Log Backups. This has not worked in several months. Does
anyone
> have an idea why a working plan would fail? Everything appears to be set
> correctly. Is there any advantage to placing optimimization in a seperate
> plan from the backups?
old TRasact Log Backups. This has not worked in several months. Does anyone
have an idea why a working plan would fail? Everything appears to be set
correctly. Is there any advantage to placing optimimization in a seperate
plan from the backups?
Here is a post from Bill at MS that outlines the most common reasons for
this:
-- Log files don't delete --
http://support.microsoft.com/default...;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Andrew J. Kelly SQL MVP
"mark_at_msb" <mark_at_msb@.discussions.microsoft.com> wrote in message
news:486897A4-7C19-4924-845D-0A724D4E4899@.microsoft.com...
> I've just become the DBA. The origianl Maintenance Plan was setup to
remove
> old TRasact Log Backups. This has not worked in several months. Does
anyone
> have an idea why a working plan would fail? Everything appears to be set
> correctly. Is there any advantage to placing optimimization in a seperate
> plan from the backups?
Subscribe to:
Posts (Atom)