SQL Server Rant
-
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: 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[^]
-
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. Ok, I'll agree with that. But, have you ever read the SQL Server books online? It's really, really helpful when learning the ways of SQL Server. Marc Clifton wrote: As for the beta-test site, they're all set up and happy now. Spiffy! :) Out of curiosity, what was the cause of the problem/fix? My initial guess was the transaction file was larger than the set limit. Jeremy Falcon
-
[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
Hey, Sounds like a great start for a new article. Lessons learned --> SQL server for programmers.
-
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. Ok, I'll agree with that. But, have you ever read the SQL Server books online? It's really, really helpful when learning the ways of SQL Server. Marc Clifton wrote: As for the beta-test site, they're all set up and happy now. Spiffy! :) Out of curiosity, what was the cause of the problem/fix? My initial guess was the transaction file was larger than the set limit. Jeremy Falcon
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!
-
Hey, Sounds like a great start for a new article. Lessons learned --> SQL server for programmers.
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!
-
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.