Hypothetically speaking...

Let's pretend I'm running a big fat batch in a giant stored procedure that's
chewing on about half a million rows, and meanwhile, some clown in MIS
decides it would be a neat idea to reboot the big momma SQL Server in the
middle of the work day.
What might happen? Think the MSSQLServer service completes the batch before
stopping, or roles it back so I get to run it all over again? [grumble]
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSenseiIf you have your batch inside a transaction you are correct. If not the
only thing that will roll back will be any statement that you are in the
middle of.
Then, get the person who rebooted the machine and take him on a little
ride...
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OjEu88IYFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Let's pretend I'm running a big fat batch in a giant stored procedure
> that's chewing on about half a million rows, and meanwhile, some clown in
> MIS decides it would be a neat idea to reboot the big momma SQL Server in
> the middle of the work day.
> What might happen? Think the MSSQLServer service completes the batch
> before stopping, or roles it back so I get to run it all over again?
> [grumble]
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>|||What a hypothetical headache :( If the batch is a single transaction, it
will all roll back. If it's a bunch of individual transactions, only the
uncommitted transactions will be rolled back - basically the last one it was
executing when your buddy pulled the plug.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OjEu88IYFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Let's pretend I'm running a big fat batch in a giant stored procedure
> that's chewing on about half a million rows, and meanwhile, some clown in
> MIS decides it would be a neat idea to reboot the big momma SQL Server in
> the middle of the work day.
> What might happen? Think the MSSQLServer service completes the batch
> before stopping, or roles it back so I get to run it all over again?
> [grumble]
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>|||> What a hypothetical headache :( If the batch is a single transaction, it
> will all roll back. If it's a bunch of individual transactions, only the
> uncommitted transactions will be rolled back - basically the last one it
> was executing when your buddy pulled the plug.
They also recently got a new SQL Server so that we now finally have a
production server and a development server.
The original one is now the dev server. But now that they've moved the
databases over to the new server, they (now that it's back up) deleted the
huge importing database that my group uses off of the dev server, and now
nobody can get in because that database is set as the "default one" on all
our logins. And the two guys that know the sa password are out of the
office. I wanna rip somebody's "hypothetical" eyes out.
So now I can't even get in and look at the hypothetical mess that got made
by my long list of little transactions.
"MIS" must mean "Masters In Silliness". I swear, working here is like
living inside a Salvador Dali painting.
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||Mike,
Do you have access to another db in the server?, if so, then use osql
utility, connect to the db you have access to and execute sp_defaultdb to
change your default db to one that you have access to.
Example:
c:\> osql.exe -S my_server -d db_i_have_access_to -E -Q "exec sp_defaultdb
'my_login', 'new_default_db'"
AMB
"Mike Labosh" wrote:

> They also recently got a new SQL Server so that we now finally have a
> production server and a development server.
> The original one is now the dev server. But now that they've moved the
> databases over to the new server, they (now that it's back up) deleted the
> huge importing database that my group uses off of the dev server, and now
> nobody can get in because that database is set as the "default one" on all
> our logins. And the two guys that know the sa password are out of the
> office. I wanna rip somebody's "hypothetical" eyes out.
> So now I can't even get in and look at the hypothetical mess that got made
> by my long list of little transactions.
> "MIS" must mean "Masters In Silliness". I swear, working here is like
> living inside a Salvador Dali painting.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>
>|||To change the default database for a login, when that database was
dropped and when "sa"-s are out of the office, use ISQL to connect to
SQL Server, as suggested by the following KB articles:
http://support.microsoft.com/defaul...b;en-us;Q196076
http://support.microsoft.com/defaul...b;en-us;Q307864
Razvan|||> Do you have access to another db in the server?, if so, then use osql
> utility, connect to the db you have access to and execute sp_defaultdb to
> change your default db to one that you have access to.
OH WOW! I would *never* have thought of that
--
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||How transactions are rolled back depends on the recovery model setting on
the database. Also, did he stop the SQL Server service, click Windows
re-start, or punch the power button?
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OjEu88IYFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Let's pretend I'm running a big fat batch in a giant stored procedure
that's
> chewing on about half a million rows, and meanwhile, some clown in MIS
> decides it would be a neat idea to reboot the big momma SQL Server in the
> middle of the work day.
> What might happen? Think the MSSQLServer service completes the batch
before
> stopping, or roles it back so I get to run it all over again? [grumble]
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>|||> How transactions are rolled back depends on the recovery model setting on
> the database.
No they arent. The recovery model determines how long committed transactions
are stored in the log, not uncommitted ones. And once they are committed
they are "more or less" useless to us (except to apply to a backup or poke
around in with a log explorer.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"JT" <someone@.microsoft.com> wrote in message
news:OOBRwgKYFHA.3584@.TK2MSFTNGP12.phx.gbl...
> How transactions are rolled back depends on the recovery model setting on
> the database. Also, did he stop the SQL Server service, click Windows
> re-start, or punch the power button?
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:OjEu88IYFHA.2128@.TK2MSFTNGP15.phx.gbl...
> that's
> before
>|||Thank you all.
The "suit" that has the sa password has fixed our logins, app-dev has
continued (amidst a rather surreal delay) and I have finished mopping up the
mess that my batch became.
In the morning I will soothe the users with a bunch of "It's ok, we just had
a glitch" foolishness.
I cannot believe I even work here. Perhaps I should change my sig to "Life
is like a Salvador Dali painting".
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b){2}" -- William Shakespeare