SQL Server Rant
-
Paul Watson wrote: Error: Transaction log full, clear log Error: Cannot clear log, transaction log full LOL! Excellent! Marc Latest AAL Article My blog Join my forum!
When you get that you are really in a pickle. Sql Server using a "sliding window" log file where a log file is broken up into multiple log segments. When a transaction is started it is marked in the current log segment as as the commits are written to the log, the active part of the log moves. If you don't backup the datbase and truncate the log the log will continue to grow until it reaches the maximum configured size, or it runs out of disk space. Commits can still continue to be made to the datbase until there is no more room left in the log. This is when you get the error message. Even when you try to clear the log, some information needs to be written to the log to signify that a checkpoint has occurred. If there isn't enough room to write this very small change (1K or so) then you are really out of log space. You can still recover from this scenario but requires that you turn on some advanced options to force the transaction to be dumped. The command is DUMP TRAN with NO LOG. You should be very careful with this command and is probably better to have a call in to MS to help you just incase. Mark, if you are doing backups and you don't want to keep transaction logs just turn on the truncate log on checkpoint option and as checkpoints occur in the system, the log is automatically truncated. The "disadvantage" to this approach is that your recoverablity is only as recent as your last backup. So if you are doing nightly backups you can loose up to a full days worth of data. If this is too long, you can configure it to do more frequent database backups, or turn on transaction log backups and backup the log with the diffs from the nightly database backup.
-
Jeremy Falcon wrote: Out of curiosity, what was the cause of the problem/fix? My initial guess was the transaction file was larger than the set limit. Well, it's sort of reading the recount of how they're resurrecting the Spirit MER: On Marc Day 1, he discovered the log file was huge. After researching the internet, he discovers from the Microsoft site that this works: BACKUP LOG pubs WITH TRUNCATE_ONLY DBCC SHRINKFILE(pubs_log, 2) On Marc Day 2, the client calls saying that the system is down and complaining about not enough space in the log file (sort of like Spirit having too many files in its memory). Marc checks the "grow log file" and all is well. On Marc Day 3, he realizes that this will simply recreate the problem all over again, and logs back into the client's system and tells SQL Server to backup the transaction logs as well, and to automatically shrink the log file. Now all is well with the world. So, in future installations (we're using MSDE for production versions), I need to make sure: 1. the DB can grow 2. the DB is backed up 3. the log can grow 4. the log is backed up and auto-shrunk 5. the backup plan makes sense 6. the backups do not themselves grow infinitely! 7. additional maintenance plans, such as shrinking the DB, are being studied. Now I just have to figure out how to automate all this as part of the MSDE installation process. Marc Latest AAL Article My blog Join my forum!
Search books online for
sp_dboptionandBACKUP, BACKUP (described). Jeremy Falcon -
Search books online for
sp_dboptionandBACKUP, BACKUP (described). Jeremy FalconJeremy Falcon wrote: Search books online for sp_dboption and BACKUP, BACKUP (described). :-D You the man! Thanks! Marc Latest AAL Article My blog Join my forum!
-
Michael A. Barnhart wrote: Sounds like a great start for a new article. Lessons learned --> SQL server for programmers. Yeah. I certainly learned my lesson. When adopting a new technology (we've been using an Access DB for years on this product and never had these kind of problems, of course we had other problems), anyways, when adopting a new technology, search for "Lessons Learned" on the Internet first! Marc Latest AAL Article My blog Join my forum!
Kind of understandable the fix you found yourself in. Access doesn't support the Durability properties of ACID based databases. The Durability part deals specifically with transaction logs so you never had to worry about this with Access. But without Durability, a whole lot more problems can occur and easily lead to lossed data and/or data corruption. I made a post above that if you don't want to worry about transaction logs you can turn on the database option Truncate Log on Checkpoint and just deal with database backups.
-
[rant] OK, I'll face it. I don't know what I'm doing. Maybe that's the real problem. Installed SQL Server at a client beta test site (thank goodness for that!), a couple weeks go by, and I discover that the log file has eaten up about 15 GIGABYTES of disk space. Was I doing backups? Of course! Was I doing backups of the TRANSACTION LOG? No. I didn't know I needed to. Backed up the database and reset the log file to 2MB, then set the log file backup schedule too. Customer calls this morning at 3AM (this is a live beta test site). They can't record anymore transactions because the log file is full. What? There it is, at 2MB. So, the log file that was growing astronomically, is now NOT growing. WTF? The "grow log file" checkbox is UNCHECKED! I didn't uncheck it! So how was it growing to begin with? And why, if I set a hard limit for the transaction log file, doesn't it just start dumping old transactions? What's the point of a hard limit if it throws its hands up in the air and gives up when it gets full? This product should CLEARLY WARN YOU of certain configurations that will lead to DISASTER. It should install with a configuration that DOES NOT CAUSE DISASTER weeks or months down the road. Got that, Microsoft? (I know, I know, it's my fault, I'm the one to blame for not knowing what I was doing.) Now I guess I'll read about how to properly install and configure SQL Server. I guess this is why DB admins can charge $150/hr. And while I'm in a ranting mood, why is the backup and restore procedure so complicated? I mean, I have to go through 5 levels (or so) of "advanced this", "advanced that" just to specify the friggin' file that I want restored!!! Some of you (yes, you know who you are) have said that the Enterprise Manager is a great tool. I think it's terrible. It's slow, awkward, and not user friendly. In fact, after working with Vale's MSDE Manager[^], I'm beginning to like it A LOT MORE than EM (except for that annoying licensing process). Maybe I'm just weak-willed. Maybe I'm not a "power user" and I just want a simple GUI to get a simple job done, like restoring a database to a different physical file without going through 10 dialogs. Maybe EM is too complicated for my simple mind. Maybe I need to raise my rates. [/rant] Marc Latest AAL Article
I'm no SQL "expert" by any means, but I have gotten over some of those hurdles and everything you say is 100% correct. The user tools and in particular Enterprise Manager are overly confusing and poorly written. It's a product that never went beyond that stage, you know the one, where everything is an option so people can tweak and play with it. Despite the fact that it could be made at least 90% more automatic with no loss of efficiencey they choose not to. I can only imagine because there would be an uproar from DB administrators paid to figure all that annoying and seldom used crap out. And don't get me started on backup and recovery, have you actually recovered yet? It's a painful process made more painful by the archaic terminology used in the user interface, everything is counter-intuitive and badly designed. If it didn't say Microsoft on it I would swear someone else completely different wrote that huge reeking pile of ...@#$@#$@Q$%
SPCA--we're here to inquire about the health of Dr. Schroedinger's cat -
Jeremy Falcon wrote: I was one of them, and I stand behind it. Also, after look at the screenshots to Vale's stuff - it's just a copy of EM and Query Analyzer. Take time to learn your tools Marc. lol. I guess you sort of missed the point of the rant. But at least now I know how to disguise programming questions in the lounge, eh? I think I do know how to use the tools, in the sense of working with the tools. I've been using EM for a long time, and I like Vale's product better. On the other hand, what I am slowly learning (and was admittedly ignorant of) was the complexity of getting everything configured "just right". Tools are great. It's great having the power and control that EM provides. But what the tool doesn't do is tell you how you should use it. As thefor the beta-test site, they're all set up and happy now. Marc Latest AAL Article My blog Join my forum!
Marc Clifton wrote: Tools are great. It's great having the power and control that EM provides. But what the tool doesn't do is tell you how you should use it. Noooo! Don't you dare back down on this! You were right the first time, it is excessively, overly complex for a modern software product.
SPCA--we're here to inquire about the health of Dr. Schroedinger's cat -
I'm no SQL "expert" by any means, but I have gotten over some of those hurdles and everything you say is 100% correct. The user tools and in particular Enterprise Manager are overly confusing and poorly written. It's a product that never went beyond that stage, you know the one, where everything is an option so people can tweak and play with it. Despite the fact that it could be made at least 90% more automatic with no loss of efficiencey they choose not to. I can only imagine because there would be an uproar from DB administrators paid to figure all that annoying and seldom used crap out. And don't get me started on backup and recovery, have you actually recovered yet? It's a painful process made more painful by the archaic terminology used in the user interface, everything is counter-intuitive and badly designed. If it didn't say Microsoft on it I would swear someone else completely different wrote that huge reeking pile of ...@#$@#$@Q$%
SPCA--we're here to inquire about the health of Dr. Schroedinger's catTo satisfy the programmer in you, you could always enter: backup database to disk='c:\backups\mydatabase.dmp' I don't think you give SEM enough credit. Ever had to change a column in the table? Or move a column within a table. To make this happen a lot of SQL has to occur from SEM to SQL Server to accomplish this. To see for yourself, use Profiler sometimes and perform a trace on all the actions that SEM does for you for these "overly confusing" options. Another thing is that SQL Server does an incredible amount of self tuning especially with the memory manager. As load increases on the database, SQL Server will grow it's memory pool to keep up with the demand. if it detects that the server is starting to page (over committed with memory), SQL Server will start to release memory from it's memory pool until it gets to a happy medium of lots of memory for database operations but not enough to cause paging. While there is always room for improvement in a product, you might see what SEM does for you behind the scene sometimes. If all these options really annoy you, you could always use TSQL to do your administration. :)
-
Marc Clifton wrote: Tools are great. It's great having the power and control that EM provides. But what the tool doesn't do is tell you how you should use it. Noooo! Don't you dare back down on this! You were right the first time, it is excessively, overly complex for a modern software product.
SPCA--we're here to inquire about the health of Dr. Schroedinger's catJohn Cardinal wrote: You were right the first time, it is excessively, overly complex for a modern software product. Sorry, I was saying that with a level of sarcasm. I should have used [sarcasm] blocks. :-D Marc Latest AAL Article My blog Join my forum!
-
To satisfy the programmer in you, you could always enter: backup database to disk='c:\backups\mydatabase.dmp' I don't think you give SEM enough credit. Ever had to change a column in the table? Or move a column within a table. To make this happen a lot of SQL has to occur from SEM to SQL Server to accomplish this. To see for yourself, use Profiler sometimes and perform a trace on all the actions that SEM does for you for these "overly confusing" options. Another thing is that SQL Server does an incredible amount of self tuning especially with the memory manager. As load increases on the database, SQL Server will grow it's memory pool to keep up with the demand. if it detects that the server is starting to page (over committed with memory), SQL Server will start to release memory from it's memory pool until it gets to a happy medium of lots of memory for database operations but not enough to cause paging. While there is always room for improvement in a product, you might see what SEM does for you behind the scene sometimes. If all these options really annoy you, you could always use TSQL to do your administration. :)
Oh, I absolutely do know what is going on behind the scenes Terry. I've used T-SQL for years and years. Sure it's easy to go from building your Model T to buying one off the lot and thinking to yourself "Gee, all I have to do is turn this crank and release this hand brake and away I go". But surely there comes a day when that Model T becomes a Honda Civic? ;)
SPCA--we're here to inquire about the health of Dr. Schroedinger's cat -
Marc Clifton wrote: But what the tool doesn't do is tell you how you should use it. Which professional tools does? Windows admin tools dont either, to get those tools you need to install Small Business Server, there you will find tools that guide you and tell you when stuff is wrong. C++ lets you do all the dangerous stuff, VB dont. EM does, bot it looks like Vale's product dont (I have never tried it, just read your posts). So... You are using the handholding VB of SQL-Server admins :P - Anders Money talks, but all mine ever says is "Goodbye!" My Photos[^]
Anders Molin wrote: C++ lets you do all the dangerous stuff, The compiler, as a tool, tries its hardest to protect you from doing stupid things though. If I say: if (a=b) ... I get a warning about an assignment. if I say: double d=5.0; int a=d; I get a warning about precision loss. If I say: double d; int a=d; I get a warning about an unassigned variable being used. That's what I mean about the tool doing something HELPFUL! An this isn't just naive programmer problems--these warnings exist because people make mistakes and typos. All I'm saying that EM could go a lot further in providing warnings, to use your analogy, like the way the C++ compiler does. Anders Molin wrote: So... You are using the handholding VB of SQL-Server admins The functionality is almost identical, in a much better designed application. And something that SQL Server is STILL missing, after all these years, is the SEQUENCE feature, that Oracle has. Not having that ability in SQL Server can be very annoying at times. Marc Latest AAL Article My blog Join my forum!
-
I'm no SQL "expert" by any means, but I have gotten over some of those hurdles and everything you say is 100% correct. The user tools and in particular Enterprise Manager are overly confusing and poorly written. It's a product that never went beyond that stage, you know the one, where everything is an option so people can tweak and play with it. Despite the fact that it could be made at least 90% more automatic with no loss of efficiencey they choose not to. I can only imagine because there would be an uproar from DB administrators paid to figure all that annoying and seldom used crap out. And don't get me started on backup and recovery, have you actually recovered yet? It's a painful process made more painful by the archaic terminology used in the user interface, everything is counter-intuitive and badly designed. If it didn't say Microsoft on it I would swear someone else completely different wrote that huge reeking pile of ...@#$@#$@Q$%
SPCA--we're here to inquire about the health of Dr. Schroedinger's catJohn Cardinal wrote: And don't get me started on backup and recovery, have you actually recovered yet? Yes, I have. Awful. Marc Latest AAL Article My blog Join my forum!
-
Marc Clifton wrote: Tools are great. It's great having the power and control that EM provides. But what the tool doesn't do is tell you how you should use it. Noooo! Don't you dare back down on this! You were right the first time, it is excessively, overly complex for a modern software product.
SPCA--we're here to inquire about the health of Dr. Schroedinger's catJohn Cardinal wrote: You were right the first time, it is excessively, overly complex for a modern software product. While I grant you that it could be improved for the smaller databases, having just finished a 2 year project dealing with 1500 tables, 400K lines of C++ code and 800 GB of storage I want the control to be able to tune the product to the nth degree. What holds true for a 10MB database doesn't hold true for a 1TB database. But just talking about the tool, I've gotten to the point where I don't use SEM because I know the specific information I want and don't need or want all that GUI getting in the way of the information I seek.
-
Oh, I absolutely do know what is going on behind the scenes Terry. I've used T-SQL for years and years. Sure it's easy to go from building your Model T to buying one off the lot and thinking to yourself "Gee, all I have to do is turn this crank and release this hand brake and away I go". But surely there comes a day when that Model T becomes a Honda Civic? ;)
SPCA--we're here to inquire about the health of Dr. Schroedinger's catFunny you should mention building a Model T because I'm building one in my shop. :D Making a streetrod out of a 1926 Model T Tudor Sedan.
-
Anders Molin wrote: C++ lets you do all the dangerous stuff, The compiler, as a tool, tries its hardest to protect you from doing stupid things though. If I say: if (a=b) ... I get a warning about an assignment. if I say: double d=5.0; int a=d; I get a warning about precision loss. If I say: double d; int a=d; I get a warning about an unassigned variable being used. That's what I mean about the tool doing something HELPFUL! An this isn't just naive programmer problems--these warnings exist because people make mistakes and typos. All I'm saying that EM could go a lot further in providing warnings, to use your analogy, like the way the C++ compiler does. Anders Molin wrote: So... You are using the handholding VB of SQL-Server admins The functionality is almost identical, in a much better designed application. And something that SQL Server is STILL missing, after all these years, is the SEQUENCE feature, that Oracle has. Not having that ability in SQL Server can be very annoying at times. Marc Latest AAL Article My blog Join my forum!
Well Marc, Some programmer I know told me he spent 2 days debugging a
for (int i = 0; i < 20; i++);
Thats the kind of stuff I mean. Or something like this
int i[10]
//fill some values into i here
int *c = i;
for (int k = 0; k < 20; k++)
{
SomeFunction(*(c++));
}Compiles fine, and works fine, but suddenly, bang, application crashed because of a overwrite. Thats the kind of things I was talking about... - Anders Money talks, but all mine ever says is "Goodbye!" My Photos[^]
-
Funny you should mention building a Model T because I'm building one in my shop. :D Making a streetrod out of a 1926 Model T Tudor Sedan.
-
Michael A. Barnhart wrote: Sounds like a great start for a new article. Lessons learned --> SQL server for programmers. Yeah. I certainly learned my lesson. When adopting a new technology (we've been using an Access DB for years on this product and never had these kind of problems, of course we had other problems), anyways, when adopting a new technology, search for "Lessons Learned" on the Internet first! Marc Latest AAL Article My blog Join my forum!
Marc Clifton wrote: when adopting a new technology, search for "Lessons Learned" on the Internet first! Or take a quick course on SQL Administration or possibly browse a book on the topic ;) I think when people move to SQL Server they have been working with MS Access or some form of ODBC and find that things are much different in the server world. The first time you boot up Enterprise manager looking for your forms or query tabs and wonder how to get to the data ;) And I think that log size limit problem has hit about everyone. You automatically take it that setting the size means it will compress and not go over that amount, not that it is going to fill your event log with the fact the db log is now over the limit! Other things are ahead of you :-D A couple quick resources: http://www.sqlmag.com/[^] http://sqlservercentral.com[^] There is one more that I use more often but the url escapes me at the moment :-O Rocky <>< www.HintsAndTips.com
-
[rant] OK, I'll face it. I don't know what I'm doing. Maybe that's the real problem. Installed SQL Server at a client beta test site (thank goodness for that!), a couple weeks go by, and I discover that the log file has eaten up about 15 GIGABYTES of disk space. Was I doing backups? Of course! Was I doing backups of the TRANSACTION LOG? No. I didn't know I needed to. Backed up the database and reset the log file to 2MB, then set the log file backup schedule too. Customer calls this morning at 3AM (this is a live beta test site). They can't record anymore transactions because the log file is full. What? There it is, at 2MB. So, the log file that was growing astronomically, is now NOT growing. WTF? The "grow log file" checkbox is UNCHECKED! I didn't uncheck it! So how was it growing to begin with? And why, if I set a hard limit for the transaction log file, doesn't it just start dumping old transactions? What's the point of a hard limit if it throws its hands up in the air and gives up when it gets full? This product should CLEARLY WARN YOU of certain configurations that will lead to DISASTER. It should install with a configuration that DOES NOT CAUSE DISASTER weeks or months down the road. Got that, Microsoft? (I know, I know, it's my fault, I'm the one to blame for not knowing what I was doing.) Now I guess I'll read about how to properly install and configure SQL Server. I guess this is why DB admins can charge $150/hr. And while I'm in a ranting mood, why is the backup and restore procedure so complicated? I mean, I have to go through 5 levels (or so) of "advanced this", "advanced that" just to specify the friggin' file that I want restored!!! Some of you (yes, you know who you are) have said that the Enterprise Manager is a great tool. I think it's terrible. It's slow, awkward, and not user friendly. In fact, after working with Vale's MSDE Manager[^], I'm beginning to like it A LOT MORE than EM (except for that annoying licensing process). Maybe I'm just weak-willed. Maybe I'm not a "power user" and I just want a simple GUI to get a simple job done, like restoring a database to a different physical file without going through 10 dialogs. Maybe EM is too complicated for my simple mind. Maybe I need to raise my rates. [/rant] Marc Latest AAL Article
Well, as far as mainstream database go, SQL Server is still easier to get started with from scratch than DB2 or Oracle. I used one of the test boxes at work, which I knew SQL Server worked on happily, to do some trials with. I got DB2 to install, but I was buggered if I could figure out stored procedures at all. I think I needed to install a C compiler, and then do some voodoo. Oracle (9i) installed too, but I creating a database seemed to take a while. So I left it overnight. It had crashed by the following morning, having reached about 30%..... And after the shoddy, slow, crap (and Java) admin tools you get shipped with both of those databases, you'll welcome Enterprise Manager with open arms :-) -- Ian Darling "The different versions of the UN*X brand operating system are numbered in a logical sequence: 5, 6, 7, 2, 2.9, 3, 4.0, III, 4.1, V, 4.2, V.2, and 4.3" - Alan Filipski
-
Well, as far as mainstream database go, SQL Server is still easier to get started with from scratch than DB2 or Oracle. I used one of the test boxes at work, which I knew SQL Server worked on happily, to do some trials with. I got DB2 to install, but I was buggered if I could figure out stored procedures at all. I think I needed to install a C compiler, and then do some voodoo. Oracle (9i) installed too, but I creating a database seemed to take a while. So I left it overnight. It had crashed by the following morning, having reached about 30%..... And after the shoddy, slow, crap (and Java) admin tools you get shipped with both of those databases, you'll welcome Enterprise Manager with open arms :-) -- Ian Darling "The different versions of the UN*X brand operating system are numbered in a logical sequence: 5, 6, 7, 2, 2.9, 3, 4.0, III, 4.1, V, 4.2, V.2, and 4.3" - Alan Filipski
Ian Darling wrote: Oracle (9i) installed too, but I creating a database seemed to take a while. Yeah, Oracle really lost it with version 9. 7i and 8i were great. Then they turned Java. Ugh. Ian Darling wrote: And after the shoddy, slow, crap (and Java) admin tools you get shipped with both of those databases, you'll welcome Enterprise Manager with open arms True. I guess I should count my blessings, and while I'm at it, not criticize the mote in someone else's eye when I've got a plank in my own. Ah well. I guess I was in a ranting mood. Still, I feel like MS could really have made the EM shine. It's like the adage about "first impressions". It doesn't make a good first impression. Marc Latest AAL Article My blog Join my forum!
-
[rant] OK, I'll face it. I don't know what I'm doing. Maybe that's the real problem. Installed SQL Server at a client beta test site (thank goodness for that!), a couple weeks go by, and I discover that the log file has eaten up about 15 GIGABYTES of disk space. Was I doing backups? Of course! Was I doing backups of the TRANSACTION LOG? No. I didn't know I needed to. Backed up the database and reset the log file to 2MB, then set the log file backup schedule too. Customer calls this morning at 3AM (this is a live beta test site). They can't record anymore transactions because the log file is full. What? There it is, at 2MB. So, the log file that was growing astronomically, is now NOT growing. WTF? The "grow log file" checkbox is UNCHECKED! I didn't uncheck it! So how was it growing to begin with? And why, if I set a hard limit for the transaction log file, doesn't it just start dumping old transactions? What's the point of a hard limit if it throws its hands up in the air and gives up when it gets full? This product should CLEARLY WARN YOU of certain configurations that will lead to DISASTER. It should install with a configuration that DOES NOT CAUSE DISASTER weeks or months down the road. Got that, Microsoft? (I know, I know, it's my fault, I'm the one to blame for not knowing what I was doing.) Now I guess I'll read about how to properly install and configure SQL Server. I guess this is why DB admins can charge $150/hr. And while I'm in a ranting mood, why is the backup and restore procedure so complicated? I mean, I have to go through 5 levels (or so) of "advanced this", "advanced that" just to specify the friggin' file that I want restored!!! Some of you (yes, you know who you are) have said that the Enterprise Manager is a great tool. I think it's terrible. It's slow, awkward, and not user friendly. In fact, after working with Vale's MSDE Manager[^], I'm beginning to like it A LOT MORE than EM (except for that annoying licensing process). Maybe I'm just weak-willed. Maybe I'm not a "power user" and I just want a simple GUI to get a simple job done, like restoring a database to a different physical file without going through 10 dialogs. Maybe EM is too complicated for my simple mind. Maybe I need to raise my rates. [/rant] Marc Latest AAL Article
I generally agree with you that Enterprise Manager is not that great (the backup/restore stuff is a pain in the !#$#). SQL Server, however, is IMHO, the easiest "real" database to install, setup and manage of all the "real" database contenders. I have worked with DB2, Oracle, Informix and several others that don't deserve a mention. DB2 is faster, but is much harder to configure and manage. Both DB2 and Oracle use awfull Java based tools that make Enterpise Manager look like the fastest, sleekest car on the planet. One serious question, though...What are you doing that generated 15GB of transaction log in a couple weeks that was being done with Access? I know many apps that can generate a log file like that, but none that could possibly run with Access. So seriously, what kind of app is it?
-
I generally agree with you that Enterprise Manager is not that great (the backup/restore stuff is a pain in the !#$#). SQL Server, however, is IMHO, the easiest "real" database to install, setup and manage of all the "real" database contenders. I have worked with DB2, Oracle, Informix and several others that don't deserve a mention. DB2 is faster, but is much harder to configure and manage. Both DB2 and Oracle use awfull Java based tools that make Enterpise Manager look like the fastest, sleekest car on the planet. One serious question, though...What are you doing that generated 15GB of transaction log in a couple weeks that was being done with Access? I know many apps that can generate a log file like that, but none that could possibly run with Access. So seriously, what kind of app is it?
Matt Gullett wrote: So seriously, what kind of app is it? Erm. This is the lounge. It's my club (*wink*) management software. While there's a lot of transactions going on, the majority of the "interaction" with the DB is managing state/status with updates. And when a club rakes in $120,000 to $200,000 a month, that's a lot of account updates. (two weeks was a bit of an exaggeration, come to think of it. But not by much...) Marc Latest AAL Article My blog Join my forum!