Discussion:
dbcc loginfo(MyDB) has many rows all with Status 2
(too old to reply)
Jason Huang
2009-11-03 08:14:27 UTC
Permalink
Hi,

In our SQL Server 2000, we have a database, when I run the command DBCC
loginfo(MyDB).
The resultset it returns with 369 rows, all of them have Status 2.
But when I run the sp_helpfile, the MyDB_Data is only approximate 300Mb, but
the MyDB_Log is about 4600Mb.
I would like to shrink the Log file smaller, but with all rows with Status
2, I'm not sure how to do that.
Thanks for help.


Jason
Uri Dimant
2009-11-03 08:44:38 UTC
Permalink
Jason
Create a table and inserti into dummy data to 'move' those rows, btw status
2 means that SQL Server 'needs' those transactions and they have not yet
flushed to
the disk. Now that you should see status 0 at the bottom and can shrink
the LOG.
Post by Jason Huang
Hi,
In our SQL Server 2000, we have a database, when I run the command DBCC
loginfo(MyDB).
The resultset it returns with 369 rows, all of them have Status 2.
But when I run the sp_helpfile, the MyDB_Data is only approximate 300Mb,
but the MyDB_Log is about 4600Mb.
I would like to shrink the Log file smaller, but with all rows with Status
2, I'm not sure how to do that.
Thanks for help.
Jason
Jason Huang
2009-11-03 08:54:19 UTC
Permalink
Thanks.
But the result from dbcc loginfo are all Status 2, can't find Status 0 at
all.
Post by Uri Dimant
Jason
Create a table and inserti into dummy data to 'move' those rows, btw
status 2 means that SQL Server 'needs' those transactions and they have
not yet flushed to
the disk. Now that you should see status 0 at the bottom and can shrink
the LOG.
Post by Jason Huang
Hi,
In our SQL Server 2000, we have a database, when I run the command DBCC
loginfo(MyDB).
The resultset it returns with 369 rows, all of them have Status 2.
But when I run the sp_helpfile, the MyDB_Data is only approximate 300Mb,
but the MyDB_Log is about 4600Mb.
I would like to shrink the Log file smaller, but with all rows with
Status 2, I'm not sure how to do that.
Thanks for help.
Jason
Uri Dimant
2009-11-03 09:01:26 UTC
Permalink
Jason
Check the status after running INSERT INTO....
Post by Jason Huang
Thanks.
But the result from dbcc loginfo are all Status 2, can't find Status 0 at
all.
Post by Uri Dimant
Jason
Create a table and inserti into dummy data to 'move' those rows, btw
status 2 means that SQL Server 'needs' those transactions and they have
not yet flushed to
the disk. Now that you should see status 0 at the bottom and can shrink
the LOG.
Post by Jason Huang
Hi,
In our SQL Server 2000, we have a database, when I run the command DBCC
loginfo(MyDB).
The resultset it returns with 369 rows, all of them have Status 2.
But when I run the sp_helpfile, the MyDB_Data is only approximate 300Mb,
but the MyDB_Log is about 4600Mb.
I would like to shrink the Log file smaller, but with all rows with
Status 2, I'm not sure how to do that.
Thanks for help.
Jason
Uri Dimant
2009-11-03 09:02:08 UTC
Permalink
Also have you tried BACKUP LOG ........ and gain check the status?
Post by Jason Huang
Thanks.
But the result from dbcc loginfo are all Status 2, can't find Status 0 at
all.
Post by Uri Dimant
Jason
Create a table and inserti into dummy data to 'move' those rows, btw
status 2 means that SQL Server 'needs' those transactions and they have
not yet flushed to
the disk. Now that you should see status 0 at the bottom and can shrink
the LOG.
Post by Jason Huang
Hi,
In our SQL Server 2000, we have a database, when I run the command DBCC
loginfo(MyDB).
The resultset it returns with 369 rows, all of them have Status 2.
But when I run the sp_helpfile, the MyDB_Data is only approximate 300Mb,
but the MyDB_Log is about 4600Mb.
I would like to shrink the Log file smaller, but with all rows with
Status 2, I'm not sure how to do that.
Thanks for help.
Jason
Jeffrey Williams
2009-11-03 17:35:15 UTC
Permalink
Execute the following:

SELECT * FROM sys.databases;

Review the column 'log_reuse_wait_desc' to find out why the transaction log
is not truncating. If you see LOG BACKUP, you need to perform a transaction
log backup. If you see REPLICATION - then you have to find out why
replication is behind (usually caused by the agent job not running).

Jeff
Post by Uri Dimant
Also have you tried BACKUP LOG ........ and gain check the status?
Post by Jason Huang
Thanks.
But the result from dbcc loginfo are all Status 2, can't find Status 0 at
all.
Post by Uri Dimant
Jason
Create a table and inserti into dummy data to 'move' those rows, btw
status 2 means that SQL Server 'needs' those transactions and they have
not yet flushed to
the disk. Now that you should see status 0 at the bottom and can
shrink the LOG.
Post by Jason Huang
Hi,
In our SQL Server 2000, we have a database, when I run the command DBCC
loginfo(MyDB).
The resultset it returns with 369 rows, all of them have Status 2.
But when I run the sp_helpfile, the MyDB_Data is only approximate
300Mb, but the MyDB_Log is about 4600Mb.
I would like to shrink the Log file smaller, but with all rows with
Status 2, I'm not sure how to do that.
Thanks for help.
Jason
Loading...