Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

SQL Server Log File NOT Growing

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
adamch25
Asked:
adamch25
  • 6
  • 4
1 Solution
 
chapmandewCommented:
Are you in Simple recovery mode?
0
 
adamch25Author Commented:
No. I am in Full recovery mode.
0
 
chapmandewCommented:
Do you have a db maint plan that does log backups?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
adamch25Author Commented:
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
 
adamch25Author Commented:
If it helps, the box is running Windows 2003 SP2 and SP4 for SQL
0
 
adamch25Author Commented:
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
 
chapmandewCommented:
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
 
kosmoraiosCommented:
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
 
adamch25Author Commented:
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
 
chapmandewCommented:
ah hah!  You're welcome.
0
 
adamch25Author Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now