I have a 2000 DB which is log-shipping to another server. The log has grown huge and needs to be made smaller. Can I use DBCC SHRINKFILE without damaging the log-shipping? I know that truncating the log destroys log-shipping, but shrinking should logically not cause any harm. Do you know? Is there a better way to reduce the size of the log file without damaging log-shipping??
Thanks!
I am sure this has been asked multiple times before, but I have a DB (SQL 2K) that is involved in transactional replication. The log periodically grows to a seriously big size. How can I shrink it without damaging replication? I have opened SSIS (SQL 2005 is the subscriber) and tried to shrink the file, but to no avail. It reckons I could shrink it to 0MB, but I feel this would throw out replication.
Any help appreciated.
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.
hi,
how can I trunc an shring a db transaction log?
thanks for reply....
Hi
I could not able to find Forums in regards to 'Log Shipping' thats why posting this question in here. Appriciate if someone can provide me answers depends on their experience.
Can we switch database recovery model when log shipping is turned on ?
We want to switch from Full Recovery to Bulk Logged Recovery to make sure Bulk Insert operations during the after hours load process will have some performance gain.
Is there any possibility of loosing data ?
Thanks
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.
Hi,
I 'm sure I am missing something obvious, hopefully someone could point it out. After a failover log shipping, I want to fail back to my inital Primary server database; however, my database is marked as loading. How can I mark it as normal?
I did the failover as follow:
I did a failover log shipping from the 2 server Sv1 (Primary) and Sv2 (Secondary) by doing the following
1) Stop the primary database by using sp_change_primary_role (Sv1)
2) Change the 2nd server to primary server by running sp_change_secondary_role (Sv2)
3) Change the monitor role by running sp-change_monitor_role (Sv2)
4) Resolve the log ins - (Sv2)
5) Now I want to fail back - I copy the TRN files to Sv1 - use SQL Ent to restore the database at point in time. The task is done; however, the database is still mark as loading. I could not use sp_dboption.
I appreciate any suggestion.
Thanks in advance
I wnat to have a log file with all the updates and inserts made to the DB.
I understand I have to do a trans-log,how?
in order to make a query a transaction do I only have to declare trans...commit ??
tanx,
eyal
I have set up a simple trans rep from a server in the office to the web. both servers are NT4sp6aSQL 7.0. Tables only.
The publication and distribution db are on the server at the office, and there is a full time connection through the firewall.
The initialization and first replication works perfectly, but after that, there is a message from the snapshot agent that "no subscriptions needed initialization", the logreader says thare are no replicated transactions and the Distribution agent says there are no replicated transactions. What am I missing?
Thanks in advance
Does anyone know how to move the transaction log(s) of a LIVE database to a new location. I must move the log of a database to a new mirrored drive without any disruption to users. I cannot take the database offline or use the sp_detach_db stored procedure. Your inputs are much appreciated!
View 1 Replies View RelatedSQL7:
I have a 300MB db, and a transaction log near 1.3GB. Upon notification, I backed up the db log with truncate_only - no luck getting it smaller. Later, tried backup with no_log ( assuming the o/s was full - no diff)
I tried shinkfile (logfile,truncateonly) and no luck.
I tried dbcc opentrans to see if any pending trans. The db looks fine with dbcc checkdb. I managed to free up a mere 50MB. I checked the permissions on the db, and added backup db, and backup log in the db permissions for the user logged in (also tried this with sa)
I am unable to free up the space to the os. Can I somehow rid the log file and start off with a fresh log file? I need this space. As a patch I moved the log to a larger filesystem as a temporarily fix.
start/stop SQL- nothing? reboot -nothing? I played with waiting game. This log does not want to release space. The log grew from data loads.
Question1: Suggestions how to truncate this log? The contents are not really impt, but the space is.
Question2: Can I add another logfile, then use EMPTYFILE to transfer the contents to the newly added log file, then REMOVE The original logfile? In theory does this make sense?
Thanks,
Jason
Hello, During an intantanée arrest of replication of data base sql server 7 sp2 (nt4),
I lose keys on the subscriber, how don't have it? Thank you in advance easter
pascal
:confused:
Here's my situation (SQL2K)
We have a testing database we're using to convert large amounts of data from 1 system to another. We might process 5-6 million records, but don't care about being able to recover point-in-time.
I set recovery mode to simple, do a full backup every night. I keep getting large transaction logs. I manually run Shrink Database when I realize the logs are big
What can I do to prevent the logs from getting big in the first place ?? Can I prevent logging from happening ?
I keep reading various books and BOL, but I guess I don't quite "get it" yet ......
Any plain spoken, detailed suggestions would be very appreciated .... thanks in advance.
My database backs up just fine, but the transaction logs
are not. I created a maintenance plan for the database.
Any suggestions ?
Agent Job for transaction log backup
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0A83F1BC-EEAA-
4BDF-9374-E7FF5C1215F8 -
Rpt "d:sqldataMSSQL$PRODUCTIONLOGzeon_live_commerce DB
Maintenance Plan16.txt" -DelTxtRpt 1WEEKS -VrfyBackup -
BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 2DAYS -
CrBkSubDir -BkExt "TRN"'
The error log file for the transaction log backups is...
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'ZCHQ_SQLPRODUCTION' as 'ZCISQLSERVICE'
(trusted)
Starting maintenance plan 'zeon_live_commerce DB
Maintenance Plan1' on 6/12/2002 8:15:03 AM
Backup can not be performed on
database 'zeon_live_commerce'. This sub task is ignored.
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'zeon_live_commerce DB Maintenance
Plan1' on 6/12/2002 8:15:03 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
If you are set up for AutoCommit why would you or should you set a explicit transaction? I have noticed that in some called stored procudures from a "container" stored procedure. (Hope I got that right) that in the called stored procedure a Begin tran is used. Can anyone help with the why and what fors? It seems to me that you want to let SQL Server handle this becuase of the danger of leaving out a Commit or Rollback? But thats me. I may be very wrong? Thanks.
Tom
Hi,
I was just wondering, if I have a DB for log shipping, and change the recovery model to bulk-insert before I do a dbreindex, does the log still grow as big as the full recovery model? as in when the DB in bulk-insert model, will dbreindex still writes to the log? What i'm trying to do is try to make the log files smaller for log ship when i'm doing the db reindexing job.
Thanks.
I’m really scratching my head with this Transact-SQL, say you wanted to cycle through a set of rows, then perform an operation on each row, so in VB/DAO it might look like this:
Dim rsTables As Recordset, rsIndex As Recordset
Set rsTables = dbSource.OpenRecordset("SELECT * FROM INFORMATION_SCHEMA.tables")
Do While rsTables.EOF
Set rsIndex = dbSource.OpenRecordset("Select * From SysIndexes Where Name = '" & rsTables!Table_Name & "'")
DoSomethingToIndex rsIndex!Name
rsTables.MoveNext
Loop
How can you do this cycling with Trans-SQL?
Hi all,
Just wondering if the virtual filesize of the transaction log can be changed to suite us.
run dbcc loginfo(sagent_dev) display the followings:
FileId FileSize StartOffset FSeqNo Status Parity
213041664 8192 2110128
213041664 130498561960128
213041664 26091520197064
2524288039133184201064
2524288065404928204064
2524288070647808203064
25242880102105088210064
25242880107347968000
25242880112590848214264
25242880117833728213064
25242880123076608212064
Note that the virtual filesize can comprise of different configuration. In other database I can specify this logical size to whatever I like.
cheers
dave
We would like to replicate from a SQL 7 DB some data onto a SQL 2000 Server running SQL 2000. We plan to take some data off the SQL 7 DB's and create a Data Warehouse on the SQL 2000 Box.
Question: Will the data replicate successfully from the SQL 7 box onto the SQL 2000 box?
-thanks
-tom
Hi,
I am trying to disable transactional replication, but am having some problems. I used the wizard, however, it has taken 7 hours so far and is still not done. SQL Server (7.0) is showing the connection as runnable but it seems as if nothing is being removed. Am I missing something? Should I have done something else before running the wizard? I can't even kill the SPID.
Can anyone help or offer some advice?
Thanks in advance...
I have a trans log backup that runs every 15 minutes on san, works fine until I do a backup after a large load. I get the below error message. Anyone had this before?
Executed as user: DPSCSDOMsqlexec. The file on device 'n:sqllogsmdentallgdmp' is not a valid Microsoft Tape Format backup set. [SQLSTATE 42000] (Error 3242) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step fail
We're running Lawson software on our SQL 2000 box. We're using Veritas
Backup Exec to back up the databases to tape. I'm also using a Maint
Plan for an extra backup (kinda redundant, but I need the practice at
all this). I just added transaction logs to the maint plan (so I
thought) but I only see a log for 1 of the 3 databases.
Also, my trans log file is 11 gig. I thought backing up the trans logs
would get the trans to delete afterwards (databases are recovery
model=full). Can anyone point a newbie in the right direction.
Fortunately it's not in production yet, and we haven't had any disasters
(yet).
No I did not write this below, this is from a vendor, I used profiler and I believe their SP is causing a blocking problem on their vendor supplied DB. It thought at the least always have a begin end or a begin trans commit trans. ANy quick opinions greatly appreciated
create procedure write_planned_service_rec
@p1 varchar(20),@p2 varchar(20),@p3 varchar(20),@p4 varchar(20),@p5 varchar(20),
@p6 varchar(20),@p7 varchar(20),@p8 varchar(20),@p9 varchar(20),
@p10 varchar(20),@p11 varchar(20),@p12 varchar(20),@p13 varchar(20),@p14
varchar(20),
@p15 varchar(20),@p16 varchar(20),@p17 varchar(20),@p18 varchar(20),@p19
varchar(20),
@p20 varchar(20)
AS
IF @p20 = 'P'
update patient set date_insurance_updated = getdate() where patient_id =
@p1 and practice_id = @p13
Is there anyway in SQL to backup the Database and Transaction log without truncte the Transaction log? :o. I would like to keep all or as much of the Transaction log info as possable. I know the Trans log will get large but HD space is not a problem. I use the transaction log to report field changes usung Log Explorer.
Thanks
Guys,
Should the transaction log of the system databases be backed up and if so, how often should this be done, generally?
Currently, we are backing up the system databases daily. The transaction log of the system databases, however, is not backed up.
Recently, I was wondering what exactly could happen, which would imply that we need to back up the transaction log of the system databases.
As far as I understand, 'msdb' contains things like jobs, dts. As such, if no jobs/dts are added in the middle of the day, there is no need to back up the trans log of this db - there could even be no reason, in such case, to back this db up at all!
'master' db would change if we add/modify objects/properties and so on ... so unless many changes are made, no need for backing up its trans log.
Having said that, could anyone enlighten me as to whether it is necessary to back up the transaction log of the system dbs and/or what it depends on and/or when it is suggested to do so?
Thanks you
Writing SQL scripts with insert trans. If one of the inserts failed rollback all previous inserts. Here what I come up with but I don't think it will work.
SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
Insert 1
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
[Code] ....
May I know any reason why my Transaction Log Backup job keeps failing while the particular database set to "Standby /Read-Only"?
Kindly help. Thank You.
Regards,
Jimmy Liew
Hi!
We have a vendor's case-sensitive db which we have to Trans Rep our case-insensitive data into. Can't do it directly as it violates the Rep rule against different collations. Is there some trick or work-around we can use to implement this?
Thanks,
Michael
Hi Every one,
According to our stratergy we are taking trans log backup of every user database everuy two hours.things seem to work great and with success at the day time and at night once at 12.03 and 2.03 they fail and when i checked the error log i found that there is only one database for which its failing and it gives the following error.
Database ors: Transaction Log Backup...
Destination: [D:BackupTranLogorsors_tlog_200108280203.TRN]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4213: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot allow BACKUP LOG because file 'ors_Data' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.
We had a option called select into/bulccopy set which i disabled once this failure has occured but still the problem persists....there are no other options set...i am out of resources i donno what to do..could you suggest me something and how to solve this problem its happening in prod.
Thanks for your help.
Kavitha
Hi Every one,
According to our stratergy we are taking trans log backup of every user database everuy two hours.things seem to work great and with success at the day time and at night once at 12.03 and 2.03 they fail and when i checked the error log i found that there is only one database for which its failing and it gives the following error.
Database ors: Transaction Log Backup...
Destination: [D:BackupTranLogorsors_tlog_200108280203.TRN]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4213: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot allow BACKUP LOG because file 'ors_Data' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.
We had a option called select into/bulccopy set which i disabled once this failure has occured but still the problem persists....there are no other options set...i am out of resources i donno what to do..could you suggest me something and how to solve this problem its happening in prod.
As Said by someone i took a full backup...tooooo it does not solve the issue.
Thanks for your help.
Kavitha
This is a silly question but I am not sure of the answer.
Each day I do a complete DB backup and houly I backup the Translog file with the append mode.
Question Is the Transaction log truncated each time I do a Transaction Log Backup?
Here's what I'm trying to do (Maybe there's a better way)
I'm using Transaction Replication and a pull subscription to get all the transactions from the ORDERS table within the last year from Server_A to Server_B. I filter the rows "WHERE (ORDR_DATE > (GETDATE()) - 365)"
That works fine, BUT, the old rows in the subscriber's ORDERS table do not get deleted, so I have all the records for the last year, plus the older records that I don't want any more. Do I need another step some where that does a "Delete WHERE (ORDR_DATE < (GETDATE()) - 365)" But then I have to maintain my date logic in more than 1 place if I ever want to change it.
Any suggestions, thoughts, improvements will be appreciated .... Thanks a lot
What does this error message mean ?
The process could not bulk copy out of table '[dbo].[syncobj_0x3732313945323046]'.
I ran transaction replication successfully last night. Then it was scheduled to run again in the morning, but failed .... any ideas ?
SQL2K SP2