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?
Does anyone know who I can truncate the transaction log of a database that is on a linked server? TRUNCATE LOG [linked server].[dbo].[database] WITH TRUNCATE_ONLY does not work. Thanks, Kell
With a database size of almost 2 GB, I run the 'truncate table eventlog command' which completes successfully, but the database size only decreases by about 10 MB so stays too large - indeed the number of rows in the eventlog table is minimal, but the otehr tables in this database don't show such an amount of tables large enough to cause the size issue either. What could be the reason and how can I reduce it (possibly truncating another table but then which one, how could I determine which is too large and needs truncating?).
Please forgive the elementary nature of my question, but could someone please explain the differences between these two database backup types:
1. Log backup 2. Log backup no truncate
From what I understand and have read, the "no truncate" backup method keeps the entire transaction log indefinitely. Using the truncation method, the transaction log is either 1) compressed or 2) cleaned up so that any completed transactions are removed from the log. Which one of these is true?
And, for the big question: is it better to run a backup of the transaction log with truncation or not? Our current backup scheme is similar to the following:
Full backup every 24 hours transaction log backup every hour with no truncation
Should we insert a truncation backup somewhere in here? What is the danger of removing (or compressing) parts of the transaction log? Will this affect the restore process?
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 ??
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?
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!
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
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.
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.
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
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.
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?
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.
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.
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?
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
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??
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?
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.
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.
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.
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
I have a database SQL Server 2005 Express, that's using the Simple recovery model. There's quite a big transaction log for this database, how do I empty it when it's the Simple model? Besides, the log file hasn't been updated (acc. to the Windows file date) since yesterday while the MDF file has. Is this normal?
Is there a way to temporarily disable logging into the transaction log.
In our system, we perform purging of our database every night, where the purging consists of 2 steps:
1. For each table, insert the data, to be deleted, into a corresponding "purged" table, to remain there for one day only.
2. For each table, delete the unnecessary data (i.e. same data stored in purged tables in step 1)
During these 2 steps, the transaction log grows, and since we perform the transactional log back up, the back up at that time is huge. We are running a bit low on the hard disk space and I'd like to disable logging into the transaction log when these operations are performed.
I really don't care about being able to recover this data.
I thought that one option is to set the database to simple recovery, then perform the purging of the database, and then change back to full.
However, I think that trans log can grow even if recovery model is simple [although you won't be able to retrieve any changes].
So, is there a way to delete a portion of a table [or insert into it] so that no data is written to a transaction log (I know that we can use TRUNCATE if we need to remove whole table without logging)?
