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

Mirroring :: How To Truncate / Shrink Transaction Log If Mirror Is Enabled

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

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

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

View 1 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

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

SQL 2012 :: High Availability Group Transaction Log Shrink

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

Switched To Simple Recovery Model When Will The Transaction Log Shrink?

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

SQL Server 2008 :: Unable To Shrink Transaction Log - Could Not Locate Files

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

Transaction Log Does Not Truncate And Shrink Using A SQL Server 2005 Maintenance Plan

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

Transact SQL :: Cannot Perform Shrink File Operation Inside User Transaction

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

Mirroring Big Transaction

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

Mirroring And Rolling Back Transaction Log

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

Can I Add A Transaction Log File While Mirroring Is Enabled?

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

Mirroring :: Store Transaction Log File / When Create Mirror Database

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

Database Mirroring | Can Witness Live On Mirroring Server?

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

How To Shrink A Tranlog Which Won't Shrink

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

Mirroring :: Database Mirroring Changes In Application

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

Mirroring :: Setting Up Mirroring With Different Vlans?

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

Mirrored Backups

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

Mirrored Sql Servers

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

Failover When The Dbb Is Not Mirrored

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







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