SQL 7.0 Transaction Logs Won't Shrink!!!

Jun 5, 2002

I have seen quite a few people post this type of problem, but I am finding few solutions. Your advice and/or experiences are greatly appreciated.

Here's my scenario:

Environment: Windows NT 4.0 SP 6, SQL 7.0 (set up for Transactional Replication)

Problem: We have several remote dB machines configured for full recovery. On these machines are several dB's that are capturing aprox. 280 data points per second per unit. (Each dB represents one unit, and we have 21 units) No problem here. The problem is the transaction logs, that obviously grow profusely, will not SHRINK after, backups, dbcc commands and TSQL has been issued in failed attempts to shrink the logs. (in other words we've tried everything)

My questions are:

1. Because we are replicating, is it absolutely necessary to configure dB's for FULL recovery? How do I check in 7.0 if the dB is certainly in FULL vs. SIMPLE recovery mode?

2. I work with SQL 2000 and shrinking files is no problem. How can we shrink these log files in SQL 7.0?

It is rather urgent I find a solution as we are running out of hard drive space on our remote machines. Please help :-(

Christine

ADVERTISEMENT

How To Shrink SQL Logs

Feb 8, 2008

Just want to inquire on how to shrink my sql logs.
My disk space is running and I suspect that sql logs is the cause
of the problem..
Kindly help

View 4 Replies View Related

Backup And Shrink Trans Logs??

May 30, 2008

Hi,

I am in the process of migrating databases from a SQL Server 2000 server to a 2005 server.

I have taken full backups and restored without recovery on the 2005 server. I now need to take transaction log backups and restore with recovery tonight to complete the operation. My question is this:

Some of the transaction logs have grown very large. Is it safe practice to perform a backup tran with truncate only and then run dbcc shrinkfile to reduce the size before I back up? This would make the process much quicker.

Thanks.

View 1 Replies View Related

Shrink Transaction Log

Jan 18, 2001

Is it possible to shrink Transaction Log file? If yes, how? Urgent matter please.

Regards,

View 1 Replies View Related

Shrink SQL 6.5 Transaction Log

Mar 7, 2001

Is it possible to shrink the size of a transaction log file in SQL 6.5. Normally in SQL 7.0 I use detach and reattach but this is not supported in 6.5. The log file for a 200Mb database is 749Mb with no unused space and no open transaction. Thanks

View 2 Replies View Related

Cannot Shrink Transaction Log

Sep 25, 2002

Hi,

I have a t-log on one database which is 400mb when the database size is only 30mb. dbcc shrinkfile does not work and dbcc opentran shows no open transactions. When I tried to do a Backup log with truncate_only the following message displays:

The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.

The database in question is not a publisher and only receives 5 subscribed articles, none of which are changed very often. The database operates 24 x 7.

Does anyone have a solution to this dilemma?

Thank You

View 4 Replies View Related

Shrink Transaction Log

Sep 26, 2007

How can I reduce the size of transaction log of a database...?pls hel me...

View 2 Replies View Related

Cannot Shrink The Transaction Log

Jul 20, 2005

Hi Friends,I have tried almost everything but I cant seem to shrink thetransaction log.Executing DBCC SQLPERF(LOGSPACE)gives me this info:Database Log Size (MB) Log Space Used (%) StatusMY_eems 368.49219 16.034182 0I made a complete backup of the database and transaction log and thenexecuted this statement:DBCC SHRINKFILE (MYeems_log, 1)and this is the message I gotCannot shrink log file 2 (ghgeems_Log) because all logical log filesare in use.DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages------ ------ ----------- ----------- ----------- --------------17 2 42880 34561 42880 34560(1 row(s) affected)DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.How can I get the transaction log to its minimum size?Any help will be appreciated,ThanksA.B

View 5 Replies View Related

Transaction Log Will Not Shrink

Mar 12, 2008

I have 4 large sql dbs. All 4 have been trimmed down by deleted recs from the db, then shrinking. 3 out of the 4 shrunk successfully and the log files are much smaller than the data files. On the 4th the data file shrunk to 14G, but the log file stayed at 47G. I have tried truncating and then shrinking, but it never gets any smaller.

Any suggestions on why this particular one will not shrink?

View 3 Replies View Related

Transaction Log Doesn't Shrink

Jul 31, 2000

Hi Everyone,
I have a SQLServer 7.0 conversion db. Data file is 89 mg. The log has grown into 238 mg. Db and log are backuped nightly. Optimization and integrity jobs run weekly. The transaction log keeps growing! Truncate log on checkpoint is on and autoshrink is on.
What else can I do to truncate my log?

LT

View 3 Replies View Related

Can't Shrink Transaction Log From 30GB To 1GB

Jun 1, 2000

We have a user database that was initially allocated 90GB for data file, 30GB for transaction log file. We decided to shrink the data file to be 2GB, log file to be 1GB using 'DBCC SHRINKFILE'. The result of data file is 2GB as we expected. But log file is 5,764 MB no matter how many times we shrink it or run 'backup log with truncateonly' against it. Can this be something to do with how transaction log allocate its segments called virtual log files? Is 5GB the smallest unit of log segment that can be shrunked? Is there any way around this? We are in a production enviroment, and this is pretty urgent.
Thanks in advance.

Tung

View 1 Replies View Related

SQL Server 7 Transaction Log - Can't Shrink!

Jun 10, 1999

For some reason (I'm sure it was my code) my transaction log segments grew to a huge size (>1 GB each). They are normally in the area of 150 MB each, so I wanted to shrink them back down. But I have one transaction log segment that won't shrink! I'm a relatively new administrator, but I've tried shrinking the database (which worked for all but one segment) and truncating the logs, I've tried issuing a CHECKPOINT, I've tried stopping and restarting SQL Server, but I can't get that last segment to shrink to a normal size. Any ideas?

Thanks,

Robin

View 2 Replies View Related

Shrink Transaction Log After Replication

Sep 9, 2005

Hi!

View 1 Replies View Related

Transaction Log Backup Did Not Shrink Ldf - Help Please

Aug 15, 2007

I am a bit confused. I was told that backing up the TLog cleared out all the commited transactions which shrinks the log. Is this true, becuase I started with a 72 gig transaction log and the created a maint plan to back it up as a .trn. After this was successful the 72gig .ldf was still the same size. What am I doing wrong to get this log size down?

Thanks!

View 6 Replies View Related

Shrink The Transaction Log File In SQL7

Sep 6, 2001

I have a database (sql server 7.0) that has some big unused space in the transaction log. I tried to shrink the transaction log, but it seems it is not working. I used the same procedures to successfully shrink the log file for database of SQL Server 2000.

Here are what I tried:

shrink database from the Enterprise Manager.
or
dbcc shrinkfile (Spoper_Log, 40)
backup log Spoper with truncate_only

Does anybody know how to shrink the transaction log in SQL Server 7? This is a production database and I can't afford to lose any data.

View 4 Replies View Related

Mirroring :: How To Shrink Transaction Log On Mirrored DB

Oct 7, 2015

Today, I became an involuntary DBA until my company finds someone else. We have an SQL R2 DB that is mirrored that failed dur to the transaction log growing and filling up the disk. As a band aid remedy, I was able to add 20GB to the disk (it is a VM) and then I backed up the Transaction log and did select log_reuse_wait_desc from sys.databases where name = 'mydb' and the result showed that the backup was successful (NOTHING, I think was the result).

After this I was able to resume the mirror and everything has been running fine. What I need to do now is to shrink the Transaction log so that if it starts to grow again, I will get an alert and can avoid today's issue. From what I have read, I can just use DBCC SHRINKFILE [logname] after I do a backup and the changes will be reflected on the mirror as well. I have shrunk the T-log in non-mirrored servers before, just don't know if there are any key differences.

View 6 Replies View Related

Truncate Transaction Log And SHRINK DATABASE

Aug 28, 2007

Is it possible to truncate Transaction Log and Shrink DATABASE while the database is being used by users or the database becomes unuvailable during this operations?

Thank you.

View 2 Replies View Related

SQL 2012 :: Argument Daily Transaction Log Shrink?

Jul 7, 2015

For a few days now I have a discussion with a colleague about shrinking the transaction log as a daily maintenance job on an OLTP database. The problem is I cant figure out a way to convince her she is doing something really wrong. Its not the first discussion.. Maintenance Plans.

She implemented this "solution" with a lot of customers as a solution against VLFs fragmentation and huge transaction log sizes. My thoughts about doing this is very clear and I have used the following arguments without success to convince her:

- To solve too many VLFs you have to focus on the actual size of the transaction log and the autogrowth settings in combination with regularly transaction log backups. Check the biggest transaction and modify the transaction log size based on this. Not use shrinking as a solution for solving many VLFs.

- Shrinking the transaction log file on a daily basis that is disk I/O intensive. When the transaction log file is too small for new transactions, the transaction log needs to grow and this will cause disk I/O, this can cause performance problems.

- It looks unprofessional.

These steps are used every morning at 6:00 AM and a transaction log backup is made every 30 minutes.

Step 1
DBCC SHRINKFILE (N'' , 0, TRUNCATEONLY);
go

Step 2
ALTER DATABASE
MODIFY FILE (NAME = N'', SIZE = 4098MB);
GO

My main purpose is making sure the customers have the best possible configuration and I cant accept this is being implemented. Are there any more arguments available for this issue?

View 2 Replies View Related

Transaction Logs

Apr 24, 2002

What is the best way to clear the transaction logs. My backup job each night is ending because it says it is running out of disk space and I need to clear up the transaction logs. Any help is appreciated. I see many different options (trucate option, auto shrink, etc.), just need some assistance tosome more specific best approaches. Thanks

View 3 Replies View Related

Transaction Logs

Aug 13, 2001

Im having issues truncating my transaction logs. I have logs in excess of 40 gigs. All the info in the BOL is very vague. Any assistance would be apreciated.

Regards

SeTi Ni

View 5 Replies View Related

Transaction Logs

Jun 22, 2000

When we backup the transaction log,will it truncate the log or it will grow from there?Truncate log on check point is desabled.
Thanks!

View 2 Replies View Related

Transaction Logs

Aug 30, 2000

to take the transaction log back up regularly I should have truncate log on check point false. If I do so then how will I truncate the log.
regards,
Renu

View 3 Replies View Related

Transaction Logs

Aug 20, 1998

I am trying to import records via bcp (about 1,500,000 records) and I keep running out of disk space. Is there any way to limit or do away with the transaction log (and still be able to import)?

View 2 Replies View Related

Transaction Logs Help!!!!!!!!!

Dec 14, 1999

how do i decrease the transaction log example 500mb to 100mb, can you help me with the syntax pls!!

View 1 Replies View Related

Transaction Logs

Sep 2, 2004

I need to move transaction logs to a different drive and am looking for a way to do it without detaching the db.

Can I create a second transaction log file for the DB and then delete the original log file?

What is the best way to do this for db's that are being replicated?

Thanks,
Ken Nicholson

View 3 Replies View Related

Transaction Logs

Jun 13, 2002

I need to seperate my SMS data files and transaction logs onto two seperate drives. Can anyone assist me with this?

thanks...

View 1 Replies View Related

Transaction Logs

Aug 2, 2004

HELP!!!!

When I look at the Database maintience plan history entry for backup I have a message that reads: "Backup can not be performed on this database. This sub task is ignored".

Have anyone come across this error before?? As part of the Maintenance plan some transaction log are being backup and some aren't instead they receive the message above.

Thanks

Lystra

View 2 Replies View Related

Transaction Logs

Nov 19, 2004

Has anyone ever had the problem of the IT department not backing up thier transaction logs simply because they didnt find it necessary??

View 3 Replies View Related

Transaction Logs

Jan 8, 2004

i have several sql servers doing maintenance plans and backing up the transaction logs to tape. unfortunately it seems that the server keeps adding the transaction logs to the same tape, without overwriting them. Nowadays a transaction log backup to disk takes 2 minutes but when done to tape it is taking up 1hr54minutes. What can i do so that the tape is automatically initialised without having to do it manually... Thanks

View 1 Replies View Related

Transaction Logs

Apr 9, 2008

i was wondering if smeone could give me a breif over view how far back do these go

View 12 Replies View Related

Transaction Logs And LDF

Jun 11, 2008

HI All,

Is this is a good idea or not?

If i attached my SQL Server to SAN only for DATA and LOG (Ldf) drives
through the FC. then we put *.bak and *.trn on to local disk RAID 1 drive?

Any performance isues?

View 3 Replies View Related

Transaction Logs

Dec 7, 2007

Is there a way to view the transactions from a .TRN transaction log file? If so can I overwrite some of the transactions on the file and then restore from it? I am just curious.

View 2 Replies View Related

Transaction Logs

Mar 11, 2007

Hi, I know just about nothing about SQL Server. I am getting this error:The log file for database 'my_database' is full. Back up the transaction logfor the database to free up some log space.I can't access the transaction logs to back them up. I am told that my ISP'stech support should have it set up to shrink those logs automatically everyso often. Is that true?Why are they needed? Up till Monday, the logs are just of our getting SQLServer set up, so couldn't the logs just be deleted? Most of my site can'trun with this problem.I'd appreciate any enlightenment anyone can give me!Thanks, Jill

View 1 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved