As the subject says...
I will admit to some stupidity. I inadvertently deleted
the log file.
On top of that, it appears my backups have failed, so no
back up.
Data file still exists.
Please tell me there is a way to recover.
Thanks in advance.
SvenI would give MS PSS a call if it's important to get it back.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
But if all else fails here is something you can try:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
Andrew J. Kelly
SQL Server MVP
"Sven Hansen" <svenhansen@.shaw.ca> wrote in message
news:079001c3af02$a07d2fa0$a101280a@.phx.gbl...
> As the subject says...
> I will admit to some stupidity. I inadvertently deleted
> the log file.
> On top of that, it appears my backups have failed, so no
> back up.
> Data file still exists.
> Please tell me there is a way to recover.
> Thanks in advance.
> Sven|||If you have the data file, then you can bring back your
database by using "sp_attach_db" coomand.
Look in BOL, for syntax and more info. Its very easy.
Good Luck!
-SQLVarad (MCDBA-1999,MCSE-1999)
>--Original Message--
>As the subject says...
>I will admit to some stupidity. I inadvertently deleted
>the log file.
>On top of that, it appears my backups have failed, so no
>back up.
>Data file still exists.
>Please tell me there is a way to recover.
>Thanks in advance.
>Sven
>.
>|||Since you have only data file, use this syntax
Example:
EXEC sp_attach_db @.dbname = 'pubs',
'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
SQL Server will automatically attach a new log file.
Good Luck!
SQLVarad (MCDBA-1999,MCSE-1999)
>--Original Message--
>As the subject says...
>I will admit to some stupidity. I inadvertently deleted
>the log file.
>On top of that, it appears my backups have failed, so no
>back up.
>Data file still exists.
>Please tell me there is a way to recover.
>Thanks in advance.
>Sven
>.
>|||You saved my life... and likely my job... :)
Thank you.
The script worked perfectly.
Take care and thanks again.
Sven
>--Original Message--
>I would give MS PSS a call if it's important to get it
back.
>http://support.microsoft.com/default.aspx?scid=fh;EN-
US;sql SQL Support
>http://www.mssqlserver.com/faq/general-pss.asp MS PSS
>But if all else fails here is something you can try:
>http://www.sqlservercentral.com/scripts/scriptdetails.asp
?scriptid=599
>Restoring a .mdf
>
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Sven Hansen" <svenhansen@.shaw.ca> wrote in message
>news:079001c3af02$a07d2fa0$a101280a@.phx.gbl...
>> As the subject says...
>> I will admit to some stupidity. I inadvertently
deleted
>> the log file.
>> On top of that, it appears my backups have failed, so
no
>> back up.
>> Data file still exists.
>> Please tell me there is a way to recover.
>> Thanks in advance.
>> Sven
>
>.
>
Showing posts with label backups. Show all posts
Showing posts with label backups. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
HELP! Transaction Log is huge
Hi all,
I have an SQL2000 running on a W2K server. The mdf file is about 140MB but
the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and the
Transaction log backup right after is 126MB. I tried shrinking and
everything else I could think of. I even deleted the DB and restored it from
a full DB backup. The ldf still comes up with 11GB. I have another
installation of the same database on another server where the ldf shows a
normal size (40MB).
Can anybody tell me what is going on? During the restore I moved the data
files to another disk that has lots of space but there is something wrong
here and I would like to get to the bottom of it.
Thanks for your help,
ClausHi, what is your recovery set too. If it is set to "Full" you need to do a
translog backup, and truncate after.
If you set it to simple, it will checkpoint and keep the log resonable.
Try backup log (database) with truncate_only
Then shrink the log file.
"cjobes" wrote:
> Hi all,
> I have an SQL2000 running on a W2K server. The mdf file is about 140MB but
> the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and the
> Transaction log backup right after is 126MB. I tried shrinking and
> everything else I could think of. I even deleted the DB and restored it from
> a full DB backup. The ldf still comes up with 11GB. I have another
> installation of the same database on another server where the ldf shows a
> normal size (40MB).
> Can anybody tell me what is going on? During the restore I moved the data
> files to another disk that has lots of space but there is something wrong
> here and I would like to get to the bottom of it.
> Thanks for your help,
> Claus
>
>|||I have it set to full because we do a daily backup and then hourly
transaction log backups. The one instance works as expected but the other
one has this 11GB ldf and I don't seem to be able to get it down.
Now after reading your advice I switched the db to simple, run a backup and
then shrank the log file. Now it's down to 0.5MB. My question is, shouldn't
it do that automatically when the full db backup runs with the option to
remove inactive entries from the transaction log?
Thanks for your help,
Claus
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> Hi, what is your recovery set too. If it is set to "Full" you need to do a
> translog backup, and truncate after.
> If you set it to simple, it will checkpoint and keep the log resonable.
> Try backup log (database) with truncate_only
> Then shrink the log file.
>
> "cjobes" wrote:
> > Hi all,
> >
> > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
but
> > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and
the
> > Transaction log backup right after is 126MB. I tried shrinking and
> > everything else I could think of. I even deleted the DB and restored it
from
> > a full DB backup. The ldf still comes up with 11GB. I have another
> > installation of the same database on another server where the ldf shows
a
> > normal size (40MB).
> >
> > Can anybody tell me what is going on? During the restore I moved the
data
> > files to another disk that has lots of space but there is something
wrong
> > here and I would like to get to the bottom of it.
> >
> > Thanks for your help,
> >
> > Claus
> >
> >
> >|||Hi,
As soon as you run the trasnaction log backup the inactive entries of
transaction log will be cleared. But the physcal file will not get shrink
automatically until or unless you enable to database option "AUTO_SHRINK".
Turning on this option in a production server is not recommended.
Because auto shrinking will reduce the performance.
To avoid this after the transaction log backup you could do a DBCC
SHRINKFILE (refer books online) on the LDF file frequently
(Daily or weekly once.. based on the LDF growth) to reduce the LDF file
size.
DBCC SQLPERF(LOGSPACE)
will give you the exact file size and usage
Thanks
Hari
MCDBA
"cjobes" <cjobes@.nova-tech.org> wrote in message
news:uReKjIYhEHA.3992@.TK2MSFTNGP11.phx.gbl...
> I have it set to full because we do a daily backup and then hourly
> transaction log backups. The one instance works as expected but the other
> one has this 11GB ldf and I don't seem to be able to get it down.
> Now after reading your advice I switched the db to simple, run a backup
and
> then shrank the log file. Now it's down to 0.5MB. My question is,
shouldn't
> it do that automatically when the full db backup runs with the option to
> remove inactive entries from the transaction log?
> Thanks for your help,
> Claus
>
> "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > Hi, what is your recovery set too. If it is set to "Full" you need to do
a
> > translog backup, and truncate after.
> >
> > If you set it to simple, it will checkpoint and keep the log resonable.
> >
> > Try backup log (database) with truncate_only
> >
> > Then shrink the log file.
> >
> >
> > "cjobes" wrote:
> >
> > > Hi all,
> > >
> > > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
> but
> > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB
and
> the
> > > Transaction log backup right after is 126MB. I tried shrinking and
> > > everything else I could think of. I even deleted the DB and restored
it
> from
> > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > installation of the same database on another server where the ldf
shows
> a
> > > normal size (40MB).
> > >
> > > Can anybody tell me what is going on? During the restore I moved the
> data
> > > files to another disk that has lots of space but there is something
> wrong
> > > here and I would like to get to the bottom of it.
> > >
> > > Thanks for your help,
> > >
> > > Claus
> > >
> > >
> > >
>|||Thanks for the helpful advice. I will keep an eye on this.
Claus
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eWRTv$ZhEHA.2624@.TK2MSFTNGP12.phx.gbl...
> Hi,
> As soon as you run the trasnaction log backup the inactive entries of
> transaction log will be cleared. But the physcal file will not get shrink
> automatically until or unless you enable to database option "AUTO_SHRINK".
> Turning on this option in a production server is not recommended.
> Because auto shrinking will reduce the performance.
> To avoid this after the transaction log backup you could do a DBCC
> SHRINKFILE (refer books online) on the LDF file frequently
> (Daily or weekly once.. based on the LDF growth) to reduce the LDF file
> size.
> DBCC SQLPERF(LOGSPACE)
> will give you the exact file size and usage
> Thanks
> Hari
> MCDBA
>
> "cjobes" <cjobes@.nova-tech.org> wrote in message
> news:uReKjIYhEHA.3992@.TK2MSFTNGP11.phx.gbl...
> > I have it set to full because we do a daily backup and then hourly
> > transaction log backups. The one instance works as expected but the
other
> > one has this 11GB ldf and I don't seem to be able to get it down.
> >
> > Now after reading your advice I switched the db to simple, run a backup
> and
> > then shrank the log file. Now it's down to 0.5MB. My question is,
> shouldn't
> > it do that automatically when the full db backup runs with the option to
> > remove inactive entries from the transaction log?
> >
> > Thanks for your help,
> >
> > Claus
> >
> >
> > "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> > news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > > Hi, what is your recovery set too. If it is set to "Full" you need to
do
> a
> > > translog backup, and truncate after.
> > >
> > > If you set it to simple, it will checkpoint and keep the log
resonable.
> > >
> > > Try backup log (database) with truncate_only
> > >
> > > Then shrink the log file.
> > >
> > >
> > > "cjobes" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have an SQL2000 running on a W2K server. The mdf file is about
140MB
> > but
> > > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB
> and
> > the
> > > > Transaction log backup right after is 126MB. I tried shrinking and
> > > > everything else I could think of. I even deleted the DB and restored
> it
> > from
> > > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > > installation of the same database on another server where the ldf
> shows
> > a
> > > > normal size (40MB).
> > > >
> > > > Can anybody tell me what is going on? During the restore I moved the
> > data
> > > > files to another disk that has lots of space but there is something
> > wrong
> > > > here and I would like to get to the bottom of it.
> > > >
> > > > Thanks for your help,
> > > >
> > > > Claus
> > > >
> > > >
> > > >
> >
> >
>|||I have the same problem with a client as you do. I did the backup and
shrink but the log file is still at 23gigs!
Where do I find the option to 'truncate' in the SQL Server Enterprise
Manager?
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> Hi, what is your recovery set too. If it is set to "Full" you need to do a
> translog backup, and truncate after.
> If you set it to simple, it will checkpoint and keep the log resonable.
> Try backup log (database) with truncate_only
> Then shrink the log file.
>
> "cjobes" wrote:
> > Hi all,
> >
> > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
but
> > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and
the
> > Transaction log backup right after is 126MB. I tried shrinking and
> > everything else I could think of. I even deleted the DB and restored it
from
> > a full DB backup. The ldf still comes up with 11GB. I have another
> > installation of the same database on another server where the ldf shows
a
> > normal size (40MB).
> >
> > Can anybody tell me what is going on? During the restore I moved the
data
> > files to another disk that has lots of space but there is something
wrong
> > here and I would like to get to the bottom of it.
> >
> > Thanks for your help,
> >
> > Claus
> >
> >
> >|||You have to do it in the analyzer
backup log "database" with truncate_only
You can also add it as a task in a job.
"Dean Penderghast" wrote:
> I have the same problem with a client as you do. I did the backup and
> shrink but the log file is still at 23gigs!
> Where do I find the option to 'truncate' in the SQL Server Enterprise
> Manager?
> "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > Hi, what is your recovery set too. If it is set to "Full" you need to do a
> > translog backup, and truncate after.
> >
> > If you set it to simple, it will checkpoint and keep the log resonable.
> >
> > Try backup log (database) with truncate_only
> >
> > Then shrink the log file.
> >
> >
> > "cjobes" wrote:
> >
> > > Hi all,
> > >
> > > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
> but
> > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and
> the
> > > Transaction log backup right after is 126MB. I tried shrinking and
> > > everything else I could think of. I even deleted the DB and restored it
> from
> > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > installation of the same database on another server where the ldf shows
> a
> > > normal size (40MB).
> > >
> > > Can anybody tell me what is going on? During the restore I moved the
> data
> > > files to another disk that has lots of space but there is something
> wrong
> > > here and I would like to get to the bottom of it.
> > >
> > > Thanks for your help,
> > >
> > > Claus
> > >
> > >
> > >
>
>|||I did that and the log file is still 23 gigs in size. I've done a full
transaction backup and tried to resize the file and still can't get it to
shrink. Am I missing something simple here?
Dean Penderghast
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:DF200740-7079-472C-8D0B-563ECC3FBBFD@.microsoft.com...
> You have to do it in the analyzer
> backup log "database" with truncate_only
> You can also add it as a task in a job.
> "Dean Penderghast" wrote:
> > I have the same problem with a client as you do. I did the backup and
> > shrink but the log file is still at 23gigs!
> >
> > Where do I find the option to 'truncate' in the SQL Server Enterprise
> > Manager?
> >
> > "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> > news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > > Hi, what is your recovery set too. If it is set to "Full" you need to
do a
> > > translog backup, and truncate after.
> > >
> > > If you set it to simple, it will checkpoint and keep the log
resonable.
> > >
> > > Try backup log (database) with truncate_only
> > >
> > > Then shrink the log file.
> > >
> > >
> > > "cjobes" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have an SQL2000 running on a W2K server. The mdf file is about
140MB
> > but
> > > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB
and
> > the
> > > > Transaction log backup right after is 126MB. I tried shrinking and
> > > > everything else I could think of. I even deleted the DB and restored
it
> > from
> > > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > > installation of the same database on another server where the ldf
shows
> > a
> > > > normal size (40MB).
> > > >
> > > > Can anybody tell me what is going on? During the restore I moved the
> > data
> > > > files to another disk that has lots of space but there is something
> > wrong
> > > > here and I would like to get to the bottom of it.
> > > >
> > > > Thanks for your help,
> > > >
> > > > Claus
> > > >
> > > >
> > > >
> >
> >
> >sql
I have an SQL2000 running on a W2K server. The mdf file is about 140MB but
the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and the
Transaction log backup right after is 126MB. I tried shrinking and
everything else I could think of. I even deleted the DB and restored it from
a full DB backup. The ldf still comes up with 11GB. I have another
installation of the same database on another server where the ldf shows a
normal size (40MB).
Can anybody tell me what is going on? During the restore I moved the data
files to another disk that has lots of space but there is something wrong
here and I would like to get to the bottom of it.
Thanks for your help,
ClausHi, what is your recovery set too. If it is set to "Full" you need to do a
translog backup, and truncate after.
If you set it to simple, it will checkpoint and keep the log resonable.
Try backup log (database) with truncate_only
Then shrink the log file.
"cjobes" wrote:
> Hi all,
> I have an SQL2000 running on a W2K server. The mdf file is about 140MB but
> the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and the
> Transaction log backup right after is 126MB. I tried shrinking and
> everything else I could think of. I even deleted the DB and restored it from
> a full DB backup. The ldf still comes up with 11GB. I have another
> installation of the same database on another server where the ldf shows a
> normal size (40MB).
> Can anybody tell me what is going on? During the restore I moved the data
> files to another disk that has lots of space but there is something wrong
> here and I would like to get to the bottom of it.
> Thanks for your help,
> Claus
>
>|||I have it set to full because we do a daily backup and then hourly
transaction log backups. The one instance works as expected but the other
one has this 11GB ldf and I don't seem to be able to get it down.
Now after reading your advice I switched the db to simple, run a backup and
then shrank the log file. Now it's down to 0.5MB. My question is, shouldn't
it do that automatically when the full db backup runs with the option to
remove inactive entries from the transaction log?
Thanks for your help,
Claus
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> Hi, what is your recovery set too. If it is set to "Full" you need to do a
> translog backup, and truncate after.
> If you set it to simple, it will checkpoint and keep the log resonable.
> Try backup log (database) with truncate_only
> Then shrink the log file.
>
> "cjobes" wrote:
> > Hi all,
> >
> > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
but
> > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and
the
> > Transaction log backup right after is 126MB. I tried shrinking and
> > everything else I could think of. I even deleted the DB and restored it
from
> > a full DB backup. The ldf still comes up with 11GB. I have another
> > installation of the same database on another server where the ldf shows
a
> > normal size (40MB).
> >
> > Can anybody tell me what is going on? During the restore I moved the
data
> > files to another disk that has lots of space but there is something
wrong
> > here and I would like to get to the bottom of it.
> >
> > Thanks for your help,
> >
> > Claus
> >
> >
> >|||Hi,
As soon as you run the trasnaction log backup the inactive entries of
transaction log will be cleared. But the physcal file will not get shrink
automatically until or unless you enable to database option "AUTO_SHRINK".
Turning on this option in a production server is not recommended.
Because auto shrinking will reduce the performance.
To avoid this after the transaction log backup you could do a DBCC
SHRINKFILE (refer books online) on the LDF file frequently
(Daily or weekly once.. based on the LDF growth) to reduce the LDF file
size.
DBCC SQLPERF(LOGSPACE)
will give you the exact file size and usage
Thanks
Hari
MCDBA
"cjobes" <cjobes@.nova-tech.org> wrote in message
news:uReKjIYhEHA.3992@.TK2MSFTNGP11.phx.gbl...
> I have it set to full because we do a daily backup and then hourly
> transaction log backups. The one instance works as expected but the other
> one has this 11GB ldf and I don't seem to be able to get it down.
> Now after reading your advice I switched the db to simple, run a backup
and
> then shrank the log file. Now it's down to 0.5MB. My question is,
shouldn't
> it do that automatically when the full db backup runs with the option to
> remove inactive entries from the transaction log?
> Thanks for your help,
> Claus
>
> "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > Hi, what is your recovery set too. If it is set to "Full" you need to do
a
> > translog backup, and truncate after.
> >
> > If you set it to simple, it will checkpoint and keep the log resonable.
> >
> > Try backup log (database) with truncate_only
> >
> > Then shrink the log file.
> >
> >
> > "cjobes" wrote:
> >
> > > Hi all,
> > >
> > > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
> but
> > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB
and
> the
> > > Transaction log backup right after is 126MB. I tried shrinking and
> > > everything else I could think of. I even deleted the DB and restored
it
> from
> > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > installation of the same database on another server where the ldf
shows
> a
> > > normal size (40MB).
> > >
> > > Can anybody tell me what is going on? During the restore I moved the
> data
> > > files to another disk that has lots of space but there is something
> wrong
> > > here and I would like to get to the bottom of it.
> > >
> > > Thanks for your help,
> > >
> > > Claus
> > >
> > >
> > >
>|||Thanks for the helpful advice. I will keep an eye on this.
Claus
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eWRTv$ZhEHA.2624@.TK2MSFTNGP12.phx.gbl...
> Hi,
> As soon as you run the trasnaction log backup the inactive entries of
> transaction log will be cleared. But the physcal file will not get shrink
> automatically until or unless you enable to database option "AUTO_SHRINK".
> Turning on this option in a production server is not recommended.
> Because auto shrinking will reduce the performance.
> To avoid this after the transaction log backup you could do a DBCC
> SHRINKFILE (refer books online) on the LDF file frequently
> (Daily or weekly once.. based on the LDF growth) to reduce the LDF file
> size.
> DBCC SQLPERF(LOGSPACE)
> will give you the exact file size and usage
> Thanks
> Hari
> MCDBA
>
> "cjobes" <cjobes@.nova-tech.org> wrote in message
> news:uReKjIYhEHA.3992@.TK2MSFTNGP11.phx.gbl...
> > I have it set to full because we do a daily backup and then hourly
> > transaction log backups. The one instance works as expected but the
other
> > one has this 11GB ldf and I don't seem to be able to get it down.
> >
> > Now after reading your advice I switched the db to simple, run a backup
> and
> > then shrank the log file. Now it's down to 0.5MB. My question is,
> shouldn't
> > it do that automatically when the full db backup runs with the option to
> > remove inactive entries from the transaction log?
> >
> > Thanks for your help,
> >
> > Claus
> >
> >
> > "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> > news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > > Hi, what is your recovery set too. If it is set to "Full" you need to
do
> a
> > > translog backup, and truncate after.
> > >
> > > If you set it to simple, it will checkpoint and keep the log
resonable.
> > >
> > > Try backup log (database) with truncate_only
> > >
> > > Then shrink the log file.
> > >
> > >
> > > "cjobes" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have an SQL2000 running on a W2K server. The mdf file is about
140MB
> > but
> > > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB
> and
> > the
> > > > Transaction log backup right after is 126MB. I tried shrinking and
> > > > everything else I could think of. I even deleted the DB and restored
> it
> > from
> > > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > > installation of the same database on another server where the ldf
> shows
> > a
> > > > normal size (40MB).
> > > >
> > > > Can anybody tell me what is going on? During the restore I moved the
> > data
> > > > files to another disk that has lots of space but there is something
> > wrong
> > > > here and I would like to get to the bottom of it.
> > > >
> > > > Thanks for your help,
> > > >
> > > > Claus
> > > >
> > > >
> > > >
> >
> >
>|||I have the same problem with a client as you do. I did the backup and
shrink but the log file is still at 23gigs!
Where do I find the option to 'truncate' in the SQL Server Enterprise
Manager?
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> Hi, what is your recovery set too. If it is set to "Full" you need to do a
> translog backup, and truncate after.
> If you set it to simple, it will checkpoint and keep the log resonable.
> Try backup log (database) with truncate_only
> Then shrink the log file.
>
> "cjobes" wrote:
> > Hi all,
> >
> > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
but
> > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and
the
> > Transaction log backup right after is 126MB. I tried shrinking and
> > everything else I could think of. I even deleted the DB and restored it
from
> > a full DB backup. The ldf still comes up with 11GB. I have another
> > installation of the same database on another server where the ldf shows
a
> > normal size (40MB).
> >
> > Can anybody tell me what is going on? During the restore I moved the
data
> > files to another disk that has lots of space but there is something
wrong
> > here and I would like to get to the bottom of it.
> >
> > Thanks for your help,
> >
> > Claus
> >
> >
> >|||You have to do it in the analyzer
backup log "database" with truncate_only
You can also add it as a task in a job.
"Dean Penderghast" wrote:
> I have the same problem with a client as you do. I did the backup and
> shrink but the log file is still at 23gigs!
> Where do I find the option to 'truncate' in the SQL Server Enterprise
> Manager?
> "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > Hi, what is your recovery set too. If it is set to "Full" you need to do a
> > translog backup, and truncate after.
> >
> > If you set it to simple, it will checkpoint and keep the log resonable.
> >
> > Try backup log (database) with truncate_only
> >
> > Then shrink the log file.
> >
> >
> > "cjobes" wrote:
> >
> > > Hi all,
> > >
> > > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
> but
> > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and
> the
> > > Transaction log backup right after is 126MB. I tried shrinking and
> > > everything else I could think of. I even deleted the DB and restored it
> from
> > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > installation of the same database on another server where the ldf shows
> a
> > > normal size (40MB).
> > >
> > > Can anybody tell me what is going on? During the restore I moved the
> data
> > > files to another disk that has lots of space but there is something
> wrong
> > > here and I would like to get to the bottom of it.
> > >
> > > Thanks for your help,
> > >
> > > Claus
> > >
> > >
> > >
>
>|||I did that and the log file is still 23 gigs in size. I've done a full
transaction backup and tried to resize the file and still can't get it to
shrink. Am I missing something simple here?
Dean Penderghast
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:DF200740-7079-472C-8D0B-563ECC3FBBFD@.microsoft.com...
> You have to do it in the analyzer
> backup log "database" with truncate_only
> You can also add it as a task in a job.
> "Dean Penderghast" wrote:
> > I have the same problem with a client as you do. I did the backup and
> > shrink but the log file is still at 23gigs!
> >
> > Where do I find the option to 'truncate' in the SQL Server Enterprise
> > Manager?
> >
> > "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> > news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > > Hi, what is your recovery set too. If it is set to "Full" you need to
do a
> > > translog backup, and truncate after.
> > >
> > > If you set it to simple, it will checkpoint and keep the log
resonable.
> > >
> > > Try backup log (database) with truncate_only
> > >
> > > Then shrink the log file.
> > >
> > >
> > > "cjobes" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have an SQL2000 running on a W2K server. The mdf file is about
140MB
> > but
> > > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB
and
> > the
> > > > Transaction log backup right after is 126MB. I tried shrinking and
> > > > everything else I could think of. I even deleted the DB and restored
it
> > from
> > > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > > installation of the same database on another server where the ldf
shows
> > a
> > > > normal size (40MB).
> > > >
> > > > Can anybody tell me what is going on? During the restore I moved the
> > data
> > > > files to another disk that has lots of space but there is something
> > wrong
> > > > here and I would like to get to the bottom of it.
> > > >
> > > > Thanks for your help,
> > > >
> > > > Claus
> > > >
> > > >
> > > >
> >
> >
> >sql
Wednesday, March 21, 2012
HELP! Lost LDF file, multiple MDF files, no backups!
I am desperate...
I have a database (I didn't build this one...) where there are 4 MDF
files, and in trying to clear out the two LDF files, which where
initially set to autogrow and were larger than the actual database,
and backup of the transaction log didn't shrink it, I detached the
database, deleted the LDF files, and tried to reattach, which, when
there is a single MDF file, it works!! I cannot reattach this
database!!
Here is the error:
Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
aborted.
Device activation error. The physical file name 'd:\yadayada_01.ldf'
may be incorrect.
Device activation error. The physical file name 'd:\yadayada_02.ldf'
may be incorrect.
I've tried everything I can think of... trying to figure this out
before someone notices (its not a very often used database).
Please help...
Thanks,
CalgaryDataGrl
Hello,
Try using the Create Database with Attach_Rebuild_Log option. See the below
eg:-
USE [master]
GO
CREATE DATABASE [Test] ON
(FILENAME = N'D:\Data\Test_data.mdf')
FOR ATTACH_REBUILD_LOG
GO
Thanks
Hari
"CalgaryDataGrl" <calgarydatagrl@.gmail.com> wrote in message
news:1171323042.569709.53480@.l53g2000cwa.googlegro ups.com...
>I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
>
|||On 12 Feb, 23:30, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
Please don't delete log files. That's a great way to lose data.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||On Feb 13, 5:30 am, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
You should have kept a backup (copy the files into a separate
directory) before making drastic changes so that you can revert to
original state in case things just work out the way you wanted (in
this situation).
I have a database (I didn't build this one...) where there are 4 MDF
files, and in trying to clear out the two LDF files, which where
initially set to autogrow and were larger than the actual database,
and backup of the transaction log didn't shrink it, I detached the
database, deleted the LDF files, and tried to reattach, which, when
there is a single MDF file, it works!! I cannot reattach this
database!!
Here is the error:
Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
aborted.
Device activation error. The physical file name 'd:\yadayada_01.ldf'
may be incorrect.
Device activation error. The physical file name 'd:\yadayada_02.ldf'
may be incorrect.
I've tried everything I can think of... trying to figure this out
before someone notices (its not a very often used database).
Please help...
Thanks,
CalgaryDataGrl
Hello,
Try using the Create Database with Attach_Rebuild_Log option. See the below
eg:-
USE [master]
GO
CREATE DATABASE [Test] ON
(FILENAME = N'D:\Data\Test_data.mdf')
FOR ATTACH_REBUILD_LOG
GO
Thanks
Hari
"CalgaryDataGrl" <calgarydatagrl@.gmail.com> wrote in message
news:1171323042.569709.53480@.l53g2000cwa.googlegro ups.com...
>I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
>
|||On 12 Feb, 23:30, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
Please don't delete log files. That's a great way to lose data.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||On Feb 13, 5:30 am, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
You should have kept a backup (copy the files into a separate
directory) before making drastic changes so that you can revert to
original state in case things just work out the way you wanted (in
this situation).
HELP! Lost LDF file, multiple MDF files, no backups!
I am desperate...
I have a database (I didn't build this one...) where there are 4 MDF
files, and in trying to clear out the two LDF files, which where
initially set to autogrow and were larger than the actual database,
and backup of the transaction log didn't shrink it, I detached the
database, deleted the LDF files, and tried to reattach, which, when
there is a single MDF file, it works!! I cannot reattach this
database!!
Here is the error:
Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
aborted.
Device activation error. The physical file name 'd:\yadayada_01.ldf'
may be incorrect.
Device activation error. The physical file name 'd:\yadayada_02.ldf'
may be incorrect.
I've tried everything I can think of... trying to figure this out
before someone notices (its not a very often used database).
Please help...
Thanks,
CalgaryDataGrlHello,
Try using the Create Database with Attach_Rebuild_Log option. See the below
eg:-
USE [master]
GO
CREATE DATABASE [Test] ON
(FILENAME = N'D:\Data\Test_data.mdf')
FOR ATTACH_REBUILD_LOG
GO
Thanks
Hari
"CalgaryDataGrl" <calgarydatagrl@.gmail.com> wrote in message
news:1171323042.569709.53480@.l53g2000cwa.googlegroups.com...
>I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
>|||On Feb 12, 6:09 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hello,
> Try using the Create Database with Attach_Rebuild_Log option. See the bel
ow
> eg:-
> USE [master]
> GO
> CREATE DATABASE [Test] ON
> (FILENAME = N'D:\Data\Test_data.mdf')
> FOR ATTACH_REBUILD_LOG
> GO
> Thanks
> Hari
>
Hi Hari,
I'm getting an error:
Server: Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'ATTACH_REBUILD_LOG'.
Any thoughts?
Stacy|||Are you on SQL Server 2005?
You should probably contact product support to help walk you through this
correctly - and you should get yourself a decent backup strategy. Send me
mail through the blog link below if you want help with this.
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"CalgaryDataGrl" <calgarydatagrl@.gmail.com> wrote in message
news:1171556088.308708.76810@.p10g2000cwp.googlegroups.com...
> On Feb 12, 6:09 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hi Hari,
> I'm getting an error:
>
> Server: Msg 102, Level 15, State 1, Line 3
> Incorrect syntax near 'ATTACH_REBUILD_LOG'.
> Any thoughts?
> Stacy
>|||On 12 Feb, 23:30, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
Please don't delete log files. That's a great way to lose data.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Feb 13, 5:30 am, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
You should have kept a backup (copy the files into a separate
directory) before making drastic changes so that you can revert to
original state in case things just work out the way you wanted (in
this situation).
I have a database (I didn't build this one...) where there are 4 MDF
files, and in trying to clear out the two LDF files, which where
initially set to autogrow and were larger than the actual database,
and backup of the transaction log didn't shrink it, I detached the
database, deleted the LDF files, and tried to reattach, which, when
there is a single MDF file, it works!! I cannot reattach this
database!!
Here is the error:
Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
aborted.
Device activation error. The physical file name 'd:\yadayada_01.ldf'
may be incorrect.
Device activation error. The physical file name 'd:\yadayada_02.ldf'
may be incorrect.
I've tried everything I can think of... trying to figure this out
before someone notices (its not a very often used database).
Please help...
Thanks,
CalgaryDataGrlHello,
Try using the Create Database with Attach_Rebuild_Log option. See the below
eg:-
USE [master]
GO
CREATE DATABASE [Test] ON
(FILENAME = N'D:\Data\Test_data.mdf')
FOR ATTACH_REBUILD_LOG
GO
Thanks
Hari
"CalgaryDataGrl" <calgarydatagrl@.gmail.com> wrote in message
news:1171323042.569709.53480@.l53g2000cwa.googlegroups.com...
>I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
>|||On Feb 12, 6:09 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hello,
> Try using the Create Database with Attach_Rebuild_Log option. See the bel
ow
> eg:-
> USE [master]
> GO
> CREATE DATABASE [Test] ON
> (FILENAME = N'D:\Data\Test_data.mdf')
> FOR ATTACH_REBUILD_LOG
> GO
> Thanks
> Hari
>
Hi Hari,
I'm getting an error:
Server: Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'ATTACH_REBUILD_LOG'.
Any thoughts?
Stacy|||Are you on SQL Server 2005?
You should probably contact product support to help walk you through this
correctly - and you should get yourself a decent backup strategy. Send me
mail through the blog link below if you want help with this.
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"CalgaryDataGrl" <calgarydatagrl@.gmail.com> wrote in message
news:1171556088.308708.76810@.p10g2000cwp.googlegroups.com...
> On Feb 12, 6:09 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hi Hari,
> I'm getting an error:
>
> Server: Msg 102, Level 15, State 1, Line 3
> Incorrect syntax near 'ATTACH_REBUILD_LOG'.
> Any thoughts?
> Stacy
>|||On 12 Feb, 23:30, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
Please don't delete log files. That's a great way to lose data.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Feb 13, 5:30 am, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
You should have kept a backup (copy the files into a separate
directory) before making drastic changes so that you can revert to
original state in case things just work out the way you wanted (in
this situation).
HELP! Lost LDF file, multiple MDF files, no backups!
I am desperate...
I have a database (I didn't build this one...) where there are 4 MDF
files, and in trying to clear out the two LDF files, which where
initially set to autogrow and were larger than the actual database,
and backup of the transaction log didn't shrink it, I detached the
database, deleted the LDF files, and tried to reattach, which, when
there is a single MDF file, it works!! I cannot reattach this
database!!
Here is the error:
Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
aborted.
Device activation error. The physical file name 'd:\yadayada_01.ldf'
may be incorrect.
Device activation error. The physical file name 'd:\yadayada_02.ldf'
may be incorrect.
I've tried everything I can think of... trying to figure this out
before someone notices (its not a very often used database).
Please help...
Thanks,
CalgaryDataGrlHello,
Try using the Create Database with Attach_Rebuild_Log option. See the below
eg:-
USE [master]
GO
CREATE DATABASE [Test] ON
(FILENAME = N'D:\Data\Test_data.mdf')
FOR ATTACH_REBUILD_LOG
GO
Thanks
Hari
"CalgaryDataGrl" <calgarydatagrl@.gmail.com> wrote in message
news:1171323042.569709.53480@.l53g2000cwa.googlegroups.com...
>I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
>|||On Feb 12, 6:09 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hello,
> Try using the Create Database with Attach_Rebuild_Log option. See the below
> eg:-
> USE [master]
> GO
> CREATE DATABASE [Test] ON
> (FILENAME = N'D:\Data\Test_data.mdf')
> FOR ATTACH_REBUILD_LOG
> GO
> Thanks
> Hari
>
Hi Hari,
I'm getting an error:
Server: Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'ATTACH_REBUILD_LOG'.
Any thoughts?
Stacy|||Are you on SQL Server 2005?
You should probably contact product support to help walk you through this
correctly - and you should get yourself a decent backup strategy. Send me
mail through the blog link below if you want help with this.
--
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"CalgaryDataGrl" <calgarydatagrl@.gmail.com> wrote in message
news:1171556088.308708.76810@.p10g2000cwp.googlegroups.com...
> On Feb 12, 6:09 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
>> Hello,
>> Try using the Create Database with Attach_Rebuild_Log option. See the
>> below
>> eg:-
>> USE [master]
>> GO
>> CREATE DATABASE [Test] ON
>> (FILENAME = N'D:\Data\Test_data.mdf')
>> FOR ATTACH_REBUILD_LOG
>> GO
>> Thanks
>> Hari
> Hi Hari,
> I'm getting an error:
>
> Server: Msg 102, Level 15, State 1, Line 3
> Incorrect syntax near 'ATTACH_REBUILD_LOG'.
> Any thoughts?
> Stacy
>|||On 12 Feb, 23:30, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
Please don't delete log files. That's a great way to lose data.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Feb 13, 5:30 am, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
You should have kept a backup (copy the files into a separate
directory) before making drastic changes so that you can revert to
original state in case things just work out the way you wanted (in
this situation).
I have a database (I didn't build this one...) where there are 4 MDF
files, and in trying to clear out the two LDF files, which where
initially set to autogrow and were larger than the actual database,
and backup of the transaction log didn't shrink it, I detached the
database, deleted the LDF files, and tried to reattach, which, when
there is a single MDF file, it works!! I cannot reattach this
database!!
Here is the error:
Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
aborted.
Device activation error. The physical file name 'd:\yadayada_01.ldf'
may be incorrect.
Device activation error. The physical file name 'd:\yadayada_02.ldf'
may be incorrect.
I've tried everything I can think of... trying to figure this out
before someone notices (its not a very often used database).
Please help...
Thanks,
CalgaryDataGrlHello,
Try using the Create Database with Attach_Rebuild_Log option. See the below
eg:-
USE [master]
GO
CREATE DATABASE [Test] ON
(FILENAME = N'D:\Data\Test_data.mdf')
FOR ATTACH_REBUILD_LOG
GO
Thanks
Hari
"CalgaryDataGrl" <calgarydatagrl@.gmail.com> wrote in message
news:1171323042.569709.53480@.l53g2000cwa.googlegroups.com...
>I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
>|||On Feb 12, 6:09 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hello,
> Try using the Create Database with Attach_Rebuild_Log option. See the below
> eg:-
> USE [master]
> GO
> CREATE DATABASE [Test] ON
> (FILENAME = N'D:\Data\Test_data.mdf')
> FOR ATTACH_REBUILD_LOG
> GO
> Thanks
> Hari
>
Hi Hari,
I'm getting an error:
Server: Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'ATTACH_REBUILD_LOG'.
Any thoughts?
Stacy|||Are you on SQL Server 2005?
You should probably contact product support to help walk you through this
correctly - and you should get yourself a decent backup strategy. Send me
mail through the blog link below if you want help with this.
--
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"CalgaryDataGrl" <calgarydatagrl@.gmail.com> wrote in message
news:1171556088.308708.76810@.p10g2000cwp.googlegroups.com...
> On Feb 12, 6:09 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
>> Hello,
>> Try using the Create Database with Attach_Rebuild_Log option. See the
>> below
>> eg:-
>> USE [master]
>> GO
>> CREATE DATABASE [Test] ON
>> (FILENAME = N'D:\Data\Test_data.mdf')
>> FOR ATTACH_REBUILD_LOG
>> GO
>> Thanks
>> Hari
> Hi Hari,
> I'm getting an error:
>
> Server: Msg 102, Level 15, State 1, Line 3
> Incorrect syntax near 'ATTACH_REBUILD_LOG'.
> Any thoughts?
> Stacy
>|||On 12 Feb, 23:30, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
Please don't delete log files. That's a great way to lose data.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Feb 13, 5:30 am, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
You should have kept a backup (copy the files into a separate
directory) before making drastic changes so that you can revert to
original state in case things just work out the way you wanted (in
this situation).
Subscribe to:
Posts (Atom)