Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2004-09-24 : 08:09:18
|
| Hello, me again!I now have a different problem, more administrative I believe. In a previous post (deleting duplicate rows from a table), i needed to delete information from a large table holding nearly 1 billion records. However, I am too scared to do it without a backup system. I would backup the database, but it is too large and we do not have enough room (rubbish I know). Anyway, I chose to create a new identical table on which to practice the delete first. However, when inserting all the records into the table (which was taking a very long time, which is expected mind), I received an error message after an hour (very frustrating):Server: Msg 9002, Level 17, State 6, Line 1The log file for database 'Trigger' is full. Back up the transaction log for the database to free up some log space.However, when I try to back up the log I get the error message:Write on 'TriggerBackup' failed, status = 112. See sql error log for details. BACKUP LOG is terminating abnormally.There is only 1360MB free. This is the same error I got when trying to backup the DB. What is the problem? Not enough room? I am going nuts here as I get error msg after error msg, and in true microsoft style, they mean absolute boll*^ks (status 112??????). Sorry for my curtness, but you must be able to feel my frustration.Heeeeeeeeelp!Hearty head pats |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2004-09-24 : 08:22:08
|
| Try dbcc shrinkfile !. In your case, I suggest to USE BCP to copy the all the records into another table!- Sekar |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-24 : 09:14:58
|
| "I would backup the database, but it is too large and we do not have enough room"So how are regular backups taken then?Delete the rows in batches, that will stop it using massive amounts of space.You could similarly create the "test" table with only a proportion of the rows - obviously you need enough to have some duplciates to play with.SET ROWCOUNT 100000SELECT *INTO MyNewTempTableFROM MyRealTableSET ROWCOUNT 0 -- This is VERY important!will grab you 100,000 rows at random.I presume you are either running your database in SIMPLE Recovery Mode, or doing regular transaction backups to release the log space?You may have extended the LOG file attempting the table copy, so it may now need shrinking back to a reasonable size - I think you need to make sure that the database is backed up after the shrink 'coz any transaction logs will be unusable until a full backup is taken. |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-09-24 : 09:42:50
|
| Thanks for your help. In answer to your question, we have no backup system at the moment, but is something I have been desperately pushing forward (we are a small company). We have no disaster recovery plan either. Basically, we are well and truly 'effed' (putting it politely) if, god forbid, something should happen. I have taken your advice, and have shrunk the transaction log (retaining the sequence of transactions), yet there is still not enough disc space to do a backup of just the log, or to insert just 10 rows.The transaction log is set to 3000mb max! You will have to excuse me, as I have never done any administrative work, but it will be something I have to do! Any other suggestions?Hearty head pats |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-24 : 13:17:16
|
| Is the database set to SIMPLE recovery model? (Properties in Enterprise Manager) - if not the log will just grow-and-grow as you are not doing backups.What size is the LDF file? (That will be in MSSQL's DATA folder). That might be huge (your shrinking may not have reduced the file's physical size)Is the SQL Backup folder set to Compressed? (You will get about a 50% saving - which might help!)Is there a network share you could backup to? Perhaps your C: drive is visible across the LAN? Even if not to tape, a single backup copy on another machine would provide some safeguard.Alternatively I can charge you 1,200 quid a day for consultancy to put you house in order; of course you will get a free drink, rest assured <bg>Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-24 : 13:20:10
|
| The default setting for the recovery model is FULL. With this model, you must perform transaction log backups regularly. Since you aren't and since you probably didn't change the default, this is why you have this problem. If you don't require point in time recovery option, then change it to SIMPLE as Kristen mentioned. Right click on your database in EM, go to properties then to Options tab. After that change has been made, you'll need to shrink the tlog using DBCC SHRINKFILE.Tara |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-26 : 10:51:26
|
after setting the model to simple, i suggest you perform the deletions by batch (smaller chunks). from experience, a not so reliable network can ruin a plan. i only had 1.6 million records to transfer and had 1 million transferred just to be rolled back due to connection problems or log file full. just an idea... |
|
|
|
|
|
|
|
|
Backing up not working!