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
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
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
Mar 13, 2008
I am trying to mirror a database and I keep getting the error Msg 1469 saying that "Database mirroring cannot be enabled because the database is an auto close database on one of the partners. Well.. the database properties is saying "false" under auto-close. What else could be wrong?
View 12 Replies
View Related
Jun 14, 2015
I am using SQl Server 2012 Database Mirroring with around 40 gb as mdf and 1 gb as ldf. Now my ldf size increased . How to reduce ldf size while mirror enabled with mirror server and witness server. Can shrink the ldf with mirror enables.
View 4 Replies
View Related
Jan 24, 2008
Is it possible to query linked servers without the Distributed Transaction Coordinator service enabled or allowing network access?
Is that ONLY for transactions? What if I just wanted to read the data and nothing else?
View 13 Replies
View Related
Oct 2, 2014
I have a scenario where a customer is going to be using Log Shipping to the DR site; however, we need to maintain the normal backup strategy on the current system. (i.e. Nightly Full, Every 6 Hour Differential and Hourly Transaction Log backup)I know how to setup Transaction Log Shipping and Fail-over to DR and backup but now the local backup strategy is going to be an issue. I use the [URL] .... maintenance solution currently.
Is it even possible to do regular backups locally keeping data integrity for your backup strategy with Transaction Log Shipping enabled?
View 2 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
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
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
Apr 23, 2008
When I try to create a subscription to my SQL Server Compact 3.5-database file, it gives an SqlCeException-message that says that the file is not enabled for replication. How do enable it?
My SQL Server Management Studio won´t connect to my compact server file right now, so that method is not an option for me right now.
View 1 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
Jan 7, 2008
My question is what is the best way to manage the log file size in a Mirrored database session?
Setting log file to autogrow by %
Setting log file to autogrow by mb
Setting log file to restrict growth by %
Setting log file to restrict growth by mb
The above aforementioned followed by a backup log "dbnamehere" with truncate_only, followed by a DBCC SHRINK file I'm assuming is not an option since the log file is constantly being applied to the mirror.
I ask this because I have a DB that can pass some 1,000,000 rows into just one of my tables a day and I need to make sure about this before I flip the switch.
Anyone's help inthis matter is much appreciated.
Thx,
View 4 Replies
View Related
Aug 28, 2007
How do I shrink a log file in a database that's been mirrored?
DBCC ShrinkFile(id) reports the size can drop considerably, but even after a CHECKPOINT, it doesn't shrink.
DBCC ShrinkFile('name') says it doesn't find the file name. The properties dialog for the database in SSMS doesn't list any files, even though sp_helpdb says they're there.
Must I disable mirroring, fix DBCC ShrinkSomething, then re-enable mirroring? Can I just pause mirroring?
View 5 Replies
View Related
Jun 12, 2007
We currently have a 10GB database that is functioning properly mirrored. The only issue we have is that the log files grow very quickly during the early morning hours when a large number of transactions hit our DB from scheduled jobs. We have transaction log backup and shrink job that runs every 3 hours to backup the log and shrink the logical file to 10GB. In most cases this will shrink the log back down to it's desired size of 10GB. However, on some mornings it takes more repetitions of the log backup/shrink job to return the log size back to "normal". During this these times when it does not shrink the DB effectively, I get a report of the job process by email that states "Cannot shrink log file 2 (e_Log) because all logical log files are in use." I run a DBCC OPENTRAN command, and there are no open transactions. Eventually, the log file will return to it's normal size of 10GB through the log file backup job. I have a few questions though.
1. Is this normal behavior during moderate database use?
2. Does mirroring have any effect on the SIZE of the log file?
3. Is it normal for the size of the data file to be smaller than the size of the log file on a heavily used database?
4. Does anyone have any suggestions to better maintain the log file size?
Thank you,
Greg
View 1 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
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
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
Mar 10, 2000
Can I keep a transaction log file on a different NT box in the same domain. The machine which is having the SQL server doesnt have space.
Thanks,
Sam G
View 1 Replies
View Related
Jul 15, 2004
Hie,
I have a transaction log files which grow to 24 Go but today there is only 100 mo uses, my backup is less than 100 mo but my files is 24 go, how can i reduce the size of my log file ?
View 1 Replies
View Related
Jul 16, 2004
:eek: I made a DTS package to import a bunch of data from text files into a db...and the log file keeps filling up.
This is a hosted server and I only have 35mb log file.
I'm really new to the DTS Designer...so what I set up in the DTS is
1. FTP 9 text files down into a temp folder
2. Delete all data in the 9 corresponding tables in the db.
3. import the data from the text files back into the db.
My client will need this DTS run daily.
What are my options for making sure the transaction log doesn't fill up everyday?
The transactions by the DTS (all the deletes and inserts) don't really need to be logged at all.
Is there some kind of maintenance plan I or the net admin could set up?
Is there a SQL command I could add to the DTS to make it not log the transactions that belong to this DTS?
Is there a better way to setup the DTS?
Thanks for any help!!!
Question 2:
Is there a "notify me if someone posts to this thread" feature anywhere on this site? I didn't see it. :eek:
View 2 Replies
View Related
Sep 24, 2006
Does the sql log file have to be in the same location as the DB or can the logfile be placed in another drive.
View 3 Replies
View Related
Feb 7, 2007
Hi,
My SQL2000 DB limit is 50GB. I can see I have used under 30 but Avaibale space is only about 11!? The Transaction Log file has taken
over 1/5 of my "precious" space.
Could you advise why TLF has grown so big?
Is it always relative to used space or somethig else as well?
What purpose does it serve?
Can I shrink it or how about bkp and Delete it?
Appreciate any help
Gezza
View 1 Replies
View Related
Jul 18, 2006
Hi All,I have a dillema that I created 2 transaction file before I started totake log back. Now I changed my recovery model from simple to full. DoI still need to keep my second transaction file? If I remove it wouldit be a problem for my system and log back up?If somebody responds me I really appreciate itRegards
View 5 Replies
View Related
Apr 30, 2007
I have a transaction log file that is 14 Gegabytes is this normal?
I had shinkred the database and files but is still the same.
I'm really new on SQL so I'm trying to undertand this.
Thanks for your help.
View 5 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
Feb 14, 2008
Hi all,
In my database the transaction log file size reached to 195 GB,
how can i make this to 2 mb? And also i want to set this to 2 mb only.
initially it was set to 185 GB.
please help soon.
Thanks!
View 3 Replies
View Related
Apr 17, 2002
How the size of transaction file is calculated. are there any standards.
I created my database with 2000 mb data filesize and 200 log file size.
now my datafile size has reach to 11000 mb but log file size is still 200.
Therefore when I take the online backup my logsize totally consumed and I have to cancel my backup.
Please reply ASAP.
regards
Sanjeev Lamba
View 1 Replies
View Related
Nov 29, 1999
I created a database and had its file size as automatic grow. Now the database file is of 17 MB and its transaction log file size is 230 MB. After checking transaction log file properties I came to that it is using 13 mb only and the rest of the 230 MB i.e 217 MB is free. I want that area in the transaction log to be freed and get the transaction file size to its actual size. Any help will be greatly appreciated.
Thank you in advance.
P.S: It is very urgent.
View 1 Replies
View Related
Apr 16, 2002
I have been getting some strange results while trying to reduce the size of a transaction log file. The background is the database was fully backed up last night. There has been no activity since the backup. A transaction backup was performed 30 minuted prior to my testing. Here is what I tried:
1. dbcc shrinkdatabase ('mydb',1)
Resulted in no change to the size of the log file (Still 110meg)
2. Right clicked on the database in Enterprise Manager and selected Shrink Database. I selected the log file to shrink and it shrunk to 29meg.
3. Tried dbcc shrinkdatabase ('mydb',1) again and no change.
4. Issued backup log mydb with truncate_only and
dbcc shrinkdatabase ('mydb',1) and the size shrunk to 2 meg.
Am I doing something wrong? I thought that after a full backup and backing up the log file, the out of date log records would be able to be shrunk out.
also, why does the Enterprise Manager shrink work differently from the dbcc command?
Any help would be greatly appreciated.
Thanks,
Ken Nicholson
View 1 Replies
View Related
Feb 9, 2005
Hello
We have a SQL Server 2000 database with 2 transaction log files.
The 2nd file was created when we were running out of disk space and the person creating it was not familiar with the dbcc shrink command.
I now want to get rid of the 2nd log file. I ran the following steps with no success:
DBCC SHRINKFILE ('Log_file', EMPTYFILE )
--Message: Cannot shrink log file 3 (log_file) because all logical log files are in use.
ALTER DATABASE db1 REMOVE FILE 'Log_file'
--Message: The file 'Log_file' cannot be removed because it is not empty.
There are no users or open transactions in the database. I have also tried sp_detach_db and sp_attach_single_file_db but that does not work either as the database attaches both the transaction logs back.
Please advise.
Thanks
Nina
View 2 Replies
View Related
Jan 30, 2008
Hi All,
Before posting this, I did search for backing up the transaction log file(.LDF).Currently transaction log file is 4 GB size and I want to reduce it to few MBs.Will the following procedure work?
1)Take the backup of transaction log file by executing the statement-
BACKUP LOG <name of DB> TO <name of db>backup
2)Run DBCC shrink file statement to reduce the log file size-
DBCC SHRINKFILE(<name of log file>,25)
This is as per procedure explained in http://support.microsoft.com/kb/272318
Will this free up 4GB physical space on drive? or anything else I need to do ?
Thanks
Prasanna
View 5 Replies
View Related
