I have a DB with two transaction log files. I'd like have
one of them removed.
After running the following set of commands successfully...
backup log db_tdadatamart with truncate_only
dbcc shrinkfile ('datamartLog', EMPTYFILE)
...when I try to remove the second file using this
command...
alter database db_datamart
remove file datamartLog
...I get this error:
The file 'datamartLog' cannot be removed because it is not
empty.
Thanks in advance for your help.Run DBCC OPENTRAN to ensure there are no open transactions. And you might
want to make sure you have adone a log backup after that command as well.
--
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
> I have a DB with two transaction log files. I'd like have
> one of them removed.
> After running the following set of commands successfully...
> backup log db_tdadatamart with truncate_only
> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> ...when I try to remove the second file using this
> command...
> alter database db_datamart
> remove file datamartLog
> ...I get this error:
> The file 'datamartLog' cannot be removed because it is not
> empty.
> Thanks in advance for your help.|||'No active open transactions' were reported... still
encoutner the same problem.
Thanks.
>--Original Message--
>Run DBCC OPENTRAN to ensure there are no open
transactions. And you might
>want to make sure you have adone a log backup after that
command as well.
>--
>Andrew J. Kelly SQL MVP
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
>> I have a DB with two transaction log files. I'd like
have
>> one of them removed.
>> After running the following set of commands
successfully...
>> backup log db_tdadatamart with truncate_only
>> dbcc shrinkfile ('datamartLog', EMPTYFILE)
>> ...when I try to remove the second file using this
>> command...
>> alter database db_datamart
>> remove file datamartLog
>> ...I get this error:
>> The file 'datamartLog' cannot be removed because it is
not
>> empty.
>> Thanks in advance for your help.
>
>.
>|||Did you do a Log backup? Any chance this is related?
http://support.microsoft.com/default.aspx?scid=kb;en-us;324432&Product=sql2k
--
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx.gbl...
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
> >--Original Message--
> >Run DBCC OPENTRAN to ensure there are no open
> transactions. And you might
> >want to make sure you have adone a log backup after that
> command as well.
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
> >> I have a DB with two transaction log files. I'd like
> have
> >> one of them removed.
> >>
> >> After running the following set of commands
> successfully...
> >>
> >> backup log db_tdadatamart with truncate_only
> >> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> >>
> >> ...when I try to remove the second file using this
> >> command...
> >>
> >> alter database db_datamart
> >> remove file datamartLog
> >>
> >> ...I get this error:
> >>
> >> The file 'datamartLog' cannot be removed because it is
> not
> >> empty.
> >>
> >> Thanks in advance for your help.
> >
> >
> >.
> >|||I have some information about DBCC LOGINFO etc on my article regarding shrinking of database files:
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/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx.gbl...
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
> >--Original Message--
> >Run DBCC OPENTRAN to ensure there are no open
> transactions. And you might
> >want to make sure you have adone a log backup after that
> command as well.
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
> >> I have a DB with two transaction log files. I'd like
> have
> >> one of them removed.
> >>
> >> After running the following set of commands
> successfully...
> >>
> >> backup log db_tdadatamart with truncate_only
> >> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> >>
> >> ...when I try to remove the second file using this
> >> command...
> >>
> >> alter database db_datamart
> >> remove file datamartLog
> >>
> >> ...I get this error:
> >>
> >> The file 'datamartLog' cannot be removed because it is
> not
> >> empty.
> >>
> >> Thanks in advance for your help.
> >
> >
> >.
> >sql
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Thursday, March 29, 2012
dbcc shrinkfile
I used dbcc shrinkfile to shrink transaction log, but it worked for only one day. When I checked the properties, transaction log was back to the size I started with. TL was 1586 MB and I set the target size to 1 MB. Any idea why it happened?...i believe the scope of the size specification is limited just to the run you executed, and as transactions build up it will grow, you may see that it is not using all of the space allocated (thru taskpad view) but what that means is that it had grown to the current size thru autogrow, to meet the needs of the log...
...two things to check, see how the tran log growth is specified (look at the databases attribute), and also look at how often the log is backed up. It could be that a batch job executes a large number of transactions causing it to grow to its current size and then releasing it once a backup has occured.
...this note assumes that the database is in "FULL" recovery mode and that tran log backups are occuring...
...given the previous statement it could be that if growth is a result of a large batch run that the log size is optimal and continued shrinking would only add overhead as it would have to repeatedly "autogrow"...
...hope this helps...sql
...two things to check, see how the tran log growth is specified (look at the databases attribute), and also look at how often the log is backed up. It could be that a batch job executes a large number of transactions causing it to grow to its current size and then releasing it once a backup has occured.
...this note assumes that the database is in "FULL" recovery mode and that tran log backups are occuring...
...given the previous statement it could be that if growth is a result of a large batch run that the log size is optimal and continued shrinking would only add overhead as it would have to repeatedly "autogrow"...
...hope this helps...sql
dbcc shrinkfile
Hi All,
I have a job that has multiple steps. Step 1 rebuilds the indexes, step 2 truncates the transaction log, and step 3 shrinks the transaction log via dbcc shrinkfile command. The job has been running for quite a while without any problems until this past weekend. The job ran successfully but when I looked at the size of the transaction log, it was the same as before the job ran. I have read on BOL that if part of the logical log is in the virtual logs beyond the target_size mark, SQL Server 2000 frees as much space as possible and issues an informational message. My questions is where is this message stored? How can I read it?
Thanks.The informational message is output to the console device, which is the messages window in Query Analyzer or the job step history for SQL Agent jobs.
-PatP|||I checked the job history and it showed that the transaction log shrinking step successed. So that doesn't help me.|||Then my next guess would be that you had open transactions spanning log segments, preventing the log from shrinking. No error message is issued when that happens.
-PatP|||Try the following:
use your_DB
go
checkpoint
go
BACKUP database your_DB TO DISK = 'junk1'
go
BACKUP LOG your_DB TO DISK='junk2'
go
BACKUP database your_DB TO DISK = 'junk3'
go
dbcc shrinkdatabase(your_DB)
go
dbcc shrinkfile(your_DB)
go|||Look into
1. backup log [databasename] with no_log
and
2. dump tran [databasename] with no_log
I usually use number 2 if the dbcc shrinkfile(dbname,1) doesn't work.
Good luck
I have a job that has multiple steps. Step 1 rebuilds the indexes, step 2 truncates the transaction log, and step 3 shrinks the transaction log via dbcc shrinkfile command. The job has been running for quite a while without any problems until this past weekend. The job ran successfully but when I looked at the size of the transaction log, it was the same as before the job ran. I have read on BOL that if part of the logical log is in the virtual logs beyond the target_size mark, SQL Server 2000 frees as much space as possible and issues an informational message. My questions is where is this message stored? How can I read it?
Thanks.The informational message is output to the console device, which is the messages window in Query Analyzer or the job step history for SQL Agent jobs.
-PatP|||I checked the job history and it showed that the transaction log shrinking step successed. So that doesn't help me.|||Then my next guess would be that you had open transactions spanning log segments, preventing the log from shrinking. No error message is issued when that happens.
-PatP|||Try the following:
use your_DB
go
checkpoint
go
BACKUP database your_DB TO DISK = 'junk1'
go
BACKUP LOG your_DB TO DISK='junk2'
go
BACKUP database your_DB TO DISK = 'junk3'
go
dbcc shrinkdatabase(your_DB)
go
dbcc shrinkfile(your_DB)
go|||Look into
1. backup log [databasename] with no_log
and
2. dump tran [databasename] with no_log
I usually use number 2 if the dbcc shrinkfile(dbname,1) doesn't work.
Good luck
dbcc shrinkfile
I have a DB with two transaction log files. I'd like have
one of them removed.
After running the following set of commands successfully...
backup log db_tdadatamart with truncate_only
dbcc shrinkfile ('datamartLog', EMPTYFILE)
...when I try to remove the second file using this
command...
alter database db_datamart
remove file datamartLog
...I get this error:
The file 'datamartLog' cannot be removed because it is not
empty.
Thanks in advance for your help.
Run DBCC OPENTRAN to ensure there are no open transactions. And you might
want to make sure you have adone a log backup after that command as well.
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
> I have a DB with two transaction log files. I'd like have
> one of them removed.
> After running the following set of commands successfully...
> backup log db_tdadatamart with truncate_only
> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> ...when I try to remove the second file using this
> command...
> alter database db_datamart
> remove file datamartLog
> ...I get this error:
> The file 'datamartLog' cannot be removed because it is not
> empty.
> Thanks in advance for your help.
|||'No active open transactions' were reported... still
encoutner the same problem.
Thanks.
>--Original Message--
>Run DBCC OPENTRAN to ensure there are no open
transactions. And you might
>want to make sure you have adone a log backup after that
command as well.
>--
>Andrew J. Kelly SQL MVP
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
have[vbcol=seagreen]
successfully...[vbcol=seagreen]
not
>
>.
>
|||Did you do a Log backup? Any chance this is related?
http://support.microsoft.com/default...&Product=sql2k
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx.gbl...[vbcol=seagreen]
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
> transactions. And you might
> command as well.
> message
> have
> successfully...
> not
|||I have some information about DBCC LOGINFO etc on my article regarding shrinking of database files:
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/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx.gbl...[vbcol=seagreen]
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
> transactions. And you might
> command as well.
> message
> have
> successfully...
> not
one of them removed.
After running the following set of commands successfully...
backup log db_tdadatamart with truncate_only
dbcc shrinkfile ('datamartLog', EMPTYFILE)
...when I try to remove the second file using this
command...
alter database db_datamart
remove file datamartLog
...I get this error:
The file 'datamartLog' cannot be removed because it is not
empty.
Thanks in advance for your help.
Run DBCC OPENTRAN to ensure there are no open transactions. And you might
want to make sure you have adone a log backup after that command as well.
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
> I have a DB with two transaction log files. I'd like have
> one of them removed.
> After running the following set of commands successfully...
> backup log db_tdadatamart with truncate_only
> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> ...when I try to remove the second file using this
> command...
> alter database db_datamart
> remove file datamartLog
> ...I get this error:
> The file 'datamartLog' cannot be removed because it is not
> empty.
> Thanks in advance for your help.
|||'No active open transactions' were reported... still
encoutner the same problem.
Thanks.
>--Original Message--
>Run DBCC OPENTRAN to ensure there are no open
transactions. And you might
>want to make sure you have adone a log backup after that
command as well.
>--
>Andrew J. Kelly SQL MVP
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
have[vbcol=seagreen]
successfully...[vbcol=seagreen]
not
>
>.
>
|||Did you do a Log backup? Any chance this is related?
http://support.microsoft.com/default...&Product=sql2k
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx.gbl...[vbcol=seagreen]
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
> transactions. And you might
> command as well.
> message
> have
> successfully...
> not
|||I have some information about DBCC LOGINFO etc on my article regarding shrinking of database files:
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/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx.gbl...[vbcol=seagreen]
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
> transactions. And you might
> command as well.
> message
> have
> successfully...
> not
dbcc shrinkfile
I have a DB with two transaction log files. I'd like have
one of them removed.
After running the following set of commands successfully...
backup log db_tdadatamart with truncate_only
dbcc shrinkfile ('datamartLog', EMPTYFILE)
...when I try to remove the second file using this
command...
alter database db_datamart
remove file datamartLog
...I get this error:
The file 'datamartLog' cannot be removed because it is not
empty.
Thanks in advance for your help.Run DBCC OPENTRAN to ensure there are no open transactions. And you might
want to make sure you have adone a log backup after that command as well.
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1df5a01c4546d$9c7f3f90$a001280a@.phx
.gbl...
> I have a DB with two transaction log files. I'd like have
> one of them removed.
> After running the following set of commands successfully...
> backup log db_tdadatamart with truncate_only
> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> ...when I try to remove the second file using this
> command...
> alter database db_datamart
> remove file datamartLog
> ...I get this error:
> The file 'datamartLog' cannot be removed because it is not
> empty.
> Thanks in advance for your help.|||'No active open transactions' were reported... still
encoutner the same problem.
Thanks.
>--Original Message--
>Run DBCC OPENTRAN to ensure there are no open
transactions. And you might
>want to make sure you have adone a log backup after that
command as well.
>--
>Andrew J. Kelly SQL MVP
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1df5a01c4546d$9c7f3f90$a001280a@.phx
.gbl...
have[vbcol=seagreen]
successfully...[vbcol=seagreen]
not[vbcol=seagreen]
>
>.
>|||Did you do a Log backup? Any chance this is related?
http://support.microsoft.com/defaul...2&Product=sql2k
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx
.gbl...[vbcol=seagreen]
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
>
> transactions. And you might
> command as well.
> message
> have
> successfully...
> not|||I have some information about DBCC LOGINFO etc on my article regarding shrin
king of database files:
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/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx
.gbl...[vbcol=seagreen]
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
>
> transactions. And you might
> command as well.
> message
> have
> successfully...
> notsql
one of them removed.
After running the following set of commands successfully...
backup log db_tdadatamart with truncate_only
dbcc shrinkfile ('datamartLog', EMPTYFILE)
...when I try to remove the second file using this
command...
alter database db_datamart
remove file datamartLog
...I get this error:
The file 'datamartLog' cannot be removed because it is not
empty.
Thanks in advance for your help.Run DBCC OPENTRAN to ensure there are no open transactions. And you might
want to make sure you have adone a log backup after that command as well.
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1df5a01c4546d$9c7f3f90$a001280a@.phx
.gbl...
> I have a DB with two transaction log files. I'd like have
> one of them removed.
> After running the following set of commands successfully...
> backup log db_tdadatamart with truncate_only
> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> ...when I try to remove the second file using this
> command...
> alter database db_datamart
> remove file datamartLog
> ...I get this error:
> The file 'datamartLog' cannot be removed because it is not
> empty.
> Thanks in advance for your help.|||'No active open transactions' were reported... still
encoutner the same problem.
Thanks.
>--Original Message--
>Run DBCC OPENTRAN to ensure there are no open
transactions. And you might
>want to make sure you have adone a log backup after that
command as well.
>--
>Andrew J. Kelly SQL MVP
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1df5a01c4546d$9c7f3f90$a001280a@.phx
.gbl...
have[vbcol=seagreen]
successfully...[vbcol=seagreen]
not[vbcol=seagreen]
>
>.
>|||Did you do a Log backup? Any chance this is related?
http://support.microsoft.com/defaul...2&Product=sql2k
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx
.gbl...[vbcol=seagreen]
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
>
> transactions. And you might
> command as well.
> message
> have
> successfully...
> not|||I have some information about DBCC LOGINFO etc on my article regarding shrin
king of database files:
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/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx
.gbl...[vbcol=seagreen]
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
>
> transactions. And you might
> command as well.
> message
> have
> successfully...
> notsql
Tuesday, March 27, 2012
DBCC shrinkdatabase , transaction log shrink
Hi All,
Last night I created sql JOB to shrink the database.
I used the following command
dbcc shrinkdatabase('xxxx')
I scheduled it at 11:00 PM.
Today morning when I saw the job status, it says it failed.
Duration of job run was 2 hours.
In the job history it is saying as below.
Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE 42000]
(Error 3140) DBCC
execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE
01000] (Error 2528). The step failed.
It puts the following in the eventlog.
SQL Server Scheduled Job 'Shrink xxxx Database'
(0x935A70A0A9B0A643B6336153DD61E128) - Status:
Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The Job
was invoked by
Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This is our production database and it is being logshipped to other server.
My database recovory model set to FULL.
In DB options I unchecked auto shrink option.
I want to shirink database only in night time.
Question-1: Do you guys have any idea why this job failed?
Question-2:Usually what is the permissible value of transactionlog (.ldf)
file size?
Some times to reduce the transaction log file (.LDF) , I am doing as below.
I detach the database and rename the .ldf file name, and attach the database
with only mdf file
,SQL server creates new ldf file with minimum size. delete the old .ldf file.
Question-3: By doing this , AM I loosing some data/transactions stored in
old .ldf file?
Any kind of help is greatly appreciated.
Thanks
Kumar
Kumar,
Try doing the SHRINKDATABASE manually (not as a job) to rule out the
possible causes for you failure. If this op is just to reduce the size of
the transaction log, try DBCC SHRINKFILE instead of your current process.
Also see:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318/
and
Tibor's page:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:813BD9B6-E8B8-43FA-894B-D2D3A5DF2D1B@.microsoft.com...
> Hi All,
> Last night I created sql JOB to shrink the database.
> I used the following command
> dbcc shrinkdatabase('xxxx')
> I scheduled it at 11:00 PM.
> Today morning when I saw the job status, it says it failed.
> Duration of job run was 2 hours.
> In the job history it is saying as below.
> Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE
> 42000]
> (Error 3140) DBCC
> execution completed. If DBCC printed error messages, contact your system
> administrator. [SQLSTATE
> 01000] (Error 2528). The step failed.
>
> It puts the following in the eventlog.
> SQL Server Scheduled Job 'Shrink xxxx Database'
> (0x935A70A0A9B0A643B6336153DD61E128) - Status:
> Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The
> Job
> was invoked by
> Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> This is our production database and it is being logshipped to other
> server.
>
> My database recovory model set to FULL.
> In DB options I unchecked auto shrink option.
> I want to shirink database only in night time.
> Question-1: Do you guys have any idea why this job failed?
>
> Question-2:Usually what is the permissible value of transactionlog (.ldf)
> file size?
> Some times to reduce the transaction log file (.LDF) , I am doing as
> below.
> I detach the database and rename the .ldf file name, and attach the
> database
> with only mdf file
> ,SQL server creates new ldf file with minimum size. delete the old .ldf
> file.
>
> Question-3: By doing this , AM I loosing some data/transactions stored in
> old .ldf file?
>
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
>
>
>
>
sql
Last night I created sql JOB to shrink the database.
I used the following command
dbcc shrinkdatabase('xxxx')
I scheduled it at 11:00 PM.
Today morning when I saw the job status, it says it failed.
Duration of job run was 2 hours.
In the job history it is saying as below.
Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE 42000]
(Error 3140) DBCC
execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE
01000] (Error 2528). The step failed.
It puts the following in the eventlog.
SQL Server Scheduled Job 'Shrink xxxx Database'
(0x935A70A0A9B0A643B6336153DD61E128) - Status:
Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The Job
was invoked by
Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This is our production database and it is being logshipped to other server.
My database recovory model set to FULL.
In DB options I unchecked auto shrink option.
I want to shirink database only in night time.
Question-1: Do you guys have any idea why this job failed?
Question-2:Usually what is the permissible value of transactionlog (.ldf)
file size?
Some times to reduce the transaction log file (.LDF) , I am doing as below.
I detach the database and rename the .ldf file name, and attach the database
with only mdf file
,SQL server creates new ldf file with minimum size. delete the old .ldf file.
Question-3: By doing this , AM I loosing some data/transactions stored in
old .ldf file?
Any kind of help is greatly appreciated.
Thanks
Kumar
Kumar,
Try doing the SHRINKDATABASE manually (not as a job) to rule out the
possible causes for you failure. If this op is just to reduce the size of
the transaction log, try DBCC SHRINKFILE instead of your current process.
Also see:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318/
and
Tibor's page:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:813BD9B6-E8B8-43FA-894B-D2D3A5DF2D1B@.microsoft.com...
> Hi All,
> Last night I created sql JOB to shrink the database.
> I used the following command
> dbcc shrinkdatabase('xxxx')
> I scheduled it at 11:00 PM.
> Today morning when I saw the job status, it says it failed.
> Duration of job run was 2 hours.
> In the job history it is saying as below.
> Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE
> 42000]
> (Error 3140) DBCC
> execution completed. If DBCC printed error messages, contact your system
> administrator. [SQLSTATE
> 01000] (Error 2528). The step failed.
>
> It puts the following in the eventlog.
> SQL Server Scheduled Job 'Shrink xxxx Database'
> (0x935A70A0A9B0A643B6336153DD61E128) - Status:
> Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The
> Job
> was invoked by
> Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> This is our production database and it is being logshipped to other
> server.
>
> My database recovory model set to FULL.
> In DB options I unchecked auto shrink option.
> I want to shirink database only in night time.
> Question-1: Do you guys have any idea why this job failed?
>
> Question-2:Usually what is the permissible value of transactionlog (.ldf)
> file size?
> Some times to reduce the transaction log file (.LDF) , I am doing as
> below.
> I detach the database and rename the .ldf file name, and attach the
> database
> with only mdf file
> ,SQL server creates new ldf file with minimum size. delete the old .ldf
> file.
>
> Question-3: By doing this , AM I loosing some data/transactions stored in
> old .ldf file?
>
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
>
>
>
>
sql
DBCC shrinkdatabase , transaction log shrink
Hi All,
Last night I created sql JOB to shrink the database.
I used the following command
dbcc shrinkdatabase('xxxx')
I scheduled it at 11:00 PM.
Today morning when I saw the job status, it says it failed.
Duration of job run was 2 hours.
In the job history it is saying as below.
Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE 420
00]
(Error 3140) DBCC
execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE
01000] (Error 2528). The step failed.
It puts the following in the eventlog.
SQL Server Scheduled Job 'Shrink xxxx Database'
(0x935A70A0A9B0A643B6336153DD61E128) - Status:
Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The Job
was invoked by
Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This is our production database and it is being logshipped to other server.
My database recovory model set to FULL.
In DB options I unchecked auto shrink option.
I want to shirink database only in night time.
Question-1: Do you guys have any idea why this job failed?
Question-2:Usually what is the permissible value of transactionlog (.ldf)
file size?
Some times to reduce the transaction log file (.LDF) , I am doing as below.
I detach the database and rename the .ldf file name, and attach the database
with only mdf file
,SQL server creates new ldf file with minimum size. delete the old .ldf file
.
Question-3: By doing this , AM I loosing some data/transactions stored in
old .ldf file?
Any kind of help is greatly appreciated.
Thanks
KumarKumar,
Try doing the SHRINKDATABASE manually (not as a job) to rule out the
possible causes for you failure. If this op is just to reduce the size of
the transaction log, try DBCC SHRINKFILE instead of your current process.
Also see:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318/
and
Tibor's page:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:813BD9B6-E8B8-43FA-894B-D2D3A5DF2D1B@.microsoft.com...
> Hi All,
> Last night I created sql JOB to shrink the database.
> I used the following command
> dbcc shrinkdatabase('xxxx')
> I scheduled it at 11:00 PM.
> Today morning when I saw the job status, it says it failed.
> Duration of job run was 2 hours.
> In the job history it is saying as below.
> Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE
> 42000]
> (Error 3140) DBCC
> execution completed. If DBCC printed error messages, contact your system
> administrator. [SQLSTATE
> 01000] (Error 2528). The step failed.
>
> It puts the following in the eventlog.
> SQL Server Scheduled Job 'Shrink xxxx Database'
> (0x935A70A0A9B0A643B6336153DD61E128) - Status:
> Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The
> Job
> was invoked by
> Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> This is our production database and it is being logshipped to other
> server.
>
> My database recovory model set to FULL.
> In DB options I unchecked auto shrink option.
> I want to shirink database only in night time.
> Question-1: Do you guys have any idea why this job failed?
>
> Question-2:Usually what is the permissible value of transactionlog (.ldf)
> file size?
> Some times to reduce the transaction log file (.LDF) , I am doing as
> below.
> I detach the database and rename the .ldf file name, and attach the
> database
> with only mdf file
> ,SQL server creates new ldf file with minimum size. delete the old .ldf
> file.
>
> Question-3: By doing this , AM I loosing some data/transactions stored in
> old .ldf file?
>
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
>
>
>
>
Last night I created sql JOB to shrink the database.
I used the following command
dbcc shrinkdatabase('xxxx')
I scheduled it at 11:00 PM.
Today morning when I saw the job status, it says it failed.
Duration of job run was 2 hours.
In the job history it is saying as below.
Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE 420
00]
(Error 3140) DBCC
execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE
01000] (Error 2528). The step failed.
It puts the following in the eventlog.
SQL Server Scheduled Job 'Shrink xxxx Database'
(0x935A70A0A9B0A643B6336153DD61E128) - Status:
Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The Job
was invoked by
Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This is our production database and it is being logshipped to other server.
My database recovory model set to FULL.
In DB options I unchecked auto shrink option.
I want to shirink database only in night time.
Question-1: Do you guys have any idea why this job failed?
Question-2:Usually what is the permissible value of transactionlog (.ldf)
file size?
Some times to reduce the transaction log file (.LDF) , I am doing as below.
I detach the database and rename the .ldf file name, and attach the database
with only mdf file
,SQL server creates new ldf file with minimum size. delete the old .ldf file
.
Question-3: By doing this , AM I loosing some data/transactions stored in
old .ldf file?
Any kind of help is greatly appreciated.
Thanks
KumarKumar,
Try doing the SHRINKDATABASE manually (not as a job) to rule out the
possible causes for you failure. If this op is just to reduce the size of
the transaction log, try DBCC SHRINKFILE instead of your current process.
Also see:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318/
and
Tibor's page:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:813BD9B6-E8B8-43FA-894B-D2D3A5DF2D1B@.microsoft.com...
> Hi All,
> Last night I created sql JOB to shrink the database.
> I used the following command
> dbcc shrinkdatabase('xxxx')
> I scheduled it at 11:00 PM.
> Today morning when I saw the job status, it says it failed.
> Duration of job run was 2 hours.
> In the job history it is saying as below.
> Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE
> 42000]
> (Error 3140) DBCC
> execution completed. If DBCC printed error messages, contact your system
> administrator. [SQLSTATE
> 01000] (Error 2528). The step failed.
>
> It puts the following in the eventlog.
> SQL Server Scheduled Job 'Shrink xxxx Database'
> (0x935A70A0A9B0A643B6336153DD61E128) - Status:
> Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The
> Job
> was invoked by
> Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> This is our production database and it is being logshipped to other
> server.
>
> My database recovory model set to FULL.
> In DB options I unchecked auto shrink option.
> I want to shirink database only in night time.
> Question-1: Do you guys have any idea why this job failed?
>
> Question-2:Usually what is the permissible value of transactionlog (.ldf)
> file size?
> Some times to reduce the transaction log file (.LDF) , I am doing as
> below.
> I detach the database and rename the .ldf file name, and attach the
> database
> with only mdf file
> ,SQL server creates new ldf file with minimum size. delete the old .ldf
> file.
>
> Question-3: By doing this , AM I loosing some data/transactions stored in
> old .ldf file?
>
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
>
>
>
>
DBCC shrinkdatabase , transaction log shrink
Hi All,
Last night I created sql JOB to shrink the database.
I used the following command
dbcc shrinkdatabase('xxxx')
I scheduled it at 11:00 PM.
Today morning when I saw the job status, it says it failed.
Duration of job run was 2 hours.
In the job history it is saying as below.
Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE 42000]
(Error 3140) DBCC
execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE
01000] (Error 2528). The step failed.
It puts the following in the eventlog.
SQL Server Scheduled Job 'Shrink xxxx Database'
(0x935A70A0A9B0A643B6336153DD61E128) - Status:
Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The Job
was invoked by
Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This is our production database and it is being logshipped to other server.
My database recovory model set to FULL.
In DB options I unchecked auto shrink option.
I want to shirink database only in night time.
Question-1: Do you guys have any idea why this job failed?
Question-2:Usually what is the permissible value of transactionlog (.ldf)
file size?
Some times to reduce the transaction log file (.LDF) , I am doing as below.
I detach the database and rename the .ldf file name, and attach the database
with only mdf file
,SQL server creates new ldf file with minimum size. delete the old .ldf file.
Question-3: By doing this , AM I loosing some data/transactions stored in
old .ldf file?
Any kind of help is greatly appreciated.
Thanks
KumarKumar,
Try doing the SHRINKDATABASE manually (not as a job) to rule out the
possible causes for you failure. If this op is just to reduce the size of
the transaction log, try DBCC SHRINKFILE instead of your current process.
Also see:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318/
and
Tibor's page:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:813BD9B6-E8B8-43FA-894B-D2D3A5DF2D1B@.microsoft.com...
> Hi All,
> Last night I created sql JOB to shrink the database.
> I used the following command
> dbcc shrinkdatabase('xxxx')
> I scheduled it at 11:00 PM.
> Today morning when I saw the job status, it says it failed.
> Duration of job run was 2 hours.
> In the job history it is saying as below.
> Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE
> 42000]
> (Error 3140) DBCC
> execution completed. If DBCC printed error messages, contact your system
> administrator. [SQLSTATE
> 01000] (Error 2528). The step failed.
>
> It puts the following in the eventlog.
> SQL Server Scheduled Job 'Shrink xxxx Database'
> (0x935A70A0A9B0A643B6336153DD61E128) - Status:
> Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The
> Job
> was invoked by
> Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> This is our production database and it is being logshipped to other
> server.
>
> My database recovory model set to FULL.
> In DB options I unchecked auto shrink option.
> I want to shirink database only in night time.
> Question-1: Do you guys have any idea why this job failed?
>
> Question-2:Usually what is the permissible value of transactionlog (.ldf)
> file size?
> Some times to reduce the transaction log file (.LDF) , I am doing as
> below.
> I detach the database and rename the .ldf file name, and attach the
> database
> with only mdf file
> ,SQL server creates new ldf file with minimum size. delete the old .ldf
> file.
>
> Question-3: By doing this , AM I loosing some data/transactions stored in
> old .ldf file?
>
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
>
>
>
>
Last night I created sql JOB to shrink the database.
I used the following command
dbcc shrinkdatabase('xxxx')
I scheduled it at 11:00 PM.
Today morning when I saw the job status, it says it failed.
Duration of job run was 2 hours.
In the job history it is saying as below.
Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE 42000]
(Error 3140) DBCC
execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE
01000] (Error 2528). The step failed.
It puts the following in the eventlog.
SQL Server Scheduled Job 'Shrink xxxx Database'
(0x935A70A0A9B0A643B6336153DD61E128) - Status:
Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The Job
was invoked by
Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This is our production database and it is being logshipped to other server.
My database recovory model set to FULL.
In DB options I unchecked auto shrink option.
I want to shirink database only in night time.
Question-1: Do you guys have any idea why this job failed?
Question-2:Usually what is the permissible value of transactionlog (.ldf)
file size?
Some times to reduce the transaction log file (.LDF) , I am doing as below.
I detach the database and rename the .ldf file name, and attach the database
with only mdf file
,SQL server creates new ldf file with minimum size. delete the old .ldf file.
Question-3: By doing this , AM I loosing some data/transactions stored in
old .ldf file?
Any kind of help is greatly appreciated.
Thanks
KumarKumar,
Try doing the SHRINKDATABASE manually (not as a job) to rule out the
possible causes for you failure. If this op is just to reduce the size of
the transaction log, try DBCC SHRINKFILE instead of your current process.
Also see:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318/
and
Tibor's page:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:813BD9B6-E8B8-43FA-894B-D2D3A5DF2D1B@.microsoft.com...
> Hi All,
> Last night I created sql JOB to shrink the database.
> I used the following command
> dbcc shrinkdatabase('xxxx')
> I scheduled it at 11:00 PM.
> Today morning when I saw the job status, it says it failed.
> Duration of job run was 2 hours.
> In the job history it is saying as below.
> Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE
> 42000]
> (Error 3140) DBCC
> execution completed. If DBCC printed error messages, contact your system
> administrator. [SQLSTATE
> 01000] (Error 2528). The step failed.
>
> It puts the following in the eventlog.
> SQL Server Scheduled Job 'Shrink xxxx Database'
> (0x935A70A0A9B0A643B6336153DD61E128) - Status:
> Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The
> Job
> was invoked by
> Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> This is our production database and it is being logshipped to other
> server.
>
> My database recovory model set to FULL.
> In DB options I unchecked auto shrink option.
> I want to shirink database only in night time.
> Question-1: Do you guys have any idea why this job failed?
>
> Question-2:Usually what is the permissible value of transactionlog (.ldf)
> file size?
> Some times to reduce the transaction log file (.LDF) , I am doing as
> below.
> I detach the database and rename the .ldf file name, and attach the
> database
> with only mdf file
> ,SQL server creates new ldf file with minimum size. delete the old .ldf
> file.
>
> Question-3: By doing this , AM I loosing some data/transactions stored in
> old .ldf file?
>
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
>
>
>
>
DBCC SHRINKDATABASE
SQL Server 2000 on Windows 2003
I want to shrink the Transaction Log file .
I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink log
file 2 (IRIS_Log) because all logical log files are in use.
What should I do?
Thanks,
Dan
Hi,
You should backup the tranasction log before doing the DBCC SHRINK command.
Incase if you do not
need the transaction log backup then you can truncate the transaction log
using
Backup log <dbname> with Truncate_only
After doing eithe log backup or teuncate you could try Shrink. I recommend
you to DBCC SHRINKFILE to shrink the database files
Thanks
Hari
SQL Server MVP
"danA" <dea@.deamon.com> wrote in message
news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
> SQL Server 2000 on Windows 2003
> I want to shrink the Transaction Log file .
> I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink
> log file 2 (IRIS_Log) because all logical log files are in use.
> What should I do?
> Thanks,
> Dan
>
>
|||Many thanks Hari, it worked. Dan
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OgaKWGZmFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You should backup the transaction log before doing the DBCC SHRINK
> command. Incase if you do not
> need the transaction log backup then you can truncate the transaction log
> using
> Backup log <dbname> with Truncate_only
> After doing eithe log backup or teuncate you could try Shrink. I recommend
> you to DBCC SHRINKFILE to shrink the database files
> Thanks
> Hari
> SQL Server MVP
>
> "danA" <dea@.deamon.com> wrote in message
> news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
>
I want to shrink the Transaction Log file .
I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink log
file 2 (IRIS_Log) because all logical log files are in use.
What should I do?
Thanks,
Dan
Hi,
You should backup the tranasction log before doing the DBCC SHRINK command.
Incase if you do not
need the transaction log backup then you can truncate the transaction log
using
Backup log <dbname> with Truncate_only
After doing eithe log backup or teuncate you could try Shrink. I recommend
you to DBCC SHRINKFILE to shrink the database files
Thanks
Hari
SQL Server MVP
"danA" <dea@.deamon.com> wrote in message
news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
> SQL Server 2000 on Windows 2003
> I want to shrink the Transaction Log file .
> I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink
> log file 2 (IRIS_Log) because all logical log files are in use.
> What should I do?
> Thanks,
> Dan
>
>
|||Many thanks Hari, it worked. Dan
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OgaKWGZmFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You should backup the transaction log before doing the DBCC SHRINK
> command. Incase if you do not
> need the transaction log backup then you can truncate the transaction log
> using
> Backup log <dbname> with Truncate_only
> After doing eithe log backup or teuncate you could try Shrink. I recommend
> you to DBCC SHRINKFILE to shrink the database files
> Thanks
> Hari
> SQL Server MVP
>
> "danA" <dea@.deamon.com> wrote in message
> news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
>
DBCC SHRINKDATABASE
SQL Server 2000 on Windows 2003
I want to shrink the Transaction Log file .
I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink log
file 2 (IRIS_Log) because all logical log files are in use.
What should I do?
Thanks,
DanHi,
You should backup the tranasction log before doing the DBCC SHRINK command.
Incase if you do not
need the transaction log backup then you can truncate the transaction log
using
Backup log <dbname> with Truncate_only
After doing eithe log backup or teuncate you could try Shrink. I recommend
you to DBCC SHRINKFILE to shrink the database files
Thanks
Hari
SQL Server MVP
"danA" <dea@.deamon.com> wrote in message
news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
> SQL Server 2000 on Windows 2003
> I want to shrink the Transaction Log file .
> I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink
> log file 2 (IRIS_Log) because all logical log files are in use.
> What should I do?
> Thanks,
> Dan
>
>|||Many thanks Hari, it worked. Dan
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OgaKWGZmFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You should backup the transaction log before doing the DBCC SHRINK
> command. Incase if you do not
> need the transaction log backup then you can truncate the transaction log
> using
> Backup log <dbname> with Truncate_only
> After doing eithe log backup or teuncate you could try Shrink. I recommend
> you to DBCC SHRINKFILE to shrink the database files
> Thanks
> Hari
> SQL Server MVP
>
> "danA" <dea@.deamon.com> wrote in message
> news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
>> SQL Server 2000 on Windows 2003
>> I want to shrink the Transaction Log file .
>> I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink
>> log file 2 (IRIS_Log) because all logical log files are in use.
>> What should I do?
>> Thanks,
>> Dan
>>
>
I want to shrink the Transaction Log file .
I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink log
file 2 (IRIS_Log) because all logical log files are in use.
What should I do?
Thanks,
DanHi,
You should backup the tranasction log before doing the DBCC SHRINK command.
Incase if you do not
need the transaction log backup then you can truncate the transaction log
using
Backup log <dbname> with Truncate_only
After doing eithe log backup or teuncate you could try Shrink. I recommend
you to DBCC SHRINKFILE to shrink the database files
Thanks
Hari
SQL Server MVP
"danA" <dea@.deamon.com> wrote in message
news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
> SQL Server 2000 on Windows 2003
> I want to shrink the Transaction Log file .
> I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink
> log file 2 (IRIS_Log) because all logical log files are in use.
> What should I do?
> Thanks,
> Dan
>
>|||Many thanks Hari, it worked. Dan
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OgaKWGZmFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You should backup the transaction log before doing the DBCC SHRINK
> command. Incase if you do not
> need the transaction log backup then you can truncate the transaction log
> using
> Backup log <dbname> with Truncate_only
> After doing eithe log backup or teuncate you could try Shrink. I recommend
> you to DBCC SHRINKFILE to shrink the database files
> Thanks
> Hari
> SQL Server MVP
>
> "danA" <dea@.deamon.com> wrote in message
> news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
>> SQL Server 2000 on Windows 2003
>> I want to shrink the Transaction Log file .
>> I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink
>> log file 2 (IRIS_Log) because all logical log files are in use.
>> What should I do?
>> Thanks,
>> Dan
>>
>
DBCC SHRINKDATABASE
SQL Server 2000 on Windows 2003
I want to shrink the Transaction Log file .
I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink log
file 2 (IRIS_Log) because all logical log files are in use.
What should I do?
Thanks,
DanHi,
You should backup the tranasction log before doing the DBCC SHRINK command.
Incase if you do not
need the transaction log backup then you can truncate the transaction log
using
Backup log <dbname> with Truncate_only
After doing eithe log backup or teuncate you could try Shrink. I recommend
you to DBCC SHRINKFILE to shrink the database files
Thanks
Hari
SQL Server MVP
"danA" <dea@.deamon.com> wrote in message
news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
> SQL Server 2000 on Windows 2003
> I want to shrink the Transaction Log file .
> I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink
> log file 2 (IRIS_Log) because all logical log files are in use.
> What should I do?
> Thanks,
> Dan
>
>|||Many thanks Hari, it worked. Dan
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OgaKWGZmFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You should backup the transaction log before doing the DBCC SHRINK
> command. Incase if you do not
> need the transaction log backup then you can truncate the transaction log
> using
> Backup log <dbname> with Truncate_only
> After doing eithe log backup or teuncate you could try Shrink. I recommend
> you to DBCC SHRINKFILE to shrink the database files
> Thanks
> Hari
> SQL Server MVP
>
> "danA" <dea@.deamon.com> wrote in message
> news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
>
I want to shrink the Transaction Log file .
I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink log
file 2 (IRIS_Log) because all logical log files are in use.
What should I do?
Thanks,
DanHi,
You should backup the tranasction log before doing the DBCC SHRINK command.
Incase if you do not
need the transaction log backup then you can truncate the transaction log
using
Backup log <dbname> with Truncate_only
After doing eithe log backup or teuncate you could try Shrink. I recommend
you to DBCC SHRINKFILE to shrink the database files
Thanks
Hari
SQL Server MVP
"danA" <dea@.deamon.com> wrote in message
news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
> SQL Server 2000 on Windows 2003
> I want to shrink the Transaction Log file .
> I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink
> log file 2 (IRIS_Log) because all logical log files are in use.
> What should I do?
> Thanks,
> Dan
>
>|||Many thanks Hari, it worked. Dan
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OgaKWGZmFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You should backup the transaction log before doing the DBCC SHRINK
> command. Incase if you do not
> need the transaction log backup then you can truncate the transaction log
> using
> Backup log <dbname> with Truncate_only
> After doing eithe log backup or teuncate you could try Shrink. I recommend
> you to DBCC SHRINKFILE to shrink the database files
> Thanks
> Hari
> SQL Server MVP
>
> "danA" <dea@.deamon.com> wrote in message
> news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
>
Wednesday, March 21, 2012
dbcc opentran results
I have a database with a large (and growing) transaction log. Running
dbcc opentran yields the following two rows:
REPL_DIST_OLD_LSN(0:0:0)
REPL_NONDIST_OLD_LSN(508734:17171:1)
A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.
You might want to post this to the replication group, as you are more likely to find replication experts
there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190756.45c8213c@.posting.google.c om...
> I have a database with a large (and growing) transaction log. Running
> dbcc opentran yields the following two rows:
> REPL_DIST_OLD_LSN (0:0:0)
> REPL_NONDIST_OLD_LSN (508734:17171:1)
> A search through BOL and the news groups yields no information on the
> meaning of these values. The database is published nightly using
> snapshot replication. Any help interpreting these values would be
> greatly appreciated. My goal is to truncate the log back to a more
> reasonable size. Thanks.
|||To me, it seems to indicate that you have at least one table that is setup
for transactional replication, but the log reader is not running. Probaly
failed with an error. Could you check your distribution server to make sure
the log reader is running?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190756.45c8213c@.posting.google.c om...
I have a database with a large (and growing) transaction log. Running
dbcc opentran yields the following two rows:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (508734:17171:1)
A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.
dbcc opentran yields the following two rows:
REPL_DIST_OLD_LSN(0:0:0)
REPL_NONDIST_OLD_LSN(508734:17171:1)
A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.
You might want to post this to the replication group, as you are more likely to find replication experts
there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190756.45c8213c@.posting.google.c om...
> I have a database with a large (and growing) transaction log. Running
> dbcc opentran yields the following two rows:
> REPL_DIST_OLD_LSN (0:0:0)
> REPL_NONDIST_OLD_LSN (508734:17171:1)
> A search through BOL and the news groups yields no information on the
> meaning of these values. The database is published nightly using
> snapshot replication. Any help interpreting these values would be
> greatly appreciated. My goal is to truncate the log back to a more
> reasonable size. Thanks.
|||To me, it seems to indicate that you have at least one table that is setup
for transactional replication, but the log reader is not running. Probaly
failed with an error. Could you check your distribution server to make sure
the log reader is running?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190756.45c8213c@.posting.google.c om...
I have a database with a large (and growing) transaction log. Running
dbcc opentran yields the following two rows:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (508734:17171:1)
A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.
dbcc opentran results
I have a database with a large (and growing) transaction log. Running
dbcc opentran yields the following two rows:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (508734:17171:1)
A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.You might want to post this to the replication group, as you are more likely to find replication experts
there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190756.45c8213c@.posting.google.com...
> I have a database with a large (and growing) transaction log. Running
> dbcc opentran yields the following two rows:
> REPL_DIST_OLD_LSN (0:0:0)
> REPL_NONDIST_OLD_LSN (508734:17171:1)
> A search through BOL and the news groups yields no information on the
> meaning of these values. The database is published nightly using
> snapshot replication. Any help interpreting these values would be
> greatly appreciated. My goal is to truncate the log back to a more
> reasonable size. Thanks.|||To me, it seems to indicate that you have at least one table that is setup
for transactional replication, but the log reader is not running. Probaly
failed with an error. Could you check your distribution server to make sure
the log reader is running?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190756.45c8213c@.posting.google.com...
I have a database with a large (and growing) transaction log. Running
dbcc opentran yields the following two rows:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (508734:17171:1)
A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.
dbcc opentran yields the following two rows:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (508734:17171:1)
A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.You might want to post this to the replication group, as you are more likely to find replication experts
there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190756.45c8213c@.posting.google.com...
> I have a database with a large (and growing) transaction log. Running
> dbcc opentran yields the following two rows:
> REPL_DIST_OLD_LSN (0:0:0)
> REPL_NONDIST_OLD_LSN (508734:17171:1)
> A search through BOL and the news groups yields no information on the
> meaning of these values. The database is published nightly using
> snapshot replication. Any help interpreting these values would be
> greatly appreciated. My goal is to truncate the log back to a more
> reasonable size. Thanks.|||To me, it seems to indicate that you have at least one table that is setup
for transactional replication, but the log reader is not running. Probaly
failed with an error. Could you check your distribution server to make sure
the log reader is running?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190756.45c8213c@.posting.google.com...
I have a database with a large (and growing) transaction log. Running
dbcc opentran yields the following two rows:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (508734:17171:1)
A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.
dbcc opentran results
I have a database with a large (and growing) transaction log. Running
dbcc opentran yields the following two rows:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (508734:17171:1)
A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.
it is very rare to see the REPL_NONDIST_OLD_LSN value.
What it means is that 1) your log reader agent is stopped, 2) your log
reader agent is way behind.
What is the status of your log reader agent?
Can you also run this in your publication database?
DBCC traceon(3604)
DBCC log(databasename)
DBCC traceoff
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190922.3dc6706d@.posting.google.c om...
> I have a database with a large (and growing) transaction log. Running
> dbcc opentran yields the following two rows:
> REPL_DIST_OLD_LSN (0:0:0)
> REPL_NONDIST_OLD_LSN (508734:17171:1)
> A search through BOL and the news groups yields no information on the
> meaning of these values. The database is published nightly using
> snapshot replication. Any help interpreting these values would be
> greatly appreciated. My goal is to truncate the log back to a more
> reasonable size. Thanks.
|||one more thing, are you running DataMirror?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190922.3dc6706d@.posting.google.c om...
> I have a database with a large (and growing) transaction log. Running
> dbcc opentran yields the following two rows:
> REPL_DIST_OLD_LSN (0:0:0)
> REPL_NONDIST_OLD_LSN (508734:17171:1)
> A search through BOL and the news groups yields no information on the
> meaning of these values. The database is published nightly using
> snapshot replication. Any help interpreting these values would be
> greatly appreciated. My goal is to truncate the log back to a more
> reasonable size. Thanks.
|||Thanks for the prompt reply.
I am confused. We are using snapshot replication. I wasn't aware that a
log reader agent was part of the snapshot replication implementation.
There are no log reader agents showing in the replication monitor.
I ran the dbcc log(database) command against the publication database.
It returned a huge number of rows, too many to include here. Is there
something specific you are looking for?
Thanks again.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I missed that fact that you are using snapshot replication. The log reader
is not used in snapshot replication, as you so kindly point out.
What the traceflag will allow you to do is to get an idea of where your log
reader is at in reading the transaction log, i.e. how many more rows it has
to read, or how far behind it is.
Again with snapshot replication it won't really tell you anything.
you can take the value 508734:17171:1, convert it to hex
0007C33E:00017171:0001 and see where this hex value is in the Current LSN of
the results set returned from the trace flag.
It is possible that this corresponds to an open transaction which you might
want to kill. DBCC opentran should give you the spid for this. You might
also want to review some of the kb articles on shrinking the transaction
log, ie
http://support.microsoft.com/default...&Product=sql2k
http://support.microsoft.com/default...b;EN-US;256650
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Larry Myers" <lmyers@.swnformtics.com> wrote in message
news:%2309dhrhhEHA.1888@.TK2MSFTNGP10.phx.gbl...
> Thanks for the prompt reply.
> I am confused. We are using snapshot replication. I wasn't aware that a
> log reader agent was part of the snapshot replication implementation.
> There are no log reader agents showing in the replication monitor.
> I ran the dbcc log(database) command against the publication database.
> It returned a huge number of rows, too many to include here. Is there
> something specific you are looking for?
> Thanks again.
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
sql
dbcc opentran yields the following two rows:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (508734:17171:1)
A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.
it is very rare to see the REPL_NONDIST_OLD_LSN value.
What it means is that 1) your log reader agent is stopped, 2) your log
reader agent is way behind.
What is the status of your log reader agent?
Can you also run this in your publication database?
DBCC traceon(3604)
DBCC log(databasename)
DBCC traceoff
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190922.3dc6706d@.posting.google.c om...
> I have a database with a large (and growing) transaction log. Running
> dbcc opentran yields the following two rows:
> REPL_DIST_OLD_LSN (0:0:0)
> REPL_NONDIST_OLD_LSN (508734:17171:1)
> A search through BOL and the news groups yields no information on the
> meaning of these values. The database is published nightly using
> snapshot replication. Any help interpreting these values would be
> greatly appreciated. My goal is to truncate the log back to a more
> reasonable size. Thanks.
|||one more thing, are you running DataMirror?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190922.3dc6706d@.posting.google.c om...
> I have a database with a large (and growing) transaction log. Running
> dbcc opentran yields the following two rows:
> REPL_DIST_OLD_LSN (0:0:0)
> REPL_NONDIST_OLD_LSN (508734:17171:1)
> A search through BOL and the news groups yields no information on the
> meaning of these values. The database is published nightly using
> snapshot replication. Any help interpreting these values would be
> greatly appreciated. My goal is to truncate the log back to a more
> reasonable size. Thanks.
|||Thanks for the prompt reply.
I am confused. We are using snapshot replication. I wasn't aware that a
log reader agent was part of the snapshot replication implementation.
There are no log reader agents showing in the replication monitor.
I ran the dbcc log(database) command against the publication database.
It returned a huge number of rows, too many to include here. Is there
something specific you are looking for?
Thanks again.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I missed that fact that you are using snapshot replication. The log reader
is not used in snapshot replication, as you so kindly point out.
What the traceflag will allow you to do is to get an idea of where your log
reader is at in reading the transaction log, i.e. how many more rows it has
to read, or how far behind it is.
Again with snapshot replication it won't really tell you anything.
you can take the value 508734:17171:1, convert it to hex
0007C33E:00017171:0001 and see where this hex value is in the Current LSN of
the results set returned from the trace flag.
It is possible that this corresponds to an open transaction which you might
want to kill. DBCC opentran should give you the spid for this. You might
also want to review some of the kb articles on shrinking the transaction
log, ie
http://support.microsoft.com/default...&Product=sql2k
http://support.microsoft.com/default...b;EN-US;256650
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Larry Myers" <lmyers@.swnformtics.com> wrote in message
news:%2309dhrhhEHA.1888@.TK2MSFTNGP10.phx.gbl...
> Thanks for the prompt reply.
> I am confused. We are using snapshot replication. I wasn't aware that a
> log reader agent was part of the snapshot replication implementation.
> There are no log reader agents showing in the replication monitor.
> I ran the dbcc log(database) command against the publication database.
> It returned a huge number of rows, too many to include here. Is there
> something specific you are looking for?
> Thanks again.
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
sql
dbcc opentran results
I have a database with a large (and growing) transaction log. Running
dbcc opentran yields the following two rows:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (508734:17171:1)
A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.You might want to post this to the replication group, as you are more likely
to find replication experts
there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190756.45c8213c@.posting.google.com...
> I have a database with a large (and growing) transaction log. Running
> dbcc opentran yields the following two rows:
> REPL_DIST_OLD_LSN (0:0:0)
> REPL_NONDIST_OLD_LSN (508734:17171:1)
> A search through BOL and the news groups yields no information on the
> meaning of these values. The database is published nightly using
> snapshot replication. Any help interpreting these values would be
> greatly appreciated. My goal is to truncate the log back to a more
> reasonable size. Thanks.|||To me, it seems to indicate that you have at least one table that is setup
for transactional replication, but the log reader is not running. Probaly
failed with an error. Could you check your distribution server to make sure
the log reader is running?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190756.45c8213c@.posting.google.com...
I have a database with a large (and growing) transaction log. Running
dbcc opentran yields the following two rows:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (508734:17171:1)
A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.
dbcc opentran yields the following two rows:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (508734:17171:1)
A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.You might want to post this to the replication group, as you are more likely
to find replication experts
there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190756.45c8213c@.posting.google.com...
> I have a database with a large (and growing) transaction log. Running
> dbcc opentran yields the following two rows:
> REPL_DIST_OLD_LSN (0:0:0)
> REPL_NONDIST_OLD_LSN (508734:17171:1)
> A search through BOL and the news groups yields no information on the
> meaning of these values. The database is published nightly using
> snapshot replication. Any help interpreting these values would be
> greatly appreciated. My goal is to truncate the log back to a more
> reasonable size. Thanks.|||To me, it seems to indicate that you have at least one table that is setup
for transactional replication, but the log reader is not running. Probaly
failed with an error. Could you check your distribution server to make sure
the log reader is running?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190756.45c8213c@.posting.google.com...
I have a database with a large (and growing) transaction log. Running
dbcc opentran yields the following two rows:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (508734:17171:1)
A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.
Monday, March 19, 2012
dbcc opentran
How do i read this ?
Replicated Transaction Information:
Oldest distributed LSN : (494721:133301:236)
Oldest non-distributed LSN : (494721:133372:1)
What are those values ? What should i watch for between the distributed and
non distributed LSN ?
Which table in the distribution db holds the LSN info so that it needs where
to start from i.e. which record it needs to fetch from the log file to
insert into the distribution db ?
Thanks
Hassan,
this is my take on the DBCC OPENTRAN -
if your log reader is keeping up, the Oldest non-distributed LSN is (0:0:0):
Oldest distributed LSN : (10:384:4)
Oldest non-distributed LSN : (0:0:0)
if it's not able to keep up or is disabled, the output will be in the
following format:
Oldest distributed LSN : (10:388:4)
Oldest non-distributed LSN : (10:390:1)
If you have an open transaction, the format of the output will include a
section at the top:
Oldest active transaction:
SPID (server process ID) : 55
UID (user ID) : 1
Name : mytran
LSN : (10:391:1)
Start time : May 16 2005 10:10:28:920AM
Replicated Transaction Information:
Oldest distributed LSN : (10:388:4)
Oldest non-distributed LSN : (10:390:1)
Using this, you can use dbcc inputbuffer (55) to find the open transaction's
TSQL, and then decide to kill it if necessary.
So, in your case you don't have any open transactions, and your log reader
agent has not read some (committed) transactions from the transaction log,
so they're not yet written to the distribution database. The numbers in
brackets are the log sequence numbers. You can run select * from
::fn_dblog(null,null) to have a look at the list of these, or for more
details you can see them in LogExplorer.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Replicated Transaction Information:
Oldest distributed LSN : (494721:133301:236)
Oldest non-distributed LSN : (494721:133372:1)
What are those values ? What should i watch for between the distributed and
non distributed LSN ?
Which table in the distribution db holds the LSN info so that it needs where
to start from i.e. which record it needs to fetch from the log file to
insert into the distribution db ?
Thanks
Hassan,
this is my take on the DBCC OPENTRAN -
if your log reader is keeping up, the Oldest non-distributed LSN is (0:0:0):
Oldest distributed LSN : (10:384:4)
Oldest non-distributed LSN : (0:0:0)
if it's not able to keep up or is disabled, the output will be in the
following format:
Oldest distributed LSN : (10:388:4)
Oldest non-distributed LSN : (10:390:1)
If you have an open transaction, the format of the output will include a
section at the top:
Oldest active transaction:
SPID (server process ID) : 55
UID (user ID) : 1
Name : mytran
LSN : (10:391:1)
Start time : May 16 2005 10:10:28:920AM
Replicated Transaction Information:
Oldest distributed LSN : (10:388:4)
Oldest non-distributed LSN : (10:390:1)
Using this, you can use dbcc inputbuffer (55) to find the open transaction's
TSQL, and then decide to kill it if necessary.
So, in your case you don't have any open transactions, and your log reader
agent has not read some (committed) transactions from the transaction log,
so they're not yet written to the distribution database. The numbers in
brackets are the log sequence numbers. You can run select * from
::fn_dblog(null,null) to have a look at the list of these, or for more
details you can see them in LogExplorer.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
dbcc opentran
How do I do anything with the results of this guy? How do I find out what
these LSN's are doing?
Replicated Transaction Information:
Oldest distributed LSN : (574262:1479:4)
Oldest non-distributed LSN : (574262:1498:1)
SQL2K SP3
TIA, ChrisR
I think it means, that you have transactional replication setup in this
database. And the logreader agent has not yet picked up that transaction.
Make sure your log reader agent for this published database is running. If
it isn't running or failed, then you have to start it and fix any errors it
is encountering. Once log reader picks up all the replicable transactions,
there won't be any replication open transactions.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ChrisR" <bla@.noemail.com> wrote in message
news:%23ODpR8R5EHA.3124@.TK2MSFTNGP11.phx.gbl...
> How do I do anything with the results of this guy? How do I find out what
> these LSN's are doing?
> Replicated Transaction Information:
> Oldest distributed LSN : (574262:1479:4)
> Oldest non-distributed LSN : (574262:1498:1)
> --
> SQL2K SP3
> TIA, ChrisR
>
these LSN's are doing?
Replicated Transaction Information:
Oldest distributed LSN : (574262:1479:4)
Oldest non-distributed LSN : (574262:1498:1)
SQL2K SP3
TIA, ChrisR
I think it means, that you have transactional replication setup in this
database. And the logreader agent has not yet picked up that transaction.
Make sure your log reader agent for this published database is running. If
it isn't running or failed, then you have to start it and fix any errors it
is encountering. Once log reader picks up all the replicable transactions,
there won't be any replication open transactions.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ChrisR" <bla@.noemail.com> wrote in message
news:%23ODpR8R5EHA.3124@.TK2MSFTNGP11.phx.gbl...
> How do I do anything with the results of this guy? How do I find out what
> these LSN's are doing?
> Replicated Transaction Information:
> Oldest distributed LSN : (574262:1479:4)
> Oldest non-distributed LSN : (574262:1498:1)
> --
> SQL2K SP3
> TIA, ChrisR
>
dbcc opentran
How do I do anything with the results of this guy? How do I find out what
these LSN's are doing?
Replicated Transaction Information:
Oldest distributed LSN : (574262:1479:4)
Oldest non-distributed LSN : (574262:1498:1)
--
SQL2K SP3
TIA, ChrisRI think it means, that you have transactional replication setup in this
database. And the logreader agent has not yet picked up that transaction.
Make sure your log reader agent for this published database is running. If
it isn't running or failed, then you have to start it and fix any errors it
is encountering. Once log reader picks up all the replicable transactions,
there won't be any replication open transactions.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ChrisR" <bla@.noemail.com> wrote in message
news:%23ODpR8R5EHA.3124@.TK2MSFTNGP11.phx.gbl...
> How do I do anything with the results of this guy? How do I find out what
> these LSN's are doing?
> Replicated Transaction Information:
> Oldest distributed LSN : (574262:1479:4)
> Oldest non-distributed LSN : (574262:1498:1)
> --
> SQL2K SP3
> TIA, ChrisR
>
these LSN's are doing?
Replicated Transaction Information:
Oldest distributed LSN : (574262:1479:4)
Oldest non-distributed LSN : (574262:1498:1)
--
SQL2K SP3
TIA, ChrisRI think it means, that you have transactional replication setup in this
database. And the logreader agent has not yet picked up that transaction.
Make sure your log reader agent for this published database is running. If
it isn't running or failed, then you have to start it and fix any errors it
is encountering. Once log reader picks up all the replicable transactions,
there won't be any replication open transactions.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ChrisR" <bla@.noemail.com> wrote in message
news:%23ODpR8R5EHA.3124@.TK2MSFTNGP11.phx.gbl...
> How do I do anything with the results of this guy? How do I find out what
> these LSN's are doing?
> Replicated Transaction Information:
> Oldest distributed LSN : (574262:1479:4)
> Oldest non-distributed LSN : (574262:1498:1)
> --
> SQL2K SP3
> TIA, ChrisR
>
Sunday, March 11, 2012
DBCC INEDXDEFRAG cauing transaction log growth
Hello!
I have noticed that when executing DBCC INDEXDEFRAG on big table (SQL
Server 2000 SP4), transaction log starts growing significantly. I was hoping
that INDEXDEFRAG is implemented as a serious of short transactions and
shouldn't cause significant transaction log growth. Our database is in
SIMPLE recovery mode. We have the same database/defragmentation process
running on SQL Server 2005 and never experienced this problem.
Any thoughts on this matter are greatly appreciated.
Thanks,
IgorMake sure you didn't wrap it in a transaction or have another long running
transaction open.
--
Andrew J. Kelly SQL MVP
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%233eJTLz5GHA.3952@.TK2MSFTNGP04.phx.gbl...
> Hello!
> I have noticed that when executing DBCC INDEXDEFRAG on big table (SQL
> Server 2000 SP4), transaction log starts growing significantly. I was
> hoping that INDEXDEFRAG is implemented as a serious of short transactions
> and shouldn't cause significant transaction log growth. Our database is in
> SIMPLE recovery mode. We have the same database/defragmentation process
> running on SQL Server 2005 and never experienced this problem.
> Any thoughts on this matter are greatly appreciated.
>
> Thanks,
> Igor
>
I have noticed that when executing DBCC INDEXDEFRAG on big table (SQL
Server 2000 SP4), transaction log starts growing significantly. I was hoping
that INDEXDEFRAG is implemented as a serious of short transactions and
shouldn't cause significant transaction log growth. Our database is in
SIMPLE recovery mode. We have the same database/defragmentation process
running on SQL Server 2005 and never experienced this problem.
Any thoughts on this matter are greatly appreciated.
Thanks,
IgorMake sure you didn't wrap it in a transaction or have another long running
transaction open.
--
Andrew J. Kelly SQL MVP
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%233eJTLz5GHA.3952@.TK2MSFTNGP04.phx.gbl...
> Hello!
> I have noticed that when executing DBCC INDEXDEFRAG on big table (SQL
> Server 2000 SP4), transaction log starts growing significantly. I was
> hoping that INDEXDEFRAG is implemented as a serious of short transactions
> and shouldn't cause significant transaction log growth. Our database is in
> SIMPLE recovery mode. We have the same database/defragmentation process
> running on SQL Server 2005 and never experienced this problem.
> Any thoughts on this matter are greatly appreciated.
>
> Thanks,
> Igor
>
DBCC INDEXDEFRAG and Transaction Log
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:393199
Hi,
Today I met a problem with DBCC INDEXDEFRAG on SQL Server 2000 Enterprise
with SP3. I run DBCC INDEXDEFRAG on a table with only one primary key. The
size of the table is about 120MB, the size of the index is about 1.6MB.
Before running DBCC INDEXDEFRAG, I run DBCC SHOWCONTIG and found the Logical
Scan Fragmentation was about 10%. The problem is that the transaction log I
backup after running DBCC INDEXDEFRAG on this table is about 600MB. I did
backup transaction log before this operation. There is no other activities
on this server. And I checked there wasn't any open transaction. I don't
know why DBCC INDEXDEFRAG on a small index created so big transaction log.
I appreciate any information and help !
Thanks!
Bill
Bill Wang wrote:
> Hi,
> Today I met a problem with DBCC INDEXDEFRAG on SQL Server 2000
> Enterprise with SP3. I run DBCC INDEXDEFRAG on a table with only one
> primary key. The size of the table is about 120MB, the size of the
> index is about 1.6MB. Before running DBCC INDEXDEFRAG, I run DBCC
> SHOWCONTIG and found the Logical Scan Fragmentation was about 10%.
> The problem is that the transaction log I backup after running DBCC
> INDEXDEFRAG on this table is about 600MB. I did backup transaction
> log before this operation. There is no other activities on this
> server. And I checked there wasn't any open transaction. I don't
> know why DBCC INDEXDEFRAG on a small index created so big
> transaction log.
> I appreciate any information and help !
> Thanks!
>
> Bill
A problem existed in SQL 2000 RTM, but was fixed in SP1. Can you confirm
you are running SP3.
http://support.microsoft.com/kb/q282286/
THe following article may explain some of the looging requirements. See
the "Logging Considerations: DBCC DBREINDEX vs. DBCC INDEXDEFRAG"
section.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Yes, David. It's SP3. I read both of articles before this operation. I will
try on another table and comfirm this problem.
Thanks for oyur help!
Bill
"David Gugick" wrote:
> Bill Wang wrote:
> A problem existed in SQL 2000 RTM, but was fixed in SP1. Can you confirm
> you are running SP3.
> http://support.microsoft.com/kb/q282286/
> THe following article may explain some of the looging requirements. See
> the "Logging Considerations: DBCC DBREINDEX vs. DBCC INDEXDEFRAG"
> section.
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
Hi,
Today I met a problem with DBCC INDEXDEFRAG on SQL Server 2000 Enterprise
with SP3. I run DBCC INDEXDEFRAG on a table with only one primary key. The
size of the table is about 120MB, the size of the index is about 1.6MB.
Before running DBCC INDEXDEFRAG, I run DBCC SHOWCONTIG and found the Logical
Scan Fragmentation was about 10%. The problem is that the transaction log I
backup after running DBCC INDEXDEFRAG on this table is about 600MB. I did
backup transaction log before this operation. There is no other activities
on this server. And I checked there wasn't any open transaction. I don't
know why DBCC INDEXDEFRAG on a small index created so big transaction log.
I appreciate any information and help !
Thanks!
Bill
Bill Wang wrote:
> Hi,
> Today I met a problem with DBCC INDEXDEFRAG on SQL Server 2000
> Enterprise with SP3. I run DBCC INDEXDEFRAG on a table with only one
> primary key. The size of the table is about 120MB, the size of the
> index is about 1.6MB. Before running DBCC INDEXDEFRAG, I run DBCC
> SHOWCONTIG and found the Logical Scan Fragmentation was about 10%.
> The problem is that the transaction log I backup after running DBCC
> INDEXDEFRAG on this table is about 600MB. I did backup transaction
> log before this operation. There is no other activities on this
> server. And I checked there wasn't any open transaction. I don't
> know why DBCC INDEXDEFRAG on a small index created so big
> transaction log.
> I appreciate any information and help !
> Thanks!
>
> Bill
A problem existed in SQL 2000 RTM, but was fixed in SP1. Can you confirm
you are running SP3.
http://support.microsoft.com/kb/q282286/
THe following article may explain some of the looging requirements. See
the "Logging Considerations: DBCC DBREINDEX vs. DBCC INDEXDEFRAG"
section.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Yes, David. It's SP3. I read both of articles before this operation. I will
try on another table and comfirm this problem.
Thanks for oyur help!
Bill
"David Gugick" wrote:
> Bill Wang wrote:
> A problem existed in SQL 2000 RTM, but was fixed in SP1. Can you confirm
> you are running SP3.
> http://support.microsoft.com/kb/q282286/
> THe following article may explain some of the looging requirements. See
> the "Logging Considerations: DBCC DBREINDEX vs. DBCC INDEXDEFRAG"
> section.
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
Subscribe to:
Posts (Atom)