managing Transaction Log file

Hi,
My problem is dealing with the size of Transaction log
file of our main database. It's very huge like 14GB. I
have tried the backing the log file up then DBCC
SHRINKFILE command on the log file. It's still the same
size I started out with. So far whatever I've done didn't
make difference on the size of the log file. Is there an
effective procedure to deal with this issue?
Thank you in advance for your help.
OryFirst, it's important to understand how transactions are
entered into the transaction log file. The log file is
essentially a circular queue. It wraps around. Think of
it as a donut. Within the log file there is a marker that
points to the active portion of the log. That marker
could be at the beginning of the file, in the middle, or
at the end. If the active portion of the log is at the
end of the file, SQL Server will not allow you shrink the
file. Somehow, we have to move that marker to the
beginning of the file so that we can successfully shrink
the file.
It is a very good KB article on this subject. The article
number is Q256650(How to Shrink the SQL Server 7.0
Transaction Log). You can do a search on www.microsoft.com
for this article. This article has a nice discussion on
various reasons why your attempts to shrink a log file
might not succeed. One of the reasons discussed is the
one I described above. The article provides a method for
identifying the problem and the solution to the problem in
the form of a Transact-SQL script. Identification of the
problem is accomplished by running a DBCC command called
LOGINFO. The syntax is DBCC LOGINFO (database_name). The
article describes what to look for in the output. The
script provided in the article for resolving the problem
does the following, in general terms:
. It creates a dummy table in the database for which
you are trying to shrink the log file.
. It then inserts a bunch of rows into this dummy
table. This has the effect of forcing the pointer to the
active portion of the log to wrap around to the beginning
of the log file.
. It then shrinks the log file to the size you want.
. Then it drops the dummy table.
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
>--Original Message--
>Hi,
>My problem is dealing with the size of Transaction log
>file of our main database. It's very huge like 14GB. I
>have tried the backing the log file up then DBCC
>SHRINKFILE command on the log file. It's still the same
>size I started out with. So far whatever I've done didn't
>make difference on the size of the log file. Is there an
>effective procedure to deal with this issue?
>Thank you in advance for your help.
> Ory
>.
>|||Refer to whichever applies to your version of SQL Server:
INF: How to Shrink the SQL Server 7.0 Transaction Log
(Q256650)
http://support.microsoft.com/?id=256650
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Mon, 13 Oct 2003 13:00:13 -0700, "Ory" <Ory@.nomail.org>
wrote:
>Hi,
>My problem is dealing with the size of Transaction log
>file of our main database. It's very huge like 14GB. I
>have tried the backing the log file up then DBCC
>SHRINKFILE command on the log file. It's still the same
>size I started out with. So far whatever I've done didn't
>make difference on the size of the log file. Is there an
>effective procedure to deal with this issue?
>Thank you in advance for your help.
> Ory|||Thanks Sue but I had tried the step in that article but
didn't resolve my problem. So I tried the easiest method
in the KB which is to detach from the database then delete
the log file (or rename) in the server then use
sp_attach_using_single_file to the database name and
physical path of the data file. Finally it creates a new
transaction log file. Sums it up for me.
>--Original Message--
>Refer to whichever applies to your version of SQL Server:
>INF: How to Shrink the SQL Server 7.0 Transaction Log
>(Q256650)
>http://support.microsoft.com/?id=256650
>INF: Shrinking the Transaction Log in SQL Server 2000 with
>DBCC SHRINKFILE
>http://support.microsoft.com/?id=272318
>-Sue
>On Mon, 13 Oct 2003 13:00:13 -0700, "Ory" <Ory@.nomail.org>
>wrote:
>>Hi,
>>My problem is dealing with the size of Transaction log
>>file of our main database. It's very huge like 14GB. I
>>have tried the backing the log file up then DBCC
>>SHRINKFILE command on the log file. It's still the same
>>size I started out with. So far whatever I've done
didn't
>>make difference on the size of the log file. Is there an
>>effective procedure to deal with this issue?
>>Thank you in advance for your help.
>> Ory
>.
>|||Thank you for your useful answer, I've ran the
shrink_database script in the article but it didn't shrink
the log file still. So I tried the easiesst method in the
book to detach the database then delete the log file then
db_attach_using_single_file pointing at the physical file
path and logical db name. This worked well because it
created a new minimum sized log file (or empty one you may
think as). I realize I need to have a more permanent
solution than this. This is not an ideal dba procedure.
Thank you again. Bye now:). Ory.
>--Original Message--
>First, it's important to understand how transactions are
>entered into the transaction log file. The log file is
>essentially a circular queue. It wraps around. Think of
>it as a donut. Within the log file there is a marker
that
>points to the active portion of the log. That marker
>could be at the beginning of the file, in the middle, or
>at the end. If the active portion of the log is at the
>end of the file, SQL Server will not allow you shrink the
>file. Somehow, we have to move that marker to the
>beginning of the file so that we can successfully shrink
>the file.
>It is a very good KB article on this subject. The
article
>number is Q256650(How to Shrink the SQL Server 7.0
>Transaction Log). You can do a search on
www.microsoft.com
>for this article. This article has a nice discussion on
>various reasons why your attempts to shrink a log file
>might not succeed. One of the reasons discussed is the
>one I described above. The article provides a method for
>identifying the problem and the solution to the problem
in
>the form of a Transact-SQL script. Identification of the
>problem is accomplished by running a DBCC command called
>LOGINFO. The syntax is DBCC LOGINFO (database_name).
The
>article describes what to look for in the output. The
>script provided in the article for resolving the problem
>does the following, in general terms:
>.. It creates a dummy table in the database for which
>you are trying to shrink the log file.
>.. It then inserts a bunch of rows into this dummy
>table. This has the effect of forcing the pointer to the
>active portion of the log to wrap around to the beginning
>of the log file.
>.. It then shrinks the log file to the size you want.
>.. Then it drops the dummy table.
>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>http://www.microsoft.com/info/cpyright.htm
>>--Original Message--
>>Hi,
>>My problem is dealing with the size of Transaction log
>>file of our main database. It's very huge like 14GB. I
>>have tried the backing the log file up then DBCC
>>SHRINKFILE command on the log file. It's still the same
>>size I started out with. So far whatever I've done
didn't
>>make difference on the size of the log file. Is there an
>>effective procedure to deal with this issue?
>>Thank you in advance for your help.
>> Ory
>>.
>.
>|||I don't remember those articles or the KB recommending this
approach for shrinking a log file. As dbcc shrinkfile is a
bit different on SQL 7 and SQL 2000, it's hard to say what
the problem could be as you didn't post the version of SQL
Server. On SQL 7, it's a deferred operation so that can play
a part in what you experienced.
-Sue
On Mon, 13 Oct 2003 16:54:00 -0700, "Ory" <Ory@.nomail.org>
wrote:
>Thanks Sue but I had tried the step in that article but
>didn't resolve my problem. So I tried the easiest method
>in the KB which is to detach from the database then delete
>the log file (or rename) in the server then use
>sp_attach_using_single_file to the database name and
>physical path of the data file. Finally it creates a new
>transaction log file. Sums it up for me.
>
>>--Original Message--
>>Refer to whichever applies to your version of SQL Server:
>>INF: How to Shrink the SQL Server 7.0 Transaction Log
>>(Q256650)
>>http://support.microsoft.com/?id=256650
>>INF: Shrinking the Transaction Log in SQL Server 2000 with
>>DBCC SHRINKFILE
>>http://support.microsoft.com/?id=272318
>>-Sue
>>On Mon, 13 Oct 2003 13:00:13 -0700, "Ory" <Ory@.nomail.org>
>>wrote:
>>Hi,
>>My problem is dealing with the size of Transaction log
>>file of our main database. It's very huge like 14GB. I
>>have tried the backing the log file up then DBCC
>>SHRINKFILE command on the log file. It's still the same
>>size I started out with. So far whatever I've done
>didn't
>>make difference on the size of the log file. Is there an
>>effective procedure to deal with this issue?
>>Thank you in advance for your help.
>> Ory
>>.|||Consider yourself lucky you didn't get a corrupt/suspect database! :-)
(I do understand that you did a database backup first.)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Ory" <Ory@.nomail.org> wrote in message news:0a7c01c391e5$45c6f170$a301280a@.phx.gbl...
> Thanks Sue but I had tried the step in that article but
> didn't resolve my problem. So I tried the easiest method
> in the KB which is to detach from the database then delete
> the log file (or rename) in the server then use
> sp_attach_using_single_file to the database name and
> physical path of the data file. Finally it creates a new
> transaction log file. Sums it up for me.
>
> >--Original Message--
> >Refer to whichever applies to your version of SQL Server:
> >
> >INF: How to Shrink the SQL Server 7.0 Transaction Log
> >(Q256650)
> >http://support.microsoft.com/?id=256650
> >
> >INF: Shrinking the Transaction Log in SQL Server 2000 with
> >DBCC SHRINKFILE
> >http://support.microsoft.com/?id=272318
> >
> >-Sue
> >
> >On Mon, 13 Oct 2003 13:00:13 -0700, "Ory" <Ory@.nomail.org>
> >wrote:
> >
> >>Hi,
> >>
> >>My problem is dealing with the size of Transaction log
> >>file of our main database. It's very huge like 14GB. I
> >>have tried the backing the log file up then DBCC
> >>SHRINKFILE command on the log file. It's still the same
> >>size I started out with. So far whatever I've done
> didn't
> >>make difference on the size of the log file. Is there an
> >>effective procedure to deal with this issue?
> >>
> >>Thank you in advance for your help.
> >>
> >> Ory
> >
> >.
> >|||Hi Sue,
I'm using SQL Server 2000. You're right that it wasn't in
KB the article it was
in //evolvedcode.net/content/code_sqllogshrink/ . The
format that I used for shrinking before that didn't work
was DBCC SHRINKFILE (xxx.log, target_size_in_MB) in Query
Analyzer. I get a result set on Actual and estimated size
of the file which doen't help much. Anyways, thank you for
your reply. I post a message as a developer/dba_to_be
every two to three months or so.
Bye.
>--Original Message--
>I don't remember those articles or the KB recommending
this
>approach for shrinking a log file. As dbcc shrinkfile is a
>bit different on SQL 7 and SQL 2000, it's hard to say what
>the problem could be as you didn't post the version of SQL
>Server. On SQL 7, it's a deferred operation so that can
play
>a part in what you experienced.
>-Sue
>On Mon, 13 Oct 2003 16:54:00 -0700, "Ory" <Ory@.nomail.org>
>wrote:
>>Thanks Sue but I had tried the step in that article but
>>didn't resolve my problem. So I tried the easiest method
>>in the KB which is to detach from the database then
delete
>>the log file (or rename) in the server then use
>>sp_attach_using_single_file to the database name and
>>physical path of the data file. Finally it creates a new
>>transaction log file. Sums it up for me.
>>
>>--Original Message--
>>Refer to whichever applies to your version of SQL
Server:
>>INF: How to Shrink the SQL Server 7.0 Transaction Log
>>(Q256650)
>>http://support.microsoft.com/?id=256650
>>INF: Shrinking the Transaction Log in SQL Server 2000
with
>>DBCC SHRINKFILE
>>http://support.microsoft.com/?id=272318
>>-Sue
>>On Mon, 13 Oct 2003 13:00:13 -0700, "Ory"
<Ory@.nomail.org>
>>wrote:
>>Hi,
>>My problem is dealing with the size of Transaction log
>>file of our main database. It's very huge like 14GB. I
>>have tried the backing the log file up then DBCC
>>SHRINKFILE command on the log file. It's still the
same
>>size I started out with. So far whatever I've done
>>didn't
>>make difference on the size of the log file. Is there
an
>>effective procedure to deal with this issue?
>>Thank you in advance for your help.
>> Ory
>>.
>.
>|||There are plenty of posts on sites about deleting logs,
rebuilding logs, etc. It's just a totally bad practice - it
risks the ability to maintain transactional consistency in
your database. Unfortunately, someone follows the advice,
doesn't see any harmful affects and then posts the solution
again. But there are plenty of people who have basically
ruined their databases by doing this. It may not even be
noticeable immediately. You could be in a situation where
you have a problem, can solve it using methods that don't
harm your database but you loose the ability to do this by
running some of these scripts. The transaction log is a
critically important piece of your database system and
that's something people seem to lose sight of sometimes.
-Sue
On Tue, 14 Oct 2003 11:58:52 -0700, "Ory"
<ory@.nomail.nospam.org> wrote:
>Hi Sue,
>I'm using SQL Server 2000. You're right that it wasn't in
>KB the article it was
>in //evolvedcode.net/content/code_sqllogshrink/ . The
>format that I used for shrinking before that didn't work
>was DBCC SHRINKFILE (xxx.log, target_size_in_MB) in Query
>Analyzer. I get a result set on Actual and estimated size
>of the file which doen't help much. Anyways, thank you for
>your reply. I post a message as a developer/dba_to_be
>every two to three months or so.
>Bye.
>
>>--Original Message--
>>I don't remember those articles or the KB recommending
>this
>>approach for shrinking a log file. As dbcc shrinkfile is a
>>bit different on SQL 7 and SQL 2000, it's hard to say what
>>the problem could be as you didn't post the version of SQL
>>Server. On SQL 7, it's a deferred operation so that can
>play
>>a part in what you experienced.
>>-Sue
>>On Mon, 13 Oct 2003 16:54:00 -0700, "Ory" <Ory@.nomail.org>
>>wrote:
>>Thanks Sue but I had tried the step in that article but
>>didn't resolve my problem. So I tried the easiest method
>>in the KB which is to detach from the database then
>delete
>>the log file (or rename) in the server then use
>>sp_attach_using_single_file to the database name and
>>physical path of the data file. Finally it creates a new
>>transaction log file. Sums it up for me.
>>
>>--Original Message--
>>Refer to whichever applies to your version of SQL
>Server:
>>INF: How to Shrink the SQL Server 7.0 Transaction Log
>>(Q256650)
>>http://support.microsoft.com/?id=256650
>>INF: Shrinking the Transaction Log in SQL Server 2000
>with
>>DBCC SHRINKFILE
>>http://support.microsoft.com/?id=272318
>>-Sue
>>On Mon, 13 Oct 2003 13:00:13 -0700, "Ory"
><Ory@.nomail.org>
>>wrote:
>>Hi,
>>My problem is dealing with the size of Transaction log
>>file of our main database. It's very huge like 14GB. I
>>have tried the backing the log file up then DBCC
>>SHRINKFILE command on the log file. It's still the
>same
>>size I started out with. So far whatever I've done
>>didn't
>>make difference on the size of the log file. Is there
>an
>>effective procedure to deal with this issue?
>>Thank you in advance for your help.
>> Ory
>>.
>>
>>.|||(MRS OR MS) Hoegemeier,
You're chewing me up?. Believe me I tried scripts and
commands from Microsoft sites mostly. Which I didn't get
anywhere. I don't have comfort of long time for these
problems I have to go on with running our application
scripts against the database. However, I acknowledge that
this is just a temporary solution. More I become
knowledgable more I will implement more permanent and
sustaining solutions. I'm below your experience and know-
how in SQL Server but in Real World shortcuts and relative
quick fixes keeps running the IT departments. And also
keep the problem solver image. That's all.
*\Ory
>--Original Message--
>There are plenty of posts on sites about deleting logs,
>rebuilding logs, etc. It's just a totally bad practice -
it
>risks the ability to maintain transactional consistency in
>your database. Unfortunately, someone follows the advice,
>doesn't see any harmful affects and then posts the
solution
>again. But there are plenty of people who have basically
>ruined their databases by doing this. It may not even be
>noticeable immediately. You could be in a situation where
>you have a problem, can solve it using methods that don't
>harm your database but you loose the ability to do this by
>running some of these scripts. The transaction log is a
>critically important piece of your database system and
>that's something people seem to lose sight of sometimes.
>-Sue
>On Tue, 14 Oct 2003 11:58:52 -0700, "Ory"
><ory@.nomail.nospam.org> wrote:
>>Hi Sue,
>>I'm using SQL Server 2000. You're right that it wasn't
in
>>KB the article it was
>>in //evolvedcode.net/content/code_sqllogshrink/ . The
>>format that I used for shrinking before that didn't work
>>was DBCC SHRINKFILE (xxx.log, target_size_in_MB) in
Query
>>Analyzer. I get a result set on Actual and estimated
size
>>of the file which doen't help much. Anyways, thank you
for
>>your reply. I post a message as a developer/dba_to_be
>>every two to three months or so.
>>Bye.
>>
>>--Original Message--
>>I don't remember those articles or the KB recommending
>>this
>>approach for shrinking a log file. As dbcc shrinkfile
is a
>>bit different on SQL 7 and SQL 2000, it's hard to say
what
>>the problem could be as you didn't post the version of
SQL
>>Server. On SQL 7, it's a deferred operation so that can
>>play
>>a part in what you experienced.
>>-Sue
>>On Mon, 13 Oct 2003 16:54:00 -0700, "Ory"
<Ory@.nomail.org>
>>wrote:
>>Thanks Sue but I had tried the step in that article
but
>>didn't resolve my problem. So I tried the easiest
method
>>in the KB which is to detach from the database then
>>delete
>>the log file (or rename) in the server then use
>>sp_attach_using_single_file to the database name and
>>physical path of the data file. Finally it creates a
new
>>transaction log file. Sums it up for me.
>>
>>--Original Message--
>>Refer to whichever applies to your version of SQL
>>Server:
>>INF: How to Shrink the SQL Server 7.0 Transaction Log
>>(Q256650)
>>http://support.microsoft.com/?id=256650
>>INF: Shrinking the Transaction Log in SQL Server 2000
>>with
>>DBCC SHRINKFILE
>>http://support.microsoft.com/?id=272318
>>-Sue
>>On Mon, 13 Oct 2003 13:00:13 -0700, "Ory"
>><Ory@.nomail.org>
>>wrote:
>>Hi,
>>My problem is dealing with the size of Transaction
log
>>file of our main database. It's very huge like 14GB.
I
>>have tried the backing the log file up then DBCC
>>SHRINKFILE command on the log file. It's still the
>>same
>>size I started out with. So far whatever I've done
>>didn't
>>make difference on the size of the log file. Is
there
>>an
>>effective procedure to deal with this issue?
>>Thank you in advance for your help.
>> Ory
>>.
>>
>>.
>.
>|||No, no...not chewing you up at all. Just the opposite - I
was just trying to warn you about using some of those
scripts. Of all the scripts I've seen, I think some of the
log scripts (as well some of the ones that mess with system
tables) are just the totally wrong solutions to the problem.
All too often I have seen where these "shortcuts" really
don't add up to any time, money, business saved and often
cause more problems than the original problem they were
meant to solve. I've seen places use the shortcuts and then
have problems with the database for weeks, downtime, data
loss etc. I really do understand what you are saying and
I've certainly been in the position where I have been told
to do things that are totally wrong. But they don't keep
things running sometimes as much as they keep us in crisis
mode, working later for days, etc. And a lot of times it's
just people who don't know any better who are going to
insist anyway. But it's important for you to know what's
right and what's wrong. If you warn them and they insist
(and they sign your paycheck) then often there isn't much
you can do. But if you know and you warn them, at least you
did what's right. The worse part is when we think some of
the bad practices are really the way it's suppose to be
done. But if you know better and take the time to understand
you can be in a better position to help clean up the mess.
That's why I was warning you about going down that road...
-Sue
On Tue, 14 Oct 2003 14:48:06 -0700, "Ory"
<ory@.nospam.please> wrote:
>(MRS OR MS) Hoegemeier,
>You're chewing me up?. Believe me I tried scripts and
>commands from Microsoft sites mostly. Which I didn't get
>anywhere. I don't have comfort of long time for these
>problems I have to go on with running our application
>scripts against the database. However, I acknowledge that
>this is just a temporary solution. More I become
>knowledgable more I will implement more permanent and
>sustaining solutions. I'm below your experience and know-
>how in SQL Server but in Real World shortcuts and relative
>quick fixes keeps running the IT departments. And also
>keep the problem solver image. That's all.
> *\Ory
>>--Original Message--
>>There are plenty of posts on sites about deleting logs,
>>rebuilding logs, etc. It's just a totally bad practice -
>it
>>risks the ability to maintain transactional consistency in
>>your database. Unfortunately, someone follows the advice,
>>doesn't see any harmful affects and then posts the
>solution
>>again. But there are plenty of people who have basically
>>ruined their databases by doing this. It may not even be
>>noticeable immediately. You could be in a situation where
>>you have a problem, can solve it using methods that don't
>>harm your database but you loose the ability to do this by
>>running some of these scripts. The transaction log is a
>>critically important piece of your database system and
>>that's something people seem to lose sight of sometimes.
>>-Sue
>>On Tue, 14 Oct 2003 11:58:52 -0700, "Ory"
>><ory@.nomail.nospam.org> wrote:
>>Hi Sue,
>>I'm using SQL Server 2000. You're right that it wasn't
>in
>>KB the article it was
>>in //evolvedcode.net/content/code_sqllogshrink/ . The
>>format that I used for shrinking before that didn't work
>>was DBCC SHRINKFILE (xxx.log, target_size_in_MB) in
>Query
>>Analyzer. I get a result set on Actual and estimated
>size
>>of the file which doen't help much. Anyways, thank you
>for
>>your reply. I post a message as a developer/dba_to_be
>>every two to three months or so.
>>Bye.
>>
>>--Original Message--
>>I don't remember those articles or the KB recommending
>>this
>>approach for shrinking a log file. As dbcc shrinkfile
>is a
>>bit different on SQL 7 and SQL 2000, it's hard to say
>what
>>the problem could be as you didn't post the version of
>SQL
>>Server. On SQL 7, it's a deferred operation so that can
>>play
>>a part in what you experienced.
>>-Sue
>>On Mon, 13 Oct 2003 16:54:00 -0700, "Ory"
><Ory@.nomail.org>
>>wrote:
>>Thanks Sue but I had tried the step in that article
>but
>>didn't resolve my problem. So I tried the easiest
>method
>>in the KB which is to detach from the database then
>>delete
>>the log file (or rename) in the server then use
>>sp_attach_using_single_file to the database name and
>>physical path of the data file. Finally it creates a
>new
>>transaction log file. Sums it up for me.
>>
>>--Original Message--
>>Refer to whichever applies to your version of SQL
>>Server:
>>INF: How to Shrink the SQL Server 7.0 Transaction Log
>>(Q256650)
>>http://support.microsoft.com/?id=256650
>>INF: Shrinking the Transaction Log in SQL Server 2000
>>with
>>DBCC SHRINKFILE
>>http://support.microsoft.com/?id=272318
>>-Sue
>>On Mon, 13 Oct 2003 13:00:13 -0700, "Ory"
>><Ory@.nomail.org>
>>wrote:
>>>Hi,
>>>
>>>My problem is dealing with the size of Transaction
>log
>>>file of our main database. It's very huge like 14GB.
>I
>>>have tried the backing the log file up then DBCC
>>>SHRINKFILE command on the log file. It's still the
>>same
>>>size I started out with. So far whatever I've done
>>didn't
>>>make difference on the size of the log file. Is
>there
>>an
>>>effective procedure to deal with this issue?
>>>
>>>Thank you in advance for your help.
>>>
>>> Ory
>>.
>>
>>.
>>
>>.|||And with a last name like that, Sue is very much okay by me.
It's worse to pronounce than it is to type so you were lucky
you only had to type it!
-Sue
On Tue, 14 Oct 2003 14:48:06 -0700, "Ory"
<ory@.nospam.please> wrote:
>MRS OR MS) Hoegemeier,|||I see :). It was good having a frank exchange with a Lady
of significant professional experience. Your warnings and
opinions are appreciated. Thank you.
PS. Would you tell me where you work (company)? Just
curious.
>--Original Message--
>No, no...not chewing you up at all. Just the opposite - I
>was just trying to warn you about using some of those
>scripts. Of all the scripts I've seen, I think some of the
>log scripts (as well some of the ones that mess with
system
>tables) are just the totally wrong solutions to the
problem.
>All too often I have seen where these "shortcuts" really
>don't add up to any time, money, business saved and often
>cause more problems than the original problem they were
>meant to solve. I've seen places use the shortcuts and
then
>have problems with the database for weeks, downtime, data
>loss etc. I really do understand what you are saying and
>I've certainly been in the position where I have been told
>to do things that are totally wrong. But they don't keep
>things running sometimes as much as they keep us in crisis
>mode, working later for days, etc. And a lot of times it's
>just people who don't know any better who are going to
>insist anyway. But it's important for you to know what's
>right and what's wrong. If you warn them and they insist
>(and they sign your paycheck) then often there isn't much
>you can do. But if you know and you warn them, at least
you
>did what's right. The worse part is when we think some of
>the bad practices are really the way it's suppose to be
>done. But if you know better and take the time to
understand
>you can be in a better position to help clean up the mess.
>That's why I was warning you about going down that road...
>-Sue
>On Tue, 14 Oct 2003 14:48:06 -0700, "Ory"
><ory@.nospam.please> wrote:
>>(MRS OR MS) Hoegemeier,
>>You're chewing me up?. Believe me I tried scripts and
>>commands from Microsoft sites mostly. Which I didn't get
>>anywhere. I don't have comfort of long time for these
>>problems I have to go on with running our application
>>scripts against the database. However, I acknowledge
that
>>this is just a temporary solution. More I become
>>knowledgable more I will implement more permanent and
>>sustaining solutions. I'm below your experience and know-
>>how in SQL Server but in Real World shortcuts and
relative
>>quick fixes keeps running the IT departments. And also
>>keep the problem solver image. That's all.
>> *\Ory
>>--Original Message--
>>There are plenty of posts on sites about deleting logs,
>>rebuilding logs, etc. It's just a totally bad practice -
>>it
>>risks the ability to maintain transactional consistency
in
>>your database. Unfortunately, someone follows the
advice,
>>doesn't see any harmful affects and then posts the
>>solution
>>again. But there are plenty of people who have
basically
>>ruined their databases by doing this. It may not even be
>>noticeable immediately. You could be in a situation
where
>>you have a problem, can solve it using methods that
don't
>>harm your database but you loose the ability to do this
by
>>running some of these scripts. The transaction log is a
>>critically important piece of your database system and
>>that's something people seem to lose sight of sometimes.
>>-Sue
>>On Tue, 14 Oct 2003 11:58:52 -0700, "Ory"
>><ory@.nomail.nospam.org> wrote:
>>Hi Sue,
>>I'm using SQL Server 2000. You're right that it wasn't
>>in
>>KB the article it was
>>in //evolvedcode.net/content/code_sqllogshrink/ . The
>>format that I used for shrinking before that didn't
work
>>was DBCC SHRINKFILE (xxx.log, target_size_in_MB) in
>>Query
>>Analyzer. I get a result set on Actual and estimated
>>size
>>of the file which doen't help much. Anyways, thank you
>>for
>>your reply. I post a message as a developer/dba_to_be
>>every two to three months or so.
>>Bye.
>>
>>--Original Message--
>>I don't remember those articles or the KB
recommending
>>this
>>approach for shrinking a log file. As dbcc shrinkfile
>>is a
>>bit different on SQL 7 and SQL 2000, it's hard to say
>>what
>>the problem could be as you didn't post the version
of
>>SQL
>>Server. On SQL 7, it's a deferred operation so that
can
>>play
>>a part in what you experienced.
>>-Sue
>>On Mon, 13 Oct 2003 16:54:00 -0700, "Ory"
>><Ory@.nomail.org>
>>wrote:
>>Thanks Sue but I had tried the step in that article
>>but
>>didn't resolve my problem. So I tried the easiest
>>method
>>in the KB which is to detach from the database then
>>delete
>>the log file (or rename) in the server then use
>>sp_attach_using_single_file to the database name and
>>physical path of the data file. Finally it creates a
>>new
>>transaction log file. Sums it up for me.
>>
>>>--Original Message--
>>>Refer to whichever applies to your version of SQL
>>Server:
>>>
>>>INF: How to Shrink the SQL Server 7.0 Transaction
Log
>>>(Q256650)
>>>http://support.microsoft.com/?id=256650
>>>
>>>INF: Shrinking the Transaction Log in SQL Server
2000
>>with
>>>DBCC SHRINKFILE
>>>http://support.microsoft.com/?id=272318
>>>
>>>-Sue
>>>
>>>On Mon, 13 Oct 2003 13:00:13 -0700, "Ory"
>><Ory@.nomail.org>
>>>wrote:
>>>
>>>Hi,
>>>
>>>My problem is dealing with the size of Transaction
>>log
>>>file of our main database. It's very huge like
14GB.
>>I
>>>have tried the backing the log file up then DBCC
>>>SHRINKFILE command on the log file. It's still the
>>same
>>>size I started out with. So far whatever I've done
>>didn't
>>>make difference on the size of the log file. Is
>>there
>>an
>>>effective procedure to deal with this issue?
>>>
>>>Thank you in advance for your help.
>>>
>>> Ory
>>>
>>>.
>>>
>>.
>>
>>.
>.
>|||If I told you after posting these things, I'd probably never
get a job again! But I've worked at a lot of different
places actually. I think many places experience the same
problems we've discussed.
-Sue
On Tue, 14 Oct 2003 16:45:25 -0700, "Ory"
<anonymous@.discussions.microsoft.com> wrote:
>I see :). It was good having a frank exchange with a Lady
>of significant professional experience. Your warnings and
>opinions are appreciated. Thank you.
>PS. Would you tell me where you work (company)? Just
>curious.
>
>>--Original Message--
>>No, no...not chewing you up at all. Just the opposite - I
>>was just trying to warn you about using some of those
>>scripts. Of all the scripts I've seen, I think some of the
>>log scripts (as well some of the ones that mess with
>system
>>tables) are just the totally wrong solutions to the
>problem.
>>All too often I have seen where these "shortcuts" really
>>don't add up to any time, money, business saved and often
>>cause more problems than the original problem they were
>>meant to solve. I've seen places use the shortcuts and
>then
>>have problems with the database for weeks, downtime, data
>>loss etc. I really do understand what you are saying and
>>I've certainly been in the position where I have been told
>>to do things that are totally wrong. But they don't keep
>>things running sometimes as much as they keep us in crisis
>>mode, working later for days, etc. And a lot of times it's
>>just people who don't know any better who are going to
>>insist anyway. But it's important for you to know what's
>>right and what's wrong. If you warn them and they insist
>>(and they sign your paycheck) then often there isn't much
>>you can do. But if you know and you warn them, at least
>you
>>did what's right. The worse part is when we think some of
>>the bad practices are really the way it's suppose to be
>>done. But if you know better and take the time to
>understand
>>you can be in a better position to help clean up the mess.
>>That's why I was warning you about going down that road...
>>-Sue
>>On Tue, 14 Oct 2003 14:48:06 -0700, "Ory"
>><ory@.nospam.please> wrote:
>>(MRS OR MS) Hoegemeier,
>>You're chewing me up?. Believe me I tried scripts and
>>commands from Microsoft sites mostly. Which I didn't get
>>anywhere. I don't have comfort of long time for these
>>problems I have to go on with running our application
>>scripts against the database. However, I acknowledge
>that
>>this is just a temporary solution. More I become
>>knowledgable more I will implement more permanent and
>>sustaining solutions. I'm below your experience and know-
>>how in SQL Server but in Real World shortcuts and
>relative
>>quick fixes keeps running the IT departments. And also
>>keep the problem solver image. That's all.
>> *\Ory
>>--Original Message--
>>There are plenty of posts on sites about deleting logs,
>>rebuilding logs, etc. It's just a totally bad practice -
>>it
>>risks the ability to maintain transactional consistency
>in
>>your database. Unfortunately, someone follows the
>advice,
>>doesn't see any harmful affects and then posts the
>>solution
>>again. But there are plenty of people who have
>basically
>>ruined their databases by doing this. It may not even be
>>noticeable immediately. You could be in a situation
>where
>>you have a problem, can solve it using methods that
>don't
>>harm your database but you loose the ability to do this
>by
>>running some of these scripts. The transaction log is a
>>critically important piece of your database system and
>>that's something people seem to lose sight of sometimes.
>>-Sue
>>On Tue, 14 Oct 2003 11:58:52 -0700, "Ory"
>><ory@.nomail.nospam.org> wrote:
>>Hi Sue,
>>I'm using SQL Server 2000. You're right that it wasn't
>>in
>>KB the article it was
>>in //evolvedcode.net/content/code_sqllogshrink/ . The
>>format that I used for shrinking before that didn't
>work
>>was DBCC SHRINKFILE (xxx.log, target_size_in_MB) in
>>Query
>>Analyzer. I get a result set on Actual and estimated
>>size
>>of the file which doen't help much. Anyways, thank you
>>for
>>your reply. I post a message as a developer/dba_to_be
>>every two to three months or so.
>>Bye.
>>
>>--Original Message--
>>I don't remember those articles or the KB
>recommending
>>this
>>approach for shrinking a log file. As dbcc shrinkfile
>>is a
>>bit different on SQL 7 and SQL 2000, it's hard to say
>>what
>>the problem could be as you didn't post the version
>of
>>SQL
>>Server. On SQL 7, it's a deferred operation so that
>can
>>play
>>a part in what you experienced.
>>-Sue
>>On Mon, 13 Oct 2003 16:54:00 -0700, "Ory"
>><Ory@.nomail.org>
>>wrote:
>>>Thanks Sue but I had tried the step in that article
>>but
>>>didn't resolve my problem. So I tried the easiest
>>method
>>>in the KB which is to detach from the database then
>>delete
>>>the log file (or rename) in the server then use
>>>sp_attach_using_single_file to the database name and
>>>physical path of the data file. Finally it creates a
>>new
>>>transaction log file. Sums it up for me.
>>>
>>>
>>>--Original Message--
>>>Refer to whichever applies to your version of SQL
>>Server:
>>>
>>>INF: How to Shrink the SQL Server 7.0 Transaction
>Log
>>>(Q256650)
>>>http://support.microsoft.com/?id=256650
>>>
>>>INF: Shrinking the Transaction Log in SQL Server
>2000
>>with
>>>DBCC SHRINKFILE
>>>http://support.microsoft.com/?id=272318
>>>
>>>-Sue
>>>
>>>On Mon, 13 Oct 2003 13:00:13 -0700, "Ory"
>><Ory@.nomail.org>
>>>wrote:
>>>
>>>Hi,
>>>
>>>My problem is dealing with the size of Transaction
>>log
>>>file of our main database. It's very huge like
>14GB.
>>I
>>>have tried the backing the log file up then DBCC
>>>SHRINKFILE command on the log file. It's still the
>>same
>>>size I started out with. So far whatever I've done
>>>didn't
>>>make difference on the size of the log file. Is
>>there
>>an
>>>effective procedure to deal with this issue?
>>>
>>>Thank you in advance for your help.
>>>
>>> Ory
>>>
>>>.
>>>
>>.
>>
>>.
>>
>>.|||Dear Sue, or other friend:
I've read all your talks above, and want to say that I am just in that
trouble. My database log file's size is over 1.2G, and I detached it,
and delete the log file, but when I want to attach the mdf file, it
shows 'Error 1813, could not open new database, CREATE DATABASE is
abort, Device activation error, the physical file name xxx may be
incorrect.' The server version is 2000, database size is just 300M.
How can I do?
Thanks in advanced.
Wing1993
Posted via http://dbforums.com|||This is the very reason why folks shouldn't recommend deleting log files. I suggest you restore the
database from the backup I take it you did before this operation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"wing1993" <member44167@.dbforums.com> wrote in message news:3482440.1066189506@.dbforums.com...
> Dear Sue, or other friend:
>
> I've read all your talks above, and want to say that I am just in that
> trouble. My database log file's size is over 1.2G, and I detached it,
> and delete the log file, but when I want to attach the mdf file, it
> shows 'Error 1813, could not open new database, CREATE DATABASE is
> abort, Device activation error, the physical file name xxx may be
> incorrect.' The server version is 2000, database size is just 300M.
>
> How can I do?
> Thanks in advanced.
>
> Wing1993
>
> --
> Posted via http://dbforums.com|||All,
If I still want to use this updated mdf file to restore the database,
how can i do, in fact, the last backup is over 3 days older than the bad
file. I don't want to loss the data.
But I will bear in mind that next time I shall backup database and log
file to truncate the long transaction file.
Wing1993
Posted via http://dbforums.com