marked transactions

I am running the following script in Query Analizer:
begin transaction xxxx with mark
update logmarks set logmark=2
commit transaction xxxx
go
The script runs without error and the logmarks table is updated correctly,
but when I check the logmarkhistory table to see if a row was inserted there
is nothing in there. What am I doing wrong or why does this happen?
Thanks in advance for any help.The transaction information will be stored in the logmarkhistory table
only if there is a active log backup chain.sql

Manually roll-forward transactions

I have a full backup of my database, a transaction log backup (taken after
the full). The data files have been deleted, but I do have the transaction
log. After the last transaction log backup, there where a few important
transactions that happened that I need to roll-forward. Is there a way to
get those transactions back if I only have the transaction log, and not a
backup of it?
Thanks in advance.Use the following command:
BACKUP LOG databasename WITH NO_TRUNCATE
This will back up your current log even if the data files are not available.
Then you'll be able to restore your database using your last full backup and
subsequent log backups.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...
> I have a full backup of my database, a transaction log backup (taken after
> the full). The data files have been deleted, but I do have the
transaction
> log. After the last transaction log backup, there where a few important
> transactions that happened that I need to roll-forward. Is there a way to
> get those transactions back if I only have the transaction log, and not a
> backup of it?
> Thanks in advance.
>|||So if I start up SQL Server, after I have lost my data files (from a lost
disk that housed them), I will be able to do a backup of the log? Wouldn't
the currpted status of my database prevent me from doing the tran log
backup?
Paul
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:uurB46p6DHA.1948@.TK2MSFTNGP12.phx.gbl...
> Use the following command:
> BACKUP LOG databasename WITH NO_TRUNCATE
> This will back up your current log even if the data files are not
available.
> Then you'll be able to restore your database using your last full backup
and
> subsequent log backups.
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 Programming by Example
>
> "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...
> > I have a full backup of my database, a transaction log backup (taken
after
> > the full). The data files have been deleted, but I do have the
> transaction
> > log. After the last transaction log backup, there where a few important
> > transactions that happened that I need to roll-forward. Is there a way
to
> > get those transactions back if I only have the transaction log, and not
a
> > backup of it?
> >
> > Thanks in advance.
> >
> >
>|||No, it doesn't prevent you from doing it. Actually, it's something very easy
to test. Just stop sql server, delete data files, restart sql server and
then issue the "magic" command.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:uKVQ0$p6DHA.2644@.TK2MSFTNGP11.phx.gbl...
> So if I start up SQL Server, after I have lost my data files (from a lost
> disk that housed them), I will be able to do a backup of the log?
Wouldn't
> the currpted status of my database prevent me from doing the tran log
> backup?
> Paul
> "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> news:uurB46p6DHA.1948@.TK2MSFTNGP12.phx.gbl...
> > Use the following command:
> > BACKUP LOG databasename WITH NO_TRUNCATE
> > This will back up your current log even if the data files are not
> available.
> > Then you'll be able to restore your database using your last full backup
> and
> > subsequent log backups.
> >
> > --
> > Carlos E. Rojas
> > SQL Server MVP
> > Co-Author SQL Server 2000 Programming by Example
> >
> >
> > "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> > news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...
> > > I have a full backup of my database, a transaction log backup (taken
> after
> > > the full). The data files have been deleted, but I do have the
> > transaction
> > > log. After the last transaction log backup, there where a few
important
> > > transactions that happened that I need to roll-forward. Is there a
way
> to
> > > get those transactions back if I only have the transaction log, and
not
> a
> > > backup of it?
> > >
> > > Thanks in advance.
> > >
> > >
> >
> >
>

Manually roll-forward transactions

I have a full backup of my database, a transaction log backup (taken after
the full). The data files have been deleted, but I do have the transaction
log. After the last transaction log backup, there where a few important
transactions that happened that I need to roll-forward. Is there a way to
get those transactions back if I only have the transaction log, and not a
backup of it?
Thanks in advance.Use the following command:
BACKUP LOG databasename WITH NO_TRUNCATE
This will back up your current log even if the data files are not available.
Then you'll be able to restore your database using your last full backup and
subsequent log backups.
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...

quote:

---

> I have a full backup of my database, a transaction log backup (taken after
> the full). The data files have been deleted, but I do have the

---

transaction

quote:

---

> log. After the last transaction log backup, there where a few important
> transactions that happened that I need to roll-forward. Is there a way to
> get those transactions back if I only have the transaction log, and not a
> backup of it?
> Thanks in advance.
>

---

|||So if I start up SQL Server, after I have lost my data files (from a lost
disk that housed them), I will be able to do a backup of the log? Wouldn't
the currpted status of my database prevent me from doing the tran log
backup?
Paul
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:uurB46p6DHA.1948@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Use the following command:
> BACKUP LOG databasename WITH NO_TRUNCATE
> This will back up your current log even if the data files are not

---

available.

quote:

---

> Then you'll be able to restore your database using your last full backup

---

and

quote:

---

> subsequent log backups.
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 programming by Example
>
> "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...
after[QUOTE]
> transaction
to[QUOTE]
a[QUOTE]
>

---

|||No, it doesn't prevent you from doing it. Actually, it's something very easy
to test. Just stop sql server, delete data files, restart sql server and
then issue the "magic" command.
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:uKVQ0$p6DHA.2644@.TK2MSFTNGP11.phx.gbl...

quote:

---

> So if I start up SQL Server, after I have lost my data files (from a lost
> disk that housed them), I will be able to do a backup of the log?

---

Wouldn't

quote:

---

> the currpted status of my database prevent me from doing the tran log
> backup?
> Paul
> "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> news:uurB46p6DHA.1948@.TK2MSFTNGP12.phx.gbl...
> available.
> and
> after
important[QUOTE]
way[QUOTE]
> to
not[QUOTE]
> a
>

---

Mantanace Plan not deleting old files

I have a maintanace Plan establishe that is working excep
for on nagging item. The plan does both full backups and
transaction log backups each day. It is supposed to
delete files older than 3 days old but does not. I have
to manually go and clear the old files on a regular basis
to keep from filling up the disk. I have not seen any
error or know what to look for to see if something is
wrong. I thought that if a full backup was done that it
emptyed the transaction logs but that dies not appear to
be the case either and is why I have the transition logs
in the mainance plan. Any information on how to get the
old fikes to delete?Here is a good summary of the normal issues related to this by Bill from MS:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Andrew J. Kelly SQL MVP
"Jim Abel" <jim.abel@.lmco.com> wrote in message
news:177f01c49cbe$504699e0$a301280a@.phx.gbl...
> I have a maintanace Plan establishe that is working excep
> for on nagging item. The plan does both full backups and
> transaction log backups each day. It is supposed to
> delete files older than 3 days old but does not. I have
> to manually go and clear the old files on a regular basis
> to keep from filling up the disk. I have not seen any
> error or know what to look for to see if something is
> wrong. I thought that if a full backup was done that it
> emptyed the transaction logs but that dies not appear to
> be the case either and is why I have the transition logs
> in the mainance plan. Any information on how to get the
> old fikes to delete?|||i had the same problem. RIght-click on the maintenance plan and look at the
job history for any errors.
The problem I had was someone set up a maintenance plan to backup ALL
databases and to do transaction lo backups periodically. The trouble with
that is the system DBs (and any user DBs that are not set to FULL recovery
mode) cannot have Transaction log backups performed on them. So the Backups
were running, but the delete step was not running because the transaction log
backup step failed for some of the DBs.
hope that helps
"Andrew J. Kelly" wrote:
> Here is a good summary of the normal issues related to this by Bill from MS:
>
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed to
> local drive), then you will need to modify the above (with respect to where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Abel" <jim.abel@.lmco.com> wrote in message
> news:177f01c49cbe$504699e0$a301280a@.phx.gbl...
> > I have a maintanace Plan establishe that is working excep
> > for on nagging item. The plan does both full backups and
> > transaction log backups each day. It is supposed to
> > delete files older than 3 days old but does not. I have
> > to manually go and clear the old files on a regular basis
> > to keep from filling up the disk. I have not seen any
> > error or know what to look for to see if something is
> > wrong. I thought that if a full backup was done that it
> > emptyed the transaction logs but that dies not appear to
> > be the case either and is why I have the transition logs
> > in the mainance plan. Any information on how to get the
> > old fikes to delete?
>
>

Managing Transactions in Stored Procedure (Nested)

Hi Everyone:

I have a master sp that calls 5 different sps. I would like to incorporate transaction(COMMIT and ROLLBACK) into my master sp. If do that, is that enough, or do I need to add some transaction code to the 5 sps that are being called. I would appreciate if you provide me with code, syntax and steps on how to do this for a specific situation like mine. I have read some articles on nested sps and transactions, but most are very complex examples, I just need a simple approach/advise. Thanks.

Rollback and Commit action should apply to your nested stored procedure calls. When you call a stored procedure within a transaction it executes within the context of that transaction. However, be wary about errors from the stored procedures you are calling from the master stored procedure. Review error handling within stored procedures to make sure you are equipt to handle nested errors and apply the proper transaction method.

Managing Transactions in Stored Procedure (Nested)

Hi Everyone:

I have a master sp that calls 5 different sps. I would like to incorporate transaction(COMMIT and ROLLBACK) into my master sp. If do that, is that enough, or do I need to add some transaction code to the 5 sps that are being called. I would appreciate if you provide me with code, syntax and steps on how to do this for a specific situation like mine. I have read some articles on nested sps and transactions, but most are very complex examples, I just need a simple approach/advise. Thanks.

Rollback and Commit action should apply to your nested stored procedure calls. When you call a stored procedure within a transaction it executes within the context of that transaction. However, be wary about errors from the stored procedures you are calling from the master stored procedure. Review error handling within stored procedures to make sure you are equipt to handle nested errors and apply the proper transaction method.

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

managing the transaction log

Hello!

I'm working with an SQL database that someone else has set up and this is a learning experience for me.

I understand what the transaction log is and a little about it.

What i would like to do is shrink it because it is full. If i use the wizard to truncate or shrink data it never seems to work. I have created a second log file but the server doesn't seem to use it. Increasing the log size does nothing also. DARN!

What is the best way to dump the old data?

thanks in advance?

RIMQ1 [i would like to do is shrink it because it is full]?

A1 Note: one cannot shrink a 'Full' log beyond an active VLF; moreover, one must either dump / back up the contents of a transaction log to a transaction log backup *.trn file (or truncate it) before DBCC ShrinkFile can shrink the file to any smaller size.
Frequently dumping / backing up your (production database) transaction logs to transaction log backup *.trn files will provide the means of point in time recoverability; and also keep the overall DB log size managable. (Typically, production user DBs should be using the Full backup recovery model.)

General production guidelines include:
i The use of DBCC ShrinkFile, (and / or enable autoshrink if appropriate).
ii Identify any long running transactions that may be filling up your DB Log rewrite them to be efficient.
iii Dump the DB transaction log to transaction log backup *.trn files as appropriate for the production environmen.t

DBCC ShrinkFile advantages:
* it is safe
* it may be safely used even if your DB has multiple log files (add several additional log files to your DB, then rigorously test your method)
* ordinary users may work in the DB while its files are being shrunk

Use MyDB
Go
DBCC ShrinkFile ([MyDB_Log], 1, TruncateOnly)
Go|||This was address a couple of weeks ago - check out the link:

link (http://dbforums.com/showthread.php?s=&threadid=546372)