SQL Server Rant
-
[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
DBX-based databases. Trust me. :) -- Try walking in my shoes. You stumble in my footsteps.
-
[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
Error: Transaction log full, clear log Error: Cannot clear log, transaction log full regards, Paul Watson Bluegrass South Africa Christopher Duncan quoted: "All Corvettes are red. Everything else is just a mistake." Crikey! ain't life grand? Einstein says...
-
[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
It's very strange... I don't say that SQL Server have no problems, but I never had problems like that. I am an old user of SQL Server (Since version 4.2) but this behavior is very strange. First. What version are using You? Do you have any service pack installed? Did you check the Auto Shrink option? - I recomend to use this option checked... What Recovery Model you Have? How many transactions you have in your customer per day, or may be how many Bytes increase the database p/d? Carlos Antollini Do you know piFive[^] ?
-
[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 have seen this before, There is a setting within SQL which tells SQL how to handle Transaction Logs. I believe this setting is under the database properties on one of the tabs. Basically it controls how the transaction logs will be reset. This can control if transaction logs are written to the database daily or when backups occur
-
Error: Transaction log full, clear log Error: Cannot clear log, transaction log full regards, Paul Watson Bluegrass South Africa Christopher Duncan quoted: "All Corvettes are red. Everything else is just a mistake." Crikey! ain't life grand? Einstein says...
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!
-
[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
Marc, Hope all is going well, at least now you have gotten some of the frustration off your back. Check out the following article, it might be of some help. Control Log File Growth to Avoid Out-of-Space Errors[^] :) - Nick Parker
My Blog -
[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
Step one, chill out and have a :beer:. Step two, have another :beer:. :) Step three... Marc Clifton wrote: Backed up the database and reset the log file to 2MB, then set the log file backup schedule too. Navigate to your transaction log. It'll *probably* be something like
C:\Program Files\Microsoft SQL Server\MSSQL\Data\**dbname**_Log.ldf. What's the filesize? Is it over 2MB? Marc Clifton wrote: So how was it growing to begin with? It'll automatically grow by default. Also, check out this link. I don't know if it's the cause, but it'll take us one step closer to troubleshooting it. http://support.microsoft.com/default.aspx?scid=kb;EN-US;62866[^] Marc Clifton wrote: 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. It makes sense. SQL Server is a transactional RDBMS. It has to record everything to know what needs to be rolled back, etc. if needed. If you can't record transactions, then it won’t let you update the db. Marc Clifton wrote: 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!!! In EM, go to Tools/Restore Database. It's not that hard really. Marc Clifton wrote: 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). 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. Anyway, let me know the size of your transaction log (LDF file) and we'll take it from there. Jeremy Falcon -
Step one, chill out and have a :beer:. Step two, have another :beer:. :) Step three... Marc Clifton wrote: Backed up the database and reset the log file to 2MB, then set the log file backup schedule too. Navigate to your transaction log. It'll *probably* be something like
C:\Program Files\Microsoft SQL Server\MSSQL\Data\**dbname**_Log.ldf. What's the filesize? Is it over 2MB? Marc Clifton wrote: So how was it growing to begin with? It'll automatically grow by default. Also, check out this link. I don't know if it's the cause, but it'll take us one step closer to troubleshooting it. http://support.microsoft.com/default.aspx?scid=kb;EN-US;62866[^] Marc Clifton wrote: 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. It makes sense. SQL Server is a transactional RDBMS. It has to record everything to know what needs to be rolled back, etc. if needed. If you can't record transactions, then it won’t let you update the db. Marc Clifton wrote: 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!!! In EM, go to Tools/Restore Database. It's not that hard really. Marc Clifton wrote: 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). 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. Anyway, let me know the size of your transaction log (LDF file) and we'll take it from there. Jeremy FalconJeremy 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!
-
[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
sql server is a great product it just have the "microsoft syndrom" that makes people thing it sucks a very powerfull piece of software that is too easy to manage for people that dont know much about it, and that makes people make mistakes, and then they say its a bad product... just the same as windows server, and the reason linux dont have the same reputation, as no one logs on to linux and thinks "wow, just like my win98 at home, now i'm also network admin". that happens with windows server (i've seen it several times), and the result: baaaad configuration, and the server is not stable. Sorry for any missing punctation, but i'm lazy today ;) - 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: 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