rda submitsql: transaction

Showing posts with label transaction. Show all posts

Friday, March 30, 2012

RAID1 vs RAID5 for transaction log

I was hoping that someone could point me to some good documentation on
selecting an optimum RAID configuration. While performing heavy updates
during upgrades - I've noticed dramatic performance difference between RAID
1 and RAID 5 for the transaction log.
It seems that it is less important for the data files themselves (RAID 5
doesn't seem to hurt that much).
Does this make sense where the RAID Configuration for the T-Log is more
important than the Data?
If someone could shed some light on this or point me to some good
documantation - I would greatly appreciate it.
Thanks in advanceRaid 5 performs very poorly on writes as does hp/compaq's ADG (Advanced data
guarding).
If your database is mainly reads then raid 5 is ok for the datafiles.
Also consider that if a disk fails and the array is having to construct a
phantom disk on the fly then the performance of your system will probably
make it unusable.
It's worth pulling a disk on a system before it goes live and practice the
recovery.
Personally I use a raid 10 array of 4 disks for my log (and raid 10 arrays
for my data too). A mirror pair may do for your log.
Paul
"TJT" <TJT@.nospam.com> wrote in message
news:uDWYpug5FHA.1248@.TK2MSFTNGP14.phx.gbl...
>I was hoping that someone could point me to some good documentation on
> selecting an optimum RAID configuration. While performing heavy updates
> during upgrades - I've noticed dramatic performance difference between
> RAID
> 1 and RAID 5 for the transaction log.
> It seems that it is less important for the data files themselves (RAID 5
> doesn't seem to hurt that much).
> Does this make sense where the RAID Configuration for the T-Log is more
> important than the Data?
> If someone could shed some light on this or point me to some good
> documantation - I would greatly appreciate it.
> Thanks in advance
>|||This is a multi-part message in MIME format.
--060704050306020201010502
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I've always found this website to be a good RAID level overview (even
though it's a vendor website):
http://www.acnc.com/raid.html
It's not very detailed, just brief pros & cons and how the RAID level is
constructed, but it's good info nonetheless.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
TJT wrote:
>I was hoping that someone could point me to some good documentation on
>selecting an optimum RAID configuration. While performing heavy updates
>during upgrades - I've noticed dramatic performance difference between RAID
>1 and RAID 5 for the transaction log.
>It seems that it is less important for the data files themselves (RAID 5
>doesn't seem to hurt that much).
>Does this make sense where the RAID Configuration for the T-Log is more
>important than the Data?
>If someone could shed some light on this or point me to some good
>documantation - I would greatly appreciate it.
>Thanks in advance
>
>
--060704050306020201010502
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I've always found this website to be a good RAID level overview
(even though it's a vendor website): 
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.acnc.com/raid.html</a> ">http://www.acnc.com/raid.html">http://www.acnc.com/raid.html</a> 
 
It's not very detailed, just brief pros & cons and how the RAID
level is constructed, but it's good info nonetheless. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
TJT wrote:
<blockquote cite="miduDWYpug5FHA.1248@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">I was hoping that someone could point me to some good documentation on
selecting an optimum RAID configuration. While performing heavy updates
during upgrades - I've noticed dramatic performance difference between RAID
1 and RAID 5 for the transaction log.
It seems that it is less important for the data files themselves (RAID 5
doesn't seem to hurt that much).
Does this make sense where the RAID Configuration for the T-Log is more
important than the Data?
If someone could shed some light on this or point me to some good
documantation - I would greatly appreciate it.
Thanks in advance
</pre>
</blockquote>
</body>
</html>
--060704050306020201010502--|||RAID5 is terrible for heavily updated data, and the Transaction Log is the
prototypical worst case. Go for RAID 1. For database files/filegroups it
really depends on the update load. In general the feeling has been that
RAID 10 (aka 1+0) is better for databases since you get maximum performance
and availability. But if you have lightly updated tables then RAID 5 is
going to be OK.
If I remember correctly Kalen Delaney's "Inside SQL Server 2000" book has a
good discussion about this.
--
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:e6mn%23ll5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> I've always found this website to be a good RAID level overview (even
> though it's a vendor website):
> http://www.acnc.com/raid.html
> It's not very detailed, just brief pros & cons and how the RAID level is
> constructed, but it's good info nonetheless.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> TJT wrote:
>>I was hoping that someone could point me to some good documentation on
>>selecting an optimum RAID configuration. While performing heavy updates
>>during upgrades - I've noticed dramatic performance difference between
>>RAID
>>1 and RAID 5 for the transaction log.
>>It seems that it is less important for the data files themselves (RAID 5
>>doesn't seem to hurt that much).
>>Does this make sense where the RAID Configuration for the T-Log is more
>>important than the Data?
>>If someone could shed some light on this or point me to some good
>>documantation - I would greatly appreciate it.
>>Thanks in advance
>>
>>
>

RAID stripe size confusion

setting up a new ibm xserve server.
The SCSI serveRAID controller says to use 16kb stripe for generic
"transaction processing database" (not sql server specific). but the
IBM SQL server 2005 on xserve guide says to use 64kb stripes.
posts I have seen say to use scsi controller defaults unless a reason.
which should we choose?this is for RAID10 data drives.
THe redpaper 64kb setting up with a san which we do not have.
but 16k stripe size seems odd for sql server.sql

RAID stripe size confusion

Wednesday, March 28, 2012

RAID and Transaction Logs Involved in Replication

I began documenting recommended RAID configurations for our environment. Typically I encourage using RAID 1 for T-Logs since log writes are sequential. But then I started to wonder about how replication impacts T-Logs. According to BOL:

"The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database."

This being the case, doesn't that indicate the disk spindles will be repositioned when the Log Reader Agent reads the logs and copies the data to the Distribution database? If so, is RAID 1 still the best option?

Please let me know your thoughts.

Thanks, DaveThe impact of whatever RAID you use is very little as far as the efficency of transaction log, as long as you keep it simple and small enough. In a replication environment, transaction logs will not get dumped until the Log Reader loads the marked transactions onto the Distribution database. When you have large numbers of IOs, like over 35 millions commands within 5 hours, in our company. The Log Reader works extremely well in RAID 5. But the bottleneck is in Distribution database because all transactions get in there in a big hurry and cannot be distributed fast enough.

Wish this helps! Good luck.|||Someone in another forum pointed out that typically the Log Reader runs continuously. As a result the impact on the disk spindles should be small since only a couple of data pages are involved in the I/O. The only potential issue I see is when "Sync with Backup" is turned on. When on, the Log Reader Agent only sends T-Log data to the distribution database after a T-Log backup has been performed. In our environment this occurs every two hours. Depending upon the amount of transactions being processed, this has the potential to impact performance during the time the Log Reader is copying data from the T-Log and inserting it into the Distribution database. I suspect in most environments having the Log on a RAID 1, where the database is involved in replication, would not noticeably impact performance.

Thanks, Dave

Tuesday, March 20, 2012

Quick Transaction Question.

If in .NET I open a connection to my database, then use some sql text to start a transaction, then reuse that same open connection to call several stored procedures (using SqlCommand with CommandType.StoredProcedure), before ending the transaction. Will that run as a single transaction that can be rolled back? or are the stored procedure calls unable to roll-back after each one completes?

the stored procedures are themselves atomic and would need their own rollbacks. your use of transaction would work if all your statements were sql strings executing one after the other. -- jp

|||

Did a bit of research, and it appears there is a BeginTransaction method that can be used as part of the SqlConnection object. Which allowed me to start a transaction on a connection, then call serveral stored procedures using a SqlCommand object set to CommandType.StoredProcedure, and it all works as a single transaction, rolling everything back if any of then calls fail (internally or enternally). Here is a quick code snippet:

1protected void btnSave_Click(object sender, EventArgs e) {2//Create connection string for SQL query3 String strConnect;4 strConnect = WebConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;56//Generate call to stored procedure7 SqlConnection con =new SqlConnection(strConnect);8 SqlTransaction trans =null;910//Make Calls11string myNull =null;12try {13 con.Open();14 trans = con.BeginTransaction();15int ret1 = spCall(con, trans,"two");16int ret3 = spCall(con, trans, myNull);17int ret2 = spCall(con, trans,"three");18 trans.Commit();19 Master.Message.CssClass ="Text_Message";20 Master.Message.Text = ret1.ToString();21 Master.Message.Visible =true;22 }23catch (Exception sql) {24 Master.Message.CssClass ="Text_Error";25 Master.Message.Text = sql.Message;26 Master.Message.Visible =true;27if (null != trans) {28 trans.Rollback();29 }30 }31finally {32 con.Close();33 con.Dispose();34 }35 }3637protected int spCall(SqlConnection myConn, SqlTransaction myTrans,string myParam) {3839//Generate call to stored procedure40 SqlCommand storedProcCommand =new SqlCommand("spTest", myConn);41 storedProcCommand.CommandType = CommandType.StoredProcedure;4243//Build SQL parameter list44 storedProcCommand.Parameters.AddWithValue("@.value", myParam);4546//Return code47 SqlParameter retParam = storedProcCommand.Parameters.Add("@.ReturnValue", SqlDbType.Int);48 retParam.Direction = ParameterDirection.ReturnValue;4950//Bind to transaction51 storedProcCommand.Transaction = myTrans;5253//Run stored procedure54int retCode = 0;55 SqlDataReader Reader = storedProcCommand.ExecuteReader();56 retCode = (int)storedProcCommand.Parameters["@.ReturnValue"].Value;57 Reader.Close();5859return retCode;60 }

 1CREATE PROCEDURE [dbo].[spTest]2--Parameters3 @.valuevarchar(50) =null4AS56BEGIN78SET NOCOUNT ON;910INSERT INTO tbTest11 (12 [value]13 )14VALUES15 (16 @.value17 )1819RETURN@.@.Identity2021END
My first call inserts okay, the second call fails because nulls are not accepted by [value] in the table definition., and the third call never happens due to the exception thrown by the second call, which forced a rollback of the entire transaction, of which each stored procedure is a call of. Did a fair amount of testing and everything appears to be in order, if anyone notices anything I overlooked and or that could be problematic, please let me know.

quick transaction inside a stored procedure question

a transaction block inside a sproc

if after one of the tran statements, I bust out of the sproc with a return statement

do I have to rollback explicitly first or does the tran rollback automatically?

appreciate the tip

After returning to another procedure the @.@.TRANCOUNT should be still increased by 1. You can test that by printing out the @.@.trancount variables in both procedure. In common, I would always handle the transcation context on my own by settings explizit transaction heaviour.

HTH, jens Suessmeyer.|||thanks jens|||

Could you please rate the thread as solved or helpful or whatever, that its no longer present as unanswerd ?

Thanks, Jens.

Friday, March 9, 2012

Quick Question on SP and Transactions

Hello
Say if I have a Store Procedure that creates a named
transaction.
Can I have a different SP which commits or rollbacks it ?
ThanksYes, if the second SP was called by the first SP from within the same
transaction. In that case, it would all be in the same calling stack and
transaction space.
However, I generally don't like the idea of having transaction bounds cross
procedures like that. I find that it inevitably leads to transaction and
logic errors since programmers will get conufsed about how the transactions
interact with each other.
--
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:414c01c488fb$bda7dc00$a301280a@.phx.gbl...
> Hello
> Say if I have a Store Procedure that creates a named
> transaction.
> Can I have a different SP which commits or rollbacks it ?
> Thanks
>

Wednesday, March 7, 2012

Quick Question - Mirroring 'Spooler' device

Hi
what is it that actually holds and 'spools' the transaction changes to the
mirror server - the transaction log, or A.N. other?
the log buffer, consult
http://www.microsoft.com/technet/pro.../dbmirror.mspx for more
info.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:20A2075F-DA31-4164-9219-68674B47BC32@.microsoft.com...
> Hi
> what is it that actually holds and 'spools' the transaction changes to the
> mirror server - the transaction log, or A.N. other?

Saturday, February 25, 2012

Queued Transaction Failing

Let me put this in english (very long day).
The my myfile_0.sql is my pre-snapshot script that drops
and recreates the tables.

>--Original Message--
>Hello,
>Can anyone help me with a Queued Transaction thats
failing?
>I just set this up to do a snapshot and queued
>tranactional rep. The snapshot works great the queued
part
>brings the error (last command)
>\\IMYSERVER\Replication\unc\MyDirectory\200405271 60853
>\myfile_0.sql.
>I've checked out myfile_0.sql in QA and it works, and the
>snapshot deletes and recreates the tables (thats the
>script it running).
>Thanks for looking
>Rose
>.
>
Rose,
sorry but I'm still not too clear on what you want. Is the script actually
being propagated correctly but you don't want to drop the table at the
destination? In this case the behaviour that you have is controlled by the
@.pre_creation_cmd in sp_addarticle. In the GUI this is available on the
elipsis button next to the article (table). The default option is to drop
the table, but you also have the choice to leave it unchanged, truncate it
or remove selected rows.
HTH,
Paul Ibison
|||Firstly my apologies,
Looking at it again my remark 'let me put this in
english', was not nameed at you but me, occasionally I
have a habit of puting things in without proper proof
reading.
The Reason I delete the tables is thats what was
recommended by a white paper for transactional
replication, so I thought I would try it here.
Anyway I think I might of gotten to the bottom of it. The
database was a Transaction Replication database -
Immediate before this, and what I think is happening is
that it still thinks it is, so its not allowing me to
delete.
Anyway thanks Paul, and why aren't you a MVP ?
Rose

>--Original Message--
>Rose,
>sorry but I'm still not too clear on what you want. Is
the script actually
>being propagated correctly but you don't want to drop the
table at the
>destination? In this case the behaviour that you have is
controlled by the
>@.pre_creation_cmd in sp_addarticle. In the GUI this is
available on the
>elipsis button next to the article (table). The default
option is to drop
>the table, but you also have the choice to leave it
unchanged, truncate it
>or remove selected rows.
>HTH,
>Paul Ibison
>
>.
>
|||Rose,
you can use sp_removedbreplication on the subscriber before subscribing to
remove any traces of replication, or sp_MSunmarkreplinfo on the offending
table.
Thanks for your comment - MVP status would be extremely welcome but anyway
the way I look at it is that as I train the MS course on replication
(www.pygmalion.com) answering questions is still a good way of keeping on
top of things.
Cheers,
Paul

Queued Transaction Failing

Hello,
Can anyone help me with a Queued Transaction thats failing?
I just set this up to do a snapshot and queued
tranactional rep. The snapshot works great the queued part
brings the error (last command)
\\IMYSERVER\Replication\unc\MyDirectory\2004052716 0853
\myfile_0.sql.
I've checked out myfile_0.sql in QA and it works, and the
snapshot deletes and recreates the tables (thats the
script it running).
Thanks for looking
Rose
Rose,
part of your error message is missing - please can you post up the complete
one.
I'm assuming that the missing bit talks about not being able to read the
snapshot file and that this error is produced by the distribution agent. If
this is the case then can you check that the account being used by the
distribution agent (sql server agent) has read rights on the share you're
using.
Regards,
Paul Ibison
|||Thanks for you replay Paul,
Thats all the error message I can get out of it. I have
right clicked on the Subscription, looked in error detail
and that was all that appeared.
I don't think the problem is it can't read it, as I have
some Transactional Reps pointing to the same directory.
Could it be becasue of the table changes the Queued
replication is suppose to make ?

>--Original Message--
>Rose,
>part of your error message is missing - please can you
post up the complete
>one.
>I'm assuming that the missing bit talks about not being
able to read the
>snapshot file and that this error is produced by the
distribution agent. If
>this is the case then can you check that the account
being used by the
>distribution agent (sql server agent) has read rights on
the share you're
>using.
>Regards,
>Paul Ibison
>
>.
>

Monday, February 20, 2012

Queue Reader Agent Failed

Hi,
We have set replication between two servers.In subscriber if i update any
row, the transaction is in Msreplication_queue Table.But when it try to push
to publisher it throws a error called "Invalid cursor state". I don't know
where it is going wrong.
what could be the cause of this error.
Thanks in Advance,
Rajesh
Do you have sp4 installed? There was a bug prior to sp4 which gave this
message (http://support.microsoft.com/kb/831997/EN-US/).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi
Thanks, Currently we have installed SP3.We have to install it.
"Paul Ibison" wrote:

> Do you have sp4 installed? There was a bug prior to sp4 which gave this
> message (http://support.microsoft.com/kb/831997/EN-US/).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>

Queue not disabling

HI There

My activated proc is rolling back the transaction and putting the message abck on the queue infinately ?

Normally it disabled the queue after a few rollbacks, i can see in the sql log that it just keeps rolling back and re-activating thousands of times.

It only stops when i disable activation on the queue.

WHy is the queue not disabling ?

Thanx

Did you look at sys.service_queues to see if the queue is really disabled? Does profiler show the queue_disabled event?|||

Thanx Rushi

Thats what i thought just wanted to check Thanx

|||

Sorry that comment was on the wrong post, yes i check sys.service_queues to make sure it is enabled. ANd i check the sql log and can see the activated sp activating and rolling abck thousands of times until i disable actiavtion. I find it very strange it always used to disable becuase of the poison message, but no longer not sure why, i would not even know how to disable poison messages?

Thanx

Questions on simple recovery model

Hello. What is the rational behind not allowing a transaction log to be
backed up on a DB that is using the simple recovery model and how does
xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
mode?
As we know if left unchecked logs can become huge even in simple recovery
mode. They still need backups. For that matter if they're not deemed reliable
for point in time recovery why do they even exist? We run a reporting system
with most activity at night. We're happy to use differential backups to get
us back to yesterday. Why can't the simple model not use logs at all and not
make us deal with their growth spurts? TIA
Ken Trock
1) There is no benefit to backing up a tlog that is in simple mode because
nothing useful remains there.
2) Not sure what you are asking about xp_sqlmaint and single-user mode.
3) I wasn't aware that logs grow unchecked if not backed up if the database
is in simple recovery mode. Checkpoints flush committed transactions from
the tlog in that scenario, freeing space to be reused by new transactions.
4) Tlogs exist even in simple recovery mode because sql server is a
write-to-tlog-first engine and this cannot be disabled. No database
modification (insert, update, delete) can occur unless it is first written
to log.
5) I would like to see tlogging be able to be disabled, but not for the log
growth issue. Rather it would provide a fairly significant performance
increase.
TheSQLGuru
President
Indicium Resources, Inc.
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
> Hello. What is the rational behind not allowing a transaction log to be
> backed up on a DB that is using the simple recovery model and how does
> xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
> mode?
> As we know if left unchecked logs can become huge even in simple recovery
> mode. They still need backups. For that matter if they're not deemed
> reliable
> for point in time recovery why do they even exist? We run a reporting
> system
> with most activity at night. We're happy to use differential backups to
> get
> us back to yesterday. Why can't the simple model not use logs at all and
> not
> make us deal with their growth spurts? TIA
> Ken Trock
>
|||The transaction log exists for two reasons. It's primary purpose is to act
as a transaction integrity mechanism. The ltransaction log acts in a
write-ahead fashion. That is, the transaciton officially begins when the
first log entry is written. The relevant data is modified (either in memory
or in the data file) and then the transaction is committed by writing a
second log entry. Finally, a checkpoint process notes when committed
transactions are written to the data file. This allows for transactionally
consistent recovery even on an instantaneous failure of any type. SiMPLE
recovery alows the system to auto-truncate logs once a segment is
checkpointed. Without the separate log file, there would be no way to
guarantee transactional consistency on a hardware or system failure.
The other purpose is to allow for restore (different from recovery!) up to a
specific point in time or to a named transaction. This requires FULL
recovery so that log truncation only occurs after a segment is both
checkpointed and backed up.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
> Hello. What is the rational behind not allowing a transaction log to be
> backed up on a DB that is using the simple recovery model and how does
> xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
> mode?
> As we know if left unchecked logs can become huge even in simple recovery
> mode. They still need backups. For that matter if they're not deemed
> reliable
> for point in time recovery why do they even exist? We run a reporting
> system
> with most activity at night. We're happy to use differential backups to
> get
> us back to yesterday. Why can't the simple model not use logs at all and
> not
> make us deal with their growth spurts? TIA
> Ken Trock
>
|||Thanks for the quick response SQLGuru . On 3), we've indeed had our logs grow
large. Not sure where these checkpoints have been. Also several others here
have posted the same sizing issue. So we (try to) back them up only to keep
their size manageable (maybe that executes a checkpoint). In 2000 if you do
it thru DB maintenance plans in SEM and schedule it you'll notice that the
resultant job calls master.xp_sqlmaint extended proc which is a .dll. I
haven't been able to script a successful tlog backup under simple recovery
model.
Ken
"TheSQLGuru" wrote:

> 1) There is no benefit to backing up a tlog that is in simple mode because
> nothing useful remains there.
> 2) Not sure what you are asking about xp_sqlmaint and single-user mode.
> 3) I wasn't aware that logs grow unchecked if not backed up if the database
> is in simple recovery mode. Checkpoints flush committed transactions from
> the tlog in that scenario, freeing space to be reused by new transactions.
> 4) Tlogs exist even in simple recovery mode because sql server is a
> write-to-tlog-first engine and this cannot be disabled. No database
> modification (insert, update, delete) can occur unless it is first written
> to log.
> 5) I would like to see tlogging be able to be disabled, but not for the log
> growth issue. Rather it would provide a fairly significant performance
> increase.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
> news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
>
>
|||On Wed, 15 Aug 2007 09:34:01 -0700, ktrock
<ktrock@.discussions.microsoft.com> wrote:

>As we know if left unchecked logs can become huge even in simple recovery
>mode.
The log has to be at least large enough for an entire transaction. To
be more precise, it has to hold everything from the start of the
oldest transaction up to the present moment. So it really has to be
large enough for the longest running transaction plus all the other
transactions happening at the same time. If you run really big
transactions you will need a really big log, even in Simple recovery
mode.
In Simple recovery mode you can not back up the log, SQL Server will
not accept the command.
Roy Harvey
Beacon Falls, CT

Questions on simple recovery model

Hello. What is the rational behind not allowing a transaction log to be
backed up on a DB that is using the simple recovery model and how does
xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
mode?
As we know if left unchecked logs can become huge even in simple recovery
mode. They still need backups. For that matter if they're not deemed reliabl
e
for point in time recovery why do they even exist? We run a reporting system
with most activity at night. We're happy to use differential backups to get
us back to yesterday. Why can't the simple model not use logs at all and not
make us deal with their growth spurts? TIA
Ken Trock1) There is no benefit to backing up a tlog that is in simple mode because
nothing useful remains there.
2) Not sure what you are asking about xp_sqlmaint and single-user mode.
3) I wasn't aware that logs grow unchecked if not backed up if the database
is in simple recovery mode. Checkpoints flush committed transactions from
the tlog in that scenario, freeing space to be reused by new transactions.
4) Tlogs exist even in simple recovery mode because sql server is a
write-to-tlog-first engine and this cannot be disabled. No database
modification (insert, update, delete) can occur unless it is first written
to log.
5) I would like to see tlogging be able to be disabled, but not for the log
growth issue. Rather it would provide a fairly significant performance
increase.
TheSQLGuru
President
Indicium Resources, Inc.
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
> Hello. What is the rational behind not allowing a transaction log to be
> backed up on a DB that is using the simple recovery model and how does
> xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
> mode?
> As we know if left unchecked logs can become huge even in simple recovery
> mode. They still need backups. For that matter if they're not deemed
> reliable
> for point in time recovery why do they even exist? We run a reporting
> system
> with most activity at night. We're happy to use differential backups to
> get
> us back to yesterday. Why can't the simple model not use logs at all and
> not
> make us deal with their growth spurts? TIA
> Ken Trock
>|||The transaction log exists for two reasons. It's primary purpose is to act
as a transaction integrity mechanism. The ltransaction log acts in a
write-ahead fashion. That is, the transaciton officially begins when the
first log entry is written. The relevant data is modified (either in memory
or in the data file) and then the transaction is committed by writing a
second log entry. Finally, a checkpoint process notes when committed
transactions are written to the data file. This allows for transactionally
consistent recovery even on an instantaneous failure of any type. SiMPLE
recovery alows the system to auto-truncate logs once a segment is
checkpointed. Without the separate log file, there would be no way to
guarantee transactional consistency on a hardware or system failure.
The other purpose is to allow for restore (different from recovery!) up to a
specific point in time or to a named transaction. This requires FULL
recovery so that log truncation only occurs after a segment is both
checkpointed and backed up.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
> Hello. What is the rational behind not allowing a transaction log to be
> backed up on a DB that is using the simple recovery model and how does
> xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
> mode?
> As we know if left unchecked logs can become huge even in simple recovery
> mode. They still need backups. For that matter if they're not deemed
> reliable
> for point in time recovery why do they even exist? We run a reporting
> system
> with most activity at night. We're happy to use differential backups to
> get
> us back to yesterday. Why can't the simple model not use logs at all and
> not
> make us deal with their growth spurts? TIA
> Ken Trock
>|||Thanks for the quick response SQLGuru . On 3), we've indeed had our logs gro
w
large. Not sure where these checkpoints have been. Also several others here
have posted the same sizing issue. So we (try to) back them up only to keep
their size manageable (maybe that executes a checkpoint). In 2000 if you do
it thru DB maintenance plans in SEM and schedule it you'll notice that the
resultant job calls master.xp_sqlmaint extended proc which is a .dll. I
haven't been able to script a successful tlog backup under simple recovery
model.
Ken
"TheSQLGuru" wrote:

> 1) There is no benefit to backing up a tlog that is in simple mode because
> nothing useful remains there.
> 2) Not sure what you are asking about xp_sqlmaint and single-user mode.
> 3) I wasn't aware that logs grow unchecked if not backed up if the databas
e
> is in simple recovery mode. Checkpoints flush committed transactions from
> the tlog in that scenario, freeing space to be reused by new transactions.
> 4) Tlogs exist even in simple recovery mode because sql server is a
> write-to-tlog-first engine and this cannot be disabled. No database
> modification (insert, update, delete) can occur unless it is first written
> to log.
> 5) I would like to see tlogging be able to be disabled, but not for the lo
g
> growth issue. Rather it would provide a fairly significant performance
> increase.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
> news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
>
>|||On Wed, 15 Aug 2007 09:34:01 -0700, ktrock
<ktrock@.discussions.microsoft.com> wrote:

>As we know if left unchecked logs can become huge even in simple recovery
>mode.
The log has to be at least large enough for an entire transaction. To
be more precise, it has to hold everything from the start of the
oldest transaction up to the present moment. So it really has to be
large enough for the longest running transaction plus all the other
transactions happening at the same time. If you run really big
transactions you will need a really big log, even in Simple recovery
mode.
In Simple recovery mode you can not back up the log, SQL Server will
not accept the command.
Roy Harvey
Beacon Falls, CT

Questions on simple recovery model

Hello. What is the rational behind not allowing a transaction log to be
backed up on a DB that is using the simple recovery model and how does
xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
mode?
As we know if left unchecked logs can become huge even in simple recovery
mode. They still need backups. For that matter if they're not deemed reliable
for point in time recovery why do they even exist? We run a reporting system
with most activity at night. We're happy to use differential backups to get
us back to yesterday. Why can't the simple model not use logs at all and not
make us deal with their growth spurts? TIA
Ken Trock1) There is no benefit to backing up a tlog that is in simple mode because
nothing useful remains there.
2) Not sure what you are asking about xp_sqlmaint and single-user mode.
3) I wasn't aware that logs grow unchecked if not backed up if the database
is in simple recovery mode. Checkpoints flush committed transactions from
the tlog in that scenario, freeing space to be reused by new transactions.
4) Tlogs exist even in simple recovery mode because sql server is a
write-to-tlog-first engine and this cannot be disabled. No database
modification (insert, update, delete) can occur unless it is first written
to log.
5) I would like to see tlogging be able to be disabled, but not for the log
growth issue. Rather it would provide a fairly significant performance
increase.
TheSQLGuru
President
Indicium Resources, Inc.
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
> Hello. What is the rational behind not allowing a transaction log to be
> backed up on a DB that is using the simple recovery model and how does
> xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
> mode?
> As we know if left unchecked logs can become huge even in simple recovery
> mode. They still need backups. For that matter if they're not deemed
> reliable
> for point in time recovery why do they even exist? We run a reporting
> system
> with most activity at night. We're happy to use differential backups to
> get
> us back to yesterday. Why can't the simple model not use logs at all and
> not
> make us deal with their growth spurts? TIA
> Ken Trock
>|||Thanks for the quick response SQLGuru . On 3), we've indeed had our logs grow
large. Not sure where these checkpoints have been. Also several others here
have posted the same sizing issue. So we (try to) back them up only to keep
their size manageable (maybe that executes a checkpoint). In 2000 if you do
it thru DB maintenance plans in SEM and schedule it you'll notice that the
resultant job calls master.xp_sqlmaint extended proc which is a .dll. I
haven't been able to script a successful tlog backup under simple recovery
model.
Ken
"TheSQLGuru" wrote:
> 1) There is no benefit to backing up a tlog that is in simple mode because
> nothing useful remains there.
> 2) Not sure what you are asking about xp_sqlmaint and single-user mode.
> 3) I wasn't aware that logs grow unchecked if not backed up if the database
> is in simple recovery mode. Checkpoints flush committed transactions from
> the tlog in that scenario, freeing space to be reused by new transactions.
> 4) Tlogs exist even in simple recovery mode because sql server is a
> write-to-tlog-first engine and this cannot be disabled. No database
> modification (insert, update, delete) can occur unless it is first written
> to log.
> 5) I would like to see tlogging be able to be disabled, but not for the log
> growth issue. Rather it would provide a fairly significant performance
> increase.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
> news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
> > Hello. What is the rational behind not allowing a transaction log to be
> > backed up on a DB that is using the simple recovery model and how does
> > xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
> > mode?
> >
> > As we know if left unchecked logs can become huge even in simple recovery
> > mode. They still need backups. For that matter if they're not deemed
> > reliable
> > for point in time recovery why do they even exist? We run a reporting
> > system
> > with most activity at night. We're happy to use differential backups to
> > get
> > us back to yesterday. Why can't the simple model not use logs at all and
> > not
> > make us deal with their growth spurts? TIA
> >
> > Ken Trock
> >
> >
>
>|||The transaction log exists for two reasons. It's primary purpose is to act
as a transaction integrity mechanism. The ltransaction log acts in a
write-ahead fashion. That is, the transaciton officially begins when the
first log entry is written. The relevant data is modified (either in memory
or in the data file) and then the transaction is committed by writing a
second log entry. Finally, a checkpoint process notes when committed
transactions are written to the data file. This allows for transactionally
consistent recovery even on an instantaneous failure of any type. SiMPLE
recovery alows the system to auto-truncate logs once a segment is
checkpointed. Without the separate log file, there would be no way to
guarantee transactional consistency on a hardware or system failure.
The other purpose is to allow for restore (different from recovery!) up to a
specific point in time or to a named transaction. This requires FULL
recovery so that log truncation only occurs after a segment is both
checkpointed and backed up.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
> Hello. What is the rational behind not allowing a transaction log to be
> backed up on a DB that is using the simple recovery model and how does
> xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
> mode?
> As we know if left unchecked logs can become huge even in simple recovery
> mode. They still need backups. For that matter if they're not deemed
> reliable
> for point in time recovery why do they even exist? We run a reporting
> system
> with most activity at night. We're happy to use differential backups to
> get
> us back to yesterday. Why can't the simple model not use logs at all and
> not
> make us deal with their growth spurts? TIA
> Ken Trock
>|||On Wed, 15 Aug 2007 09:34:01 -0700, ktrock
<ktrock@.discussions.microsoft.com> wrote:
>As we know if left unchecked logs can become huge even in simple recovery
>mode.
The log has to be at least large enough for an entire transaction. To
be more precise, it has to hold everything from the start of the
oldest transaction up to the present moment. So it really has to be
large enough for the longest running transaction plus all the other
transactions happening at the same time. If you run really big
transactions you will need a really big log, even in Simple recovery
mode.
In Simple recovery mode you can not back up the log, SQL Server will
not accept the command.
Roy Harvey
Beacon Falls, CT

Friday, March 30, 2012

RAID1 vs RAID5 for transaction log

RAID1 vs RAID5 for transaction log

RAID1 vs RAID5 for transaction log

RAID stripe size confusion

RAID stripe size confusion

Wednesday, March 28, 2012

RAID and Transaction Logs Involved in Replication

Tuesday, March 20, 2012

Quick Transaction Question.

quick transaction inside a stored procedure question

Friday, March 9, 2012

Quick Question on SP and Transactions

Wednesday, March 7, 2012

Quick Question - Mirroring 'Spooler' device

Saturday, February 25, 2012

Queued Transaction Failing

Queued Transaction Failing

Monday, February 20, 2012

Queue Reader Agent Failed

Queue not disabling

Questions on simple recovery model

Questions on simple recovery model

Questions on simple recovery model

rda submitsql

Blog Archive

About Me