|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Database disappeared from sysdatabases after running out of file space
Guys and gals, I'm in all sorts of trouble here. I've been nursing a server
with very little free space for my sql databases while I get a new one built, and it's been pretty safe, until a power cut the other day caused some sort of huge write to the log file and it ran out of space. I mean, really ran out. The mdf and logs are at about 29.5 gigs and the hdd is 30gig. So, I needed to backup the transaction log, reorganise and shrink the database. Couldn't do that, no elbow room, so I thought I'd move the files to a USB hdd I have on there, slow but ok temporarily, and do the reorg there. Unfortunately, detatching the database has, somehow caused the sysdatabases table to lose any knowledge of it. I've got the files in their new location and would dearly love to be able to reattach them somehow to shrink them down but I'm at a loss now. I'd really appreciate a pointer here, I've run out of ideas. Cheers Chris Weston
Show quote
Hide quote
"Chris Weston" <chrisweston[losethisl***@ntlworld.com> wrote in message Just to follow up here, I've tried attaching the database from the contextnews:w9YSf.2115$g76.696@newsfe2-gui.ntli.net... > Guys and gals, I'm in all sorts of trouble here. I've been nursing a server > with very little free space for my sql databases while I get a new one > built, and it's been pretty safe, until a power cut the other day caused > some sort of huge write to the log file and it ran out of space. I mean, > really ran out. The mdf and logs are at about 29.5 gigs and the hdd is > 30gig. So, I needed to backup the transaction log, reorganise and shrink > the database. Couldn't do that, no elbow room, so I thought I'd move the > files to a USB hdd I have on there, slow but ok temporarily, and do the > reorg there. Unfortunately, detatching the database has, somehow caused the > sysdatabases table to lose any knowledge of it. I've got the files in their > new location and would dearly love to be able to reattach them somehow to > shrink them down but I'm at a loss now. > > I'd really appreciate a pointer here, I've run out of ideas. menu on my databases folder, but it complains about the log file (all 22g of it) being faulty. Can't I attach the mdf and let the log file be recreated? I'm no sql expert but the mdf is the important part, surely? Thanks, -- Chris Weston sp_attach_single_file_db [ @dbname = ] 'dbname'
, [ @physname = ] 'physical_name' It will generate a new log file. good luck. d. Show quoteHide quote "Chris Weston" <chrisweston[losethisl***@ntlworld.com> wrote in message news:tIYSf.2054$H%3.875@newsfe5-gui.ntli.net... > > "Chris Weston" <chrisweston[losethisl***@ntlworld.com> wrote in message > news:w9YSf.2115$g76.696@newsfe2-gui.ntli.net... > > Guys and gals, I'm in all sorts of trouble here. I've been nursing a > server > > with very little free space for my sql databases while I get a new one > > built, and it's been pretty safe, until a power cut the other day caused > > some sort of huge write to the log file and it ran out of space. I mean, > > really ran out. The mdf and logs are at about 29.5 gigs and the hdd is > > 30gig. So, I needed to backup the transaction log, reorganise and shrink > > the database. Couldn't do that, no elbow room, so I thought I'd move the > > files to a USB hdd I have on there, slow but ok temporarily, and do the > > reorg there. Unfortunately, detatching the database has, somehow caused > the > > sysdatabases table to lose any knowledge of it. I've got the files in > their > > new location and would dearly love to be able to reattach them somehow to > > shrink them down but I'm at a loss now. > > > > I'd really appreciate a pointer here, I've run out of ideas. > > Just to follow up here, I've tried attaching the database from the context > menu on my databases folder, but it complains about the log file (all 22g of > it) being faulty. Can't I attach the mdf and let the log file be recreated? > I'm no sql expert but the mdf is the important part, surely? > > Thanks, > > -- > Chris Weston > > "d" <d@d.com> wrote in message news:ruZSf.1311$I7.1016@trnddc03... I appreciate the sentiment :) However, I'm not sure where to run this> sp_attach_single_file_db [ @dbname = ] 'dbname' > , [ @physname = ] 'physical_name' > > It will generate a new log file. > > > > good luck. > > command? In a query window in Enterprise Manager? Many thanks, Chris Weston Hi Chris
This is expected behavior. When you detach a database, it is completely removed from the system. It is different from DROP in that the files still exist on disk. To get the db back, you have to attach it. If you are attaching through Enterprise Manager, and SQL Server complains about the log, you can try changing the name of the log file on disk so EM can't find it, and see if a new log will be rebuilt. Or, as d suggested, you can run the procedure from sp_attach_single_file_db from a query window in Query Analyzer. Show quoteHide quote "Chris Weston" <chrisweston[losethisl***@ntlworld.com> wrote in message news:w9YSf.2115$g76.696@newsfe2-gui.ntli.net... > Guys and gals, I'm in all sorts of trouble here. I've been nursing a > server > with very little free space for my sql databases while I get a new one > built, and it's been pretty safe, until a power cut the other day caused > some sort of huge write to the log file and it ran out of space. I mean, > really ran out. The mdf and logs are at about 29.5 gigs and the hdd is > 30gig. So, I needed to backup the transaction log, reorganise and shrink > the database. Couldn't do that, no elbow room, so I thought I'd move the > files to a USB hdd I have on there, slow but ok temporarily, and do the > reorg there. Unfortunately, detatching the database has, somehow caused > the > sysdatabases table to lose any knowledge of it. I've got the files in > their > new location and would dearly love to be able to reattach them somehow to > shrink them down but I'm at a loss now. > > I'd really appreciate a pointer here, I've run out of ideas. > > Cheers > > Chris Weston > > >
Show quote
Hide quote
"Kalen Delaney" <replies@public_newsgroups.com> wrote in message Still, no joy - I have tried both these methods but still get an errornews:uKXsmotSGHA.5500@TK2MSFTNGP12.phx.gbl... > Hi Chris > > This is expected behavior. When you detach a database, it is completely > removed from the system. It is different from DROP in that the files still > exist on disk. > To get the db back, you have to attach it. > > If you are attaching through Enterprise Manager, and SQL Server complains > about the log, you can try changing the name of the log file on disk so EM > can't find it, and see if a new log will be rebuilt. > > Or, as d suggested, you can run the procedure from sp_attach_single_file_db > from a query window in Query Analyzer. saying that it can't find the .ldf file. How can I get past this? Thanks, Chris Weston Hi
The methods described by Kalen should work if you followed them exactly. Can you post the exact sp_attach_single_file_db command that you have used? Make sure that you have move or renamed the ldf file and there is enough space to create a new one. Make sure that the mdf file does exist in the directory you have specified. Make sure that directory where it wants to create the ldf file exists and that the permissions allow you to create a new file. John Show quoteHide quote "Chris Weston" <chrisweston[losethisl***@ntlworld.com> wrote in message news:dz8Tf.2219$g76.437@newsfe2-gui.ntli.net... > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message > news:uKXsmotSGHA.5500@TK2MSFTNGP12.phx.gbl... >> Hi Chris >> >> This is expected behavior. When you detach a database, it is completely >> removed from the system. It is different from DROP in that the files >> still >> exist on disk. >> To get the db back, you have to attach it. >> >> If you are attaching through Enterprise Manager, and SQL Server complains >> about the log, you can try changing the name of the log file on disk so >> EM >> can't find it, and see if a new log will be rebuilt. >> >> Or, as d suggested, you can run the procedure from > xslmailingl***@btopenworld.com>> from a query window in Query Analyzer. > > Still, no joy - I have tried both these methods but still get an error > saying that it can't find the .ldf file. How can I get past this? > > Thanks, > > Chris Weston > >
Show quote
Hide quote
"John Bell" <jbellnewspo***@hotmail.com> wrote in message It exists, it's my default sql data directory and I'm logged in asnews:e%23SFB3zSGHA.4600@TK2MSFTNGP11.phx.gbl... > Hi > > The methods described by Kalen should work if you followed them exactly. > > Can you post the exact sp_attach_single_file_db command that you have used? > > Make sure that you have move or renamed the ldf file and there is enough > space to create a new one. > > Make sure that the mdf file does exist in the directory you have specified. > > Make sure that directory where it wants to create the ldf file exists and > that the permissions allow you to create a new file. administrator. The mdf exists. I'm using EXEC sp_attach_single_file_db @dbname = 'SysCompanyA', @physname = N'h:\systemp\SyscompA.mdf' but I get an error saying that 'the physical file name 'E:\program files\...\data\syscompanyA_log.ldf may be incorrect' So is it saying it can't create or that it can't find the ldf file? -- Chris Weston HI Chris
Was E:\ the original drive? It looks like when SQL Server creates a new log file, it wants to create it on the drive where it originally existed. I have use sp_attach_db to move a db, or to rebuild a log, but never at the same time. Show quoteHide quote "Chris Weston" <chrisweston[losethisl***@ntlworld.com> wrote in message news:o0hTf.2330$g76.772@newsfe2-gui.ntli.net... > > "John Bell" <jbellnewspo***@hotmail.com> wrote in message > news:e%23SFB3zSGHA.4600@TK2MSFTNGP11.phx.gbl... >> Hi >> >> The methods described by Kalen should work if you followed them exactly. >> >> Can you post the exact sp_attach_single_file_db command that you have > used? >> >> Make sure that you have move or renamed the ldf file and there is enough >> space to create a new one. >> >> Make sure that the mdf file does exist in the directory you have > specified. >> >> Make sure that directory where it wants to create the ldf file exists and >> that the permissions allow you to create a new file. > > It exists, it's my default sql data directory and I'm logged in as > administrator. The mdf exists. I'm using > > EXEC sp_attach_single_file_db @dbname = 'SysCompanyA', > @physname = N'h:\systemp\SyscompA.mdf' > > but I get an error saying that 'the physical file name 'E:\program > files\...\data\syscompanyA_log.ldf may be incorrect' > > So is it saying it can't create or that it can't find the ldf file? > > -- > Chris Weston > > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message Kalen,news:OUXQlB9SGHA.5172@TK2MSFTNGP12.phx.gbl... > HI Chris > > Was E:\ the original drive? It looks like when SQL Server creates a new log > file, it wants to create it on the drive where it originally existed. > I have use sp_attach_db to move a db, or to rebuild a log, but never at the > same time. E:\ was the original drive, and it still exists. I've no problem with it creating the log there if need be. -- Chris Weston Chris Weston wrote:
Show quoteHide quote > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message You have to make sure that the full path/folder exists before you run > news:OUXQlB9SGHA.5172@TK2MSFTNGP12.phx.gbl... >> HI Chris >> >> Was E:\ the original drive? It looks like when SQL Server creates a new > log >> file, it wants to create it on the drive where it originally existed. >> I have use sp_attach_db to move a db, or to rebuild a log, but never at > the >> same time. > > Kalen, > > E:\ was the original drive, and it still exists. I've no problem with it > creating the log there if need be. > the command. SQL server can't create the folder so if it's not present you'll get an error message like the one you see. Regards Steen So make sure there is no file on E with the name and path the log should
have. Make sure the directory exists on E and that the Windows account that SQL Server runs under has access to write to the directory. Once the attach succeeds, it should have a very small log file. You can then detach it, move the new small log file to your new location, and then attach again, specifying explicitly the log file location. Show quoteHide quote "Chris Weston" <chrisweston[losethisl***@ntlworld.com> wrote in message news:7msTf.11031$5B4.10504@newsfe6-gui.ntli.net... > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message > news:OUXQlB9SGHA.5172@TK2MSFTNGP12.phx.gbl... >> HI Chris >> >> Was E:\ the original drive? It looks like when SQL Server creates a new > log >> file, it wants to create it on the drive where it originally existed. >> I have use sp_attach_db to move a db, or to rebuild a log, but never at > the >> same time. > > Kalen, > > E:\ was the original drive, and it still exists. I've no problem with it > creating the log there if need be. > > -- > Chris Weston > > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message Folks, thanks very much for your efforts. I couldn't attach this db, itnews:uUKGRCETGHA.4740@TK2MSFTNGP14.phx.gbl... > So make sure there is no file on E with the name and path the log should > have. Make sure the directory exists on E and that the Windows account that > SQL Server runs under has access to write to the directory. > > Once the attach succeeds, it should have a very small log file. You can then > detach it, move the new small log file to your new location, and then attach > again, specifying explicitly the log file location. just wasn't having it. I ended up restoring from a backup and we'll just have to do the work to bring it up to date. I did eventually attach the db using the procedure detailed here http://www.spaceprogram.com/knowledge/2002/06/recovering-from-deleted-log-file-on_12.html although I had to repair it using allow_data_loss, and I can use it to help me get the restored data back to usability. So, thanks for your thoughts on this one, but may I ask one question? Why the consistent top-posting in this ng? Thanks -- Chris Weston  
Other interesting topics
|
|||||||||||||||||||||||
