| Author |
Topic |
|
saboy30
Starting Member
7 Posts |
 Posted - 2007-01-30 : 17:31:08
|
I often in my job come across the following scenario:Client rings up and says Run out of server space due to SQL 2000 Transaction log has consumed all the space or has consumed a very large portion of it.what is the correct procedure in resolving this ASAP working with Full mode SQl 2000 Databases. as i have otherguys within my company that all do different things with good result and sometimes bad results.The procedure i use is the following:METHOD 11.backup both database and transaction log2.Right click the database and select Detach, which from my understanding is a clean detach method which ensures that uncommited transactions are commited to the database.3. Rename the old transaction log to .ldfOLD4. REATTACH Database which creates a new transaction log.METHOD 21. I dont use this method but im pretty sure its risky and if possiblecan someone provide me with the reasons why:1.Change database method from full to simple mode, shrink logsand then change back to full mode. basically what i am asking is what is the fastest way to sort out the above issue most effectively and with the abilty to Roleback succesfully.PLEASE dont comment on why is the transaction log so big as i dont want to look into that now all i am sking is what is the most effective method to shrink the log down and save space. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-30 : 17:35:29
|
| While in Full Recovery mode, have you tried doing a shrinkfile, then a transaction log backup (not a full backup as it does not truncate the log), then another shrinkfile. Possibly do that multiple times. But in the end, no - the two ways you have suggested are really the only sure-fire ways to get it to shrink quickly. |
 |
|
|
saboy30
Starting Member
7 Posts |
Posted - 2007-01-30 : 17:39:57
|
| The Transaction log files are generally quite large over 30 gb in size at this point what is the overhead on SQL performance on a production server when you try doing the shrinkfile ? as i am concerned that this will chew up all the available resources ??Also method two can anyone please tell me why do you need to change from full mode to simple mode and then run shrink ?? as some people do it that way but im not sure why ? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-30 : 17:47:40
|
| So I guess you do want some explanation of how logs work after all?When you put the database in simple recovery mode, it automatically truncates the log every time a checkpoint runs, so old transactions are not kept in the log until you backup. It means you cannot backup the log or recover from the log, you have to use full and differential backups only.So (oversimplifying here, but basically it works like this) if you put the database into simple mode and run a shrinkfile, the shrink can shrink much more of the log, because there are no parts being kept for log backups. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-30 : 17:53:51
|
| Here's the best method:1. BACKUP LOG WITH NO_LOG -- this will get them back up and running as now there's free space in the file2. BACKUP DATABASE -- to begin transaction log chain3. Shrink files (but only if necessary)Tara Kizer |
|
|
|
saboy30
Starting Member
7 Posts |
Posted - 2007-01-30 : 17:55:57
|
| Wicked thanks snSQL i fully understand ...Thanks alot for your helpnow with SQL 2005 I suppose Same rule of thumb applies with transaction log truncating and shrinking ...method is the same as the above ? just curious as i have not played alot with SQL2005 but no doubt in my travels will come across this..... |
|
|
|
saboy30
Starting Member
7 Posts |
Posted - 2007-01-30 : 17:58:35
|
| Here's the best method:1. BACKUP LOG WITH NO_LOG -- this will get them back up and running as now there's free space in the file2. BACKUP DATABASE -- to begin transaction log chain3. Shrink files (but only if necessary)can you clarify Tara as your post is as clear as mud to me...thank you |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-30 : 18:10:19
|
| 1. Backup the transaction log, but use the option to truncate it rather than backing it up to a file. This step frees up space in the transaction log, so new transactions are able to be performed. It does not change the tlog file size though.2. Perform full backup as Step1 has invalidated the transaction log chain, so you use the ability for point in time restores until a new full backup is performed3. Shrinks should only be done if you know that you do not need the space in the near future. This is due to the huge performance hit you receive when the file is expanded.Tara Kizer |
|
|
|
saboy30
Starting Member
7 Posts |
Posted - 2007-01-30 : 18:22:12
|
| tkizer thank you got it nowIssue is that the transaction log is actually full, and and physical tlog file is very large in size consuming a large amount of disk spaceCan we get away with simply detaching the database through GUI renamingold transaction log to .OLD and Reattaching database thus creating a new database transaction log ? in doing this is there a need to perform a database backup and transaction log backup before we detach it ?just thinking from the fastest method possible as backing up a 30 GB database and it huge transaction log file will take forever....but certainly need to know possible consiquences around doing it quickeras i need to achieve Physical Disk space ASAP in a production enviroment but doing so with precaution and a good roleback strategy so if the *S hits the fan* we can recover from it |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-30 : 18:29:07
|
| All you have to do is Step1 for my method. It's much faster than what you have described, although that method works as well. Step1 gets them back up and running, so they no longer receive the errors. You can do Step2 and Step3 when things settle down. Step1 should only take a few seconds as it's not actually backing up the transaction log. It's just truncating it.What you've described is the fastest method to reclaim the disk space, however it's not the fastest method to stop getting the errors that the customer would be getting when the transaction log was full.Tara Kizer |
|
|
|
saboy30
Starting Member
7 Posts |
Posted - 2007-01-30 : 18:46:06
|
| Excellent thanks so much for your help guys i presume this also works for SQL 2005 ?also the following?:"Shrinks should only be done if you know that you do not need the space in the near future. This is due to the huge performance hit you receive when the file is expanded"I am very interested in that statment and would like to find out more about that as every nooby SQL person i ever spoken to all swear by Shrinking and i have always been concerned about doing that but sometimes there is no other alternative or is there ?also read up that running a database maintenance plan that reindexes and reorganises the database can blow your transaction logs out as well...is this true ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-30 : 18:49:22
|
| The alternative is to add more disk space to support the transaction log. And yes that's true too.Tara Kizer |
|
|
|
saboy30
Starting Member
7 Posts |
Posted - 2007-01-30 : 18:58:16
|
| Thank you so much for your help people i expected to post on here and it would take a couple of days for someone to reply to me so big ups to sqlteam.com and its members awsum site:)thanks again :) |
|
|
|
Kristen
Test
22859 Posts |
|
|
|
Transaction log too LARGE