Recovery Model Of Subscription Database
Hi:
I am having lot of log problems with Subscription databases. Currently all my subscription databases are on Full recovery mode. I am thinking to change them to simple because I don't I will be doing point in time recovery of them.
Do the subcription databases have to be on Full mode? Can I change them to simple to keep my log small and then I do not have to backups of my logs also? Please let me know.
Thanks
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Model Database Recovery Planning
I cannot think of any reason, in our environment, why I would recover the model database. Change framework has all databases coming from DEV & QA before landing on PROD. We have never used the model database as framework of new databases either. So, if I discontinued backup of the database, what is my recovery method if it become corrupt? Since mine is not used, can I simply copy it from another server?
View Replies !
View Related
Can We Pause Log Shipping, Bring Primary Db To Simple Recovery Model And Then Back To Full R Model?
We have the following scenario, We have our Production server having database on which Few DTS packages execute every night. Most of them have Bulk Insert stored procedures running. SO we have to set Recovery Model of the database to simple for that period of time, otherwise it will blow up our logs. Is there any way we can set up log shipping between our production and standby server, but pause it for some time, set recovery model of primary db to simple, execute DTS Bulk Insert Jobs, Bring it Back to Full recovery Model AND finally bring back Log SHipping. It it possible, if yes how can we achieve this. If not what could be another DR solution in this scenario. Thanks Much Tejinder
View Replies !
View Related
Best Recovery Model
What would be the best Recovery Model for: a database which is 4 gig in size and imports via MSAccess queries and also stored procedures approximately 400,000 meg of data each month (and some other update queries are run against it) and it is also queried off of for totals on weekly basis? The problem is that the SQL Server box only has 512 meg of memory and the tranlog on this database grows tremendously each import and when update queries are run against it. This tends to slow things down a bit on our other databases. We are getting a new SQL Server box but until then, what would be the best recovery model? I currently have it as Bulk-Logged and allow the tranlog to grow by 10% (with a base of 250 meg). The tranlog grows to up to 5-10 gig and in order to shrink it, I have to change the recovery model to Simple, and then back to Bulk-Logged in order to shrink it (I've tried all the dbcc shrinkdatabase, dbcc shrinkfile, dbcc showcontig, and dbcc checkdb commands as well as BACKUP LOG dbName WITH TRUNCACTE_ONLY and nothing will shrink it unless I change the recovery model to simple.)
View Replies !
View Related
DTS - Recovery Model
SQL Server 2000 SP3. Prior to SP3 the recovery model was switched to simple during transfer (Copy object task) and changed back to the previouis setting after DTS was complete. Nice thing because performance was increased and T-Log was keep small. Now I assume that the recovery model is switched to bulk-logged causing the T-Log to explode, to be onest not in all my databases. 1.Is my interpretation regarding recovery model correct? 2.Does anybody knows the reason of this change? Any suggestion is really appreciate. Thank you very much - kind regards.
View Replies !
View Related
Recovery Model
Hello, I'm new to MS SQL Server I want to know which recovery model is good, Full or Bulk Logged as I'm doing full backup at 11:00 PM and diffential Backup at 12:30 PM and from 8:00 AM to 6:00 PM 15 min log backups. Please guide me which recovery model should I choose. Thanks Lara
View Replies !
View Related
Recovery Model
I have created a database in SQL 2000, and I am trying to change the Recovery option to FUll,so I can backup the logs. But when I select Full under the Recovery Option the OK button is grayed out. This is only happing on 2 database. Is this a bug in SQL 2000? Also,I try to do this through Alter Database but I get syntax error. Here the syntax I use: Alter database archive <recovery option> ::=Recovery{Full} Thank You, John
View Replies !
View Related
Change Recovery Model
A quick question about changing recovery model using sql statement. ALTER DATABASE test set recovery_option = RECOVERY { BULK_LOGGED } Error message: [Microsoft][ODBC SQL Server Driver]Syntax error or access violation Immediate help needed !!
View Replies !
View Related
Recovery Model To Simple
We are using a .bat script to restore several client dbs onto our sql server 2000 db. We want to set the client dbs from full recovery to simple. What command should I use in the .bat file to make this change? .bat file == :: Second, restore data from SQL Server backup file to SQL server... isql -E -S ao3ao3 -Q "RESTORE DATABASE CBSN FROM DISK = 'D:MARS_SYSDATAUPDATESCBSNCBSN.BAK' WITH MOVE 'MEDISUN_BCNV_Data' TO 'D:SQLDATACBSN_data.mdf', MOVE 'MEDISUN_BCNV_Log' TO 'D:SQLDATACBSN_log.ldf',REPLACE;"
View Replies !
View Related
Simple Recovery Model
We have a fairly large database that we use to store mom alerts and it stopped alerting as it's transaction log became full. I suggested to the owner of the database to set the simple recovery model so the log could automatically be truncated. However, it appears that the database is frequently reaching it's limit (of 3gb) and I'm having to set the limit even higher on a daily basis. Can anyone tell me why this is occuring? I understood that when the log file reaches 70% it should automatically shrink? Kind Regards Mike
View Replies !
View Related
Recovery Model Problem; Db Properities
Hello,I've follow problem - thing to consider.SQLServer 200 sp3a, ms win 2003 serverdb simple recoveryThere is a production database, wich is around 20gb big. Db is backedup each day completely, but it takes up to 30 minutes.Because there is a simple recovery model, there is no transaction logbackup (it fails anyway), and we do not have up-to-point recovery.I'm considering to switch to full recovery model, but ....The problem is, I do not want to affect performance (when the backup isrunning, database is hardly avalible).So my question will be: does the full recovery model, will be betterfor db performance (for acces and blocking db; means, does it will takeshorter?)Strategy will be (I hope ok) to back up during the week onlytransaction log (incremental), and once at the weekend, full databasebackup.Generaly, which one is better for performance?Which strategy will be the best, to keep performance at high level, butalso have the possibility to restore data (in case of emergency) fromthe newest possible backup.Thanks for helpMatik
View Replies !
View Related
Full Recovery Model - Log Doesn't Grow?
Hi all,I have a SQL Server 2000 database that is using the Full recoverymodel. The database is purely receiving inserts (and plenty of them)with maybe some view/table creation for reporting.In this state I would expect the log to grow ad infinitum but it getsto about 32% used and then empties.The log is not being backed up at all so am I missing something else?CheersDee
View Replies !
View Related
Restore From Snapshot After Changing Recovery Model
Given the follwoing scenario: You create a snapshot of a database with full recovery model, change it's recovery model to simple, then perform several updates/modifications on the database, before you finally (due to an error) restore the database from the snapshot. Will the log chain be broken or not? The resteore sets the recovery model back to full, but I'm still a bit "curious" about the transaction logs.
View Replies !
View Related
Recovery Model To Be Used For A Data Flow Task
Hello there I have a SSIS 2005 DTSX Package which has several Data flow tasks which directly dump data between tables from 2 different databases. Suppose if my destination database is named, DestinationDB, what recovery model should I select for this database so that my ldf file size does not explode? I want to keep the ldf file as small in size as possible. Currently I have used Simple Recovery model, and the ldf file size goes to around 80 GB. (Inserting around 200 million rows) Will Bulk Logged model be a better option? Also what does a SSIS 2005 Data flow task use internally? (Insert operation or some sort of Bulk Insert between DBs) Please help.
View Replies !
View Related
Log Truncation In The Simple Recovery Model - Can You Help My Understanding Of It Please
On SQL 2000 or SQL20005 will a database's log file automatically be truncated if the database is on simple recovery model? The reason I ask is that we have a database (simple recovery) that keeps growing its logfile each weekend which causes disc space problems. I am kinda new to SS but from the reading in BoL I've done was under the impression that for simple recovery model log records are only needed until the transaction has been written to disc and committed, and that SS will handle truncating obsolete records from the log where necessary. I'm doing DBCC SQLPERF(logspace) which shows this first thing on a Monday morning: Database Name Log Size (MB) Log Space Used (%) -------------- --------------- --------------------- myDB 4841.93 99.19465 Note the size of the log file - the data file is only 700MB! Issuing a DBCC OPENTRAN doesn't show any open transactions, and a CHECKPOINT doesn't do anything to reduce the log space used (which if there were dirty records in the log still not written to disc this ought to do shouldn't it?). The database is only written to as a replication subscriber. Any suggestions what would be causing the log file to fill up? At the moment I'm resorting to BACKUP LOG myDB WITH TRUNCATE_ONLY and considering scheduling this as an hourly job over the weekend - any reasons why this could be a bad idea? Many thanks, Moff
View Replies !
View Related
Recovery Model Indicator - Which System Table?
Hi I hope you can help as I am really scratching my head on this one. I am pulling together an assessment of the Disaster Recovery readiness for an organisation I am working at. Part of the assessment I am doing is the recovery model of each of the databases. I have scripts that are already pulling lots of data from 40+ servers and 500+ databases. However, I cannot seem to find anywhere within the MASTER or MSDB or the database itself where the Recovery Model flag is held. Obviously I can right click on the database and click properties and it is there, but I need to automate this task (as it will probably be a weekly assessment). I have checked sysdatabases and almost every other table, but nothing obvious as to where this flag is. Any ideas?
View Replies !
View Related
Backup Of Ldf File In Simple Recovery Model
Hello, I have a question regarding the backup for the database in Simple Recovery Model. In this Model, I know we can restore only to the last full backup or can use differential Backup, if implemented as a part of backup. But my point of confusion is about the backup of '.ldf' file, should those file should be backed up in the Maintenance Plan, if yes does it help in reducing the size of Log file? Do we need the backup of '.ldf' in phase of Restoring? As I mention my database has Simple Recovery Model, but the size of log file is around 20GB, Could not understand why as in this Model, normally it automatically truncate the Log file? Help me to clear my these doubts, thanks,
View Replies !
View Related
The Statement BACKUP LOG Is Not Allowed While The Recovery Model
Hi All, SQL newbie here. I just looked at my Job Activity monitor and found that my Transaction log backups are failing. I looked at the error and it read as follows: Executing the query "BACKUP LOG [OperationsManager] TO DISK = N'D:\SQL\MSSQL.1\MSSQL\Backup\OperationsManager\OperationsManager_backup_200805061000.trn' WITH RETAINDAYS = 1, NOFORMAT, NOINIT, NAME = N'OperationsManager_backup_20080506100001', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. I read some of the posts regarding this error, but I am not sure how to do the steps. How do I change the recovery model? I am new to this so I have no clue how to do this. Any help would be greatly appreciated. Thank you.
View Replies !
View Related
Switch Simple To Full Recovery Model
I have convert all databases to Full from Simple Recovery model. As per documentation, it looks like simple. Based on your experiences , do you think of any problem may come while doing this ? Any impact on application performance after this ? Is this work perferened to do when no body using system ? Thanks
View Replies !
View Related
Log Shipping - Switching Recovery Model In Log Shipping
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
View Replies !
View Related
&&"Simple&&" Or &&"Bulk Logged&&" Recovery Model For Fastest Import ?
We have a sql 2005 x64 database (datawarehouse related), essentially a work area for us, that we truncate and re-populate via BCP weekly. (We don't backup the database at all) . From the perspective of data-import speed what is the best recovery model to use: Bulk-Logged or Simple? (I have read sql 2005 BOL and don't find it partcularly clear on this point.) Barkingdog P.S. Anyone know of an article listing "best practices" for high-speed data import?
View Replies !
View Related
Database Recovery
Hi,I'm no where close to a SQL Server expert but we're using it as a back endto another application.I lost a PC that had MS SQL Server 2000. I was able to recover the .MDF and..LDF files but nothing else. How can I restore these files to another SQLServer installation on another PC. When I tried to attach them they are notrecognized as valid files (I get the red X instead of the green CHECK).Is this a basic security function that I'm not going to get around or shouldit be possible? Do the .mdf and .ldf files contain specific info about whatPC they were created on?Are there third party apps that might be able to at least extract thetables?Thanks,WH
View Replies !
View Related
Database Recovery
hai! i have a database which crashed recently(26th sep 2007).my last backup is on 14 sep 2007.i have restored database after two days.Now i have a database (old) with data upto 14sep 2007 and from 26sep to till date.(data from 14sep to 26sep missing).I restored database on 28th with a new database name which has data upto 26th sep 2007.how to attach these two databases to a single database to have data from beginning to tilldate. please help in this regard.
View Replies !
View Related
Database Recovery
Hi, I made an UPDATE query without specifying any criteria so I had more than 1500 records have been changed and I cannot undo the UPDATE. Is there any way I can recover the data UPDATE from the Transactions Log. I did not back up the database lately so what is the appropriate solutions for this? Any thoughts… :S
View Replies !
View Related
Database Recovery
Hi, Is there a way to Revover a Sql Server 2000 database in the absence of the backup file(the backup file got overwritten). Does Sql Server do any automatic Transaction logging? Thanks for any input
View Replies !
View Related
Recovery Of Database
Running SQL version 7 on "Terminal" Server I have a database marked in Load Receovery and we are unable to get to it. We tried MS SQL document on how to but we are still unable to recover it!
View Replies !
View Related
Database Recovery..
Hai friends.... SQL Server 2005 Database, So I want to know about Database Backup and Pointing Recovery concepts..so can u please help me and any send me related documents... --- Thanks, Nageswar.V New Horizons Cybersoft Ltd +919848854114 : nageswar@nhclindia.com
View Replies !
View Related
Database In Recovery
Hello all, Is there a way to check the status of a database in recovery. Like how far along it may be. If not is there a way to stop a database in recovery and just drop it. Thanks in advance, Mike
View Replies !
View Related
Help!! Database Always In Recovery...
Hi all, I had to change the path of .mdf and .ldf files, so I decided to: 1) Take offline the database 2) run the quey ALTER DATABASE... MODIFY to change the path 3) Bring online the database. The last step hung up (with no errors) and left the database In Recovery. When I tried to stop and restart sql server other databases changed their status In Recovery... Here is a dump of Errorlog files 2007-08-26 18:13:29.28 spid24s Starting up database 'DbOrdini'. ... ... 2007-08-26 18:13:30.09 spid24s * BEGIN STACK DUMP: 2007-08-26 18:13:30.09 spid24s * 08/26/07 18:13:30 spid 24 2007-08-26 18:13:30.09 spid24s * 2007-08-26 18:13:30.09 spid24s * Location: "logmgr.cpp":5334 2007-08-26 18:13:30.09 spid24s * Expression: !(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo) 2007-08-26 18:13:30.09 spid24s * SPID: 24 2007-08-26 18:13:30.09 spid24s * Process ID: 1380 ..... ..... 2007-08-26 18:13:30.40 spid24s Error: 17066, Severity: 16, State: 1. 2007-08-26 18:13:30.40 spid24s SQL Server Assertion: File: <"logmgr.cpp">, line=5334 Failed Assertion = '!(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted. 2007-08-26 18:13:30.40 spid24s Error: 3624, Severity: 20, State: 1. 2007-08-26 18:13:30.40 spid24s A system assertion check has failed. Check the SQL Server error log for details Could it be dangerous trying to kill this process ? If not, what is the best way do to it ? From Sql Server Activity Monitor (spid 24) or from Task Manager ? Thanks in advance
View Replies !
View Related
Database In Recovery
What does it actually mean when the database is in recovery? My database has been in recovery overnight. What is normally going on in this situation. Should I wait or take more drastic action?
View Replies !
View Related
|
TRACKER
