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
ADVERTISEMENT
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
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
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
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
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
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
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
Nov 23, 2006
How sql server will mirror big transaction ?
Example
inserting 10000000 rows in 1 transaction (columns big ,text data ...)
will it transfer data in multiple packets (size, location of temp file) to mirror ?
will it transfer data in 1 packet (size , location of temp file ) to mirror ?
Can you control transfer (size , location of temp file) ?
Thanks
Alex
View 1 Replies
View Related
Mar 17, 2008
hi,
i want to create a disaster recovery site, to which i can fail over (not automatically),
and also to have the option to return the database to a point in time?
for example,
if my principal server fails in 17:00,
i want to have the option to make the mirror server available for users from 17:00 (or at least close to that time),
and also to be able the return to the data from 16:00 (in the mirror site).
Is it possible, and what is the best way to do it?
Thanks.
View 1 Replies
View Related
Mar 7, 2008
Hi,
I'm trying to add a new transaction log file to a database with mirroring (high safety, automatic failover).
When I add the new transaction log file via SQL management studio, the mirroring status becomes suspended. When I try to restart mirroring, it attempts to re-synchronize and then goes back to the suspended state. Only after I delete the new transaction log file can I sucessfully restart mirroring.
Can you add a transaction log file to a mirrored database? Any ideas would be appreciated.
Also, I'm not sure if this matters, but I am trying this in my development environment and all 3 instances (principal, mirror, witness) are on the same server. It's a long shot, but I thought maybe both the mirror and principal are trying to create the file at the same location on disk.
Thanks,
Don
View 3 Replies
View Related
Nov 27, 2008
When Create Mirror Database Server, Where need to store the Transaction Log backup file?I took FULL Backup of my Primary Database, and I restored at my Mirror Server also. When I try to create a Mirror Database."The remote copy of database "<db_name>" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error:1412)".I am misplacing the Transaction Log backup file. Where I need to store that file?
View 10 Replies
View Related
May 3, 2008
Server A = primary SQL DBs (mirroring origination)
Server B = failover SQL DBs (mirroring destination)
For database mirroring a witness is required.
Can the witness live in another instance of SQL on server B?
View 7 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
Oct 12, 2015
Using SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....
View 5 Replies
View Related
Sep 15, 2015
I need to set up asyncronous data replication across two clustered instances of SQL 2012 across 2 Datacenters. Both the datacenters have a common domain however the vlans are different. There are only 3 small databases on the primary instance.
any issue in setting up mirroring in this case as vlans are different.
Operating system is Windows 2012.
View 2 Replies
View Related
Jul 14, 2007
Just want to know if anyone knows the actual syntax to do a Mirrored Backup
in SQL Server 2005 Ent Ed.
With Overwrite
with Verification
Disk to Disk
Also want to know if what their experience with Mirrored Backups.
Things to watch for etc.
Thanks in advance for you help on this.
Larry :-)
View 1 Replies
View Related
Nov 3, 2005
Hello:I have 2 MS SQL Servers 2000 Developer edition.Both have same settings and databases. I'd like to have them as mirroredservers - all transaction on one server must be performed on the otherautomatically. More than that I'd like to get possible if something happenedto one server, all transactions will be performed on another one.Please, give me a hint how to do that.Thanks,GB
View 6 Replies
View Related
Jan 2, 2008
Hi there!
I use different udls for a website which is partly asp pages and partly aspx pages.
Every database is mirrored, so each udl indicates a "failover partner". However we decided recently that one of the database would not be mirrored anymore. For this database we still use in the udl a "failover partner" to redirect the connection to the second database in case of a failover. For the asp pages the failover works fine even if the database is not mirrored, but it doesn't work for the aspx pages.
It would be great if anyone had an idea why the behaviour is not the same for the aspx pages, and if we can still use a "failover partner" in udls even if the database is not mirrored.. if not, what should we use to connect to another database?
Any help is welcome!
idel
View 3 Replies
View Related
