| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-19 : 08:58:27
|
| Chris writes "I have a question on truncating Transaction log on our SQL Server 2000. The LDF file is 143 GB and the MDF file for our database is 27 GB. We have set up a full recovery model for the database. How can we truncate and shrink the LDF file and also move from full recovery to simple recovery model since we need only the latest transaction logs? Any help would be really appreciated especially if you can give me the procedure on how to accomplish that since we are running out of disk space. Thanks!" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-19 : 09:36:34
|
| See if this helps:http://www.swynk.com/friends/krishnan/tranlogshrink.aspAlso search SQL Team for "shrink log" and see if the other articles help. There is a script in one of them that does a super job of shrinking the log.Once you get the log file to the size you want, you should set a maximum size for it, say 50 GB. You should also set a maximum size for the database too. ALTER DATABASE will do it, or you can change these through Enterprise Manager. |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2001-12-19 : 11:38:06
|
| Exccuse me??????? Delete the log file? Who suggests doing that? This may cause some transactional inconsistancies, and I would not suggest EVER doing that. If you want a supported (Smart) way of shrinking the log, look in Books Online at dbcc shrinkfile. Works great in SQL 2000, it has some problems in 7.0, but you can work around them, and there is a KB article describing the process.HTH-Chad |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-19 : 12:31:15
|
quote:
When you detach the database using sp_detach_db, SQL Server will know that the database was cleanly shutdown and the log file need not have to be available to attach the database again. When you attach the database again SQL Server will create a new log file for you, which will be of the minimum size.
This was in the article that was linked. If you follow the directions listed there, it is perfectly safe to delete the log file. You should also do a full database backup before any of these steps. This eliminates any possibility of transactional inconsistencies. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2001-12-19 : 15:58:48
|
| Just because it may work, I wouldn't suggest doing it. You can modify system tables too, but I wouldn't suggest doing that either.SQL Server has a command for shrinking files (DBCC Shrinkfile) so there is no reason to hack it this way.There are right ways of doing things, and wrong ways. The wrong ways may work even 99% of the time, but one of these time it will jump up and bite you. I would advocate teaching people to do it the right way so that the don't get bitten.Just my opinion though, if you want to take a chance like that, it's your DB.If something goes wrong (And you don't have a backup, like many people), good luck trying to get Product Support to help you after you tell them you deleted the log file.-Chad |
|
|
|
MuadDBA
628 Posts |
Posted - 2001-12-21 : 11:25:58
|
| Chad,Probably there are many of us not as familiar with SQL2K as you are. In SQL7, as you mentioned, the SHRINKFILE command was nearly useless for shrinking log files, and thus people had to come up with their own enterprising ways to make it smaller.If SHRINKFILE works well in SQL2K, swell, glad to hear it, and OF COURSE I would reccommend using that first, but deleting the log file was a pretty standard thing to do in SQL7 if your log file became unmanageable in size. It's perfectly safe to do, and if you have DB and TRAN log backups, you can always recover from anything wierd that might happen anyhow.And good luck getting MS product support to help you with anything SQL related. They are about as useless as they come when it goes beyond installing software and asking dumb questions. If they were more helpful, we wouldn't need sites like SQLTEAM. |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2001-12-21 : 11:45:15
|
| Hey now, wait a minute, I could care less whether or not Microsoft supported SQL Server well or not, I love SQLTEAM!*************************Just trying to get things done |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-21 : 11:54:29
|
quote:
And good luck getting MS product support to help you with anything SQL related. They are about as useless as they come when it goes beyond installing software and asking dumb questions.
That's a shame. About 2 years back I had called MS because I couldn't get my server to recover from a shutdown. They didn't even ask for my license number! The guy went through every possible issue with me until HE was satisfied that the problem was solved. He gave me his direct line and told me to call if I ever had a problem. I even called him just for general questions, and he was polite and helpful every time. I was never charged for any of these incidents except the first; and that was covered by a 4 free incident policy on the software I purchased (Visual Studio, not SQL Server!)Anyway, thought I'd let everyone know that there was a time when MS SQL Server support was kick-ass. We can always hope it returns! |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2001-12-21 : 12:19:52
|
Crazyjoe,quote:
And good luck getting MS product support to help you with anything SQL related. They are about as useless as they come when it goes beyond installing software and asking dumb questions.
I think you are way off base with MS Support. If you have had a bad experience with them I would love to hear about it.I am an MS employee, and was a SQL Support Engineer for over a year, and still have friends in that group. If that is the typical impression of MS SQL support I am sorely dissapointed, because I think they do a magnificent job.These are not novice SQL users, they are all (For the most part, there may be a few substandard SPs) highly skilled people, typically with Masters degrees in CS, or some other technical field, and a lot of SQL training. I have dealt with support from many other organizations, and I am usually very dissapointed in what I get, sometimes I wonder if the person on the other end has ever even seen the product. That is definitely not the case with MS.Anyway, I am a new member here, because I just found out about this place (The link off of microsoft.com/sql). I come in here because I think SQL Server is the best database product on the market, and I want everyone else to believe so as well. So if a SQL user is having a problem that I can help out with, I will, and hopefully that will increase their satisfaction with our product.As for DBCC Shrinkfile in 7.0, it was not useless, it just wouldn't shrink if the active part of the log was at the end of the file, so you had to run dummy transactions until the active part looped around to the front of the file. There is a KB article describing how to do this. SQL 2000 is the same, it does the dummy transactions for you though.-Chad |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-22 : 20:05:18
|
| This is an interesting thread.Exccuse me??????? Delete the log file? Who suggests doing that? This may cause some transactional inconsistancies, and I would not suggest EVER doing that.Not if the transactions are completed - e.g. via a detach.When you detach the database using sp_detach_db, SQL Server will know that the database was cleanly shutdown and the log file need not have to be available to attach the database again. When you attach the database again SQL Server will create a new log file for you, which will be of the minimum size.Agree - shouldn't be a problemJust because it may work, I wouldn't suggest doing it. You can modify system tables too, but I wouldn't suggest doing that either.Irrelevant - detach-attach procedure is a documented procedure which many people use (up to the user to decide how reliable it is, as in all cases) - modifying system table is at your own risk.the SHRINKFILE command was nearly useless for shrinking log filesNope - it does what it says - the detach - attach procedure is easier as you don't have to worry about active entries though.If something goes wrong (And you don't have a backup, like many people)You shouldn't try a detach-attach or shrinkfile without taking a backup first imho.If they (MS product support) were more helpful, we wouldn't need sites like SQLTEAM.Nope - sql team isn't going to fix bugs in the product, only tell you where you're going wrong or suggest workarounds. Most problems are fairly simple and have obvious solutions, some need a different approach - very few are real bugs in the core system and those are the ones that need to be reported to Microsoft.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
MuadDBA
628 Posts |
Posted - 2001-12-26 : 16:57:29
|
| I have never gotten past the myriad menus of MS phone support, but I based my judgement from some MS employees who came out to help us "optimize" one of our applications for SQL 7.0 when it first came out. The processes were taking so bloody long that any of our clients were still running theri databases in 6.5 mode jsut to get them by.Don't ask me what the problem was, I don't know that they ever figured it out. I know the department folded shortly afterward (6 - 8 mos) and I was already on to other job scenes, but the guys we had out there seemed to be terribly unsure of what was going on, and how to address a lot of the problems. In general, I was unimpresed with them.As for Shrinkfile being useless, I maintina my position that it *IS* fairly useless if you have to do all the dummy transactions yourself to shrink the log file. Yes, someone designed a stored procedure that would accomplish this, and it was publicized ont eh web and you can get it if you know where to find it. But, um, excuse me, why is this necessary? Why is there not a patch for 7.0 that does this automatically? To me it's an integral function of the database that you be able to shrink the log files in a somewhat consistent basis. Detach and re-attach sounds great if you can get all your users off the DB, but when you're in a production environment, that's not always feasible, heck in some of my environments, it's rarely feasible and would require me coming in at hours I don't really want to think about.Sorry if I slighted MS Support based on my experience with the folks who came out to help us (at wonderful hourly rates)...if opthers are getting better help, I guess that's a good thing. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-04 : 14:11:18
|
| Forgot to mention,Rob's favorite authour, Ken Henderson is one of these folks that"...are about as useless as they come when it goes beyond installing software and asking dumb questions"-Chad |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-04 : 14:28:08
|
quote:
Rob's favorite authour, Ken Henderson is one of these folks that"...are about as useless as they come when it goes beyond installing software and asking dumb questions"
Wow, that's harsh! What led to that opinion Chad? |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-02-04 : 14:38:12
|
| Cheez. What Chad was saying is that Ken's (has been) on the support team, and so is tarred with that particular brush -- had it been accurately wielded. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-04 : 14:54:55
|
| Rob,That is not my opinion.That is the opinion of CrazyJoe (See his post above) Ken is a Support Professional on our SQL Server support team (As I used to be), which I think is great!I just thought it would help dispell the false reputation (At least in Crazyjoe's opinion) of MS support, Since Ken is so well known on this board.-Chad |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-04 : 15:00:46
|
 I failed "Recognizing Positively Intentioned Sarcasm" in school. Thanks for filling me in!BTW Chad, I can't remember his last name, but do you know a Mark B.? Sorry if that's vague or "well Rob, they changed the qualifications for the support team, now every member's name has to be Mark B-something, that's why I'm not on that team anymore".  Anyway, he was the guy who provided all the MS help in my SQL Server virgin days, and I owe him a hearty "Thank you!" and about 10 cases of his favorite brew. If he sounds familiar please feel free to pass that on.Edited by - robvolk on 02/04/2002 15:06:49 |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-04 : 15:40:23
|
| Sorry Rob,Doesn't sound familiar, and the name is to general to search the address list.-Chad |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-02-04 : 20:15:41
|
Hey Rob, we have a Mark B. here on our team. If you want to just send me the 10 cases of beer, I'll make sure he gets them.  --------------------------------There's a new General in town... |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-02-04 : 21:59:09
|
| Well I'm not sure if you guys have a different MS to us here in Oz, but last time I tried to verify whether a bug I found was indeed a bug - they wanted my credit card number before I even got to ask the question.... (ie it wasnt' a bug listed on any site I could find)Would love to hear from any other aussies about their experience...--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-05 : 10:50:30
|
| rrb,That is the way it has to work. If the problem is in fact a bug, you will get a refund on your CC.The people who answer the phone don't have the technical expertise to determine what is/is not a bug, that is left up to the engineers. However, if we took the word of the customer, EVERY customer would say "Oh yeah this is definitely a bug, I deleted * from sysobjects, and now it doesn't work???? Give me free support."So it is assumed that every call is not a bug, and when it is determined to be a bug, then you are refunded.-Chad |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-02-05 : 18:22:45
|
| I guess that's fair enough. The down side is, if I have found a bug, I'll probably never report it to MS since I'm not too keen on taking a chance with $100 of my own money...Cést la vie--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
Next Page
|
Truncate Transaction Log