Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server Log File NOT Growing

Posted on 2008-06-25
11
Medium Priority
?
360 Views
Last Modified: 2012-08-14
I have a 15GB SQL Server 2000 database that has a log file that is immediately shrinking or NOT growing even though there are hundreds of transactions being made per hour. Four weeks ago I had made a copy of the database for testing purposes by detaching the original database, copying it, reattaching the original and also reattaching the copied database as a different name. When I run transactions against the test database the log file grows as it should. I should also note that SQL runs a job every night at midnight against the production and the test database that shrinks the log file. The script is as follows:

DBCC SHRINKFILE( [ABONLOG], 2 )
BACKUP LOG [ABON] WITH TRUNCATE_ONLY
DBCC SHRINKFILE( [ABONLOG], 2 )

DBCC SHRINKFILE( [ABONTESTLOG], 2 )
BACKUP LOG [ABONTEST] WITH TRUNCATE_ONLY
DBCC SHRINKFILE( [ABONTESTLOG], 2 )
0
Comment
Question by:adamch25
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21867204
Are you in Simple recovery mode?
0
 

Author Comment

by:adamch25
ID: 21867211
No. I am in Full recovery mode.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21867227
Do you have a db maint plan that does log backups?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:adamch25
ID: 21867279
No. But, we do have a backup that runs nightly at 2:00AM that does a full backup of the databases. The backup software is Symantec Backup Exec which is run from a different server but the agent runs from the SQL server.
0
 

Author Comment

by:adamch25
ID: 21867329
If it helps, the box is running Windows 2003 SP2 and SP4 for SQL
0
 

Author Comment

by:adamch25
ID: 21867362
I just noticed a few error messages in the SQL server logs...

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

and

Error: 15457, Severity: 0, State: 1
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 21867367
Hmmm...a full backup shouldn't do it.  Have you checked to make sure that the BackupExec software doesn't do any type of truncation of the log or anything like that?
0
 
LVL 6

Expert Comment

by:kosmoraios
ID: 21867453
The two errors above are just informational. When you change your configuration you'll see these, no need to worry. Running the RECONFIGURE command will make those go away.
0
 

Author Comment

by:adamch25
ID: 21867529
Ahhh! Found the problem and am awarding you the points. The SQL server had an old Symantec Backup Exec Agent which was hanging after the backup completed thus locking the log file. Thank you for your assistance in finding this problem.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21867553
ah hah!  You're welcome.
0
 

Author Comment

by:adamch25
ID: 22292100
After upgrading the Symantec backup Exec 12. The issue remains.  I have even tried disabling the backup jobs but the 'collapsing' log file issue remains.  I have tested the hard drives, to crc errors or bad sectors.  I've detached, renamed teh log, adn re-atatched to create a fresh log file, no luck.  I movied the mdb and log files to thier own new hdd, no luck.  I have even created a brand new database withing SQl 2000, then jsut run a bunch of transactions against it.  The log file still 'collapses' on itself even in a freshly created database.  SQL Profiler has not shown me anything helpful either.

It's jsut too wierd, at random times the data within the transaction log 'collapses' on itself, and sometimes the container will collapse down to 2MB as well.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question