Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / DB Engine / SQL Server / July 2008

Tip: Looking for answers? Try searching our database.

Trans log file is very big and can't shrink it

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
SQLFriend - 18 Jul 2008 15:07 GMT
Hello,
I have a sql server 2000 database configured on Full Recovery Model.  The
transaction log is growing quite big (26 GB currently), here is what I did
to shrink it and didn't work and hopeing that somenone will help me fixing
this issue:
1. I executed the following 3 commands:
  DBCC SHRINKFILE('DBNameLog',20)
  BACKUP LOG DBName WITH TRUNCATE_ONLY
  DBCC SHRINKFILE('DBName_Log',20)
I ran the DBCC LOGINFO('DBName') and got  581 records with status 2.
I can't backup the transaction log to a any of the 3 drives on sql server
because I don't have free enough space on them to hold the 26 GB trans log
file.
Any help is greatly appreciated.
Russell Fields - 18 Jul 2008 17:59 GMT
SQLFriend,

I suspect that your problem is because the transaction logs are not being
successfully backed up.  A common misunderstanding is that a BACKUP DATABASE
backs up the logs also, so nothing more needs to be done.  But, in order to
allow the log space to be reused it is also necessary to do BACKUP LOG
commands, so that the log is backed up separately from the database.  (So,
if you discover this problem in your setup, once you clear the log problem
then you should be able to prevent future occurances.)

In order to actually shrink the 26 GB log file, you may need to run these
steps more than once.

DBCC SHRINKFILE('DBNameLog',20)
BACKUP LOG DBName WITH TRUNCATE_ONLY
DBCC SHRINKFILE('DBName_Log',20)

Since the open rows are in the last section of the log file, you have to
patiently wait for the active part of the log to roll back to the front of
the log file.  After than the TRUNCATE_ONLY will work.  Switching the
database to the SIMPLE recovery model has the same effect as the truncate.

Once your log is shrunk and (if that is what you want) the database is
restore to FULL recovery model, do a database backup to reestablish a
starting point for the log backups, then start regularly running the log
backups.

If  you were already doing separate log backups, but the log is full because
of some enormous transaction that messed up everything for you, you should
still do the database backup once you have recovered the space.

RLF

> Hello,
> I have a sql server 2000 database configured on Full Recovery Model.  The
[quoted text clipped - 10 lines]
> file.
> Any help is greatly appreciated.
SQLFriend - 18 Jul 2008 18:43 GMT
Russel,
I did execute the 3 commands below 5 times so far and didn't help, I noticed
that log file jumped up by 2 GB to 28 GB, do you suggest that I should change
the recovery model to SIMPLE?
Thanks again for your help...

> SQLFriend,
>
[quoted text clipped - 43 lines]
> > file.
> > Any help is greatly appreciated.
Russell Fields - 18 Jul 2008 20:25 GMT
SQLFriend,

Well, some time has passed, so let's look at something else first:  Since
the log cannot be truncated past the oldest open transaction, check for that
next.

DBCC OPENTRAN(DBName)

It should either return:
No active open transactions.

Or else something like:

Transaction information for database 'DBName'.
Oldest active transaction:
   SPID (server process ID): 101
   UID (user ID) : 12
   Name          : user_transaction
   LSN           : (1231231:345345:6)
   Start time    : Jul 14 2008  4:18:44:180 AM
   SID           : 0xd8690a876b8769c8768e86f

If the start time of the oldest active transaction is quite old, then you
have what is probably a hung transaction.  Check into it and, if necessary,
KILL the transaction.  Assuming this case to be true, next run your three
steps again.

If that works, then keep your database in FULL recovery model, just leave it
that way.  Now, you can do:

BACKUP DATABASE ....
BACKUP LOG ...

Then go on about life.  I have not had this problem in quite a while, but I
have in the past had a hung transaction that caused me similar problems.

Hope that helps,
RLF

> Russel,
> I did execute the 3 commands below 5 times so far and didn't help, I
[quoted text clipped - 64 lines]
>> > file.
>> > Any help is greatly appreciated.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.