Truncate Logfile

Hot Topic

Relationships
SQL CE 3.0 (Mobile 2005) Max DB Size Question
Breakpoints in script component
Error Migrating Reporting Services
yyMMdd date format
Reliability? [What the heck?]
Adding text to RS home page folder.aspx
parameter passing with blanks - help required
StrToMember problem
Compatible?

SQL Server

Connection string requires version specific MSOLAP.3 for SSAS 9.00.3027 ???
min server memory option and memory paging
CONNECT TO SERVER PROBLEM
Analysis Services on SQL2000 SP4
Connection String Encryption for SQL Server Mobile
Mixing INNER and OUTER joins
Poor performance querying view from sproc
SQL 2005 - View with Data Column Filter
time out expired error
Can I retrieve a result set from a sp into a variable within a Execute SQL Task?

Truncate Logfile

Dear all,

I am using SAP with MSSQl 2005. My transaction logfile is full. I took backup of transaction logfile and it should be truncated and shrink after the backup, but it did nothing. There is no error but the file size remain same.

I tried from SQL server management studio selecting database--right click--shrink--file. Then i try to release unused space, shrink, but not worked.

I also checked these steps while the SAP system was stopped.

Can anyone guide me what to do now

Thanks

Imran Hasware

Answer this question

Truncate Logfile

  • 5letters

    I checked from sys.database, i got the entry LOG_BACKUP, when i checked in microsoft docementation, 2 backups were advised. So i did the second backup of the transaction log, and then the log was truncated.

    Thanks for your replies.

    Imran Hasware

  • Elie Rodrigue

    the following command will truncate and shrink the log.

    backup log somedatabase with truncate_only

    dbcc shrinkfile (Logical name lof the logfile)

    when u trucate transaction log the the log backup chain breaks. So you should take a full backup after the trucation of log. To keep the Log size undercontrol what u should do is schedule the backup of Transaction log hourly/daily/weekly what ever suits u. As Peter said, read about DBCC Shrinkfile in BOL for further info

    Read the following links to understand the Log architecture

    http://msdn2.microsoft.com/en-gb/library/ms179355.aspx

    http://msdn2.microsoft.com/en-gb/library/ms190440.aspx

    Madhu



  • Kamii47

    The other side of this, while the truncate_only option will truncate the log, this is something that you should never run against an operational database. Doing so prevents you from being able to recover to a point in time and it also prevents you from taking subsequent transaction log backups until you run a full backup.

  • TonoGam

    Log truncation and shrink file are two different things. Truncating the log does not usually result in shrinking the log file, it will remain the same size. If you are really unable to truncate the log, you should look at DBCC OPENTRAN to determine if there is an open transaction or replication or something else keeping the log from truncating. Or look at this column of sys.databases:

    select name, log_reuse_wait_desc from sys.databases

    If the log really is truncating but you really want to shrink the file, you should look at the documentation for DBCC SHRINKFILE.



  • Truncate Logfile
Answer this question