Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Avoiding Logging


Avoiding Logging

Author
Message
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47252 Visits: 44379
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.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4612 Visits: 9545
Transaction logging usually isn't an issue unless you're inserting 100,000+ records one.. at.. a.. time.. in a loop, or the DBA allows the transaction log to grow until it fills up available disk space. It's rarely an issue during the normal operation of an OLTP database. You can mitigate the negative performance effects of logging by placing the transaction log files on a seperate drive system, using the BULK COPY utility for your batch loads, and perhaps the Bulk Logged recovery model on occasion.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36124 Visits: 18744
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.


I had this argument with someone recently about loads. They were assuming that the load would fail and they'd restart it, but you need logic to allow restarts. Not everyone builds this into their processes and even if you don't have restart logic, the cleanup of old data needs to be transactional. So have you saved anything if we allowed imports w/o logging? Not sure, and honestly, not sure the vast majority of people are qualified to decide this.

Even if you are, is that the best decision for the company? The next person that does your job might not understand this type of feature and use it in other places.

If you don't need transactional logging during imports, then commit periodically, inserting batches of 10,000 or so, and running some log backups. If you're that busy and you have that much data, then I assume you ought to have some money to put log backups on separate spindles, and the t-log on separate spindles, and get better performance.

Ultimately, I don't think it's worth the risk to relational data to allow this. If the data isn't that important, or can be reloaded easily, perhaps it would be better to put this into some other structures. Maybe a NoSQL or columnar construct.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36124 Visits: 18744
Koen Verbeeck (3/8/2011)
taylor_benjamin (3/8/2011)

Thanks for reminding me how old I am, and how long I have been working with SQL Server. :-)


There was a 4.21 version??? :-P


There was a version that didn't run on Windows Wink

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
taylor_benjamin
taylor_benjamin
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 51
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.
taylor_benjamin
taylor_benjamin
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 51
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!
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36124 Visits: 18744
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16453 Visits: 13203
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?

:-D

I guess you're right. As ETL developer I only think about moving data, not so much about the features you just described...



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Donna Hawley-340938
Donna Hawley-340938
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 844
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47252 Visits: 44379
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?

:-D

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search