Help with update forms: transaction

Showing posts with label transaction. Show all posts

Wednesday, March 28, 2012

HELP! Transaction Log is huge

Hi all,
I have an SQL2000 running on a W2K server. The mdf file is about 140MB but
the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and the
Transaction log backup right after is 126MB. I tried shrinking and
everything else I could think of. I even deleted the DB and restored it from
a full DB backup. The ldf still comes up with 11GB. I have another
installation of the same database on another server where the ldf shows a
normal size (40MB).
Can anybody tell me what is going on? During the restore I moved the data
files to another disk that has lots of space but there is something wrong
here and I would like to get to the bottom of it.
Thanks for your help,
ClausHi, what is your recovery set too. If it is set to "Full" you need to do a
translog backup, and truncate after.
If you set it to simple, it will checkpoint and keep the log resonable.
Try backup log (database) with truncate_only
Then shrink the log file.
"cjobes" wrote:
> Hi all,
> I have an SQL2000 running on a W2K server. The mdf file is about 140MB but
> the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and the
> Transaction log backup right after is 126MB. I tried shrinking and
> everything else I could think of. I even deleted the DB and restored it from
> a full DB backup. The ldf still comes up with 11GB. I have another
> installation of the same database on another server where the ldf shows a
> normal size (40MB).
> Can anybody tell me what is going on? During the restore I moved the data
> files to another disk that has lots of space but there is something wrong
> here and I would like to get to the bottom of it.
> Thanks for your help,
> Claus
>
>|||I have it set to full because we do a daily backup and then hourly
transaction log backups. The one instance works as expected but the other
one has this 11GB ldf and I don't seem to be able to get it down.
Now after reading your advice I switched the db to simple, run a backup and
then shrank the log file. Now it's down to 0.5MB. My question is, shouldn't
it do that automatically when the full db backup runs with the option to
remove inactive entries from the transaction log?
Thanks for your help,
Claus
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> Hi, what is your recovery set too. If it is set to "Full" you need to do a
> translog backup, and truncate after.
> If you set it to simple, it will checkpoint and keep the log resonable.
> Try backup log (database) with truncate_only
> Then shrink the log file.
>
> "cjobes" wrote:
> > Hi all,
> >
> > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
but
> > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and
the
> > Transaction log backup right after is 126MB. I tried shrinking and
> > everything else I could think of. I even deleted the DB and restored it
from
> > a full DB backup. The ldf still comes up with 11GB. I have another
> > installation of the same database on another server where the ldf shows
a
> > normal size (40MB).
> >
> > Can anybody tell me what is going on? During the restore I moved the
data
> > files to another disk that has lots of space but there is something
wrong
> > here and I would like to get to the bottom of it.
> >
> > Thanks for your help,
> >
> > Claus
> >
> >
> >|||Hi,
As soon as you run the trasnaction log backup the inactive entries of
transaction log will be cleared. But the physcal file will not get shrink
automatically until or unless you enable to database option "AUTO_SHRINK".
Turning on this option in a production server is not recommended.
Because auto shrinking will reduce the performance.
To avoid this after the transaction log backup you could do a DBCC
SHRINKFILE (refer books online) on the LDF file frequently
(Daily or weekly once.. based on the LDF growth) to reduce the LDF file
size.
DBCC SQLPERF(LOGSPACE)
will give you the exact file size and usage
Thanks
Hari
MCDBA
"cjobes" <cjobes@.nova-tech.org> wrote in message
news:uReKjIYhEHA.3992@.TK2MSFTNGP11.phx.gbl...
> I have it set to full because we do a daily backup and then hourly
> transaction log backups. The one instance works as expected but the other
> one has this 11GB ldf and I don't seem to be able to get it down.
> Now after reading your advice I switched the db to simple, run a backup
and
> then shrank the log file. Now it's down to 0.5MB. My question is,
shouldn't
> it do that automatically when the full db backup runs with the option to
> remove inactive entries from the transaction log?
> Thanks for your help,
> Claus
>
> "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > Hi, what is your recovery set too. If it is set to "Full" you need to do
a
> > translog backup, and truncate after.
> >
> > If you set it to simple, it will checkpoint and keep the log resonable.
> >
> > Try backup log (database) with truncate_only
> >
> > Then shrink the log file.
> >
> >
> > "cjobes" wrote:
> >
> > > Hi all,
> > >
> > > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
> but
> > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB
and
> the
> > > Transaction log backup right after is 126MB. I tried shrinking and
> > > everything else I could think of. I even deleted the DB and restored
it
> from
> > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > installation of the same database on another server where the ldf
shows
> a
> > > normal size (40MB).
> > >
> > > Can anybody tell me what is going on? During the restore I moved the
> data
> > > files to another disk that has lots of space but there is something
> wrong
> > > here and I would like to get to the bottom of it.
> > >
> > > Thanks for your help,
> > >
> > > Claus
> > >
> > >
> > >
>|||Thanks for the helpful advice. I will keep an eye on this.
Claus
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eWRTv$ZhEHA.2624@.TK2MSFTNGP12.phx.gbl...
> Hi,
> As soon as you run the trasnaction log backup the inactive entries of
> transaction log will be cleared. But the physcal file will not get shrink
> automatically until or unless you enable to database option "AUTO_SHRINK".
> Turning on this option in a production server is not recommended.
> Because auto shrinking will reduce the performance.
> To avoid this after the transaction log backup you could do a DBCC
> SHRINKFILE (refer books online) on the LDF file frequently
> (Daily or weekly once.. based on the LDF growth) to reduce the LDF file
> size.
> DBCC SQLPERF(LOGSPACE)
> will give you the exact file size and usage
> Thanks
> Hari
> MCDBA
>
> "cjobes" <cjobes@.nova-tech.org> wrote in message
> news:uReKjIYhEHA.3992@.TK2MSFTNGP11.phx.gbl...
> > I have it set to full because we do a daily backup and then hourly
> > transaction log backups. The one instance works as expected but the
other
> > one has this 11GB ldf and I don't seem to be able to get it down.
> >
> > Now after reading your advice I switched the db to simple, run a backup
> and
> > then shrank the log file. Now it's down to 0.5MB. My question is,
> shouldn't
> > it do that automatically when the full db backup runs with the option to
> > remove inactive entries from the transaction log?
> >
> > Thanks for your help,
> >
> > Claus
> >
> >
> > "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> > news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > > Hi, what is your recovery set too. If it is set to "Full" you need to
do
> a
> > > translog backup, and truncate after.
> > >
> > > If you set it to simple, it will checkpoint and keep the log
resonable.
> > >
> > > Try backup log (database) with truncate_only
> > >
> > > Then shrink the log file.
> > >
> > >
> > > "cjobes" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have an SQL2000 running on a W2K server. The mdf file is about
140MB
> > but
> > > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB
> and
> > the
> > > > Transaction log backup right after is 126MB. I tried shrinking and
> > > > everything else I could think of. I even deleted the DB and restored
> it
> > from
> > > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > > installation of the same database on another server where the ldf
> shows
> > a
> > > > normal size (40MB).
> > > >
> > > > Can anybody tell me what is going on? During the restore I moved the
> > data
> > > > files to another disk that has lots of space but there is something
> > wrong
> > > > here and I would like to get to the bottom of it.
> > > >
> > > > Thanks for your help,
> > > >
> > > > Claus
> > > >
> > > >
> > > >
> >
> >
>|||I have the same problem with a client as you do. I did the backup and
shrink but the log file is still at 23gigs!
Where do I find the option to 'truncate' in the SQL Server Enterprise
Manager?
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> Hi, what is your recovery set too. If it is set to "Full" you need to do a
> translog backup, and truncate after.
> If you set it to simple, it will checkpoint and keep the log resonable.
> Try backup log (database) with truncate_only
> Then shrink the log file.
>
> "cjobes" wrote:
> > Hi all,
> >
> > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
but
> > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and
the
> > Transaction log backup right after is 126MB. I tried shrinking and
> > everything else I could think of. I even deleted the DB and restored it
from
> > a full DB backup. The ldf still comes up with 11GB. I have another
> > installation of the same database on another server where the ldf shows
a
> > normal size (40MB).
> >
> > Can anybody tell me what is going on? During the restore I moved the
data
> > files to another disk that has lots of space but there is something
wrong
> > here and I would like to get to the bottom of it.
> >
> > Thanks for your help,
> >
> > Claus
> >
> >
> >|||You have to do it in the analyzer
backup log "database" with truncate_only
You can also add it as a task in a job.
"Dean Penderghast" wrote:
> I have the same problem with a client as you do. I did the backup and
> shrink but the log file is still at 23gigs!
> Where do I find the option to 'truncate' in the SQL Server Enterprise
> Manager?
> "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > Hi, what is your recovery set too. If it is set to "Full" you need to do a
> > translog backup, and truncate after.
> >
> > If you set it to simple, it will checkpoint and keep the log resonable.
> >
> > Try backup log (database) with truncate_only
> >
> > Then shrink the log file.
> >
> >
> > "cjobes" wrote:
> >
> > > Hi all,
> > >
> > > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
> but
> > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and
> the
> > > Transaction log backup right after is 126MB. I tried shrinking and
> > > everything else I could think of. I even deleted the DB and restored it
> from
> > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > installation of the same database on another server where the ldf shows
> a
> > > normal size (40MB).
> > >
> > > Can anybody tell me what is going on? During the restore I moved the
> data
> > > files to another disk that has lots of space but there is something
> wrong
> > > here and I would like to get to the bottom of it.
> > >
> > > Thanks for your help,
> > >
> > > Claus
> > >
> > >
> > >
>
>|||I did that and the log file is still 23 gigs in size. I've done a full
transaction backup and tried to resize the file and still can't get it to
shrink. Am I missing something simple here?
Dean Penderghast
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:DF200740-7079-472C-8D0B-563ECC3FBBFD@.microsoft.com...
> You have to do it in the analyzer
> backup log "database" with truncate_only
> You can also add it as a task in a job.
> "Dean Penderghast" wrote:
> > I have the same problem with a client as you do. I did the backup and
> > shrink but the log file is still at 23gigs!
> >
> > Where do I find the option to 'truncate' in the SQL Server Enterprise
> > Manager?
> >
> > "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> > news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > > Hi, what is your recovery set too. If it is set to "Full" you need to
do a
> > > translog backup, and truncate after.
> > >
> > > If you set it to simple, it will checkpoint and keep the log
resonable.
> > >
> > > Try backup log (database) with truncate_only
> > >
> > > Then shrink the log file.
> > >
> > >
> > > "cjobes" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have an SQL2000 running on a W2K server. The mdf file is about
140MB
> > but
> > > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB
and
> > the
> > > > Transaction log backup right after is 126MB. I tried shrinking and
> > > > everything else I could think of. I even deleted the DB and restored
it
> > from
> > > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > > installation of the same database on another server where the ldf
shows
> > a
> > > > normal size (40MB).
> > > >
> > > > Can anybody tell me what is going on? During the restore I moved the
> > data
> > > > files to another disk that has lots of space but there is something
> > wrong
> > > > here and I would like to get to the bottom of it.
> > > >
> > > > Thanks for your help,
> > > >
> > > > Claus
> > > >
> > > >
> > > >
> >
> >
> >sql

Help! Transaction log is 40GB

The database is not even 10MB, but the Transaction log is over 40GB!
The recovery model was set to Full (I have set it to simple now) and
have tried performing a full backup with the "Remove inactive entries
from transaction log" checked.
Any advice on how to make the transaction log behave? Thes steps above
were the ones I already new.
Some info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"hobbzilla" <hobbzilla@.hotmail.com> wrote in message
news:1133998771.980365.222680@.g49g2000cwa.googlegr oups.com...
> The database is not even 10MB, but the Transaction log is over 40GB!
> The recovery model was set to Full (I have set it to simple now) and
> have tried performing a full backup with the "Remove inactive entries
> from transaction log" checked.
> Any advice on how to make the transaction log behave? Thes steps above
> were the ones I already new.
>
|||Running
DBCC SHRINKFILE(databasename_log, 2)
cleaned it up to 2MB from 40GB. Shouldn't this automatically happen
after a maintenence plan is run doing a full backup of the database?
|||hobbzilla wrote:
> Running
> DBCC SHRINKFILE(databasename_log, 2)
> cleaned it up to 2MB from 40GB. Shouldn't this automatically happen
> after a maintenence plan is run doing a full backup of the database?
>
No. A full backup will only backup the file but it will preserve the
unused space. It's also the normal procedure that you don't shrink the
file unless there's a specific reason for why it has grown to that size.
If it's due to normal production, the server will just have to use
resources to grow the file again.
Regards
Steen

Help! Transaction log is 40GB

The database is not even 10MB, but the Transaction log is over 40GB!
The recovery model was set to Full (I have set it to simple now) and
have tried performing a full backup with the "Remove inactive entries
from transaction log" checked.
Any advice on how to make the transaction log behave? Thes steps above
were the ones I already new.Some info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"hobbzilla" <hobbzilla@.hotmail.com> wrote in message
news:1133998771.980365.222680@.g49g2000cwa.googlegroups.com...
> The database is not even 10MB, but the Transaction log is over 40GB!
> The recovery model was set to Full (I have set it to simple now) and
> have tried performing a full backup with the "Remove inactive entries
> from transaction log" checked.
> Any advice on how to make the transaction log behave? Thes steps above
> were the ones I already new.
>|||Running
DBCC SHRINKFILE(databasename_log, 2)
cleaned it up to 2MB from 40GB. Shouldn't this automatically happen
after a maintenence plan is run doing a full backup of the database?|||hobbzilla wrote:
> Running
> DBCC SHRINKFILE(databasename_log, 2)
> cleaned it up to 2MB from 40GB. Shouldn't this automatically happen
> after a maintenence plan is run doing a full backup of the database?
>
No. A full backup will only backup the file but it will preserve the
unused space. It's also the normal procedure that you don't shrink the
file unless there's a specific reason for why it has grown to that size.
If it's due to normal production, the server will just have to use
resources to grow the file again.
Regards
Steen

Help! Transaction log is 40GB

The database is not even 10MB, but the Transaction log is over 40GB!
The recovery model was set to Full (I have set it to simple now) and
have tried performing a full backup with the "Remove inactive entries
from transaction log" checked.
Any advice on how to make the transaction log behave? Thes steps above
were the ones I already new.Some info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"hobbzilla" <hobbzilla@.hotmail.com> wrote in message
news:1133998771.980365.222680@.g49g2000cwa.googlegroups.com...
> The database is not even 10MB, but the Transaction log is over 40GB!
> The recovery model was set to Full (I have set it to simple now) and
> have tried performing a full backup with the "Remove inactive entries
> from transaction log" checked.
> Any advice on how to make the transaction log behave? Thes steps above
> were the ones I already new.
>|||Running
DBCC SHRINKFILE(databasename_log, 2)
cleaned it up to 2MB from 40GB. Shouldn't this automatically happen
after a maintenence plan is run doing a full backup of the database?|||hobbzilla wrote:
> Running
> DBCC SHRINKFILE(databasename_log, 2)
> cleaned it up to 2MB from 40GB. Shouldn't this automatically happen
> after a maintenence plan is run doing a full backup of the database?
>
No. A full backup will only backup the file but it will preserve the
unused space. It's also the normal procedure that you don't shrink the
file unless there's a specific reason for why it has grown to that size.
If it's due to normal production, the server will just have to use
resources to grow the file again.
Regards
Steen

HELP! Transaction log huge, & I'm out of space!

I have several .ldf files for one of my databases and one
of them is taking up 23 gigs. I really dont need to save
the transaction logs so I would like to just delete them
and start with a smaller log file. We created another
log file on c: now just so we can keep things running but
that is going to fill up very soon as well.
Is there any ways I can just delete the all the
transaction logs and just create one that is very small?
When I try to delete them I get an error message that
says I cant delete the file because it is not empty.
When I take the database offline to delete the file I
cant do that either because I cant access the database.
Any suggestions?Two things:
A) Put your databases into Simple Recovery mode. They're probably in
Full Recovery mode now, which is why your logs are growing so quickly.
B) To truncate the logs, run:
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
Then use sp_helpfile to get the file ID of the log file(s), and for
each, run:
DBCC SHRINKFILE(<FileID>, EMPTYFILE)
This should drop any space available back to the operating system.
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?|||Hi,
Detach the database. Then delete the .ldf (rename it to be on the safe
side). Then reattach the database. It will create a new .ldf. Once this
is achieved, you can permanently delete that .ldf.
If you never need the .ldf, change the recovery model to simple.
HTH,
Bernie Yaeger
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?|||You can't attach a database with multiple log files using
sp_attach_single_file_db. The method Adam suggested is the right approach
to remove unneeded log files.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:hsuYc.11778$Es2.6336186@.news4.srv.hcvlny.cv.net...
> Hi,
> Detach the database. Then delete the .ldf (rename it to be on the safe
> side). Then reattach the database. It will create a new .ldf. Once this
> is achieved, you can permanently delete that .ldf.
> If you never need the .ldf, change the recovery model to simple.
> HTH,
> Bernie Yaeger
> "Groundid" <groundid@.comcast.net> wrote in message
> news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
>> I have several .ldf files for one of my databases and one
>> of them is taking up 23 gigs. I really dont need to save
>> the transaction logs so I would like to just delete them
>> and start with a smaller log file. We created another
>> log file on c: now just so we can keep things running but
>> that is going to fill up very soon as well.
>> Is there any ways I can just delete the all the
>> transaction logs and just create one that is very small?
>> When I try to delete them I get an error message that
>> says I cant delete the file because it is not empty.
>> When I take the database offline to delete the file I
>> cant do that either because I cant access the database.
>> Any suggestions?
>|||Hi Dan,
If you either delete or rename (at the DOS level) the log file, you will not
be attaching a database with multiple log files.
Bernie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O7raWMjjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> You can't attach a database with multiple log files using
> sp_attach_single_file_db. The method Adam suggested is the right approach
> to remove unneeded log files.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:hsuYc.11778$Es2.6336186@.news4.srv.hcvlny.cv.net...
> > Hi,
> >
> > Detach the database. Then delete the .ldf (rename it to be on the safe
> > side). Then reattach the database. It will create a new .ldf. Once
this
> > is achieved, you can permanently delete that .ldf.
> >
> > If you never need the .ldf, change the recovery model to simple.
> >
> > HTH,
> >
> > Bernie Yaeger
> >
> > "Groundid" <groundid@.comcast.net> wrote in message
> > news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> >> I have several .ldf files for one of my databases and one
> >> of them is taking up 23 gigs. I really dont need to save
> >> the transaction logs so I would like to just delete them
> >> and start with a smaller log file. We created another
> >> log file on c: now just so we can keep things running but
> >> that is going to fill up very soon as well.
> >>
> >> Is there any ways I can just delete the all the
> >> transaction logs and just create one that is very small?
> >> When I try to delete them I get an error message that
> >> says I cant delete the file because it is not empty.
> >> When I take the database offline to delete the file I
> >> cant do that either because I cant access the database.
> >> Any suggestions?
> >
> >
>|||"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> If you either delete or rename (at the DOS level) the log file, you will
not
> be attaching a database with multiple log files.
Bernie,
To understand what Dan is talking about (and to get a glimpse into how I
once destroyed a weeks' worth of work and decided to never again manually
delete the log file):
Create a database with two log files. Detach it. Delete one (or both)
of the log files. Now try to re-attach it.|||Hi groundid,
Dont delete, just shrink the file. Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Idetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
4. Check the current file size by execution
DBCC SQLPERF(LOGSPACE)
Have a look into the below article on shrink file,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Note:
Please perform a full database backup after performing the above steps, as
well as schedule a transaction log backup frequently based on ur
requirement.
Thanks
Hari
MCDBA
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?|||Bernie, try the script below.
CREATE DATABASE TwoLogFiles ON
(NAME='TwoLogFiles',
FILENAME='C:\TwoLogFiles.mdf')
LOG ON
(NAME='TwoLogFiles_Log1',
FILENAME='C:\TwoLogFiles_Log1.ldf'),
(NAME='TwoLogFiles_Log2',
FILENAME='C:\TwoLogFiles_Log2.ldf')
EXEC sp_detach_db 'TwoLogFiles'
EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
EXEC sp_attach_db
'TwoLogFiles',
'C:\TwoLogFiles.mdf',
'C:\TwoLogFiles_Log1.ldf'
EXEC sp_attach_single_file_db
'TwoLogFiles',
'C:\TwoLogFiles.mdf'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> Hi Dan,
> If you either delete or rename (at the DOS level) the log file, you will
> not
> be attaching a database with multiple log files.
> Bernie
>|||Hi Dan, Adam,
Why are you guys talking about 2 transaction logs? This guy has one.
Here's a process I've used countless times and haven't experienced any
problem:
1.. right click the database in em;
2.. select all tasks;
3.. select detach database;
4.. proceed to detach;
5.. delete the .ldf at the DOS level;
6.. right click the 'databases' line beneath the server in em; all tasks;
attach the .mdf (browse for it to the appropriate sub);
7.. it will automatically create its own blank transaction log;
I am using the simple recovery model, but I don't know if this makes any
difference.
Now tell me why this doesn't work in your opinion and explain to me why it
always works for me (SQL Server 2000).
Bernie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uvhweXkjEHA.536@.TK2MSFTNGP11.phx.gbl...
> Bernie, try the script below.
> CREATE DATABASE TwoLogFiles ON
> (NAME='TwoLogFiles',
> FILENAME='C:\TwoLogFiles.mdf')
> LOG ON
> (NAME='TwoLogFiles_Log1',
> FILENAME='C:\TwoLogFiles_Log1.ldf'),
> (NAME='TwoLogFiles_Log2',
> FILENAME='C:\TwoLogFiles_Log2.ldf')
> EXEC sp_detach_db 'TwoLogFiles'
> EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
> EXEC sp_attach_db
> 'TwoLogFiles',
> 'C:\TwoLogFiles.mdf',
> 'C:\TwoLogFiles_Log1.ldf'
> EXEC sp_attach_single_file_db
> 'TwoLogFiles',
> 'C:\TwoLogFiles.mdf'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> > Hi Dan,
> >
> > If you either delete or rename (at the DOS level) the log file, you will
> > not
> > be attaching a database with multiple log files.
> >
> > Bernie
> >
>|||Hi Dan, Adam,
Why are you guys talking about 2 transaction logs? This guy has one.
Here's a process I've used countless times and haven't experienced any
problem:
1.. right click the database in em;
2.. select all tasks;
3.. select detach database;
4.. proceed to detach;
5.. delete the .ldf at the DOS level;
6.. right click the 'databases' line beneath the server in em; all tasks;
attach the .mdf (browse for it to the appropriate sub);
7.. it will automatically create its own blank transaction log;
I am using the simple recovery model, but I don't know if this makes any
difference.
Now tell me why this doesn't work in your opinion and explain to me why it
always works for me (SQL Server 2000).
Bernie
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23k44GyjjEHA.1136@.tk2msftngp13.phx.gbl...
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> >
> > If you either delete or rename (at the DOS level) the log file, you will
> not
> > be attaching a database with multiple log files.
> Bernie,
> To understand what Dan is talking about (and to get a glimpse into how
I
> once destroyed a weeks' worth of work and decided to never again manually
> delete the log file):
> Create a database with two log files. Detach it. Delete one (or
both)
> of the log files. Now try to re-attach it.
>|||Bernie,
> Why are you guys talking about 2 transaction logs? This guy has one.
Here's a quote from the OP:
"I have several .ldf files for one of my databases and one
of them is taking up 23 gigs."
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/|||> Why are you guys talking about 2 transaction logs? This guy has one.
As Tibor mentioned, the OP has multiple logs.
The procedure you suggest out to work with a single log file but is not the
preferred method. Even with a single log, the best approach to reduce log
file size is DBCC SHRINKFILE. This doesn't require the database to be
detached.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:MiFYc.18971$Es2.8127856@.news4.srv.hcvlny.cv.net...
> Hi Dan, Adam,
> Why are you guys talking about 2 transaction logs? This guy has one.
> Here's a process I've used countless times and haven't experienced any
> problem:
> 1.. right click the database in em;
> 2.. select all tasks;
> 3.. select detach database;
> 4.. proceed to detach;
> 5.. delete the .ldf at the DOS level;
> 6.. right click the 'databases' line beneath the server in em; all tasks;
> attach the .mdf (browse for it to the appropriate sub);
> 7.. it will automatically create its own blank transaction log;
> I am using the simple recovery model, but I don't know if this makes any
> difference.
> Now tell me why this doesn't work in your opinion and explain to me why it
> always works for me (SQL Server 2000).
> Bernie
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uvhweXkjEHA.536@.TK2MSFTNGP11.phx.gbl...
>> Bernie, try the script below.
>> CREATE DATABASE TwoLogFiles ON
>> (NAME='TwoLogFiles',
>> FILENAME='C:\TwoLogFiles.mdf')
>> LOG ON
>> (NAME='TwoLogFiles_Log1',
>> FILENAME='C:\TwoLogFiles_Log1.ldf'),
>> (NAME='TwoLogFiles_Log2',
>> FILENAME='C:\TwoLogFiles_Log2.ldf')
>> EXEC sp_detach_db 'TwoLogFiles'
>> EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
>> EXEC sp_attach_db
>> 'TwoLogFiles',
>> 'C:\TwoLogFiles.mdf',
>> 'C:\TwoLogFiles_Log1.ldf'
>> EXEC sp_attach_single_file_db
>> 'TwoLogFiles',
>> 'C:\TwoLogFiles.mdf'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
>> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
>> > Hi Dan,
>> >
>> > If you either delete or rename (at the DOS level) the log file, you
>> > will
>> > not
>> > be attaching a database with multiple log files.
>> >
>> > Bernie
>> >
>>
>|||Hi Tibor,
You're correct - I misread his question. Tx
Bernie
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23l2Sv9ojEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Bernie,
> > Why are you guys talking about 2 transaction logs? This guy has one.
> Here's a quote from the OP:
> "I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs."
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||Hi Dan,
You're correct - I misread the initial question. Sorry.
Bernie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ewXt$lpjEHA.2788@.tk2msftngp13.phx.gbl...
> > Why are you guys talking about 2 transaction logs? This guy has one.
> As Tibor mentioned, the OP has multiple logs.
> The procedure you suggest out to work with a single log file but is not
the
> preferred method. Even with a single log, the best approach to reduce log
> file size is DBCC SHRINKFILE. This doesn't require the database to be
> detached.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:MiFYc.18971$Es2.8127856@.news4.srv.hcvlny.cv.net...
> > Hi Dan, Adam,
> >
> > Why are you guys talking about 2 transaction logs? This guy has one.
> > Here's a process I've used countless times and haven't experienced any
> > problem:
> > 1.. right click the database in em;
> > 2.. select all tasks;
> > 3.. select detach database;
> > 4.. proceed to detach;
> > 5.. delete the .ldf at the DOS level;
> > 6.. right click the 'databases' line beneath the server in em; all
tasks;
> > attach the .mdf (browse for it to the appropriate sub);
> > 7.. it will automatically create its own blank transaction log;
> > I am using the simple recovery model, but I don't know if this makes any
> > difference.
> >
> > Now tell me why this doesn't work in your opinion and explain to me why
it
> > always works for me (SQL Server 2000).
> >
> > Bernie
> >
> > "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> > news:uvhweXkjEHA.536@.TK2MSFTNGP11.phx.gbl...
> >> Bernie, try the script below.
> >>
> >> CREATE DATABASE TwoLogFiles ON
> >> (NAME='TwoLogFiles',
> >> FILENAME='C:\TwoLogFiles.mdf')
> >> LOG ON
> >> (NAME='TwoLogFiles_Log1',
> >> FILENAME='C:\TwoLogFiles_Log1.ldf'),
> >> (NAME='TwoLogFiles_Log2',
> >> FILENAME='C:\TwoLogFiles_Log2.ldf')
> >>
> >> EXEC sp_detach_db 'TwoLogFiles'
> >>
> >> EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
> >>
> >> EXEC sp_attach_db
> >> 'TwoLogFiles',
> >> 'C:\TwoLogFiles.mdf',
> >> 'C:\TwoLogFiles_Log1.ldf'
> >>
> >> EXEC sp_attach_single_file_db
> >> 'TwoLogFiles',
> >> 'C:\TwoLogFiles.mdf'
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> >> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> >> > Hi Dan,
> >> >
> >> > If you either delete or rename (at the DOS level) the log file, you
> >> > will
> >> > not
> >> > be attaching a database with multiple log files.
> >> >
> >> > Bernie
> >> >
> >>
> >>
> >
> >
>

HELP! Transaction Log Backup!

Hello!!

I am relatively new to SQL and am having a problem. I have taken over
a SQL 2000 db that did not have any backup plans scheduled for it. I
created a maintenance plan that backs up the database every night and
another plan that backs up the system databases once a week. I ran
both these jobs successfully, but when I created a third plan to
backup the transaction log it fails with the following message:
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed. I then executed the following
statement: sp_grantlogin 'NT Authority\System' which did not work.
I still do not understand why I can't backup the log? The database
backs up fine with no errors so it can't be a permission thing and I
created the job with a maintenance plan so it has to be in full
recovery mode. Right? How do I tell? Thank you for all your
responses!!

NS"tech_guru" <nstimely@.cityofpasadena.net> wrote in message
news:3ff7efe5.0409091230.2126a3af@.posting.google.c om...
> Hello!!
> I am relatively new to SQL and am having a problem. I have taken over
> a SQL 2000 db that did not have any backup plans scheduled for it. I
> created a maintenance plan that backs up the database every night and
> another plan that backs up the system databases once a week. I ran
> both these jobs successfully, but when I created a third plan to
> backup the transaction log it fails with the following message:
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> 42000] (Error 22029). The step failed. I then executed the following
> statement: sp_grantlogin 'NT Authority\System' which did not work.
> I still do not understand why I can't backup the log? The database
> backs up fine with no errors so it can't be a permission thing and I
> created the job with a maintenance plan so it has to be in full
> recovery mode. Right? How do I tell? Thank you for all your
> responses!!

It probably is a permissions problem.

Where are you backing up to? If it's any place other than the same box, you
need to run SQL admin as a domain account, SYSTEM only has rights on the
local system.

> NS|||tech_guru (nstimely@.cityofpasadena.net) writes:
> I am relatively new to SQL and am having a problem. I have taken over
> a SQL 2000 db that did not have any backup plans scheduled for it. I
> created a maintenance plan that backs up the database every night and
> another plan that backs up the system databases once a week. I ran
> both these jobs successfully, but when I created a third plan to
> backup the transaction log it fails with the following message:
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> 42000] (Error 22029). The step failed. I then executed the following
> statement: sp_grantlogin 'NT Authority\System' which did not work.
> I still do not understand why I can't backup the log? The database
> backs up fine with no errors so it can't be a permission thing and I
> created the job with a maintenance plan so it has to be in full
> recovery mode. Right? How do I tell? Thank you for all your
> responses!!

I would double-check that the database is not in simple recovery mode.

One way to find out what the problem is, would be to go to the job
step, find the Advanced tab, and specify an output file. Hopefully
you can get a better error message that way.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have the same problem. I output the log and recevied the following
message:

Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
Server 'O4' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'LouiseTest MaintenancePlan_2' on 1/28/2005
1:59:53 PM
Backup can not be performed on database 'LouiseTest'. This sub task is
ignored.

End of maintenance plan 'LouiseTest MaintenancePlan_2' on 1/28/2005
1:59:53 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)

What setting must I change? Is it an access thing?

Erland Sommarskog wrote:
> tech_guru (nstimely@.cityofpasadena.net) writes:
> > I am relatively new to SQL and am having a problem. I have taken
over
> > a SQL 2000 db that did not have any backup plans scheduled for it.
I
> > created a maintenance plan that backs up the database every night
and
> > another plan that backs up the system databases once a week. I ran
> > both these jobs successfully, but when I created a third plan to
> > backup the transaction log it fails with the following message:
> > Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed.
[SQLSTATE
> > 42000] (Error 22029). The step failed. I then executed the
following
> > statement: sp_grantlogin 'NT Authority\System' which did not
work.
> > I still do not understand why I can't backup the log? The
database
> > backs up fine with no errors so it can't be a permission thing and
I
> > created the job with a maintenance plan so it has to be in full
> > recovery mode. Right? How do I tell? Thank you for all your
> > responses!!
> I would double-check that the database is not in simple recovery
mode.
> One way to find out what the problem is, would be to go to the job
> step, find the Advanced tab, and specify an output file. Hopefully
> you can get a better error message that way.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||chrispycrunch (chrispycrunch@.gmail.com) writes:
> I have the same problem. I output the log and recevied the following
> message:
>
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
> Server 'O4' as 'NT AUTHORITY\SYSTEM' (trusted)
> Starting maintenance plan 'LouiseTest MaintenancePlan_2' on 1/28/2005
> 1:59:53 PM
> Backup can not be performed on database 'LouiseTest'. This sub task is
> ignored.
> End of maintenance plan 'LouiseTest MaintenancePlan_2' on 1/28/2005
> 1:59:53 PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
> What setting must I change? Is it an access thing?

And just like the guy who had the same problem, you need to find the
job under SQL Server Agent->Jobs, right-click it and Select View job
history and check the box "Show step details" in the upper-right corner.
Then you will find what the error actually is.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks - that worked. Another problem is transaction backups:

Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.

The database is on a network drive.
I am creating the plan locally. Could that be an issue? The same thing
is done for index tuning and database backup (i figured out you should
save the files by the default location) and those tasks work.|||chrispycrunch (chrispycrunch@.gmail.com) writes:
> Thanks - that worked. Another problem is transaction backups:
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> 42000] (Error 22029). The step failed.
> The database is on a network drive.

And the actual error message is?

When performing any access to network drives from SQL Server, including
backups, the account under which SQL Server runs must have access to
the network drive. Typically this is not the case if the SQL Server runs
as Local System. The remedy is run SQL Server under a domain user with
the necessary access rights.

You change the account for SQL Server by right-clicking My Computer,
select Manage. Find Services, then the MS SQL Server service. Open it,
and log-on information is on the second tab.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, for the help and for tolerating my easy questions...I'm very
new to the 'admin' of NT, SQL, etc.
Also, I noticed the account was locked out, so see if that does the
trick.|||Update: that didn't work - i'm frustrated! Is there a book or website
that can give further instructions on windows security?|||chrispycrunch (chrispycrunch@.gmail.com) writes:
> Update: that didn't work - i'm frustrated!

And the error message is?

To repeat from earlier in the thread:

> Is there a book or website that can give further instructions on windows
> security?

Probably, but Windows is not my speciality. Anyway, without the error
message, books are not going to help you.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help! The transaction log is full error in SSIS Execute SQL Task when I execute a DELETE SQL que

Dear all:

I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :

Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".

So this confused me, any one has any experience on this?

Many thanks,

Tomorrow

Please help me ~~~

|||This issue seems tobe more related to the database engine; you maybe in better luck there...|||

Lets be clear, the transaction log and management is a SQL engine issue, and nothing to do with SSIS. You could run the same SQL in any query submission tool, and would fail in the same manner.

Troubleshooting a Full Transaction Log (Error 9002)
(http://msdn2.microsoft.com/en-us/library/ms175495.aspx)

Just because you have 6GB of free disk space does not mean that disk space is available to the log file. What are the growth options? I strongly believe is very bad practice to rely on auto-growth of data or log files. It can seriously impact performance of a system, so should be actively managed rather than forgotten.

|||

Thanks all.

Tomorrow

Help! The transaction log is full error in SSIS Execute SQL Task when I execute a DELETE SQL que

Dear all:

I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :

But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".

So this confused me, any one has any experience on this?

Many thanks,

Tomorrow

Please help me ~~~

|||This issue seems tobe more related to the database engine; you maybe in better luck there...|||

Lets be clear, the transaction log and management is a SQL engine issue, and nothing to do with SSIS. You could run the same SQL in any query submission tool, and would fail in the same manner.

Troubleshooting a Full Transaction Log (Error 9002)
(http://msdn2.microsoft.com/en-us/library/ms175495.aspx)

|||

Thanks all.

Tomorrow

Help! The transaction log is full error in SSIS Execute SQL Task when I execute a DELETE SQL

Dear all:

I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :

But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".

So this confused me, any one has any experience on this?

Many thanks,

Tomorrow

Please help me ~~~

|||This issue seems tobe more related to the database engine; you maybe in better luck there...|||

Lets be clear, the transaction log and management is a SQL engine issue, and nothing to do with SSIS. You could run the same SQL in any query submission tool, and would fail in the same manner.

Troubleshooting a Full Transaction Log (Error 9002)
(http://msdn2.microsoft.com/en-us/library/ms175495.aspx)

|||

Thanks all.

Tomorrow

Friday, March 23, 2012

help! no clue on my requirement and where to start

Hi,
I am not even sure where and what to do on this requirements
"If the system crashes before all the information during transaction fails, none of those changes will be saved to the database."
what kind of info you need from my DB?
no clue.
thanksHas the instructor covered the "ACID" requirements in class or homework yet?

-PatPsql

Monday, March 19, 2012

HELP! I have deleted my LDF file

I have delated my transaction log file (it was 1,3 Gb big). I'm trying everything to recover my DB without success..
Does someone could help me
thanks a lot, GiovanniHi,
Did you detached the database and deleted the transaction log file, or just
stopped SQL server and deleted the file.
If you deleted the file after detaching then usethe system procedure to
attach the database with single MDF file. (Make it a try even if you
have deleted the file with out detach)
sp_attach_single_file_db 'DBNAME','location and filename of MDF file'
(Refer books online for more info
The command will fail incase if you have more than one LDF otr MDF file.
If you have mutiple MDF/LDF files you may need to restore from last
successful database backup or contact Microsot support.
Thanks
Hari
MCDBA
"Giovanni BO" <giovanni.gambaro@.businessobjects.com> wrote in message
news:955A7588-A85F-4DB4-BB2C-802780350503@.microsoft.com...
> I have delated my transaction log file (it was 1,3 Gb big). I'm trying
everything to recover my DB without success...
> Does someone could help me?
> thanks a lot, Giovanni

HELP! I have deleted my LDF file

I have delated my transaction log file (it was 1,3 Gb big). I'm trying every
thing to recover my DB without success...
Does someone could help me?
thanks a lot, GiovanniHi,
Did you detached the database and deleted the transaction log file, or just
stopped SQL server and deleted the file.
If you deleted the file after detaching then usethe system procedure to
attach the database with single MDF file. (Make it a try even if you
have deleted the file with out detach)
sp_attach_single_file_db 'DBNAME','location and filename of MDF file'
(Refer books online for more info
The command will fail incase if you have more than one LDF otr MDF file.
If you have mutiple MDF/LDF files you may need to restore from last
successful database backup or contact Microsot support.
Thanks
Hari
MCDBA
"Giovanni BO" <giovanni.gambaro@.businessobjects.com> wrote in message
news:955A7588-A85F-4DB4-BB2C-802780350503@.microsoft.com...
> I have delated my transaction log file (it was 1,3 Gb big). I'm trying
everything to recover my DB without success...
> Does someone could help me?
> thanks a lot, Giovanni

Monday, March 12, 2012

Help! Don't understand transactions

Hi guys,
I'm having troubles with transaction control.
I recently decided to add transaction control to some operations on an
app I am developing (Dreamweaver MX 2004, ASP, VBScript, ADO and
SQLServer 2000).
I started by doing a test web page with an update of up to 3 different
tables driven by simple pushbuttons, and more buttonds for BEGIN TRAN,
COMMIT and ROLLBACK.
Worked perfectly. It did exactly what I wanted, and even though I was
using separate record set variable for each of the commands and tables,
and closing my connection after every BEGIN TRAN.
So I decided to build that same kind of logic in my app, and test it. I
built in an error in a chain of inserts and updates I am doing, and I
put an error trap around each of the possible error-producing actions.
When an error occur, I check if a transaction has been started (which is
indicated by a flag set by my code when calling up the BEGIN TRAN), and
if yes, I rollback.
The results are very unimpressive: it doesn't work. The error that I
built in is to insert a duplicate row, but before that, I have inserted
proper rows. The code detects the error, executes the roll back, but the
rows inserted after the BEGIN TRAN and before the error are in the DB !
I.e. it looks like the BEGIN TRAN does not work. How can I check this,
and what could be the reasons that my test app works fine, and my real
app doesn't ? In the real app, the error is happening on a stored
procedure call, which is the one inserting the rows. Is the behaviour of
an SP different than a normal SQL statement sent over ADO ?
I have attached my test page.
Can you tell me:
- when should I issue the BEGIN TRAN ? Before the very first INSERT /
UPDATE, or can it be even before that (I do quite a bit of reading
(SELECT) before doing the first INSERT)
- on which connection
- should I have all the updating / inserting SQL statements going on one
and the same connection, or can I use different connections ?
- somebody told me there was a difference if I was using the
ActiveConnection object as opposed to just a connection object.
- I changed my routine that does the BEGIN / COMMIT / ROLLBACK to this
in my real app:
sub TransControl(action)
Set conn= Server.CreateObject("ADODB.Connection")
conn.Open MM_SDS_STRING
select case action
case "BEGIN"
sql= "BEGIN TRANSACTION"
Response.Write("Starting transaction <br>")
case "COMMIT"
sql= "COMMIT TRANSACTION"
Response.Write("Committing transaction <br>")
case "ROLLBACK"
sql= "ROLLBACK TRANSACTION"
Response.Write("Rolling back transaction <br>")
case else
end select
Set rs= conn.Execute(sql)
conn.Close
set conn= nothing
set rs = nothing
end sub
Does this make any difference to my DoIt routine in the test page ?
- and finally: I just noticed that I use the follwing 2 syntaxes:
conn.Open <connection string>
and also:
conn.Open = <connection string>
What's the right syntax ? Starngely, both seem to work
I am a bit lost, and would appreciate a lot if you could point me in the
right direction.
Thanks
Bernard
bthouin wrote:[vbcol=seagreen]
The transaction must always be on the same connection. You want a
transaction to run as quickly as possible, so issue if you are executing
multiple procedures and they must be a part of the same transaction,
issue the begin tran, execute the procedures, and then rollback or
commit the transaction.
For example:
create table tran_test( col1 int)
go
create proc dbo.tran_test_insert (@.i int)
as
Insert into dbo.tran_test (col1) values (@.i)
go
-- test 1
Select * from dbo.tran_test
Begin Tran
Exec dbo.tran_test_insert 1
Exec dbo.tran_test_insert 2
Exec dbo.tran_test_insert 3
Rollback
Select * from dbo.tran_test
go
-- test 2
Select * from dbo.tran_test
Begin Tran
Exec dbo.tran_test_insert 1
Exec dbo.tran_test_insert 2
Exec dbo.tran_test_insert 3
Commit Tran
Select * from dbo.tran_test
go
drop proc dbo.tran_test_insert
go
drop table dbo.tran_test
go
David Gugick
Imceda Software
www.imceda.com
|||Hi David,
Thanks for answer. I'm aware of the need to have quick transactions. But
I'm not working directly at the DB level, i.e. I'm not only using stored
procedures, I'm mostly building my SQL statements and executing them
through ADO. So your example is clear, but I can't apply it directly.
For one, there is no explicit connection in stored procedures. So that
makes it harder for me, as I am using the typical VB-like ASP ADO
commands. And Dreamweaver, when you let it generate all the data access
stuff, builds by default a new connection for every access, and closes
it after the access, plus it destroys the connection object. Although I
am not using Dreamweaver-generating capabilities, I have "inherited"
that usage concept of - constructing the access object; - accessing the
data; - closing the connection and destroying the access object, just to
keep memory clean.
And, what IS a connection ? Is it that which is established by saying:
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open <connection string>
Or the alternative:
Set rs_T = Server.CreateObject("ADODB.Command")
rs.ActiveConnection = <connection string>
?
Is there any difference between the 2 possibilities ?
What's the difference between the "conn" connection object in the 1st
possibility and the "rs" command object (also called record set) in the
2nd possibility ?
All this is not clear to me, and I'd be VERY thankful for some answers.
Regards
Bernard
David Gugick wrote:
> bthouin wrote:
>
> The transaction must always be on the same connection. You want a
> transaction to run as quickly as possible, so issue if you are executing
> multiple procedures and they must be a part of the same transaction,
> issue the begin tran, execute the procedures, and then rollback or
> commit the transaction.
> For example:
> create table tran_test( col1 int)
> go
> create proc dbo.tran_test_insert (@.i int)
> as
> Insert into dbo.tran_test (col1) values (@.i)
> go
> -- test 1
> Select * from dbo.tran_test
> Begin Tran
> Exec dbo.tran_test_insert 1
> Exec dbo.tran_test_insert 2
> Exec dbo.tran_test_insert 3
> Rollback
> Select * from dbo.tran_test
> go
> -- test 2
> Select * from dbo.tran_test
> Begin Tran
> Exec dbo.tran_test_insert 1
> Exec dbo.tran_test_insert 2
> Exec dbo.tran_test_insert 3
> Commit Tran
> Select * from dbo.tran_test
> go
> drop proc dbo.tran_test_insert
> go
> drop table dbo.tran_test
> go
>
>
|||bthouin wrote:
> Hi David,
> Thanks for answer. I'm aware of the need to have quick transactions.
> But I'm not working directly at the DB level, i.e. I'm not only using
> stored procedures, I'm mostly building my SQL statements and
> executing them through ADO. So your example is clear, but I can't
> apply it directly. For one, there is no explicit connection in stored
> procedures. So that makes it harder for me, as I am using the typical
> VB-like ASP ADO commands. And Dreamweaver, when you let it generate
> all the data access stuff, builds by default a new connection for
> every access, and closes it after the access, plus it destroys the
> connection object. Although I am not using Dreamweaver-generating
> capabilities, I have "inherited" that usage concept of - constructing
> the access object; - accessing the data; - closing the connection and
> destroying the access object, just to keep memory clean.
>
[vbcol=seagreen]
That's not true. It may be that the way the code is generated, the
procedure executes on a short-lived connection, but one is there. I
don't know if this is a web app or not, but in any case, using that
paradigm does not allow you to use a transaction the way you want. Every
time you create a new connection, by using Conn.Open, you get a "new"
connection. It's possible if you are using connection pooling (and you
should be with that type of design) that you "could" get the same
connection twice, but you can never depend on it.
If you need to mix procedures and SQL in any combination and have those
statements be in the same transaction, you need to use the same
connection.
David Gugick
Imceda Software
www.imceda.com

Wednesday, March 28, 2012

HELP! Transaction Log is huge

Help! Transaction log is 40GB

Help! Transaction log is 40GB

Help! Transaction log is 40GB

HELP! Transaction log huge, & I'm out of space!

HELP! Transaction log huge, & I'm out of space!

HELP! Transaction log huge, & I'm out of space!

HELP! Transaction Log Backup!

Help! The transaction log is full error in SSIS Execute SQL Task when I execute a DELETE SQL que

Help! The transaction log is full error in SSIS Execute SQL Task when I execute a DELETE SQL que

Help! The transaction log is full error in SSIS Execute SQL Task when I execute a DELETE SQL

Friday, March 23, 2012

help! no clue on my requirement and where to start

Monday, March 19, 2012

HELP! I have deleted my LDF file

HELP! I have deleted my LDF file

Monday, March 12, 2012

Help! Don't understand transactions

Help with update forms

Blog Archive

About Me