Help!!!! Restore file mdf after exec "Drop table..." to previous state.

Hi all.
First, sorry about my poor english.

I have a database which above 6 gb data and i have droped all table in my database.
I try to restore from log file but my database is set to simple backup and auto shrink so log file doesn't help anymore.
I have used some software to recovery from log file too but useless.
My only hope right now is mdf file.
Please help me. How could i restore my mdf file to state before i droped tables.
Thanks

Since you are in simple recovery model, you can't use transaction log backups and none of the recovery tools on the market will be able to help since they rely on the contents of the transaction log to get the data back. If you don't have a backup of the database, the data is gone.

|||

There is actually a VERY brute force way that you really aren't going to like. Dropping objects in SQL Server works very similar to deleting a file on the operating system. You don't actually wipe out the file, you just remove an 8 byte header that essentially says "I'm a valid file". In SQL Server, you remove the object from the SQL Server metadata. Once the entries for the object have been removed from sysindexes, SQL Server will no longer find the data. Unless you have done something to reallocate those pages to another object in which case everything will be wiped out on that page, you can actually get to the raw pages. This is NOT pretty. You can use the DBCC PAGE command to read a page. You would wind up running this one page at a time and extracting the data from each page as you go. Then you would have to reconstruct everything and manually reload the data. (Did I mention, you REALLY don't want to have to do this which is why you always take frequent backups.)

So, step 1 in this process is to immediately create a backup of the database. Step 2 is to immediately put a backup plan in place so that the database is being backed up on a routine basis. Then you can use the following set of commands to manually extract the data. Below is an example. You would substitute your database name. You would then start at file = 1 and page = 1 and incrementally walk through page 2, 3, 4, ... and then repeat the process for any other files present in the database. The final step in the process would be to test your backup strategy and continue to test it on a frequent basis. (If you don't keep a backup of a database, you are basically saying that it is unimportant and you don't care about losing it.)

--dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

dbcc traceon (3604)

dbcc page (pubs,1,88,3)

go

|||Thanks very much.
As you mentioned, i try to use DBCC PAGE and it has a lot of thing which i don't understand.
Please show me the way to read info from what DBCC Page return and meaning of those.
Thanks again.|||You are reading the raw storage on a page using this command. It is easier to just point you at a book. Go out and pick up a copy of "Inside SQL Server 2000" by Kalen Delaney. There are about 100 pages of material that relate back to interpreting and utilizing the output from DBCC PAGE.|||I will read it and find what i can do with that book.
You help me very much, thanks. :)|||BTW, the DBCC PAGE command is an undocumented command which means it is not supported by Microsoft (but nobody is stopping you from not using them). Try Googling around for some references on these undocumented commands, stored procedures and functions and you will find a lot of useful ones.|||I have read the book which you mentioned. I try to find out exactly what you mean when you said i can read pages in mdf file one by one but i don't get it.
Could you tell me which tools or language or anything else to perform that idea.
Thanks|||

If you query sysfiles within a database, you get the list of FileIDs. In general, if you have an mdf file, it will be fileID = 1.

So you use DBCC page to read each page manually by doing the following:

1. Open a query window.

2. Execute:

DBCC TRACEON(3604)

DBCC PAGE(<database name>,1,1,3)

3. Read the contents of the page and extract any data that you need

4. Execute:

DBCC TRACEON(3604)

DBCC PAGE(<database name>,1,2,3)

5. Read the contents of the page and extract any data that you need

6. Execute:

DBCC TRACEON(3604)

DBCC PAGE(<database name>,1,3,3)

7. Read the contents of the page and extract any data that you need

8. Execute:

DBCC TRACEON(3604)

DBCC PAGE(<database name>,1,4,3)

Etc. There isn't any tool to do this. You do this in Transact-SQL. Like I said when I brought this up, this is an absolute last resort if you can access the database, it is EXTREMELY ugly, EXTREMELY resource intensive, and EXTREMELY time consuming. Why, because you pull page 1, then page 2, then page 3, then page 4, then page 5, then.... until you get an error message that the page doesn't exist. Even in a small database, you can have hundreds or thousands of pages, so that means that in even a small database, you would have to execute DBCC PAGE hundreds or thousands of times, read the contents of the page, and manually extract the data. In production databases, you can easily have millions or billions or pages.

Now you get the idea why everyone jumps up and down about backups and why it would be nice if Microsoft could ship a 100 foot tall poster in the box with SQL Server that has exactly one message on it, backup your databases. If you don't have backups and you need to recover data, there are few, if any options, and you are not going to like ANY of the options.

|||Michael Hotek,
I already exec Dbcc page and i get a lot of things which i don't understand.
So, what do you mean when you said:"Read the contents of the page and extract any data that you need". How to read and extract it? By T-SQL or something else?
About my database, I give up.So, right now,I just want to know more about SQL and the way to extract the contents of the page.
Thanks
|||Copy it from the results window and paste it into Notepad, another Query window etc. This is a 100% manual process. There are zero tools. There are no shortcuts. There is no easy way to do this. In essence you are going to manually type back in every single piece of data within the database. If you go through about 100 pages in your database and look at the results, the data is pretty self-explanatory. Since you are starting at page 1, there are going to be entire pages that you aren't going to be able to do things with such as the PFS page, GAM page, SGAM page, index pages, etc. But, there are data pages in your database and when you hit one, it will be very apparent where the data is and what it means. Other than that, it's up to you. You have to look at the results pane and you have to highlight the data and copy it back out into something else in order to stick it back into your database.|||Michael Hotek
Now, I understand what you mean, thanks. :)sql

Help!! Database always In Recovery...

Hi all,

I had to change the path of .mdf and .ldf files, so I decided to:

1) Take offline the database

2) run the quey ALTER DATABASE... MODIFY to change the path

3) Bring online the database.

The last step hung up (with no errors) and left the database In Recovery.

When I tried to stop and restart sql server other databases changed their status In Recovery...

Here is a dump of Errorlog files

2007-08-26 18:13:29.28 spid24s Starting up database 'DbOrdini'.

...

...

2007-08-26 18:13:30.09 spid24s * BEGIN STACK DUMP:
2007-08-26 18:13:30.09 spid24s * 08/26/07 18:13:30 spid 24
2007-08-26 18:13:30.09 spid24s *
2007-08-26 18:13:30.09 spid24s * Location: "logmgr.cpp":5334
2007-08-26 18:13:30.09 spid24s * Expression: !(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)
2007-08-26 18:13:30.09 spid24s * SPID: 24
2007-08-26 18:13:30.09 spid24s * Process ID: 1380
.....

.....

2007-08-26 18:13:30.40 spid24s Error: 17066, Severity: 16, State: 1.
2007-08-26 18:13:30.40 spid24s SQL Server Assertion: File: <"logmgr.cpp">, line=5334 Failed Assertion = '!(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
2007-08-26 18:13:30.40 spid24s Error: 3624, Severity: 20, State: 1.
2007-08-26 18:13:30.40 spid24s A system assertion check has failed. Check the SQL Server error log for details

Could it be dangerous trying to kill this process ? If not, what is the best way do to it ? From Sql Server Activity Monitor (spid 24) or from Task Manager ?

Thanks in advance

you should have used Detach /attach method in this scenario. I hope u have kept backup.

(a) Detach db first

(b) copy the files to whereever u want

(c) attach the db

this should work

|||

Thanks for your answer!!

I have a backup, but I can't restore it. If I try to detach the database I get this error:

Cannot detach the database .. because it is currently in use (Sql Server Error 3703).

I can't do anything about this database, I tried to drop it, to open in emergency mode,...but nothing to do.

If I kill the process from Task Manager, do you think I colud damage other databases ?

Thanks

|||

You can't detach database when somebody is connected to it. Kill the process it will not harm... But i repeat again before doing anything on database you should have backup. Its dba mantra... Use SP_Who and see who all are connected to it kill the process and detach and attach.

otherwise you can restore the datbase with some other name using WITH MOVE option.

Madhu

|||

I have a database backup, but I would like to detach the db hanging in recovery, because when I stop and restart Sql Server other databases change their status in recovering...

I executed sp_who and sp_who2 and I get a strange result: the spid 24 (the id I get from error log files) is referred to a different database. Here is the row

CPU TIME

24 BACKGROUND sa . otherdb UNKNOWN TOKEN 13368187 43 01/01 00:00:00

To fix this problem do I have to kill the spid 24, and then detach and attach the db ?

Thanks again

|||

since the spid points to a different db as suggested by Madhu, you can try to detach it now and move the files to diff location and then attach...else try to drop the database and restore from latest backup......if nothing works try to brig the db into emergency mode as below and export the data out to a diff db

alter database dbname set emergency........may be now this command might work as there is no exclusive access to the db........

|||

I have tried to detach the database, but there's no hope, I get the message

Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.

and ALTER DATABASE dbname SET Emergency returns

User does not have permission to alter database dbname or the database does not exist.

SpId24 is a system process, so I can't kill from Management Studio.

Thanks for your replies

|||

try this...

(a) Stop the service

(c) Copy the MDF and LDF file to someother location

(d) Delete the mdf and LDF file from the current location

(e) restart the service

(f) Attach the MDF & LDF file back to server

Madhu

|||

Great!! It's worked!!!

Thanks all for the support

Help!! Database always In Recovery...

Hi all,

I had to change the path of .mdf and .ldf files, so I decided to:

1) Take offline the database

2) run the quey ALTER DATABASE... MODIFY to change the path

3) Bring online the database.

The last step hung up (with no errors) and left the database In Recovery.

When I tried to stop and restart sql server other databases changed their status In Recovery...

Here is a dump of Errorlog files

2007-08-26 18:13:29.28 spid24s Starting up database 'DbOrdini'.

...

...

2007-08-26 18:13:30.09 spid24s * BEGIN STACK DUMP:
2007-08-26 18:13:30.09 spid24s * 08/26/07 18:13:30 spid 24
2007-08-26 18:13:30.09 spid24s *
2007-08-26 18:13:30.09 spid24s * Location: "logmgr.cpp":5334
2007-08-26 18:13:30.09 spid24s * Expression: !(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)
2007-08-26 18:13:30.09 spid24s * SPID: 24
2007-08-26 18:13:30.09 spid24s * Process ID: 1380
.....

.....

2007-08-26 18:13:30.40 spid24s Error: 17066, Severity: 16, State: 1.
2007-08-26 18:13:30.40 spid24s SQL Server Assertion: File: <"logmgr.cpp">, line=5334 Failed Assertion = '!(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
2007-08-26 18:13:30.40 spid24s Error: 3624, Severity: 20, State: 1.
2007-08-26 18:13:30.40 spid24s A system assertion check has failed. Check the SQL Server error log for details

Could it be dangerous trying to kill this process ? If not, what is the best way do to it ? From Sql Server Activity Monitor (spid 24) or from Task Manager ?

Thanks in advance

you should have used Detach /attach method in this scenario. I hope u have kept backup.

(a) Detach db first

(b) copy the files to whereever u want

(c) attach the db

this should work

|||

Thanks for your answer!!

I have a backup, but I can't restore it. If I try to detach the database I get this error:

Cannot detach the database .. because it is currently in use (Sql Server Error 3703).

I can't do anything about this database, I tried to drop it, to open in emergency mode,...but nothing to do.

If I kill the process from Task Manager, do you think I colud damage other databases ?

Thanks

|||

You can't detach database when somebody is connected to it. Kill the process it will not harm... But i repeat again before doing anything on database you should have backup. Its dba mantra... Use SP_Who and see who all are connected to it kill the process and detach and attach.

otherwise you can restore the datbase with some other name using WITH MOVE option.

Madhu

|||

I have a database backup, but I would like to detach the db hanging in recovery, because when I stop and restart Sql Server other databases change their status in recovering...

I executed sp_who and sp_who2 and I get a strange result: the spid 24 (the id I get from error log files) is referred to a different database. Here is the row

CPU TIME

24 BACKGROUND sa . otherdb UNKNOWN TOKEN 13368187 43 01/01 00:00:00

To fix this problem do I have to kill the spid 24, and then detach and attach the db ?

Thanks again

|||

since the spid points to a different db as suggested by Madhu, you can try to detach it now and move the files to diff location and then attach...else try to drop the database and restore from latest backup......if nothing works try to brig the db into emergency mode as below and export the data out to a diff db

alter database dbname set emergency........may be now this command might work as there is no exclusive access to the db........

|||

I have tried to detach the database, but there's no hope, I get the message

Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.

and ALTER DATABASE dbname SET Emergency returns

User does not have permission to alter database dbname or the database does not exist.

SpId24 is a system process, so I can't kill from Management Studio.

Thanks for your replies

|||

try this...

(a) Stop the service

(c) Copy the MDF and LDF file to someother location

(d) Delete the mdf and LDF file from the current location

(e) restart the service

(f) Attach the MDF & LDF file back to server

Madhu

|||

Great!! It's worked!!!

Thanks all for the support

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

Help! MDF and LDF is there but database is gone!

I have been using SQL Server 2000 for a while now but am not an expert on it.
I used the Database Copy Wizard to "copy" about 5 databases to a new server
--at the same time. I have copied several other databases previously with no
problems using this. This time, however, the databases were copied to the
new server but it disappeared on the "source" server. I tried copying them
back into the source server but I get a name conflict error which, from
research, meant the mdf and ldf files of the disappearing db's are still
there! Can you please tell me how I can restore these databases? Thank you
very much.
--
_________________
Freedom is nothing else but a chance to be better. (Albert Camus)Perhaps you detached the database. Check out sp_attach_db in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"betterbest" <betterbest@.discussions.microsoft.com> wrote in message
news:A2D36B41-619D-4032-BA7C-4E6141BDC422@.microsoft.com...
I have been using SQL Server 2000 for a while now but am not an expert on
it.
I used the Database Copy Wizard to "copy" about 5 databases to a new server
--at the same time. I have copied several other databases previously with
no
problems using this. This time, however, the databases were copied to the
new server but it disappeared on the "source" server. I tried copying them
back into the source server but I get a name conflict error which, from
research, meant the mdf and ldf files of the disappearing db's are still
there! Can you please tell me how I can restore these databases? Thank you
very much.
--
_________________
Freedom is nothing else but a chance to be better. (Albert Camus)|||Oops! I accidentally posted same question 3x! Message said question did not
post previously. I deleted the other two on my profile just now, sorry.
Anyway, Tom I did not detach but I think the Copy Database Wizard does that
as part of the process, right? Sometimes our network hiccups and programs
could not access the sql server... would intermittent network disconnects
cause this too? I will look up in BOL and get back. Thanks.
"Tom Moreau" wrote:
> Perhaps you detached the database. Check out sp_attach_db in the BOL.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "betterbest" <betterbest@.discussions.microsoft.com> wrote in message
> news:A2D36B41-619D-4032-BA7C-4E6141BDC422@.microsoft.com...
> I have been using SQL Server 2000 for a while now but am not an expert on
> it.
> I used the Database Copy Wizard to "copy" about 5 databases to a new server
> --at the same time. I have copied several other databases previously with
> no
> problems using this. This time, however, the databases were copied to the
> new server but it disappeared on the "source" server. I tried copying them
> back into the source server but I get a name conflict error which, from
> research, meant the mdf and ldf files of the disappearing db's are still
> there! Can you please tell me how I can restore these databases? Thank you
> very much.
> --
> _________________
> Freedom is nothing else but a chance to be better. (Albert Camus)
>|||sp_attach_db worked! I am concluding that somewhere along the "Copy Database
Wizard" process the database was not re-attached for several possible
reasons. Thank you so much. I'll let Santa do his part on you :)
"betterbest" wrote:
> Oops! I accidentally posted same question 3x! Message said question did not
> post previously. I deleted the other two on my profile just now, sorry.
> Anyway, Tom I did not detach but I think the Copy Database Wizard does that
> as part of the process, right? Sometimes our network hiccups and programs
> could not access the sql server... would intermittent network disconnects
> cause this too? I will look up in BOL and get back. Thanks.
> "Tom Moreau" wrote:
> > Perhaps you detached the database. Check out sp_attach_db in the BOL.
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > "betterbest" <betterbest@.discussions.microsoft.com> wrote in message
> > news:A2D36B41-619D-4032-BA7C-4E6141BDC422@.microsoft.com...
> > I have been using SQL Server 2000 for a while now but am not an expert on
> > it.
> > I used the Database Copy Wizard to "copy" about 5 databases to a new server
> > --at the same time. I have copied several other databases previously with
> > no
> > problems using this. This time, however, the databases were copied to the
> > new server but it disappeared on the "source" server. I tried copying them
> > back into the source server but I get a name conflict error which, from
> > research, meant the mdf and ldf files of the disappearing db's are still
> > there! Can you please tell me how I can restore these databases? Thank you
> > very much.
> > --
> > _________________
> > Freedom is nothing else but a chance to be better. (Albert Camus)
> >
> >|||It sounds odd that the Copy Database Wizard would do such a thing. You may
want to use the profiler to verify that it is doing that. It doesn't seem
like it should.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"betterbest" <betterbest@.discussions.microsoft.com> wrote in message
news:3BFB67FB-21B1-4935-AA30-7BC4FADBA343@.microsoft.com...
sp_attach_db worked! I am concluding that somewhere along the "Copy
Database
Wizard" process the database was not re-attached for several possible
reasons. Thank you so much. I'll let Santa do his part on you :)
"betterbest" wrote:
> Oops! I accidentally posted same question 3x! Message said question did
> not
> post previously. I deleted the other two on my profile just now, sorry.
> Anyway, Tom I did not detach but I think the Copy Database Wizard does
> that
> as part of the process, right? Sometimes our network hiccups and programs
> could not access the sql server... would intermittent network disconnects
> cause this too? I will look up in BOL and get back. Thanks.
> "Tom Moreau" wrote:
> > Perhaps you detached the database. Check out sp_attach_db in the BOL.
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > "betterbest" <betterbest@.discussions.microsoft.com> wrote in message
> > news:A2D36B41-619D-4032-BA7C-4E6141BDC422@.microsoft.com...
> > I have been using SQL Server 2000 for a while now but am not an expert
> > on
> > it.
> > I used the Database Copy Wizard to "copy" about 5 databases to a new
> > server
> > --at the same time. I have copied several other databases previously
> > with
> > no
> > problems using this. This time, however, the databases were copied to
> > the
> > new server but it disappeared on the "source" server. I tried copying
> > them
> > back into the source server but I get a name conflict error which, from
> > research, meant the mdf and ldf files of the disappearing db's are still
> > there! Can you please tell me how I can restore these databases? Thank
> > you
> > very much.
> > --
> > _________________
> > Freedom is nothing else but a chance to be better. (Albert Camus)
> >
> >

Help! MDF and LDF is there but database is gone!

I have been using SQL Server 2000 for a while now but am not an expert on it.
I used the Database Copy Wizard to "copy" about 5 databases to a new server
--at the same time. I have copied several other databases previously with no
problems using this. This time, however, the databases were copied to the
new server but it disappeared on the "source" server. I tried copying them
back into the source server but I get a name conflict error which, from
research, meant the mdf and ldf files of the disappearing db's are still
there! Can you please tell me how I can restore these databases? Thank you
very much.
_________________
Freedom is nothing else but a chance to be better. (Albert Camus)
Perhaps you detached the database. Check out sp_attach_db in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"betterbest" <betterbest@.discussions.microsoft.com> wrote in message
news:A2D36B41-619D-4032-BA7C-4E6141BDC422@.microsoft.com...
I have been using SQL Server 2000 for a while now but am not an expert on
it.
I used the Database Copy Wizard to "copy" about 5 databases to a new server
--at the same time. I have copied several other databases previously with
no
problems using this. This time, however, the databases were copied to the
new server but it disappeared on the "source" server. I tried copying them
back into the source server but I get a name conflict error which, from
research, meant the mdf and ldf files of the disappearing db's are still
there! Can you please tell me how I can restore these databases? Thank you
very much.
_________________
Freedom is nothing else but a chance to be better. (Albert Camus)
|||Oops! I accidentally posted same question 3x! Message said question did not
post previously. I deleted the other two on my profile just now, sorry.
Anyway, Tom I did not detach but I think the Copy Database Wizard does that
as part of the process, right? Sometimes our network hiccups and programs
could not access the sql server... would intermittent network disconnects
cause this too? I will look up in BOL and get back. Thanks.
"Tom Moreau" wrote:

> Perhaps you detached the database. Check out sp_attach_db in the BOL.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "betterbest" <betterbest@.discussions.microsoft.com> wrote in message
> news:A2D36B41-619D-4032-BA7C-4E6141BDC422@.microsoft.com...
> I have been using SQL Server 2000 for a while now but am not an expert on
> it.
> I used the Database Copy Wizard to "copy" about 5 databases to a new server
> --at the same time. I have copied several other databases previously with
> no
> problems using this. This time, however, the databases were copied to the
> new server but it disappeared on the "source" server. I tried copying them
> back into the source server but I get a name conflict error which, from
> research, meant the mdf and ldf files of the disappearing db's are still
> there! Can you please tell me how I can restore these databases? Thank you
> very much.
> --
> _________________
> Freedom is nothing else but a chance to be better. (Albert Camus)
>
|||sp_attach_db worked! I am concluding that somewhere along the "Copy Database
Wizard" process the database was not re-attached for several possible
reasons. Thank you so much. I'll let Santa do his part on you
"betterbest" wrote:
[vbcol=seagreen]
> Oops! I accidentally posted same question 3x! Message said question did not
> post previously. I deleted the other two on my profile just now, sorry.
> Anyway, Tom I did not detach but I think the Copy Database Wizard does that
> as part of the process, right? Sometimes our network hiccups and programs
> could not access the sql server... would intermittent network disconnects
> cause this too? I will look up in BOL and get back. Thanks.
> "Tom Moreau" wrote:
|||It sounds odd that the Copy Database Wizard would do such a thing. You may
want to use the profiler to verify that it is doing that. It doesn't seem
like it should.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"betterbest" <betterbest@.discussions.microsoft.com> wrote in message
news:3BFB67FB-21B1-4935-AA30-7BC4FADBA343@.microsoft.com...
sp_attach_db worked! I am concluding that somewhere along the "Copy
Database
Wizard" process the database was not re-attached for several possible
reasons. Thank you so much. I'll let Santa do his part on you
"betterbest" wrote:
[vbcol=seagreen]
> Oops! I accidentally posted same question 3x! Message said question did
> not
> post previously. I deleted the other two on my profile just now, sorry.
> Anyway, Tom I did not detach but I think the Copy Database Wizard does
> that
> as part of the process, right? Sometimes our network hiccups and programs
> could not access the sql server... would intermittent network disconnects
> cause this too? I will look up in BOL and get back. Thanks.
> "Tom Moreau" wrote:

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).

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).

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).

HELP! LOG file has grown too large

I'm a newbie so please bear with me.
We have a database where the MDF is 2.5 GB in size but the LDF has grown to
45GB (the maintenance plan had included a complete backup but no Transaction
Log Backup and I presume this is why the log file has been bloating out of
proportion during the last months)
Now the disk is approaching full capacity and I'm worried about the
consequences.
In order to remedy this situation I would like to do the following:
Do a full backup of the database, detach the database, delete the log file
and then reattach the database to create a new normal sized logfile.
Can someone tell me if this is the best way to achieve this without
compromising the database?
I've tried DBCC SHRINKFILE (database_log, 2) in query analyser but get the
error message "Incorrect syntax near '_log' " and don't know how to work
round this.
I've also tried selecting 'shrink database...' in Enterprise Manager but
that doesn't make any difference to the log file size.
I know that in MS Exchange, you can get rid of all the log files simply by
performing a full backup of the exchange database. Doesn't it work the same
in SQL server?
Some downtime for the database is permissable on this network.
any help is greatly appreciated
ericBackup Transaction Log. with Tuncate Only param
And try to schedule Log Backup at least once a day
"Eric" <eric@.anon.com> wrote in message
news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm a newbie so please bear with me.
> We have a database where the MDF is 2.5 GB in size but the LDF has grown
to
> 45GB (the maintenance plan had included a complete backup but no
Transaction
> Log Backup and I presume this is why the log file has been bloating out of
> proportion during the last months)
> Now the disk is approaching full capacity and I'm worried about the
> consequences.
> In order to remedy this situation I would like to do the following:
> Do a full backup of the database, detach the database, delete the log file
> and then reattach the database to create a new normal sized logfile.
> Can someone tell me if this is the best way to achieve this without
> compromising the database?
> I've tried DBCC SHRINKFILE (database_log, 2) in query analyser but get the
> error message "Incorrect syntax near '_log' " and don't know how to work
> round this.
> I've also tried selecting 'shrink database...' in Enterprise Manager but
> that doesn't make any difference to the log file size.
> I know that in MS Exchange, you can get rid of all the log files simply by
> performing a full backup of the exchange database. Doesn't it work the
same
> in SQL server?
> Some downtime for the database is permissable on this network.
> any help is greatly appreciated
> eric
>|||If your database in FULL RECOVERY so
perform BACKUP LOG file and then do DBCC SHRINKFILE(...) otherwise detach
the database and remove the log ans re-attach it
"Eric" <eric@.anon.com> wrote in message
news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm a newbie so please bear with me.
> We have a database where the MDF is 2.5 GB in size but the LDF has grown
to
> 45GB (the maintenance plan had included a complete backup but no
Transaction
> Log Backup and I presume this is why the log file has been bloating out of
> proportion during the last months)
> Now the disk is approaching full capacity and I'm worried about the
> consequences.
> In order to remedy this situation I would like to do the following:
> Do a full backup of the database, detach the database, delete the log file
> and then reattach the database to create a new normal sized logfile.
> Can someone tell me if this is the best way to achieve this without
> compromising the database?
> I've tried DBCC SHRINKFILE (database_log, 2) in query analyser but get the
> error message "Incorrect syntax near '_log' " and don't know how to work
> round this.
> I've also tried selecting 'shrink database...' in Enterprise Manager but
> that doesn't make any difference to the log file size.
> I know that in MS Exchange, you can get rid of all the log files simply by
> performing a full backup of the exchange database. Doesn't it work the
same
> in SQL server?
> Some downtime for the database is permissable on this network.
> any help is greatly appreciated
> eric
>|||> Can someone tell me if this is the best way to achieve this without
> compromising the database?
No! You risk that the database is corrupt (suspect) when you start SQL Server. It you aren't
prepared to restore from a backup, don't do this!!!
I have an article about shrink database files. In about the middle of this article, I have some
links to KB articles etc. Read them. They have all the information you need.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> I know that in MS Exchange, you can get rid of all the log files simply by
> performing a full backup of the exchange database. Doesn't it work the same
> in SQL server?
No. A database backup does not empty the transaction log file(s).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eric" <eric@.anon.com> wrote in message news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm a newbie so please bear with me.
> We have a database where the MDF is 2.5 GB in size but the LDF has grown to
> 45GB (the maintenance plan had included a complete backup but no Transaction
> Log Backup and I presume this is why the log file has been bloating out of
> proportion during the last months)
> Now the disk is approaching full capacity and I'm worried about the
> consequences.
> In order to remedy this situation I would like to do the following:
> Do a full backup of the database, detach the database, delete the log file
> and then reattach the database to create a new normal sized logfile.
> Can someone tell me if this is the best way to achieve this without
> compromising the database?
> I've tried DBCC SHRINKFILE (database_log, 2) in query analyser but get the
> error message "Incorrect syntax near '_log' " and don't know how to work
> round this.
> I've also tried selecting 'shrink database...' in Enterprise Manager but
> that doesn't make any difference to the log file size.
> I know that in MS Exchange, you can get rid of all the log files simply by
> performing a full backup of the exchange database. Doesn't it work the same
> in SQL server?
> Some downtime for the database is permissable on this network.
> any help is greatly appreciated
> eric
>|||But if I execute 'BACKUP LOG databasename WITH TRUNCATE_ONLY' in query
analyser, I get a 'incorrect syntax near databasename' error message.
any ideas?
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:udyGj#kwEHA.4028@.TK2MSFTNGP15.phx.gbl...
> Backup Transaction Log. with Tuncate Only param
> And try to schedule Log Backup at least once a day
>
> "Eric" <eric@.anon.com> wrote in message
> news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...|||BACKUP LOG ecatalog WITH TRUNCATE_ONLY
I just Try this and it works fine...
What is ur SQL version.. ( it works fine with SQL 7 and 2000)
"Eric" <eric@.anon.com> wrote in message
news:%23MZFxGlwEHA.1192@.tk2msftngp13.phx.gbl...
> But if I execute 'BACKUP LOG databasename WITH TRUNCATE_ONLY' in query
> analyser, I get a 'incorrect syntax near databasename' error message.
> any ideas?
>
> "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> news:udyGj#kwEHA.4028@.TK2MSFTNGP15.phx.gbl...
>
> > Backup Transaction Log. with Tuncate Only param
> >
> > And try to schedule Log Backup at least once a day
> >
> >
> > "Eric" <eric@.anon.com> wrote in message
> > news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
>|||Thanks Tibor,
but when I perform a DBCC SHRINKFILE I just get an error message 'incorrect
syntax near...'
What am I doing wrong?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OmiAJBlwEHA.3416@.TK2MSFTNGP09.phx.gbl...
> > Can someone tell me if this is the best way to achieve this without
> > compromising the database?
> No! You risk that the database is corrupt (suspect) when you start SQL
Server. It you aren't
> prepared to restore from a backup, don't do this!!!
> I have an article about shrink database files. In about the middle of this
article, I have some
> links to KB articles etc. Read them. They have all the information you
need.
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> > I know that in MS Exchange, you can get rid of all the log files simply
by
> > performing a full backup of the exchange database. Doesn't it work the
same
> > in SQL server?
> No. A database backup does not empty the transaction log file(s).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eric" <eric@.anon.com> wrote in message
news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> > I'm a newbie so please bear with me.
> >
> > We have a database where the MDF is 2.5 GB in size but the LDF has grown
to
> > 45GB (the maintenance plan had included a complete backup but no
Transaction
> > Log Backup and I presume this is why the log file has been bloating out
of
> > proportion during the last months)
> > Now the disk is approaching full capacity and I'm worried about the
> > consequences.
> >
> > In order to remedy this situation I would like to do the following:
> > Do a full backup of the database, detach the database, delete the log
file
> > and then reattach the database to create a new normal sized logfile.
> > Can someone tell me if this is the best way to achieve this without
> > compromising the database?
> > I've tried DBCC SHRINKFILE (database_log, 2) in query analyser but get
the
> > error message "Incorrect syntax near '_log' " and don't know how to work
> > round this.
> > I've also tried selecting 'shrink database...' in Enterprise Manager but
> > that doesn't make any difference to the log file size.
> >
> > I know that in MS Exchange, you can get rid of all the log files simply
by
> > performing a full backup of the exchange database. Doesn't it work the
same
> > in SQL server?
> >
> > Some downtime for the database is permissable on this network.
> >
> > any help is greatly appreciated
> >
> > eric
> >
> >
>|||SQL2000 with SP3
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OjxQsNlwEHA.1976@.TK2MSFTNGP09.phx.gbl...
> BACKUP LOG ecatalog WITH TRUNCATE_ONLY
> I just Try this and it works fine...
> What is ur SQL version.. ( it works fine with SQL 7 and 2000)
>
> "Eric" <eric@.anon.com> wrote in message
> news:%23MZFxGlwEHA.1192@.tk2msftngp13.phx.gbl...
> > But if I execute 'BACKUP LOG databasename WITH TRUNCATE_ONLY' in query
> > analyser, I get a 'incorrect syntax near databasename' error message.
> >
> > any ideas?
> >
> >
> > "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> > news:udyGj#kwEHA.4028@.TK2MSFTNGP15.phx.gbl...
> >
> >
> > > Backup Transaction Log. with Tuncate Only param
> > >
> > > And try to schedule Log Backup at least once a day
> > >
> > >
> > > "Eric" <eric@.anon.com> wrote in message
> > > news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> >
> >
>|||What is your database compatibility level?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eric" <eric@.anon.com> wrote in message news:OMxCiPlwEHA.2196@.TK2MSFTNGP14.phx.gbl...
> Thanks Tibor,
> but when I perform a DBCC SHRINKFILE I just get an error message 'incorrect
> syntax near...'
> What am I doing wrong?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OmiAJBlwEHA.3416@.TK2MSFTNGP09.phx.gbl...
> > > Can someone tell me if this is the best way to achieve this without
> > > compromising the database?
> >
> > No! You risk that the database is corrupt (suspect) when you start SQL
> Server. It you aren't
> > prepared to restore from a backup, don't do this!!!
> >
> > I have an article about shrink database files. In about the middle of this
> article, I have some
> > links to KB articles etc. Read them. They have all the information you
> need.
> > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >
> >
> > > I know that in MS Exchange, you can get rid of all the log files simply
> by
> > > performing a full backup of the exchange database. Doesn't it work the
> same
> > > in SQL server?
> >
> > No. A database backup does not empty the transaction log file(s).
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Eric" <eric@.anon.com> wrote in message
> news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> > > I'm a newbie so please bear with me.
> > >
> > > We have a database where the MDF is 2.5 GB in size but the LDF has grown
> to
> > > 45GB (the maintenance plan had included a complete backup but no
> Transaction
> > > Log Backup and I presume this is why the log file has been bloating out
> of
> > > proportion during the last months)
> > > Now the disk is approaching full capacity and I'm worried about the
> > > consequences.
> > >
> > > In order to remedy this situation I would like to do the following:
> > > Do a full backup of the database, detach the database, delete the log
> file
> > > and then reattach the database to create a new normal sized logfile.
> > > Can someone tell me if this is the best way to achieve this without
> > > compromising the database?
> > > I've tried DBCC SHRINKFILE (database_log, 2) in query analyser but get
> the
> > > error message "Incorrect syntax near '_log' " and don't know how to work
> > > round this.
> > > I've also tried selecting 'shrink database...' in Enterprise Manager but
> > > that doesn't make any difference to the log file size.
> > >
> > > I know that in MS Exchange, you can get rid of all the log files simply
> by
> > > performing a full backup of the exchange database. Doesn't it work the
> same
> > > in SQL server?
> > >
> > > Some downtime for the database is permissable on this network.
> > >
> > > any help is greatly appreciated
> > >
> > > eric
> > >
> > >
> >
> >
>|||80
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#pRS$olwEHA.392@.TK2MSFTNGP12.phx.gbl...
> What is your database compatibility level?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eric" <eric@.anon.com> wrote in message
news:OMxCiPlwEHA.2196@.TK2MSFTNGP14.phx.gbl...
> > Thanks Tibor,
> > but when I perform a DBCC SHRINKFILE I just get an error message
'incorrect
> > syntax near...'
> > What am I doing wrong?
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:OmiAJBlwEHA.3416@.TK2MSFTNGP09.phx.gbl...
> > > > Can someone tell me if this is the best way to achieve this without
> > > > compromising the database?
> > >
> > > No! You risk that the database is corrupt (suspect) when you start SQL
> > Server. It you aren't
> > > prepared to restore from a backup, don't do this!!!
> > >
> > > I have an article about shrink database files. In about the middle of
this
> > article, I have some
> > > links to KB articles etc. Read them. They have all the information you
> > need.
> > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> > >
> > >
> > > > I know that in MS Exchange, you can get rid of all the log files
simply
> > by
> > > > performing a full backup of the exchange database. Doesn't it work
the
> > same
> > > > in SQL server?
> > >
> > > No. A database backup does not empty the transaction log file(s).
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Eric" <eric@.anon.com> wrote in message
> > news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> > > > I'm a newbie so please bear with me.
> > > >
> > > > We have a database where the MDF is 2.5 GB in size but the LDF has
grown
> > to
> > > > 45GB (the maintenance plan had included a complete backup but no
> > Transaction
> > > > Log Backup and I presume this is why the log file has been bloating
out
> > of
> > > > proportion during the last months)
> > > > Now the disk is approaching full capacity and I'm worried about the
> > > > consequences.
> > > >
> > > > In order to remedy this situation I would like to do the following:
> > > > Do a full backup of the database, detach the database, delete the
log
> > file
> > > > and then reattach the database to create a new normal sized logfile.
> > > > Can someone tell me if this is the best way to achieve this without
> > > > compromising the database?
> > > > I've tried DBCC SHRINKFILE (database_log, 2) in query analyser but
get
> > the
> > > > error message "Incorrect syntax near '_log' " and don't know how to
work
> > > > round this.
> > > > I've also tried selecting 'shrink database...' in Enterprise Manager
but
> > > > that doesn't make any difference to the log file size.
> > > >
> > > > I know that in MS Exchange, you can get rid of all the log files
simply
> > by
> > > > performing a full backup of the exchange database. Doesn't it work
the
> > same
> > > > in SQL server?
> > > >
> > > > Some downtime for the database is permissable on this network.
> > > >
> > > > any help is greatly appreciated
> > > >
> > > > eric
> > > >
> > > >
> > >
> > >
> >
> >
>|||can u post the SQL statement u try.. copy and paste it.. and also the error
message.. copy everything on Analyzer
"Eric" <eric@.anon.com> wrote in message
news:uquiVdlwEHA.1512@.TK2MSFTNGP12.phx.gbl...
> SQL2000 with SP3
>
> "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> news:OjxQsNlwEHA.1976@.TK2MSFTNGP09.phx.gbl...
> > BACKUP LOG ecatalog WITH TRUNCATE_ONLY
> >
> > I just Try this and it works fine...
> >
> > What is ur SQL version.. ( it works fine with SQL 7 and 2000)
> >
> >
> > "Eric" <eric@.anon.com> wrote in message
> > news:%23MZFxGlwEHA.1192@.tk2msftngp13.phx.gbl...
> > > But if I execute 'BACKUP LOG databasename WITH TRUNCATE_ONLY' in query
> > > analyser, I get a 'incorrect syntax near databasename' error message.
> > >
> > > any ideas?
> > >
> > >
> > > "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> > > news:udyGj#kwEHA.4028@.TK2MSFTNGP15.phx.gbl...
> > >
> > >
> > > > Backup Transaction Log. with Tuncate Only param
> > > >
> > > > And try to schedule Log Backup at least once a day
> > > >
> > > >
> > > > "Eric" <eric@.anon.com> wrote in message
> > > > news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> > >
> > >
> >
> >
>|||I agree with Melih,
Please post exact commands you try to execute and exact error message.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eric" <eric@.anon.com> wrote in message news:elU3HvlwEHA.4028@.TK2MSFTNGP15.phx.gbl...
> 80
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#pRS$olwEHA.392@.TK2MSFTNGP12.phx.gbl...
> > What is your database compatibility level?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Eric" <eric@.anon.com> wrote in message
> news:OMxCiPlwEHA.2196@.TK2MSFTNGP14.phx.gbl...
> > > Thanks Tibor,
> > > but when I perform a DBCC SHRINKFILE I just get an error message
> 'incorrect
> > > syntax near...'
> > > What am I doing wrong?
> > >
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > > message news:OmiAJBlwEHA.3416@.TK2MSFTNGP09.phx.gbl...
> > > > > Can someone tell me if this is the best way to achieve this without
> > > > > compromising the database?
> > > >
> > > > No! You risk that the database is corrupt (suspect) when you start SQL
> > > Server. It you aren't
> > > > prepared to restore from a backup, don't do this!!!
> > > >
> > > > I have an article about shrink database files. In about the middle of
> this
> > > article, I have some
> > > > links to KB articles etc. Read them. They have all the information you
> > > need.
> > > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> > > >
> > > >
> > > > > I know that in MS Exchange, you can get rid of all the log files
> simply
> > > by
> > > > > performing a full backup of the exchange database. Doesn't it work
> the
> > > same
> > > > > in SQL server?
> > > >
> > > > No. A database backup does not empty the transaction log file(s).
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "Eric" <eric@.anon.com> wrote in message
> > > news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> > > > > I'm a newbie so please bear with me.
> > > > >
> > > > > We have a database where the MDF is 2.5 GB in size but the LDF has
> grown
> > > to
> > > > > 45GB (the maintenance plan had included a complete backup but no
> > > Transaction
> > > > > Log Backup and I presume this is why the log file has been bloating
> out
> > > of
> > > > > proportion during the last months)
> > > > > Now the disk is approaching full capacity and I'm worried about the
> > > > > consequences.
> > > > >
> > > > > In order to remedy this situation I would like to do the following:
> > > > > Do a full backup of the database, detach the database, delete the
> log
> > > file
> > > > > and then reattach the database to create a new normal sized logfile.
> > > > > Can someone tell me if this is the best way to achieve this without
> > > > > compromising the database?
> > > > > I've tried DBCC SHRINKFILE (database_log, 2) in query analyser but
> get
> > > the
> > > > > error message "Incorrect syntax near '_log' " and don't know how to
> work
> > > > > round this.
> > > > > I've also tried selecting 'shrink database...' in Enterprise Manager
> but
> > > > > that doesn't make any difference to the log file size.
> > > > >
> > > > > I know that in MS Exchange, you can get rid of all the log files
> simply
> > > by
> > > > > performing a full backup of the exchange database. Doesn't it work
> the
> > > same
> > > > > in SQL server?
> > > > >
> > > > > Some downtime for the database is permissable on this network.
> > > > >
> > > > > any help is greatly appreciated
> > > > >
> > > > > eric
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||SQL Statement:
BACKUP LOG 107 WITH TRUNCATE_ONLY
Error message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '107'.
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:eZxmDzlwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> can u post the SQL statement u try.. copy and paste it.. and also the
error
> message.. copy everything on Analyzer
>
> "Eric" <eric@.anon.com> wrote in message
> news:uquiVdlwEHA.1512@.TK2MSFTNGP12.phx.gbl...
> > SQL2000 with SP3
> >
> >
> > "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> > news:OjxQsNlwEHA.1976@.TK2MSFTNGP09.phx.gbl...
> > > BACKUP LOG ecatalog WITH TRUNCATE_ONLY
> > >
> > > I just Try this and it works fine...
> > >
> > > What is ur SQL version.. ( it works fine with SQL 7 and 2000)
> > >
> > >
> > > "Eric" <eric@.anon.com> wrote in message
> > > news:%23MZFxGlwEHA.1192@.tk2msftngp13.phx.gbl...
> > > > But if I execute 'BACKUP LOG databasename WITH TRUNCATE_ONLY' in
query
> > > > analyser, I get a 'incorrect syntax near databasename' error
message.
> > > >
> > > > any ideas?
> > > >
> > > >
> > > > "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> > > > news:udyGj#kwEHA.4028@.TK2MSFTNGP15.phx.gbl...
> > > >
> > > >
> > > > > Backup Transaction Log. with Tuncate Only param
> > > > >
> > > > > And try to schedule Log Backup at least once a day
> > > > >
> > > > >
> > > > > "Eric" <eric@.anon.com> wrote in message
> > > > > news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> > > >
> > > >
> > >
> > >
> >
> >
>|||SQL Statement:
DBCC SHRINKFILE(107_log, 2)
Error message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '_log'.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#tM9T2lwEHA.3144@.TK2MSFTNGP15.phx.gbl...
> I agree with Melih,
> Please post exact commands you try to execute and exact error message.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eric" <eric@.anon.com> wrote in message
news:elU3HvlwEHA.4028@.TK2MSFTNGP15.phx.gbl...
> > 80
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:#pRS$olwEHA.392@.TK2MSFTNGP12.phx.gbl...
> > > What is your database compatibility level?
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Eric" <eric@.anon.com> wrote in message
> > news:OMxCiPlwEHA.2196@.TK2MSFTNGP14.phx.gbl...
> > > > Thanks Tibor,
> > > > but when I perform a DBCC SHRINKFILE I just get an error message
> > 'incorrect
> > > > syntax near...'
> > > > What am I doing wrong?
> > > >
> > > >
> > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> > > > message news:OmiAJBlwEHA.3416@.TK2MSFTNGP09.phx.gbl...
> > > > > > Can someone tell me if this is the best way to achieve this
without
> > > > > > compromising the database?
> > > > >
> > > > > No! You risk that the database is corrupt (suspect) when you start
SQL
> > > > Server. It you aren't
> > > > > prepared to restore from a backup, don't do this!!!
> > > > >
> > > > > I have an article about shrink database files. In about the middle
of
> > this
> > > > article, I have some
> > > > > links to KB articles etc. Read them. They have all the information
you
> > > > need.
> > > > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> > > > >
> > > > >
> > > > > > I know that in MS Exchange, you can get rid of all the log files
> > simply
> > > > by
> > > > > > performing a full backup of the exchange database. Doesn't it
work
> > the
> > > > same
> > > > > > in SQL server?
> > > > >
> > > > > No. A database backup does not empty the transaction log file(s).
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > http://www.solidqualitylearning.com/
> > > > >
> > > > >
> > > > > "Eric" <eric@.anon.com> wrote in message
> > > > news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> > > > > > I'm a newbie so please bear with me.
> > > > > >
> > > > > > We have a database where the MDF is 2.5 GB in size but the LDF
has
> > grown
> > > > to
> > > > > > 45GB (the maintenance plan had included a complete backup but no
> > > > Transaction
> > > > > > Log Backup and I presume this is why the log file has been
bloating
> > out
> > > > of
> > > > > > proportion during the last months)
> > > > > > Now the disk is approaching full capacity and I'm worried about
the
> > > > > > consequences.
> > > > > >
> > > > > > In order to remedy this situation I would like to do the
following:
> > > > > > Do a full backup of the database, detach the database, delete
the
> > log
> > > > file
> > > > > > and then reattach the database to create a new normal sized
logfile.
> > > > > > Can someone tell me if this is the best way to achieve this
without
> > > > > > compromising the database?
> > > > > > I've tried DBCC SHRINKFILE (database_log, 2) in query analyser
but
> > get
> > > > the
> > > > > > error message "Incorrect syntax near '_log' " and don't know how
to
> > work
> > > > > > round this.
> > > > > > I've also tried selecting 'shrink database...' in Enterprise
Manager
> > but
> > > > > > that doesn't make any difference to the log file size.
> > > > > >
> > > > > > I know that in MS Exchange, you can get rid of all the log files
> > simply
> > > > by
> > > > > > performing a full backup of the exchange database. Doesn't it
work
> > the
> > > > same
> > > > > > in SQL server?
> > > > > >
> > > > > > Some downtime for the database is permissable on this network.
> > > > > >
> > > > > > any help is greatly appreciated
> > > > > >
> > > > > > eric
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||It is not recommended to use such names (107) for databases, tables etc. I suggest you read in the
documentation about the rules for "standard identifier" and try to name your database, tables etc
according to those rules. If you don't follow the rules, you need to enclose the name in either
double.quotes or square brackets:
BACKUP LOG "107" WITH TRUNCATE_ONLY
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eric" <eric@.anon.com> wrote in message news:O%23TrH5lwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> SQL Statement:
> BACKUP LOG 107 WITH TRUNCATE_ONLY
> Error message:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '107'.
>
> "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> news:eZxmDzlwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> > can u post the SQL statement u try.. copy and paste it.. and also the
> error
> > message.. copy everything on Analyzer
> >
> >
> > "Eric" <eric@.anon.com> wrote in message
> > news:uquiVdlwEHA.1512@.TK2MSFTNGP12.phx.gbl...
> > > SQL2000 with SP3
> > >
> > >
> > > "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> > > news:OjxQsNlwEHA.1976@.TK2MSFTNGP09.phx.gbl...
> > > > BACKUP LOG ecatalog WITH TRUNCATE_ONLY
> > > >
> > > > I just Try this and it works fine...
> > > >
> > > > What is ur SQL version.. ( it works fine with SQL 7 and 2000)
> > > >
> > > >
> > > > "Eric" <eric@.anon.com> wrote in message
> > > > news:%23MZFxGlwEHA.1192@.tk2msftngp13.phx.gbl...
> > > > > But if I execute 'BACKUP LOG databasename WITH TRUNCATE_ONLY' in
> query
> > > > > analyser, I get a 'incorrect syntax near databasename' error
> message.
> > > > >
> > > > > any ideas?
> > > > >
> > > > >
> > > > > "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> > > > > news:udyGj#kwEHA.4028@.TK2MSFTNGP15.phx.gbl...
> > > > >
> > > > >
> > > > > > Backup Transaction Log. with Tuncate Only param
> > > > > >
> > > > > > And try to schedule Log Backup at least once a day
> > > > > >
> > > > > >
> > > > > > "Eric" <eric@.anon.com> wrote in message
> > > > > > news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks Tibor,
That was the solution for the BACKUP LOG statement.
But it doesn't seem to work for the SHRINKFILE statement.
Any ideas on that one?
I posted the exact statement and error message on the other thread
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oiqjk8lwEHA.3624@.TK2MSFTNGP09.phx.gbl...
> It is not recommended to use such names (107) for databases, tables etc. I
suggest you read in the
> documentation about the rules for "standard identifier" and try to name
your database, tables etc
> according to those rules. If you don't follow the rules, you need to
enclose the name in either
> double.quotes or square brackets:
> BACKUP LOG "107" WITH TRUNCATE_ONLY
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eric" <eric@.anon.com> wrote in message
news:O%23TrH5lwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> > SQL Statement:
> > BACKUP LOG 107 WITH TRUNCATE_ONLY
> >
> > Error message:
> > Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near '107'.
> >
> >
> > "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> > news:eZxmDzlwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> > > can u post the SQL statement u try.. copy and paste it.. and also the
> > error
> > > message.. copy everything on Analyzer
> > >
> > >
> > > "Eric" <eric@.anon.com> wrote in message
> > > news:uquiVdlwEHA.1512@.TK2MSFTNGP12.phx.gbl...
> > > > SQL2000 with SP3
> > > >
> > > >
> > > > "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> > > > news:OjxQsNlwEHA.1976@.TK2MSFTNGP09.phx.gbl...
> > > > > BACKUP LOG ecatalog WITH TRUNCATE_ONLY
> > > > >
> > > > > I just Try this and it works fine...
> > > > >
> > > > > What is ur SQL version.. ( it works fine with SQL 7 and 2000)
> > > > >
> > > > >
> > > > > "Eric" <eric@.anon.com> wrote in message
> > > > > news:%23MZFxGlwEHA.1192@.tk2msftngp13.phx.gbl...
> > > > > > But if I execute 'BACKUP LOG databasename WITH TRUNCATE_ONLY' in
> > query
> > > > > > analyser, I get a 'incorrect syntax near databasename' error
> > message.
> > > > > >
> > > > > > any ideas?
> > > > > >
> > > > > >
> > > > > > "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> > > > > > news:udyGj#kwEHA.4028@.TK2MSFTNGP15.phx.gbl...
> > > > > >
> > > > > >
> > > > > > > Backup Transaction Log. with Tuncate Only param
> > > > > > >
> > > > > > > And try to schedule Log Backup at least once a day
> > > > > > >
> > > > > > >
> > > > > > > "Eric" <eric@.anon.com> wrote in message
> > > > > > > news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||DBCC SHRINKFILE([107_log], 2)
"Eric" <eric@.anon.com> wrote in message
news:euD%23R7lwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> SQL Statement:
> DBCC SHRINKFILE(107_log, 2)
>
> Error message:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '_log'.
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:#tM9T2lwEHA.3144@.TK2MSFTNGP15.phx.gbl...
> > I agree with Melih,
> >
> > Please post exact commands you try to execute and exact error message.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Eric" <eric@.anon.com> wrote in message
> news:elU3HvlwEHA.4028@.TK2MSFTNGP15.phx.gbl...
> > > 80
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> in
> > > message news:#pRS$olwEHA.392@.TK2MSFTNGP12.phx.gbl...
> > > > What is your database compatibility level?
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "Eric" <eric@.anon.com> wrote in message
> > > news:OMxCiPlwEHA.2196@.TK2MSFTNGP14.phx.gbl...
> > > > > Thanks Tibor,
> > > > > but when I perform a DBCC SHRINKFILE I just get an error message
> > > 'incorrect
> > > > > syntax near...'
> > > > > What am I doing wrong?
> > > > >
> > > > >
> > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote
> > > in
> > > > > message news:OmiAJBlwEHA.3416@.TK2MSFTNGP09.phx.gbl...
> > > > > > > Can someone tell me if this is the best way to achieve this
> without
> > > > > > > compromising the database?
> > > > > >
> > > > > > No! You risk that the database is corrupt (suspect) when you
start
> SQL
> > > > > Server. It you aren't
> > > > > > prepared to restore from a backup, don't do this!!!
> > > > > >
> > > > > > I have an article about shrink database files. In about the
middle
> of
> > > this
> > > > > article, I have some
> > > > > > links to KB articles etc. Read them. They have all the
information
> you
> > > > > need.
> > > > > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> > > > > >
> > > > > >
> > > > > > > I know that in MS Exchange, you can get rid of all the log
files
> > > simply
> > > > > by
> > > > > > > performing a full backup of the exchange database. Doesn't it
> work
> > > the
> > > > > same
> > > > > > > in SQL server?
> > > > > >
> > > > > > No. A database backup does not empty the transaction log
file(s).
> > > > > > --
> > > > > > Tibor Karaszi, SQL Server MVP
> > > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > > http://www.solidqualitylearning.com/
> > > > > >
> > > > > >
> > > > > > "Eric" <eric@.anon.com> wrote in message
> > > > > news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> > > > > > > I'm a newbie so please bear with me.
> > > > > > >
> > > > > > > We have a database where the MDF is 2.5 GB in size but the LDF
> has
> > > grown
> > > > > to
> > > > > > > 45GB (the maintenance plan had included a complete backup but
no
> > > > > Transaction
> > > > > > > Log Backup and I presume this is why the log file has been
> bloating
> > > out
> > > > > of
> > > > > > > proportion during the last months)
> > > > > > > Now the disk is approaching full capacity and I'm worried
about
> the
> > > > > > > consequences.
> > > > > > >
> > > > > > > In order to remedy this situation I would like to do the
> following:
> > > > > > > Do a full backup of the database, detach the database, delete
> the
> > > log
> > > > > file
> > > > > > > and then reattach the database to create a new normal sized
> logfile.
> > > > > > > Can someone tell me if this is the best way to achieve this
> without
> > > > > > > compromising the database?
> > > > > > > I've tried DBCC SHRINKFILE (database_log, 2) in query analyser
> but
> > > get
> > > > > the
> > > > > > > error message "Incorrect syntax near '_log' " and don't know
how
> to
> > > work
> > > > > > > round this.
> > > > > > > I've also tried selecting 'shrink database...' in Enterprise
> Manager
> > > but
> > > > > > > that doesn't make any difference to the log file size.
> > > > > > >
> > > > > > > I know that in MS Exchange, you can get rid of all the log
files
> > > simply
> > > > > by
> > > > > > > performing a full backup of the exchange database. Doesn't it
> work
> > > the
> > > > > same
> > > > > > > in SQL server?
> > > > > > >
> > > > > > > Some downtime for the database is permissable on this network.
> > > > > > >
> > > > > > > any help is greatly appreciated
> > > > > > >
> > > > > > > eric
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks Tibor and Melih.
You guys are stars!
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:#Da6WMmwEHA.1404@.TK2MSFTNGP11.phx.gbl...
> DBCC SHRINKFILE([107_log], 2)
>
> "Eric" <eric@.anon.com> wrote in message
> news:euD%23R7lwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> > SQL Statement:
> > DBCC SHRINKFILE(107_log, 2)
> >
> >
> > Error message:
> > Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near '_log'.
> >
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > message news:#tM9T2lwEHA.3144@.TK2MSFTNGP15.phx.gbl...
> > > I agree with Melih,
> > >
> > > Please post exact commands you try to execute and exact error message.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Eric" <eric@.anon.com> wrote in message
> > news:elU3HvlwEHA.4028@.TK2MSFTNGP15.phx.gbl...
> > > > 80
> > > >
> > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote
> > in
> > > > message news:#pRS$olwEHA.392@.TK2MSFTNGP12.phx.gbl...
> > > > > What is your database compatibility level?
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > http://www.solidqualitylearning.com/
> > > > >
> > > > >
> > > > > "Eric" <eric@.anon.com> wrote in message
> > > > news:OMxCiPlwEHA.2196@.TK2MSFTNGP14.phx.gbl...
> > > > > > Thanks Tibor,
> > > > > > but when I perform a DBCC SHRINKFILE I just get an error message
> > > > 'incorrect
> > > > > > syntax near...'
> > > > > > What am I doing wrong?
> > > > > >
> > > > > >
> > > > > > "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com>
> > wrote
> > > > in
> > > > > > message news:OmiAJBlwEHA.3416@.TK2MSFTNGP09.phx.gbl...
> > > > > > > > Can someone tell me if this is the best way to achieve this
> > without
> > > > > > > > compromising the database?
> > > > > > >
> > > > > > > No! You risk that the database is corrupt (suspect) when you
> start
> > SQL
> > > > > > Server. It you aren't
> > > > > > > prepared to restore from a backup, don't do this!!!
> > > > > > >
> > > > > > > I have an article about shrink database files. In about the
> middle
> > of
> > > > this
> > > > > > article, I have some
> > > > > > > links to KB articles etc. Read them. They have all the
> information
> > you
> > > > > > need.
> > > > > > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> > > > > > >
> > > > > > >
> > > > > > > > I know that in MS Exchange, you can get rid of all the log
> files
> > > > simply
> > > > > > by
> > > > > > > > performing a full backup of the exchange database. Doesn't
it
> > work
> > > > the
> > > > > > same
> > > > > > > > in SQL server?
> > > > > > >
> > > > > > > No. A database backup does not empty the transaction log
> file(s).
> > > > > > > --
> > > > > > > Tibor Karaszi, SQL Server MVP
> > > > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > > > http://www.solidqualitylearning.com/
> > > > > > >
> > > > > > >
> > > > > > > "Eric" <eric@.anon.com> wrote in message
> > > > > > news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> > > > > > > > I'm a newbie so please bear with me.
> > > > > > > >
> > > > > > > > We have a database where the MDF is 2.5 GB in size but the
LDF
> > has
> > > > grown
> > > > > > to
> > > > > > > > 45GB (the maintenance plan had included a complete backup
but
> no
> > > > > > Transaction
> > > > > > > > Log Backup and I presume this is why the log file has been
> > bloating
> > > > out
> > > > > > of
> > > > > > > > proportion during the last months)
> > > > > > > > Now the disk is approaching full capacity and I'm worried
> about
> > the
> > > > > > > > consequences.
> > > > > > > >
> > > > > > > > In order to remedy this situation I would like to do the
> > following:
> > > > > > > > Do a full backup of the database, detach the database,
delete
> > the
> > > > log
> > > > > > file
> > > > > > > > and then reattach the database to create a new normal sized
> > logfile.
> > > > > > > > Can someone tell me if this is the best way to achieve this
> > without
> > > > > > > > compromising the database?
> > > > > > > > I've tried DBCC SHRINKFILE (database_log, 2) in query
analyser
> > but
> > > > get
> > > > > > the
> > > > > > > > error message "Incorrect syntax near '_log' " and don't know
> how
> > to
> > > > work
> > > > > > > > round this.
> > > > > > > > I've also tried selecting 'shrink database...' in Enterprise
> > Manager
> > > > but
> > > > > > > > that doesn't make any difference to the log file size.
> > > > > > > >
> > > > > > > > I know that in MS Exchange, you can get rid of all the log
> files
> > > > simply
> > > > > > by
> > > > > > > > performing a full backup of the exchange database. Doesn't
it
> > work
> > > > the
> > > > > > same
> > > > > > > > in SQL server?
> > > > > > > >
> > > > > > > > Some downtime for the database is permissable on this
network.
> > > > > > > >
> > > > > > > > any help is greatly appreciated
> > > > > > > >
> > > > > > > > eric
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||"Eric" <eric@.anon.com> wrote in message
news:OIncXDmwEHA.4040@.TK2MSFTNGP10.phx.gbl...
> Thanks Tibor,
> That was the solution for the BACKUP LOG statement.
> But it doesn't seem to work for the SHRINKFILE statement.
> Any ideas on that one?
> I posted the exact statement and error message on the other thread
Try [] i.e. [107]
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:Oiqjk8lwEHA.3624@.TK2MSFTNGP09.phx.gbl...
> > It is not recommended to use such names (107) for databases, tables etc.
I
> suggest you read in the
> > documentation about the rules for "standard identifier" and try to name
> your database, tables etc
> > according to those rules. If you don't follow the rules, you need to
> enclose the name in either
> > double.quotes or square brackets:
> >
> > BACKUP LOG "107" WITH TRUNCATE_ONLY
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Eric" <eric@.anon.com> wrote in message
> news:O%23TrH5lwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> > > SQL Statement:
> > > BACKUP LOG 107 WITH TRUNCATE_ONLY
> > >
> > > Error message:
> > > Server: Msg 170, Level 15, State 1, Line 1
> > > Line 1: Incorrect syntax near '107'.
> > >
> > >
> > > "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> > > news:eZxmDzlwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> > > > can u post the SQL statement u try.. copy and paste it.. and also
the
> > > error
> > > > message.. copy everything on Analyzer
> > > >
> > > >
> > > > "Eric" <eric@.anon.com> wrote in message
> > > > news:uquiVdlwEHA.1512@.TK2MSFTNGP12.phx.gbl...
> > > > > SQL2000 with SP3
> > > > >
> > > > >
> > > > > "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> > > > > news:OjxQsNlwEHA.1976@.TK2MSFTNGP09.phx.gbl...
> > > > > > BACKUP LOG ecatalog WITH TRUNCATE_ONLY
> > > > > >
> > > > > > I just Try this and it works fine...
> > > > > >
> > > > > > What is ur SQL version.. ( it works fine with SQL 7 and 2000)
> > > > > >
> > > > > >
> > > > > > "Eric" <eric@.anon.com> wrote in message
> > > > > > news:%23MZFxGlwEHA.1192@.tk2msftngp13.phx.gbl...
> > > > > > > But if I execute 'BACKUP LOG databasename WITH TRUNCATE_ONLY'
in
> > > query
> > > > > > > analyser, I get a 'incorrect syntax near databasename' error
> > > message.
> > > > > > >
> > > > > > > any ideas?
> > > > > > >
> > > > > > >
> > > > > > > "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> > > > > > > news:udyGj#kwEHA.4028@.TK2MSFTNGP15.phx.gbl...
> > > > > > >
> > > > > > >
> > > > > > > > Backup Transaction Log. with Tuncate Only param
> > > > > > > >
> > > > > > > > And try to schedule Log Backup at least once a day
> > > > > > > >
> > > > > > > >
> > > > > > > > "Eric" <eric@.anon.com> wrote in message
> > > > > > > > news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>