| Author |
Topic |
|
riggsy
Starting Member
1 Post |
 Posted - 2005-08-18 : 06:31:10
|
| One of the DBA's from our sister company is trying to re-attach a database, unfortunately he has no db skills whatsoever and has failed to perform regular db/log backups therefore his file sizes are as follows: mdf = 70GB, ldf= 60GB.The attach job has been left running for approx 3 hours at present and has still not completed, is this likely to take hours before it comes back online again?I've never attempted to reattach a db with such large log files before so I'm unable to give him any advice, my databases usually attach in seconds. Is the delay he is experiencing due to the large log size? |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-18 : 06:49:14
|
| I've not tried that one!Restoring a database (from a backup file) to a new database, with files that large, would take ages IME.Assuming the files being attached are "clean" (i.e. copied when SQL Server was NOT running, or when DB was detached) then doing a single-file-attach of just the MDF might work, and be quicker - that will recreate a new, empty, LDF file AFAIKKristen |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-08-18 : 06:51:37
|
| 3 hours? sp_attach_db runs in seconds for me, even with VLDBs ... something is wrong.Jay White |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-18 : 10:32:01
|
quote:
Originally posted by Page47
3 hours? sp_attach_db runs in seconds for me, even with VLDBs ... something is wrong.Jay White
Did he detach the database in the middle of a long running transaction, like an index rebuild? If so, the whole transaction will be rolled back, which can take hours depending on how much work had to be undone.Is the process doing any IOs?Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-18 : 12:39:35
|
| Can he just use the sp_attach_single_file_db instead of sp_attach_db? You'll lose whatever is in the tlog, but possibly he can afford that.Tara |
|
|
|
SQLTEAMSteve
Starting Member
8 Posts |
Posted - 2005-08-18 : 13:09:07
|
| There's no version of the original database online currently? I'd send him info on how to shrink files @ the log/mdf level, THEN make a backup, restore, etc._________________________Stephen R Montgomery |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-08-18 : 13:30:58
|
| I didn't know you could detach mid-transaction ... I wouldn't a thunk it would work that way.Jay White |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-18 : 14:21:07
|
quote:
Originally posted by Page47
I didn't know you could detach mid-transaction ... I wouldn't a thunk it would work that way.Jay White
Not using detach it doesn't - but sometimes I've seen people detach a database by killing SQL and copying the files to try to avoid having to rollback a long-running transaction.Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-18 : 15:27:33
|
| Paul,If you stop the MSSQLSERVER service while a long running transaction was running, then when the service is started up, the transaction is rolled back prior to the database being made available. Why isn't this true in the scenario you mentioned? Is the data partially committed?Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-18 : 15:34:14
|
"Not using detach it doesn't - but sometimes I've seen people detach a database by killing SQL and copying the files to try to avoid having to rollback a long-running transaction"Darn it, I've been giving people advice that "So long as SQL Server was stopped when you copied the files ..."Of course if they did an emergency stop, and then copied the files ..., there might be significant large transactions that would need rolling back ...Actually what happens if you do a single-file-attach in those circumstances? - Does SQL have some way of knowing that some pages are not "real"?  Mine-field ... why can't folks use Tara's Blogg's Sprocs to do some decent backups?! :-(Kristen |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-18 : 16:56:52
|
quote:
Originally posted by SQLTEAMSteve
There's no version of the original database online currently? I'd send him info on how to shrink files @ the log/mdf level, THEN make a backup, restore, etc._________________________Stephen R Montgomery
Shrinking the data files has no effect on the backup size, only on the size of file needed to do the restore. Backup doesn't backup the unused space. It could be a highwatermarking issue he's having during creation of the file to restore into...Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-18 : 17:04:05
|
| I've caused some confusion - my apologies.If the database is shutdown cleanly, then copying files works fine. If the server is hard-shutdown such that the database is not cleanly shutdown, then copying the files will cause a potentially long rollback on attach.Tara - you're right. What I meant was I'd seen people mistakenly try to avoid that by doing a hard shutdown and copying the files (i.e. not understanding how recovery works)IIRC, single-file attach doesn't work for a database that has a current log LSN > the last checkpoint LSN, but I may be wrong and don't have sql2k box handy to check.ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-19 : 03:13:49
|
| Paul, thanks for the clarification, I'm clear on the issues now.Kristen |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-08-19 : 10:13:05
|
quote:
Originally posted by paulrandalDid he detach the database in the middle of a long running transaction, like an index rebuild? If so, the whole transaction will be rolled back, which can take hours depending on how much work had to be undone.
OK, so in this quote, you are saying "detach" but not meaning "by running sp_detach_db", but rather meaning "drop kick the SQL Server and copy data files" ...When someone pulls the plug on SQL, without a polite shut down of the service, I don't consider that "detaching" ... Jay White |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-19 : 11:35:56
|
quote:
Originally posted by Page47
quote:
Originally posted by paulrandalDid he detach the database in the middle of a long running transaction, like an index rebuild? If so, the whole transaction will be rolled back, which can take hours depending on how much work had to be undone.
OK, so in this quote, you are saying "detach" but not meaning "by running sp_detach_db", but rather meaning "drop kick the SQL Server and copy data files" ...When someone pulls the plug on SQL, without a polite shut down of the service, I don't consider that "detaching" ... Jay White
I should have quoted detach - sorry for the confusion.Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
|
Attach DB - How long?