[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Suspicious Transaction Log.

Posted on 2006-06-07
Medium Priority
Last Modified: 2012-08-14
Not really sure if this is a problem but it falls under the, 'I don't recall that looking like that', category.

One of the Transaction logs on one of my SQL2000(sp3) DB seems unusually large.

The db itself is, 3,000,000Kb but the accompanying Trans. Log is 54,000,000Kb.

I run a MaintPlan at 12 each night that utilizes all the tabs (Optimiziation, Backup Db, Backup Logs, etc.) then I use Veritas to come along around 3 and back up everything to tape.

Couple things are bothering me.  

First, the size of the trans log. Second, the fact that neither the MaintPlan or the tape backup are flushing or compacting the trans log and third, the fact that I am starting to see various events and log entries making mention of the fact the the trans log backup failed due to disk space (have 35Gb free. surely that's enough).

Do I have a problem?
Question by:mbath20110
LVL 75

Accepted Solution

Anthony Perkins earned 672 total points
ID: 16853490
>>have 35Gb free. surely that's enough<<
Perhaps I am stating the obvious, but 54,000,000Kb. > 35GB

Or am I missing something?
LVL 27

Expert Comment

ID: 16853598
Do you have the maint plan or tape backup performing a shrink file command? I assume that this database is in recovery model FULL.

LVL 43

Expert Comment

by:Eugene Z
ID: 16854586

run Transaction log backup - looks like you have Full recovery mode
then shrink the database\log file
and if you do not need transaction log be backed up (for the dbrestore topoint of failure) just swith Recovery to Simple (see in EM DB properies)
you do not have much free space for Optimiziation (it is reindexing)
so or get more HDD space or use Index Defrag instead reindex
oe reindex table by table - and truncate - shrink database, etc..
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

LVL 23

Expert Comment

ID: 16854898
LVL 10

Assisted Solution

TAB8 earned 664 total points
ID: 16856604
A shrink will only work if your log file has freespace in it ... if it hasent you could do the following to create a new log file

1, Backup the db
2, Detach the DB
3, rename the logfile <name>.old
4, in EM try and reatach the DB .. if will warn you that there is no log file and will offer to try and create a new log file for you , this usualy works
5, if it worked then delete the logfile <name>.old ...   if it didnt work rename the logfile <name>.old to the correct name and reattach the DB
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 664 total points
ID: 17485808
idea and solutions have been provided

Featured Post

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.

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

834 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