March 8, 2011 at 9:11 am
A little history about why one would ever even want no transactions...
Back in the old days when you spent $6k for a server with 32Meg (Yes Meg) of ram so you could have awesome performance, if you imported lots of data it really helped to turn off transaction logging, drop indexes, import data, rebuild statistics and rebuild indexes.
I worked on a data warehouse that imported a million health care claims monthly using a month-end extract. Using BCP to bring data into staging tables resulted in import of those million rows in 1 hour, where previously it took a whole week.
The need for this kind of tuning no longer exists in todays hardware and software capabilities. SSIS can do the kind of imports we did standing on it's head drinking coffee, smoking a non-politcally correct cigarette, reading a book and watching 2 movies.
So, how did we get around the possibilities of corruption?
1) Backup the whole database prior to any BCP activity, start over if it failed.
2) Use a staging database. If the import failed, we simply deleted the database and replaced it with a template. If we could we would break stuff down in batches and pick up where the last batch failed.
BCP was dangerous in those days. You were completely un-protected until it was complete and you performed a complete database backup. But with a data warehouse bringing in millions of rows, there were very few options outside of going to bigger hardware with another database vendor.
So, hats off to MS...look how far you've come baby!
March 8, 2011 at 10:14 am
taylor_benjamin (3/8/2011)
Wow! That really goes back. Yes, the first MS version of SQL Server was to take Sybase and port it to run on OS/2.I am not sure, but as I recall, version 4.21 was the first version to run under the Windows OS (Window NT Advanced Server was the branding as I recall).
Version 4.21a was the first version of SQL Server to break away from Sybase as a purely MS code base.
Actually, 4.20 came from Sybase and ran on OS/2, then was ported to Windows Advanced Server v3.1. I'm not sure is it was 4.21a or 4.21b that moved, but I went through all of them.
SQL Server 6.0/6.5 still contained some Sybase code as well. SQL Server 7.0 was a rewrite that was completely MS.
March 8, 2011 at 10:49 am
GilaMonster (3/8/2011)
Koen Verbeeck (3/8/2011)
If a data load fails, the destination table can be truncated and the load can start over again, so you would not have to worry about inconsistency.What would happen if the server failed at the point SQL was modifying the allocation constructs or system tables in the database and there was no logging? Not so easy to fix.
Logging is not just for the user data. It's for page allocations, allocation page modifications, system table modifications and a while lot more.
As BI developer I can only say the following thing:
Oink?
😀
I guess you're right. As ETL developer I only think about moving data, not so much about the features you just described...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 8, 2011 at 11:49 am
I thought I remembered a Version 1.0 of SQL Server and sure enough, there was. According to Wikipedia, Version 1.0 was released in 1989 as an OS/2 version on 16-bit. Version 1.1 was released in 1991, again on OS/2. Then in 1993 was the first Windows NT edition which was SQL Server 4.21. http://en.wikipedia.org/wiki/Microsoft_SQL_Server
March 8, 2011 at 1:12 pm
Koen Verbeeck (3/8/2011)
GilaMonster (3/8/2011)
Koen Verbeeck (3/8/2011)
If a data load fails, the destination table can be truncated and the load can start over again, so you would not have to worry about inconsistency.What would happen if the server failed at the point SQL was modifying the allocation constructs or system tables in the database and there was no logging? Not so easy to fix.
Logging is not just for the user data. It's for page allocations, allocation page modifications, system table modifications and a while lot more.
As BI developer I can only say the following thing:
Oink?
😀
I guess you're right. As ETL developer I only think about moving data, not so much about the features you just described...
Exactly. I suspect most people asking for no logging don't think about the system/structure changes that get logged. If a load were to fail part way through, it would not be a case just of truncating the destination table and starting again.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2011 at 2:10 pm
Those of us who were developing applications in FoxPro, MS Access, or some other ISAM platform back in the 80's and early 90's know the perils of a transactionally inconsistent database. When users hit the power button on their PC before going home, tables would be updated but not the indexes, meaning that records would mysteriously disappear and later reappear again after re-indexing. If the network went down, table headers would get corrupted and unusable, etc. ad nauseum.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 8, 2011 at 2:31 pm
AH! I remember the days, but I was using DBase III+, Alpha4 and Clipper.
A colleague of mine showed me his first SELECT statement in SQL 4.2 (SQL NT) and I totally didn't 'get it'. This was back in the early 90's and I failed to grasp what a 'set' was. I'm glad he was excited. It lead me to investigate further a few years later. My first SELECT was in 6.0.
The product has come a long way. In fact, much of my knowledge is still from SQL7 and 2000 (of which, I STILL have some (nameless) clients on both!). I have few clients who migrated all the way to 2008/R2 - so I'm going through the learning process now.
At any rate, you brought back some horrifying memories which I spent 20 years trying to mentally block, thank you very much. Now, I won't be able to sleep tonight.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 8, 2011 at 2:41 pm
[Jim].[dba].[Murphy] (3/8/2011)
AH! I remember the days, but I was using DBase III+, Alpha4 and Clipper.
dBase 4 was awesome with SQL. They couldnt talk to each other (or if they could, I couldnt work it out) and so at the time I was like "Why the hell would I bother with SQL?"
O' how times have changed 😛
March 9, 2011 at 9:37 am
It could be helpfull to avoid logging in scenario where you want to "UPGRADE" the data in a BD with numerous conversions script:
1- You take a full backup of the database
2- You run all the conversion scrips (*) without logging
* If an error occurs during the "UPGRADE", you correct the script, restore the backup en replay the conversion scripts.
This can dramatically lower the time it take to "UPGRADE" the data (once all error have been corrected)!
Regards.
Carl
March 9, 2011 at 11:00 pm
Lynn Pettis (3/8/2011)
Actually, it is possible to do this in Oracle. I had a nice discussion about it with one of the Oracle gurus where I work. He would never recommend using this capability on an online database, but would use it in a tightly controlled batch process where online activity is prevented from accessing the database. He would also take precautions, including ensuring that there was a backup prior to and after the batch process.Perhaps it is because this can be done in Oracle that people think SQL Server has a similar capability.
I am glad there isn't this feature in SQL Server unlike Oracle. It's good to see all of the precaution by a seasoned DBA. However, I think there would be a higher probability of the DBA not being seasoned or taking all of those precautions in SQL server (we know there are many accidental dba's already in SQL Server). Having that feature would be too risky if you ask me.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 10, 2011 at 10:18 am
It might help in upgrades, but I don't think the risk of the feature being used elsewhere is worth implementing it.
March 10, 2011 at 10:51 am
Carl B. (3/9/2011)
It could be helpfull to avoid logging in scenario where you want to "UPGRADE" the data in a BD with numerous conversions script:1- You take a full backup of the database
2- You run all the conversion scrips (*) without logging
* If an error occurs during the "UPGRADE", you correct the script, restore the backup en replay the conversion scripts.
This can dramatically lower the time it take to "UPGRADE" the data (once all error have been corrected)!
Regards.
Carl
If you have a scenario where you need to update millions of rows, and don't care about ROLLBACK capability for the entire process, then try updating the table in batches of 10,000 rows at a time while checkpointing and truncating the transaction log between updates.
For example:
update MyBigTable set ... where MyID >= 0 and MyID < 100000;
checkpoint;
backup log MyBigDatabase with truncate_only;
dbcc shrinkfile ( MyBigDatabase_Log, 1);
update MyBigTable set ... where MyID >= 100000 and MyID < 200000;
checkpoint;
backup log MyBigDatabase with truncate_only;
dbcc shrinkfile ( MyBigDatabase_Log, 1);
. . .
. . .
Of course do your full backup before. Also, this would be more appropriate in a datawarehouse that get periodically reloaded than in an online transaction database.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 10, 2011 at 11:19 am
mmmm. Not sure about that. A log truncation could have bad side effects if any part of that database is used by others; rows being modified in some way. Can possibly loose recoverability.
And shrinking the log is not a great idea in most cases; lots of side effects from physical file fragmentation on disk to VLF's ever growing in number.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 10, 2011 at 11:37 am
Eric M Russell (3/10/2011)
If you have a scenario where you need to update millions of rows, and don't care about ROLLBACK capability for the entire process, then try updating the table in batches of 10,000 rows at a time while checkpointing and truncating the transaction log between updates.For example:
update MyBigTable set ... where MyID >= 0 and MyID < 100000;
checkpoint;
backup log MyBigDatabase with truncate_only;
dbcc shrinkfile ( MyBigDatabase_Log, 1);
update MyBigTable set ... where MyID >= 100000 and MyID < 200000;
checkpoint;
backup log MyBigDatabase with truncate_only;
dbcc shrinkfile ( MyBigDatabase_Log, 1);
. . .
. . .
Of course do your full backup before. Also, this would be more appropriate in a datawarehouse that get periodically reloaded than in an online transaction database.
If you want to do that, just set the DB into simple recovery. Also, shrinking the log each step is highly counter-productive. The next update will cause it to grow immediately, that will just slow the whole process down, not to mention the file-level fragmentation.
If you really don't care about recoverability, set DB to simple, do update. Then decide whether or not to shrink the log and change the recovery model back to full/bulk-logged (and backup if you do)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 10, 2011 at 11:40 am
[Jim].[dba].[Murphy] (3/10/2011)
A log truncation could have bad side effects if any part of that database is used by others; rows being modified in some way.
A log truncation runs each time the log is backed up in full/bulk logged and at each checkpoint in simple. It doesn't break the DB, it has no consistency effects. It just breaks the log chain (in full/bulk-logged)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply