November 20, 2007 at 7:18 am
Hello everyone,
I got a BIG problem over here,
I got a DB with 8.500.000 records. I just recently ran an update query to all records mention above, for as specific field. I've created several indexes too.
I had 14GB free disk space but the transaction log got so long that, the update crush and my SQL Server (Developer Edition SP2) tried to recovery my DB. Due to insufficient disk space this procedure failed too.
I made some deletions and now my free disk space restored to 23GB.
What do I do next recover my Database now?
I don't care about the transactions, I have the data for the field I want to update in another table, but inside the same DB that have been crushed.
Please, any help would be appreciated!
Thank you very much in advance,
Kostas
November 20, 2007 at 9:56 am
Kosta,
You indicated you don't care about the database, I would do the following to get the transaction log file more manageable
1. Eliminate your transaction log using the BACKUP LOG WITH NO_LOG option
2. Shrink your log file using DBCC SHRINKFILE
3. Go to Database properties and check your auto-growth for the Log, suggest to put a limit on the auto-growth ceiling
4. Do a full backup of mdf and ldf files
JTS
November 20, 2007 at 10:19 am
Hello JTS,
Thank you very much for your response,
Well, I'm in the middle of another procedure now, just to see If I can save any of my transactions.
I'm running these statements with the following sequence:
ALTER DATABASE MYDB SET ONLINE; --(My .ldf file does exist)
GO
ALTER DATABASE MYDB SET EMERGENCY; --(This is where I'm stuck now, takes too long (i'm in 18 minutes now)
GO
DBCC CHECKDB ('MYDB', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS --to commit (if can) any uncommited transactions
Am I executing these statements at the right order?
Thank you very much in advance,
Kostas
November 20, 2007 at 10:32 am
Kostas,
this is what I would do, it sounds like your transaction log is severely huge.....
Are you setting to emergency status because you cannot get into the database or the database is suspect mode?
You use this status only for that type of problem. Your problem is space issue.
Cancel the execution of emergency status do what I suggest (it will take time to backup the file with no log)
Then after my steps you can do DBCC CHECKDB ('MYDB', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS
November 20, 2007 at 10:46 am
Hi jsheldon,
Yup, my Log is huge, 40GB.
I'm running the EMERGENCY MODE, so I can afterwards run the DBCC CHECKDB. When I tried to run the DBCC cmd, an error message appeared, something like "could not do the operation because of deferred transactions?" :blink:
You think I better stop it? It is running over 45 minutes now.
Was it wise to SET my DB back ONLINE?
Thank you very much in advance,
Kostas
November 20, 2007 at 11:59 am
To play it safe let the emergency mode continue..
In the answer of putting the database back online if this is a database that has a front-end application that you can control through administration (like a Citrix application or terminal server application) revoke user permission to the application that is associated to this database to avoid users trying to connect to it.
Let me know how it goes.
November 20, 2007 at 12:13 pm
Hi JS,
Well, I'm afraid I couldn't wait and stopped it. I ran the steps as you wrote me before and now looks like running like a charm again.
Thank you very very much for your precious help!
Goodnight from Greece,
Kostas
November 20, 2007 at 12:50 pm
Great!!! Glad to hear it!!:D
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply