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
ADVERTISEMENT
Jan 10, 2012
I am trying to reorganise the log files on a server, (long story short they are fragmented so I want to shrink and reset the initial size and growth) and I am unable to shrink them. When I run the following:
use test
DBCC SHRINKFILE(test_log, TRUNCATEONLY)
--or
use
DBCC SHRINKFILE(test_log,2, TRUNCATEONLY)
I get the following message:
Msg 8985, Level 16, State 1, Line 1
Could not locate file 'test_log' for database 'test' in sys.database_files. The file either does not exist, or was dropped.
I get this message for every database on the server. I got the logical name of the file using sp_helpfile and have checked it against sys.masterfiles, sys.database_files and sys.sysaltfiles, all match up and confirm the name 'test_log'.
I rebooted the server last night and was able to shrink the first couple of .ldf's I tried so I presumed it was fixed. This morning when I try again i get the sanme error, I don't see anything in the SQL server or system logs that indicates a change.
I am able to add new log files and remove log files, however if I add a new log file (test_log2) and then try and truncate that file I get the same error.
View 9 Replies
View Related
May 15, 2006
We are using SQL Server 2005 (SP1). I have created a maintenance plan that backs up up the datebase every night. The problem is that the transaction log is continuing to grow. I have been told that a full backup will automatically truncate and shrink the transaction log. However, this is not happening. How can I truncate and shrink the transaction log after a full backup as part of our maintenance plan. Thank you.
View 29 Replies
View Related
Jan 18, 2001
Is it possible to shrink Transaction Log file? If yes, how? Urgent matter please.
Regards,
View 1 Replies
View Related
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
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
Sep 26, 2007
How can I reduce the size of transaction log of a database...?pls hel me...
View 2 Replies
View Related
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
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
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
View 1 Replies
View Related
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
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
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
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
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
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
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
Feb 3, 2015
I have a Customer running a database in a High Availability Group and I am not familiar with the set up... They have a transaction log that quadrupled in size during a data import and update which was generated by an external application. They have limited server space and would like to shrink the log again now as this import / update only happens once a year. The way this has always been dealt with in the past was by shrinking the DB and logs after the update.
Now however, when attempting to do a log or db shrink, an error message is generated which says that the log cannot be shrunk as the DB is in use as part of an Availability Group....
The more I search and try to read up on this subject, it looks like the DB has to be removed from the Availability Group before the log can be shrunk and then the Availability Group has to be re-created or restored in some way. Is there a simple solution to this conundrum?
View 9 Replies
View Related
Jul 23, 2005
My trancaction log is 25GB and my database file is 39GB. I justswitched to the 'Simple' recovery model from the 'Full' recovery model.When if ever can I expect the size of the transaction log to reduce insize? Is there anything else that I should do to aide with thereduction?Thanks,Peter
View 5 Replies
View Related
Aug 18, 2015
We have a SQL server 2012 database with size 200mb and Transaction Log has gone up to 34GB.
We have SQL server Mirror is enabled and principal database is running with Full Recovery Model.
How can I truncate/Shrink Log files? Will it impact on existing mirroring setup? I am very much new to SQL server.
View 17 Replies
View Related
Sep 18, 2015
I am getting an error about "Cannot perform a shrinkfile operation inside a user transaction", but I don't have a shrinkfile command in my procedure. Does SQL hang on to that command if it was received earlier in a different procedure?
View 5 Replies
View Related
Feb 7, 2007
How to shrink LDF in SQL Server 7 ?
We are using SQL Server 7 under Windows 2000 Advance Server with current Service Pack.
View 3 Replies
View Related
Jan 5, 2007
If you have problems shrinking a transaction log no matter what commands you issue, here's a way to shrink the tranlog:
1. Right click on the properties of the database and go to the options tab.
2. Change the recovery mode to simple.
3. Right click again on the database - go to all tasks - shrink database.
4. Shrink the database.
5. Change the recovery mode back to what you had it as.
I found this out by trial and error as I could not find any documentation on it and no matter what I tried, I could not get the transaction log to shrink.
View 4 Replies
View Related
Jul 4, 2007
First of all thanks for taking interest in this thread.
I have database in Microsoft SQL Server and its size is appx. 427 GB. It consists of data from 2006. Everyday I expire data of 2-3 days and then try to shrink database as much as possible.
But it frees only 2-3 MB after 7-8 hours process. And in that database there some files which do not shrink even I put one of them on shrinking for whole day.
And as per company policy, I am only allowed to expire data which are 15 months older.
I am facing very much problem as my daily import and publish activity some times got failed for that reason.
If anyone knows the solution please tell me the solution.
View 14 Replies
View Related
Oct 26, 2007
use borden_europe
set nocount on
print @@version
select DATABASEPROPERTYEX( 'borden_europe' , 'recovery' )
select * from sysfiles
dbcc loginfo ('borden_europe')
dbcc opentran ( Borden_Europe )
backup log Borden_Europe with truncate_only
DBCC SHRINKFILE(borden_europe_log, 1)
dbcc loginfo ('borden_europe')
Yields this output:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )
---------------------------------------------------------------------------------------------------
SIMPLE
fileid groupid size maxsize growth status perf name filename
------ ------- ----------- ----------- ----------- ----------- ----------- ------------------------
1 1 204336 -1 20 1081346 0 Borden_EUROPE e:Program FilesMicrosoft SQL ServerMSSQLdataBorden_Europe.mdf
2 0 7889 -1 20 1081410 0 Borden_EUROPE_log d:program filesMicrosoft SQL ServerMSSQLlogsBorden_Europe_log.ldf
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- ---------------------- ---------------------- ----------- ----------- ------ ----------
2 32309248 8192 78110 2 128 0
2 32309248 32317440 78109 0 128 0
(2 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Cannot shrink log file 2 (Borden_EUROPE_log) because total number of logical log files cannot be fewer than 2.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
5 2 7889 7889 7888 7888
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- ---------------------- ---------------------- ----------- ----------- ------ ---------------------------
2 32309248 8192 78112 2 64 0
2 32309248 32317440 78111 0 64 0
(2 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
View 3 Replies
View Related
Jun 10, 2015
I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed. I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log. What are the steps to restore full database back and one day's transaction log file. Note: there is no differential database backup and transaction backup.
View 8 Replies
View Related
Jun 19, 2015
I wrote a custom procedure to shrink the db log files alone. It works well always but yesterday in one of the server the job execution was failed.
Error msg:
: The log for database 'ReportServer$DB' failed to grow while shrink in progress. Please retry. [SQLSTATE 42000] (Error 50000) Error 9020, Severity 16, State 2,
The code is v simple. It just shrinks the db log files of all user dbs.
I also checked and confirm you that against all dbs the auto-shrink feature is disabled.
View 1 Replies
View Related
Apr 28, 2015
I have some questions :
1.Is it OK to shrink Master, model ? the transaction log for those databases are full .
2.Is it OK to set the Recovery model of Database MODEL as SIMPLE ? at the moment it is in FULL recovery model and the transaction log is Full too
View 1 Replies
View Related
Oct 12, 2015
how to shrink log files in SQL 2014 alwaysOn ?
View 1 Replies
View Related
Apr 30, 2015
I would like to know what happens if i shrink the database with truncate only option and do a full backup or transaction log backup ? are the full backup or transaction log backup valid? I know that the performance of the database is bad if i shrink the database. What happens to full backup or transaction log backups?
View 9 Replies
View Related
May 31, 2008
Hi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
View 1 Replies
View Related
Feb 22, 2007
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
View 3 Replies
View Related
