Sunday, March 11, 2012

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
"Chris Weston" <chrisweston[losethislot]@.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
|||sp_attach_single_file_db [ @.dbname = ] 'dbname'
, [ @.physname = ] 'physical_name'
It will generate a new log file.
good luck.
d.
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:tIYSf.2054$H%3.875@.newsfe5-gui.ntli.net...[vbcol=seagreen]
> "Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
> news:w9YSf.2115$g76.696@.newsfe2-gui.ntli.net...
> server
mean,[vbcol=seagreen]
shrink[vbcol=seagreen]
the[vbcol=seagreen]
> the
> their
to
> 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...
> sp_attach_single_file_db [ @.dbname = ] 'dbname'
> , [ @.physname = ] 'physical_name'
> It will generate a new log file.
>
> good luck.
>
I appreciate the sentiment However, I'm not sure where to run this
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.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris Weston" <chrisweston[losethislot]@.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
>
>
|||"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
sp_attach_single_file_db
> 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
|||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
"Chris Weston" <chrisweston[losethislot]@.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...
> xslmailinglist@.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
>
|||"John Bell" <jbellnewsposts@.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
|||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.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:o0hTf.2330$g76.772@.newsfe2-gui.ntli.net...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:e%23SFB3zSGHA.4600@.TK2MSFTNGP11.phx.gbl...
> used?
> specified.
> 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
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

No comments:

Post a Comment