Question about restoring transaction log

Hello everyone
I have accidentally deleted a whole bunch of tables with
data from a SQL Server 2000 database (I kwon this soesn't
look not very clever but ... I thought a DSN was
pointing to my MSDE on my PC but it was pointing to the
Win 2000 server). I hadn't made any backup copies yet but
have read that all the information the SQL DBMS manages
is saved in a file called the transaction log. My
transaction log is about 120 MB (the real one on the
server) and hope that the info I have deleted is still
stored there somehow.
The question I pose here is obviously how can I restore
back this tables if posible.
Thank you in advance for any reply
Antonio MiguelHi,
This is assuming your database's recovery model is not set to "Simple".
You are going to do a point-in-time restore. First do a full database
backup make sure "Delete Inactive Entries from Transaction Log" is not
checked; then, backup the transaction Log.
I usually use Enterprise Manager for restoring databases, BOL has the steps
for using Query Analyzer. Using EM, when restoring the database check the
box beside "Point in time restore" enter the time prior to when you deleted
the tables.
"Antonio Miguel" <amgarcia@.dephimatica.es> wrote in message
news:00d001c39276$27386770$a401280a@.phx.gbl...
> Hello everyone
> I have accidentally deleted a whole bunch of tables with
> data from a SQL Server 2000 database (I kwon this soesn't
> look not very clever but ... I thought a DSN was
> pointing to my MSDE on my PC but it was pointing to the
> Win 2000 server). I hadn't made any backup copies yet but
> have read that all the information the SQL DBMS manages
> is saved in a file called the transaction log. My
> transaction log is about 120 MB (the real one on the
> server) and hope that the info I have deleted is still
> stored there somehow.
> The question I pose here is obviously how can I restore
> back this tables if posible.
> Thank you in advance for any reply
> Antonio Miguel|||If Antonio never did a database backup, then the log is truncated at 70% full even if the recovery
mode is full. This is called the "auto-truncate mode" and is not the same thing as simple recovery
mode.
So, Antonio's best bet might be Log Explorer from www.lumigent.com.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Bianca Blount" <blountb@.noemail.com> wrote in message
news:u0%23aBMokDHA.2776@.tk2msftngp13.phx.gbl...
> Hi,
> This is assuming your database's recovery model is not set to "Simple".
> You are going to do a point-in-time restore. First do a full database
> backup make sure "Delete Inactive Entries from Transaction Log" is not
> checked; then, backup the transaction Log.
> I usually use Enterprise Manager for restoring databases, BOL has the steps
> for using Query Analyzer. Using EM, when restoring the database check the
> box beside "Point in time restore" enter the time prior to when you deleted
> the tables.
>
> "Antonio Miguel" <amgarcia@.dephimatica.es> wrote in message
> news:00d001c39276$27386770$a401280a@.phx.gbl...
> > Hello everyone
> >
> > I have accidentally deleted a whole bunch of tables with
> > data from a SQL Server 2000 database (I kwon this soesn't
> > look not very clever but ... I thought a DSN was
> > pointing to my MSDE on my PC but it was pointing to the
> > Win 2000 server). I hadn't made any backup copies yet but
> > have read that all the information the SQL DBMS manages
> > is saved in a file called the transaction log. My
> > transaction log is about 120 MB (the real one on the
> > server) and hope that the info I have deleted is still
> > stored there somehow.
> > The question I pose here is obviously how can I restore
> > back this tables if posible.
> >
> > Thank you in advance for any reply
> > Antonio Miguel
>|||Thanks for the information. I have not heard of "Auto-Recovery" mode. What
is it and is it something that can be controlled by the DBA?
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:O6OQzgukDHA.708@.TK2MSFTNGP10.phx.gbl...
> If Antonio never did a database backup, then the log is truncated at 70%
full even if the recovery
> mode is full. This is called the "auto-truncate mode" and is not the same
thing as simple recovery
> mode.
> So, Antonio's best bet might be Log Explorer from www.lumigent.com.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>|||Not "auto recovery". It is called "auto-truncate". This means that if SQL Server know that a backup
of the transaction log will be useless (say you never performed a database backup in the first
place, for instance), then SQL Server will empty the log when it is 70% full. This is badly
documented in Books Online. Last time I looked it took a long time before I found the few word that
BOL mentions on this.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Bianca Blount" <blountb@.noemail.com> wrote in message news:ei$zeDxkDHA.2776@.tk2msftngp13.phx.gbl...
> Thanks for the information. I have not heard of "Auto-Recovery" mode. What
> is it and is it something that can be controlled by the DBA?
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:O6OQzgukDHA.708@.TK2MSFTNGP10.phx.gbl...
> > If Antonio never did a database backup, then the log is truncated at 70%
> full even if the recovery
> > mode is full. This is called the "auto-truncate mode" and is not the same
> thing as simple recovery
> > mode.
> >
> > So, Antonio's best bet might be Log Explorer from www.lumigent.com.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
>

Question about Point Time Recovery

Hello All,
I have questions about point in time.
I took database backups and transaction log like the following.
Database backup at 1:00 pm on July 20
Transaction backup at 2:00 pm on July 20
Database backup at 1:00 pm on July 21
Transaction backup at 2:00 pm on July 21
Can I restore the database to 3:00 pm on July 20?
Can I restore the database to only 2:00 pm on July 20?
Would you please explain it?
Are there any good documents or web site about point time recovery?
If point in time restore button is grey, I cannot restore a database to point
in time? Is it right?
Thanks in advance,
Do.
Message posted via http://www.droptable.com
Hi,
POINT-IN-TIME recovery can be made only if your database is FULL recovery
model. BULK_LOGGED recovery model will not allow
POINT-IN_TIME recovery.
Based on your backups below you could do a seperate point in time recovery
till 2:00 pm on July 20 or 3:00 pm on July 20
(Provided your database recovery is FULL)
-- For 2:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 02:00 PM'
GO
-- For 3:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_21july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 03:00 PM'
GO
Are there any good documents or web site about point time recovery?
See POINT IN TIME in books online
If point in time restore button is grey, I cannot restore a database to
point in time? Is it right?
"Do P via droptable.com" <forum@.droptable.com> wrote in message
news:51A93BABEA594@.droptable.com...
> Hello All,
> I have questions about point in time.
> I took database backups and transaction log like the following.
> Database backup at 1:00 pm on July 20
> Transaction backup at 2:00 pm on July 20
> Database backup at 1:00 pm on July 21
> Transaction backup at 2:00 pm on July 21
> Can I restore the database to 3:00 pm on July 20?
> Can I restore the database to only 2:00 pm on July 20?
> Would you please explain it?
> Are there any good documents or web site about point time recovery?
> If point in time restore button is grey, I cannot restore a database to
> point
> in time? Is it right?
>
> Thanks in advance,
> Do.
>
> --
> Message posted via http://www.droptable.com
|||On Thu, 21 Jul 2005 17:27:51 GMT, Do P via droptable.com wrote:

>Hello All,
>I have questions about point in time.
>I took database backups and transaction log like the following.
>Database backup at 1:00 pm on July 20
>Transaction backup at 2:00 pm on July 20
>Database backup at 1:00 pm on July 21
>Transaction backup at 2:00 pm on July 21
>Can I restore the database to 3:00 pm on July 20?
Hi Do,
Yes. First restore from the full backup, with the WITH NORECOVERY
option. Next, restore the transaction log backup of July 20 2:00 PM,
again with the WITH NORECOVERY option. Finally, restore from the second
transaction log, this time without WITH NORECOVERY, but with a STOPAT
parameter.

>Are there any good documents or web site about point time recovery?
Books Online is a great start.

>If point in time restore button is grey, I cannot restore a database to point
>in time? Is it right?
Either that, or you didn't supply all the needed information yet, or you
have to restore some other backup first before you can restore to the
desired point in time. I recommend that you start using Query Analyzer
and typing the RESTORE DATABASE and RESTORE LOG commands. This gives you
much more control over what will happen.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hello Hugo
Thanks for your update.
If I use the ''Point in time restore'' option, at the end of the restore, my
database is ALWAYS in ''Loading'' state even if I checked the ''Leave
database operational''. Would you please let me know how to restore a
database to point in time in Enterprise Manager? How can I avoid this from
happening?
Best Regards,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||Perhaps a refresh issue? Try closing down Enterprise Manager and start it again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:51B72295E6DA2@.droptable.com...
> Hello Hugo
> Thanks for your update.
> If I use the ''Point in time restore'' option, at the end of the restore, my
> database is ALWAYS in ''Loading'' state even if I checked the ''Leave
> database operational''. Would you please let me know how to restore a
> database to point in time in Enterprise Manager? How can I avoid this from
> happening?
> Best Regards,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200507/1

Question about Point Time Recovery

Hello All,
I have questions about point in time.
I took database backups and transaction log like the following.
Database backup at 1:00 pm on July 20
Transaction backup at 2:00 pm on July 20
Database backup at 1:00 pm on July 21
Transaction backup at 2:00 pm on July 21
Can I restore the database to 3:00 pm on July 20?
Can I restore the database to only 2:00 pm on July 20?
Would you please explain it?
Are there any good documents or web site about point time recovery?
If point in time restore button is grey, I cannot restore a database to point
in time? Is it right?
Thanks in advance,
Do.
--
Message posted via http://www.sqlmonster.comHi,
POINT-IN-TIME recovery can be made only if your database is FULL recovery
model. BULK_LOGGED recovery model will not allow
POINT-IN_TIME recovery.
Based on your backups below you could do a seperate point in time recovery
till 2:00 pm on July 20 or 3:00 pm on July 20
(Provided your database recovery is FULL)
-- For 2:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 02:00 PM'
GO
-- For 3:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_21july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 03:00 PM'
GO
Are there any good documents or web site about point time recovery?
See POINT IN TIME in books online
If point in time restore button is grey, I cannot restore a database to
point in time? Is it right?
"Do P via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:51A93BABEA594@.SQLMonster.com...
> Hello All,
> I have questions about point in time.
> I took database backups and transaction log like the following.
> Database backup at 1:00 pm on July 20
> Transaction backup at 2:00 pm on July 20
> Database backup at 1:00 pm on July 21
> Transaction backup at 2:00 pm on July 21
> Can I restore the database to 3:00 pm on July 20?
> Can I restore the database to only 2:00 pm on July 20?
> Would you please explain it?
> Are there any good documents or web site about point time recovery?
> If point in time restore button is grey, I cannot restore a database to
> point
> in time? Is it right?
>
> Thanks in advance,
> Do.
>
> --
> Message posted via http://www.sqlmonster.com|||On Thu, 21 Jul 2005 17:27:51 GMT, Do P via SQLMonster.com wrote:
>Hello All,
>I have questions about point in time.
>I took database backups and transaction log like the following.
>Database backup at 1:00 pm on July 20
>Transaction backup at 2:00 pm on July 20
>Database backup at 1:00 pm on July 21
>Transaction backup at 2:00 pm on July 21
>Can I restore the database to 3:00 pm on July 20?
Hi Do,
Yes. First restore from the full backup, with the WITH NORECOVERY
option. Next, restore the transaction log backup of July 20 2:00 PM,
again with the WITH NORECOVERY option. Finally, restore from the second
transaction log, this time without WITH NORECOVERY, but with a STOPAT
parameter.
>Are there any good documents or web site about point time recovery?
Books Online is a great start.
>If point in time restore button is grey, I cannot restore a database to point
>in time? Is it right?
Either that, or you didn't supply all the needed information yet, or you
have to restore some other backup first before you can restore to the
desired point in time. I recommend that you start using Query Analyzer
and typing the RESTORE DATABASE and RESTORE LOG commands. This gives you
much more control over what will happen.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo
Thanks for your update.
If I use the ''Point in time restore'' option, at the end of the restore, my
database is ALWAYS in ''Loading'' state even if I checked the ''Leave
database operational''. Would you please let me know how to restore a
database to point in time in Enterprise Manager? How can I avoid this from
happening?
Best Regards,
Do.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||Perhaps a refresh issue? Try closing down Enterprise Manager and start it again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:51B72295E6DA2@.SQLMonster.com...
> Hello Hugo
> Thanks for your update.
> If I use the ''Point in time restore'' option, at the end of the restore, my
> database is ALWAYS in ''Loading'' state even if I checked the ''Leave
> database operational''. Would you please let me know how to restore a
> database to point in time in Enterprise Manager? How can I avoid this from
> happening?
> Best Regards,
> Do.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1

Question about Point Time Recovery

Hello All,
I have questions about point in time.
I took database backups and transaction log like the following.
Database backup at 1:00 pm on July 20
Transaction backup at 2:00 pm on July 20
Database backup at 1:00 pm on July 21
Transaction backup at 2:00 pm on July 21
Can I restore the database to 3:00 pm on July 20?
Can I restore the database to only 2:00 pm on July 20?
Would you please explain it?
Are there any good documents or web site about point time recovery?
If point in time restore button is grey, I cannot restore a database to poin
t
in time? Is it right?
Thanks in advance,
Do.
Message posted via http://www.droptable.comHi,
POINT-IN-TIME recovery can be made only if your database is FULL recovery
model. BULK_LOGGED recovery model will not allow
POINT-IN_TIME recovery.
Based on your backups below you could do a seperate point in time recovery
till 2:00 pm on July 20 or 3:00 pm on July 20
(Provided your database recovery is FULL)
-- For 2:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 02:00 PM'
GO
-- For 3:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_21july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 03:00 PM'
GO
Are there any good documents or web site about point time recovery?
See POINT IN TIME in books online
If point in time restore button is grey, I cannot restore a database to
point in time? Is it right?
"Do P via droptable.com" <forum@.droptable.com> wrote in message
news:51A93BABEA594@.droptable.com...
> Hello All,
> I have questions about point in time.
> I took database backups and transaction log like the following.
> Database backup at 1:00 pm on July 20
> Transaction backup at 2:00 pm on July 20
> Database backup at 1:00 pm on July 21
> Transaction backup at 2:00 pm on July 21
> Can I restore the database to 3:00 pm on July 20?
> Can I restore the database to only 2:00 pm on July 20?
> Would you please explain it?
> Are there any good documents or web site about point time recovery?
> If point in time restore button is grey, I cannot restore a database to
> point
> in time? Is it right?
>
> Thanks in advance,
> Do.
>
> --
> Message posted via http://www.droptable.com|||On Thu, 21 Jul 2005 17:27:51 GMT, Do P via droptable.com wrote:

>Hello All,
>I have questions about point in time.
>I took database backups and transaction log like the following.
>Database backup at 1:00 pm on July 20
>Transaction backup at 2:00 pm on July 20
>Database backup at 1:00 pm on July 21
>Transaction backup at 2:00 pm on July 21
>Can I restore the database to 3:00 pm on July 20?
Hi Do,
Yes. First restore from the full backup, with the WITH NORECOVERY
option. Next, restore the transaction log backup of July 20 2:00 PM,
again with the WITH NORECOVERY option. Finally, restore from the second
transaction log, this time without WITH NORECOVERY, but with a STOPAT
parameter.

>Are there any good documents or web site about point time recovery?
Books Online is a great start.

>If point in time restore button is grey, I cannot restore a database to poi
nt
>in time? Is it right?
Either that, or you didn't supply all the needed information yet, or you
have to restore some other backup first before you can restore to the
desired point in time. I recommend that you start using Query Analyzer
and typing the RESTORE DATABASE and RESTORE LOG commands. This gives you
much more control over what will happen.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo
Thanks for your update.
If I use the ''Point in time restore'' option, at the end of the restore, my
database is ALWAYS in ''Loading'' state even if I checked the ''Leave
database operational''. Would you please let me know how to restore a
database to point in time in Enterprise Manager? How can I avoid this from
happening?
Best Regards,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||Perhaps a refresh issue? Try closing down Enterprise Manager and start it ag
ain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:51B72295E6DA2@.droptable.com...
> Hello Hugo
> Thanks for your update.
> If I use the ''Point in time restore'' option, at the end of the restore,
my
> database is ALWAYS in ''Loading'' state even if I checked the ''Leave
> database operational''. Would you please let me know how to restore a
> database to point in time in Enterprise Manager? How can I avoid this from
> happening?
> Best Regards,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200507/1

Question about ISOLATION LEVEL.

(SQL Server 2000, SP3a)
Hello, all!
I've got a question about setting the transaction isolation level within a stored
procedure.
In essence I want to have a particular piece of code in a SP use the SERIALIZABLE level.
However, I don't want this to "stick" for the rest of the connection as this BOL blurb
suggests:
<Quote>
Only one of the options can be set at a time, and it remains set for that connection until
it is explicitly changed. This becomes the default behavior unless an optimization option
is specified at the table level in the FROM clause of the statement.
<Quote>
Does the specified level really persist even if it's been invoked from a SP? Is there any
way to get the "old" level if I change it?
Alternatively, I *think* I could put this in the FROM clause (can it be in the
<table_hint_limited> section of an UPDATE instead?):
update mt with (rowlock, readcommitted)
set MyField = 1
from MyTable as mt
where MyField = 2
Or does it have to be in the FROM clause like:
update mt with (rowlock)
set MyField = 1
from MyTable as mt with (readcommitted)
where MyField = 2
Or does it matter?
Thanks for any help you can provide! :-)
John PetersonJohn,
It will stay that way for the life of the connection if you don't change it.
But why don't you just issue a SET TRANSACTION ISOLATION LEVEL Read
Committed when done?
--
Andrew J. Kelly
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OkRbjfWfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello, all!
> I've got a question about setting the transaction isolation level within a
stored
> procedure.
> In essence I want to have a particular piece of code in a SP use the
SERIALIZABLE level.
> However, I don't want this to "stick" for the rest of the connection as
this BOL blurb
> suggests:
> <Quote>
> Only one of the options can be set at a time, and it remains set for that
connection until
> it is explicitly changed. This becomes the default behavior unless an
optimization option
> is specified at the table level in the FROM clause of the statement.
> <Quote>
> Does the specified level really persist even if it's been invoked from a
SP? Is there any
> way to get the "old" level if I change it?
>
> Alternatively, I *think* I could put this in the FROM clause (can it be in
the
> <table_hint_limited> section of an UPDATE instead?):
> update mt with (rowlock, readcommitted)
> set MyField = 1
> from MyTable as mt
> where MyField = 2
> Or does it have to be in the FROM clause like:
> update mt with (rowlock)
> set MyField = 1
> from MyTable as mt with (readcommitted)
> where MyField = 2
> Or does it matter?
> Thanks for any help you can provide! :-)
> John Peterson
>|||From the SQL Server 2000 Books Online:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_create_4hk5.htm">
When a SET statement is executed from a stored procedure, the setting
remains in effect only until the stored procedure completes. The setting
is then restored to the value it had when the stored procedure was
called. This allows individual clients to set the options wanted without
affecting the logic of the stored procedure.
</Excerpt>
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OkRbjfWfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello, all!
> I've got a question about setting the transaction isolation level
within a stored
> procedure.
> In essence I want to have a particular piece of code in a SP use the
SERIALIZABLE level.
> However, I don't want this to "stick" for the rest of the connection
as this BOL blurb
> suggests:
> <Quote>
> Only one of the options can be set at a time, and it remains set for
that connection until
> it is explicitly changed. This becomes the default behavior unless an
optimization option
> is specified at the table level in the FROM clause of the statement.
> <Quote>
> Does the specified level really persist even if it's been invoked from
a SP? Is there any
> way to get the "old" level if I change it?
>
> Alternatively, I *think* I could put this in the FROM clause (can it
be in the
> <table_hint_limited> section of an UPDATE instead?):
> update mt with (rowlock, readcommitted)
> set MyField = 1
> from MyTable as mt
> where MyField = 2
> Or does it have to be in the FROM clause like:
> update mt with (rowlock)
> set MyField = 1
> from MyTable as mt with (readcommitted)
> where MyField = 2
> Or does it matter?
> Thanks for any help you can provide! :-)
> John Peterson
>|||Dan,
It's my understanding that this does not hold true for the Isolation level
and it is retained for the entire session or until it is changed again. I
guess I will have to test this to see...
--
Andrew J. Kelly
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OlGwpUXfDHA.3284@.tk2msftngp13.phx.gbl...
> From the SQL Server 2000 Books Online:
> <Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_create_4hk5.htm">
> When a SET statement is executed from a stored procedure, the setting
> remains in effect only until the stored procedure completes. The setting
> is then restored to the value it had when the stored procedure was
> called. This allows individual clients to set the options wanted without
> affecting the logic of the stored procedure.
> </Excerpt>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OkRbjfWfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello, all!
> >
> > I've got a question about setting the transaction isolation level
> within a stored
> > procedure.
> >
> > In essence I want to have a particular piece of code in a SP use the
> SERIALIZABLE level.
> > However, I don't want this to "stick" for the rest of the connection
> as this BOL blurb
> > suggests:
> >
> > <Quote>
> > Only one of the options can be set at a time, and it remains set for
> that connection until
> > it is explicitly changed. This becomes the default behavior unless an
> optimization option
> > is specified at the table level in the FROM clause of the statement.
> > <Quote>
> >
> > Does the specified level really persist even if it's been invoked from
> a SP? Is there any
> > way to get the "old" level if I change it?
> >
> >
> > Alternatively, I *think* I could put this in the FROM clause (can it
> be in the
> > <table_hint_limited> section of an UPDATE instead?):
> >
> > update mt with (rowlock, readcommitted)
> > set MyField = 1
> > from MyTable as mt
> > where MyField = 2
> >
> > Or does it have to be in the FROM clause like:
> >
> > update mt with (rowlock)
> > set MyField = 1
> > from MyTable as mt with (readcommitted)
> > where MyField = 2
> >
> > Or does it matter?
> >
> > Thanks for any help you can provide! :-)
> >
> > John Peterson
> >
> >
>|||Andrew, I ran the follow script to verify the BOL statement and it
seemed to held true, at least as reported by DBCC USEROPTIONS.
CREATE PROC MyProc
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DBCC USEROPTIONS
GO
DBCC USEROPTIONS
EXEC MyProc
DBCC USEROPTIONS
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O3ziOFYfDHA.2352@.TK2MSFTNGP12.phx.gbl...
> Dan,
> It's my understanding that this does not hold true for the Isolation
level
> and it is retained for the entire session or until it is changed
again. I
> guess I will have to test this to see...
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:OlGwpUXfDHA.3284@.tk2msftngp13.phx.gbl...
> > From the SQL Server 2000 Books Online:
> >
> > <Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_create_4hk5.htm">
> > When a SET statement is executed from a stored procedure, the
setting
> > remains in effect only until the stored procedure completes. The
setting
> > is then restored to the value it had when the stored procedure was
> > called. This allows individual clients to set the options wanted
without
> > affecting the logic of the stored procedure.
> > </Excerpt>
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:OkRbjfWfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello, all!
> > >
> > > I've got a question about setting the transaction isolation level
> > within a stored
> > > procedure.
> > >
> > > In essence I want to have a particular piece of code in a SP use
the
> > SERIALIZABLE level.
> > > However, I don't want this to "stick" for the rest of the
connection
> > as this BOL blurb
> > > suggests:
> > >
> > > <Quote>
> > > Only one of the options can be set at a time, and it remains set
for
> > that connection until
> > > it is explicitly changed. This becomes the default behavior unless
an
> > optimization option
> > > is specified at the table level in the FROM clause of the
statement.
> > > <Quote>
> > >
> > > Does the specified level really persist even if it's been invoked
from
> > a SP? Is there any
> > > way to get the "old" level if I change it?
> > >
> > >
> > > Alternatively, I *think* I could put this in the FROM clause (can
it
> > be in the
> > > <table_hint_limited> section of an UPDATE instead?):
> > >
> > > update mt with (rowlock, readcommitted)
> > > set MyField = 1
> > > from MyTable as mt
> > > where MyField = 2
> > >
> > > Or does it have to be in the FROM clause like:
> > >
> > > update mt with (rowlock)
> > > set MyField = 1
> > > from MyTable as mt with (readcommitted)
> > > where MyField = 2
> > >
> > > Or does it matter?
> > >
> > > Thanks for any help you can provide! :-)
> > >
> > > John Peterson
> > >
> > >
> >
> >
>|||Thanks guys, that helps! I didn't see that blurb, Dan -- can you tell me what section of
BOL you found that?
As it turns out, I decided to do it at the table level as in my example. Curiously, I had
READCOMMITTED at *both* the UPDATE clause and the FROM clause. That turned out to be
rather "heavy" for some reason. When I changed it to be just on the UPDATE clause, it was
substantially faster.
Thanks again!
John Peterson
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OnDkGhYfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> Andrew, I ran the follow script to verify the BOL statement and it
> seemed to held true, at least as reported by DBCC USEROPTIONS.
> CREATE PROC MyProc
> AS
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> DBCC USEROPTIONS
> GO
> DBCC USEROPTIONS
> EXEC MyProc
> DBCC USEROPTIONS
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O3ziOFYfDHA.2352@.TK2MSFTNGP12.phx.gbl...
> > Dan,
> >
> > It's my understanding that this does not hold true for the Isolation
> level
> > and it is retained for the entire session or until it is changed
> again. I
> > guess I will have to test this to see...
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:OlGwpUXfDHA.3284@.tk2msftngp13.phx.gbl...
> > > From the SQL Server 2000 Books Online:
> > >
> > > <Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_create_4hk5.htm">
> > > When a SET statement is executed from a stored procedure, the
> setting
> > > remains in effect only until the stored procedure completes. The
> setting
> > > is then restored to the value it had when the stored procedure was
> > > called. This allows individual clients to set the options wanted
> without
> > > affecting the logic of the stored procedure.
> > > </Excerpt>
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > --
> > > SQL FAQ links (courtesy Neil Pike):
> > >
> > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > http://www.sqlserverfaq.com
> > > http://www.mssqlserver.com/faq
> > > --
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:OkRbjfWfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> > > > (SQL Server 2000, SP3a)
> > > >
> > > > Hello, all!
> > > >
> > > > I've got a question about setting the transaction isolation level
> > > within a stored
> > > > procedure.
> > > >
> > > > In essence I want to have a particular piece of code in a SP use
> the
> > > SERIALIZABLE level.
> > > > However, I don't want this to "stick" for the rest of the
> connection
> > > as this BOL blurb
> > > > suggests:
> > > >
> > > > <Quote>
> > > > Only one of the options can be set at a time, and it remains set
> for
> > > that connection until
> > > > it is explicitly changed. This becomes the default behavior unless
> an
> > > optimization option
> > > > is specified at the table level in the FROM clause of the
> statement.
> > > > <Quote>
> > > >
> > > > Does the specified level really persist even if it's been invoked
> from
> > > a SP? Is there any
> > > > way to get the "old" level if I change it?
> > > >
> > > >
> > > > Alternatively, I *think* I could put this in the FROM clause (can
> it
> > > be in the
> > > > <table_hint_limited> section of an UPDATE instead?):
> > > >
> > > > update mt with (rowlock, readcommitted)
> > > > set MyField = 1
> > > > from MyTable as mt
> > > > where MyField = 2
> > > >
> > > > Or does it have to be in the FROM clause like:
> > > >
> > > > update mt with (rowlock)
> > > > set MyField = 1
> > > > from MyTable as mt with (readcommitted)
> > > > where MyField = 2
> > > >
> > > > Or does it matter?
> > > >
> > > > Thanks for any help you can provide! :-)
> > > >
> > > > John Peterson
> > > >
> > > >
> > >
> > >
> >
> >
>|||> Dan -- can you tell me what section of
> BOL you found that?
The excerpt is from the CREATE PROCEDURE topic. You can jump there
directly from the Bools Online menu bar by clicking Go --> URL and
pasting in 'tsqlref.chm::/ts_create_4hk5.htm'.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23RXiFnYfDHA.1760@.TK2MSFTNGP09.phx.gbl...
> Thanks guys, that helps! I didn't see that blurb, Dan -- can you tell
me what section of
> BOL you found that?
> As it turns out, I decided to do it at the table level as in my
example. Curiously, I had
> READCOMMITTED at *both* the UPDATE clause and the FROM clause. That
turned out to be
> rather "heavy" for some reason. When I changed it to be just on the
UPDATE clause, it was
> substantially faster.
> Thanks again!
> John Peterson
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%238x$R8dfDHA.1200@.TK2MSFTNGP09.phx.gbl...
> Hmmm. Thanks Dan. I was pretty sure that I remember reading otherwise
> somewhere so that's good to know. Not that I ever set an isolation level
> without setting it back anyway.
The problem with setting it back, IMO, is that there's not a convenient mechanism to
record the *old* isolation level with which to restore. Granted, 99.99% of the time it's
likely the default of READ COMMITTED. But we've got some clients whose default is
essentially READ UNCOMMITTED.
> Andrew J. Kelly
> SQL Server MVP
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:OnDkGhYfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> > Andrew, I ran the follow script to verify the BOL statement and it
> > seemed to held true, at least as reported by DBCC USEROPTIONS.
> >
> > CREATE PROC MyProc
> > AS
> > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> > DBCC USEROPTIONS
> > GO
> >
> > DBCC USEROPTIONS
> > EXEC MyProc
> > DBCC USEROPTIONS
> > GO
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:O3ziOFYfDHA.2352@.TK2MSFTNGP12.phx.gbl...
> > > Dan,
> > >
> > > It's my understanding that this does not hold true for the Isolation
> > level
> > > and it is retained for the entire session or until it is changed
> > again. I
> > > guess I will have to test this to see...
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > > news:OlGwpUXfDHA.3284@.tk2msftngp13.phx.gbl...
> > > > From the SQL Server 2000 Books Online:
> > > >
> > > > <Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_create_4hk5.htm">
> > > > When a SET statement is executed from a stored procedure, the
> > setting
> > > > remains in effect only until the stored procedure completes. The
> > setting
> > > > is then restored to the value it had when the stored procedure was
> > > > called. This allows individual clients to set the options wanted
> > without
> > > > affecting the logic of the stored procedure.
> > > > </Excerpt>
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > Dan Guzman
> > > > SQL Server MVP
> > > >
> > > > --
> > > > SQL FAQ links (courtesy Neil Pike):
> > > >
> > > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > > http://www.sqlserverfaq.com
> > > > http://www.mssqlserver.com/faq
> > > > --
> > > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:OkRbjfWfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> > > > > (SQL Server 2000, SP3a)
> > > > >
> > > > > Hello, all!
> > > > >
> > > > > I've got a question about setting the transaction isolation level
> > > > within a stored
> > > > > procedure.
> > > > >
> > > > > In essence I want to have a particular piece of code in a SP use
> > the
> > > > SERIALIZABLE level.
> > > > > However, I don't want this to "stick" for the rest of the
> > connection
> > > > as this BOL blurb
> > > > > suggests:
> > > > >
> > > > > <Quote>
> > > > > Only one of the options can be set at a time, and it remains set
> > for
> > > > that connection until
> > > > > it is explicitly changed. This becomes the default behavior unless
> > an
> > > > optimization option
> > > > > is specified at the table level in the FROM clause of the
> > statement.
> > > > > <Quote>
> > > > >
> > > > > Does the specified level really persist even if it's been invoked
> > from
> > > > a SP? Is there any
> > > > > way to get the "old" level if I change it?
> > > > >
> > > > >
> > > > > Alternatively, I *think* I could put this in the FROM clause (can
> > it
> > > > be in the
> > > > > <table_hint_limited> section of an UPDATE instead?):
> > > > >
> > > > > update mt with (rowlock, readcommitted)
> > > > > set MyField = 1
> > > > > from MyTable as mt
> > > > > where MyField = 2
> > > > >
> > > > > Or does it have to be in the FROM clause like:
> > > > >
> > > > > update mt with (rowlock)
> > > > > set MyField = 1
> > > > > from MyTable as mt with (readcommitted)
> > > > > where MyField = 2
> > > > >
> > > > > Or does it matter?
> > > > >
> > > > > Thanks for any help you can provide! :-)
> > > > >
> > > > > John Peterson
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

question about drives configuration

Hi,
We will build new clustering SQL Server. The question is that what kind
drives configuration for user-defined database data files and transaction log
files will improve performance, RAID 1+0, RAID 0+1, RAID 5 or RAID 10 etc.
Regards!
-Chen
The normal drive performance rules do not change on a cluster. RAID 1
(mirror) or 1+0 (stripe of mirror sets) gives the best performance, but has
the highest per-byte storage cost. RAID 5 (stripe with parity) is very low
cost but does have a performance hit for transactional data and SQL log
usage. RAID 0 + 1 (mirror of stripe sets) is not recommended. RAID 10 is a
generic term for sometimes Raid 1+0 and Sometimes RAID 0+1. Make sure which
one you are getting.
Geoff N. Hiten
Microsoft SQL Server MVP
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:F2963003-41D2-4132-B74C-FBF92EE46984@.microsoft.com...
> Hi,
> We will build new clustering SQL Server. The question is that what kind
> drives configuration for user-defined database data files and transaction
> log
> files will improve performance, RAID 1+0, RAID 0+1, RAID 5 or RAID 10 etc.
> Regards!
> -Chen
>
|||Because many vendors confuse the terms RAID 10, RAID 1+0, and RAID 0+1, use the terms mirrored striping and striped mirroring.
Striped mirroring is the best solution for a database server requiring high performance and high availability. Capacity can be a problem, however, because of the number of disks required and their cost.
More Info:
========
Striped Mirroring
===============
Striped mirroring writes logical blocks of data across two or more mirrored sets of disks, creating a single volume with no redundant information between the mirrored sets of disks. Each mirrored set of disks consists
of two or more disks. Striped mirroring provides the same high level of availability as mirroring. The available capacity of a striped mirror is equal to that of each disk in the mirror set (in a two-disk mirror set, 50
percent of the disk capacity is used for data protection). Unlike mirroring, however, striped mirroring provides symmetrical performance. Read performance is very fast because data is available from each disk in
the mirror. In striped mirror implementation, RAID array and SAN controllers support simultaneous reads from each member of the mirror, which maximizes read performance. Write performance is also fast because
writes occur simultaneously across each mirrored set.
With striped mirroring, if a drive fails, read and write performance is slightly reduced, but only for data stored on the mirror set containing the failed disks. Read and write performance to and from the other mirrored
sets is not affected. Striped mirroring can survive the failure of multiple drives, provided at least one drive in each mirrored set survives.
Mirrored Striping
===============
Mirrored striping writes logical blocks of data across two or more disks, creating a single logical volume with no redundant information between the disks, and then mirrors the volume. Read and write performance is
very fast, but mirrored striping is not as fault tolerant as striped mirroring. When a single disk in a stripe fails, the data remains available in the other stripe, but the stripe is broken. With one broken stripe, the failure of a
disk in the remaining stripe causes the data to become unavailable. In addition, when a stripe fails, read performance is slower because only one stripe is functional.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||I was under the impression that with write-back caching the performance
difference between raid 5 and the raid 01, 10,1 variants is much the same.
Is this not true?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uqzYB7ygFHA.3608@.TK2MSFTNGP12.phx.gbl...
> The normal drive performance rules do not change on a cluster. RAID 1
> (mirror) or 1+0 (stripe of mirror sets) gives the best performance, but
has
> the highest per-byte storage cost. RAID 5 (stripe with parity) is very
low
> cost but does have a performance hit for transactional data and SQL log
> usage. RAID 0 + 1 (mirror of stripe sets) is not recommended. RAID 10 is
a
> generic term for sometimes Raid 1+0 and Sometimes RAID 0+1. Make sure
which[vbcol=seagreen]
> one you are getting.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Chen" <Chen@.discussions.microsoft.com> wrote in message
> news:F2963003-41D2-4132-B74C-FBF92EE46984@.microsoft.com...
transaction[vbcol=seagreen]
etc.
>
|||If the amount of cache is sufficient to buffer all the I/O to the drives
then it may not be to much of an issue. But with direct attached storage or
low end SAN controllers you usually only get up to 512K of controller cache.
That can easily be saturated with a decent size checkpoint and then you are
at the mercy of the drives themselves. If you are looking for performance
you should not be thinking RAID 5.
Andrew J. Kelly SQL MVP
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23R0VkFghFHA.4028@.TK2MSFTNGP10.phx.gbl...
>I was under the impression that with write-back caching the performance
> difference between raid 5 and the raid 01, 10,1 variants is much the same.
> Is this not true?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uqzYB7ygFHA.3608@.TK2MSFTNGP12.phx.gbl...
> has
> low
> a
> which
> transaction
> etc.
>
|||RAID5 requires many more IO operations for a transactional change than any
of the other RAID configurations. This can impact cache performance since
it requires more cache space to handle the extra block read-writes. The
only place I find RAID5 to be good is where there is a daily load process
and everything else is read-only. transactional system, SAN or not, needs a
RAID 1+0 layoud for best performance.
Geoff N. Hiten
Microsoft SQL Server MVP
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23R0VkFghFHA.4028@.TK2MSFTNGP10.phx.gbl...
>I was under the impression that with write-back caching the performance
> difference between raid 5 and the raid 01, 10,1 variants is much the same.
> Is this not true?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uqzYB7ygFHA.3608@.TK2MSFTNGP12.phx.gbl...
> has
> low
> a
> which
> transaction
> etc.
>

Question about detaching databases.

I need to have someone clarify a couple things for me.
1. If a system running MSDE is rebooted in the middle of a transaction, I
am assuming at some point when the system is restarted, the transaction log
file is rolled back into the main database file. However, if a database is
detached before anyone connects to it, will the uncommitted transaction log
data be rolled into the main database file before the detach is performed?
2. When exactly does the uncommitted transaction log data get rolled back
into the database after a reboot in the middle of a transaction?
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
hi Ken,
Ken Varn wrote:
> I need to have someone clarify a couple things for me.
> 1. If a system running MSDE is rebooted in the middle of a
> transaction, I am assuming at some point when the system is
> restarted, the transaction log file is rolled back into the main
> database file. However, if a database is detached before anyone
> connects to it, will the uncommitted transaction log data be rolled
> into the main database file before the detach is performed?
when a system stops (crashes or is unproperly shut down), all the pending
transactions are aborted ..
when the SQL Service restarts, it will stars and recover all databases...
that's to say committed transaction will be rolled forward and uncommitted
rolled back.. the algorithm is quiet complex as SQL Server searches the best
method to perform this task to put the db on line and available as soon as
possible..
so you are not entitled to detach the db before the db has been recovered..
you can perhaps move the physical files before starting SQL Server, but then
the db will be marked as corrupted as SQL Server can no longer find the db
files as registered in it's system tables, so this is not a good idea...
> 2. When exactly does the uncommitted transaction log data get rolled
> back into the database after a reboot in the middle of a transaction?
as soon as the db is recovered... if you open the log file of SQL Server, in
the \Log folder, and inspect one of the ERRORLOG files you'll see something
like
2005-08-11 16:33:22.45 spid8 Starting up database 'msdb'.
2005-08-11 16:33:22.48 spid9 Starting up database 'pubs'.
2005-08-11 16:33:22.49 spid10 Starting up database 'Northwind'.
2005-08-11 16:33:22.51 spid11 Starting up database 'a'.
2005-08-11 16:33:23.17 spid5 Clearing tempdb database.
2005-08-11 16:33:23.21 spid11 Starting up database 'atest'.
2005-08-11 16:33:24.35 spid5 Starting up database 'tempdb'.
2005-08-11 16:33:24.45 spid3 Recovery complete.
....
if something is pending fo a specific database you'll see rollforwad and
rollback operations...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||> so you are not entitled to detach the db before the db has been
recovered..
> you can perhaps move the physical files before starting SQL Server, but
then
> the db will be marked as corrupted as SQL Server can no longer find the db
> files as registered in it's system tables, so this is not a good idea...
So that I can clarify what you are saying here. SQL Server will not allow
you to detach a database until it has completed the recovery process?
I basically want to know if it is safe to get rid of the LDF file after a
database has been detached without any adverse ramifications, like database
corruption.
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3m3sgtF159l9rU1@.individual.net...
> hi Ken,
> Ken Varn wrote:
> when a system stops (crashes or is unproperly shut down), all the pending
> transactions are aborted ..
> when the SQL Service restarts, it will stars and recover all databases...
> that's to say committed transaction will be rolled forward and uncommitted
> rolled back.. the algorithm is quiet complex as SQL Server searches the
best
> method to perform this task to put the db on line and available as soon as
> possible..
> so you are not entitled to detach the db before the db has been
recovered..
> you can perhaps move the physical files before starting SQL Server, but
then
> the db will be marked as corrupted as SQL Server can no longer find the db
> files as registered in it's system tables, so this is not a good idea...
> as soon as the db is recovered... if you open the log file of SQL Server,
in
> the \Log folder, and inspect one of the ERRORLOG files you'll see
something
> like
> 2005-08-11 16:33:22.45 spid8 Starting up database 'msdb'.
> 2005-08-11 16:33:22.48 spid9 Starting up database 'pubs'.
> 2005-08-11 16:33:22.49 spid10 Starting up database 'Northwind'.
> 2005-08-11 16:33:22.51 spid11 Starting up database 'a'.
> 2005-08-11 16:33:23.17 spid5 Clearing tempdb database.
> 2005-08-11 16:33:23.21 spid11 Starting up database 'atest'.
> 2005-08-11 16:33:24.35 spid5 Starting up database 'tempdb'.
> 2005-08-11 16:33:24.45 spid3 Recovery complete.
> ...
> if something is pending fo a specific database you'll see rollforwad and
> rollback operations...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ken,
Ken Varn wrote:
> So that I can clarify what you are saying here. SQL Server will not
> allow you to detach a database until it has completed the recovery
> process?
until the recovery has been completed, SQL Server does not allow to touch
all dbs at all... it is still "starting"...

> I basically want to know if it is safe to get rid of the LDF file
> after a database has been detached without any adverse ramifications,
> like database corruption.
actually you should not get rid of the LDF file at all, as it is a needed
part of a database... even when the simple recovery model is used you should
always provide all transaction log files... you can use
sp_attach_single_file_db when they are not present but best practice implies
forwarding them too...
say you have a db file named [myDb] with 1 physical data file as
C:\..\myDb.Mdf and a t-log file C:\..\myDb.Ldf...
you only provide me the C:\..\myDb.Mdf instructing me to attach it via
sp_attach_single_file_db ...
on my system I already have another company sw which uses a db with the very
same physical data files (and names)...
the corresponding datafile will be C:\..\myDb.Mdf as the log file in
C:\..\myDb.Ldf
as I can not put your db in the same folder I move it to
C:\anotherDir\myDb.Mdf and use sp_attach_single_file_db ... the problem,
now... SQL Server try to create a new t-log file using the available info
(sysfiles and sysfiles1 system table) and will point to the other company
database C:\..\myDb.Ldf physical file...
that log file will be pointed by 2 db and will immediately raises exception
marking 1 of the 2 db as suspect...
to solve this problem I need the t-log of your db and the use of
sp_attach_db indicating all the required physical files..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||> actually you should not get rid of the LDF file at all, as it is a needed
> part of a database... even when the simple recovery model is used you
should
> always provide all transaction log files... you can use
> sp_attach_single_file_db when they are not present but best practice
implies
> forwarding them too...
> say you have a db file named [myDb] with 1 physical data file as
> C:\..\myDb.Mdf and a t-log file C:\..\myDb.Ldf...
> you only provide me the C:\..\myDb.Mdf instructing me to attach it via
> sp_attach_single_file_db ...
> on my system I already have another company sw which uses a db with the
very
> same physical data files (and names)...
> the corresponding datafile will be C:\..\myDb.Mdf as the log file in
> C:\..\myDb.Ldf
It sounds as if as long as I do not try to attach a database onto a system
that has the same database name I can use sp_attach_single_file_db with no
adverse affects. Or, are there other issues that I should be concerned
with?
Basically, without trying to go into too much detail here, our system has an
automated process that periodically detaches databases, removes the ldf file
and archives the mdf file. The system may re-attach any of these databases
again at a later time depending on certain circumstances. We have been
seeing some database corruption issues and have tried to determine if this
process is the cause. We do know that occasionally the system is restarted
due to power failures (although the UPS is doing an orderly shutdown) and we
want to figure out if our detach and removal of LDF file process could be
the cause of databases getting corrupt after we re-attach them during a
restart from a power fail.
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3m444tF155s4gU1@.individual.net...
> hi Ken,
> Ken Varn wrote:
> until the recovery has been completed, SQL Server does not allow to touch
> all dbs at all... it is still "starting"...
>
> actually you should not get rid of the LDF file at all, as it is a needed
> part of a database... even when the simple recovery model is used you
should
> always provide all transaction log files... you can use
> sp_attach_single_file_db when they are not present but best practice
implies
> forwarding them too...
> say you have a db file named [myDb] with 1 physical data file as
> C:\..\myDb.Mdf and a t-log file C:\..\myDb.Ldf...
> you only provide me the C:\..\myDb.Mdf instructing me to attach it via
> sp_attach_single_file_db ...
> on my system I already have another company sw which uses a db with the
very
> same physical data files (and names)...
> the corresponding datafile will be C:\..\myDb.Mdf as the log file in
> C:\..\myDb.Ldf
> as I can not put your db in the same folder I move it to
> C:\anotherDir\myDb.Mdf and use sp_attach_single_file_db ... the problem,
> now... SQL Server try to create a new t-log file using the available info
> (sysfiles and sysfiles1 system table) and will point to the other company
> database C:\..\myDb.Ldf physical file...
> that log file will be pointed by 2 db and will immediately raises
exception
> marking 1 of the 2 db as suspect...
> to solve this problem I need the t-log of your db and the use of
> sp_attach_db indicating all the required physical files..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ken,
> It sounds as if as long as I do not try to attach a database onto a
> system that has the same database name I can use
> sp_attach_single_file_db with no adverse affects. Or, are there
> other issues that I should be concerned with?
should not... SQL Server will try to recreate the log file(s) based on the
original info... and if no "problem" is found it should be able to correctly
attach the db..

> Basically, without trying to go into too much detail here, our system
> has an automated process that periodically detaches databases,
> removes the ldf file and archives the mdf file. The system may
> re-attach any of these databases again at a later time depending on
> certain circumstances. We have been seeing some database corruption
> issues and have tried to determine if this process is the cause. We
> do know that occasionally the system is restarted due to power
> failures (although the UPS is doing an orderly shutdown) and we want
> to figure out if our detach and removal of LDF file process could be
> the cause of databases getting corrupt after we re-attach them during
> a restart from a power fail.
missing log files in recovery moment can be a problem... I'd archive the log
files as well, even in simple recovery model..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Question about Decision Trees and Neural Networks

I have some accounting data, with some transaction attributes and amounts.
I'm using Decision Trees to try and predict the next month's amount for certain combinations of attributes.

I've tried two different structures for the model:

A: one with 9 discrete text input attributes.
B: And another with the same 9 attributes + a avarage Amount for all combinations of the nine attribute for every transaction.

When i've processed them and look in the dependency network, it says that the strongest link for the structure A is attribute "1".
And for the second its the avarage-Amount attribute.
Okey, that seems fine, but the second strongest link in structure B is attribute "2".

Shouldn't it be attribute 1 like in structure A?

Second question, if I run the same data in a Neural Network model, the prediction becomes much worst then the decision tree.
I get many predictions that are negative values even though all training data contains positiv values.
The StDev becomes the same for every row also..
What am I doing wrong with that one. I have alot of transactions and a read somewhere that a Neural Network should work better than a decision tree in a case similar to mine.
The score in the "Lift chart" for the Neural Network model becomes 0,00 and for Decision Trees with the same data I get around 110.You might have a modeling issue here. If the individual attributes you have are transaction amounts, you should model them as continuous rather than discrete.

Question about comunication issues between Principal and Mirror

Hi there.

Imagine this scenario in High Availability mode :

The principal sends a transaction log to the mirror, then the mirror saves the log on disk and then notify the principal. What happen if the acknowledge is lost, because a network failure(back on track in 1 or 2 seconds) and the principal dont receive it ?

In this case an automatic failover will not occurr, and i dont really know what happen next...

Will the mirror resend the ack ?

The heartbeat mechanism plays an important role in solving this scenario ?

The principal must receive the ack to commit the transaction, and i′m not seeing how the problem is solved.

I have one other question. In "Database Mirroring in SQL Server 2005" document, is said that "...unlike a distributed transaction, failures to commit on the mirror will not cause a transaction rollback on the principal".

But in this case the mirror will be inconsistent with the principal. How is this problem solved ?

Thanks in advance.

RDSC wrote:

Imagine this scenario in High Availability mode :

The principal sends a transaction log to the mirror, then the mirror saves the log on disk and then notify the principal. What happen if the acknowledge is lost, because a network failure(back on track in 1 or 2 seconds) and the principal dont receive it ?

...

But in this case the mirror will be inconsistent with the principal. How is this problem solved ?

Hi there,

In high availability mode the principle will continue to serve SQL, and your mirror will be out of sync (the witness server is the key here, and an error state 6 (mirror lost) should be evident).

The inconsistency will be resolved when the mirror is successfully synchronized, depending on the cause of the problem, when the mirror is able to communicate with the witness again it should sync automatically, or you might be able to issue a ALTER DATABASE databasename SET PARTNER RESUME command to resync the mirror.

|||

Hi there, yesterday i saw some documentation about DBMirroring and suceeded in finding the answers about this issues.

That′s true what you say, thanks for the help.