Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Change recovery model from full to simple SQL Server 2000

Posted on 2010-01-07
11
Medium Priority
?
305 Views
Last Modified: 2012-08-13
I need to change the recovery model from full to simple because of a new backup system. I need to reclaim the space taken up by the log files as well. Do I need to do this after hours? Are there any pitfalls going from full to simple? After I make the change will the log files be gone, or do I manually delete them? SQL server 2000.
0
Comment
Question by:estanton
  • 6
  • 3
  • 2
11 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26202514
> need to reclaim the space taken up by the log files as well
why dont you take regular backups of your log files ?
>Are there any pitfalls going from full to simple?
in your case i dont think it matters anyways, since you are not taking any log backups, there will be a chance that you loss lot of data in case of a disk failure, The simple recovery mode, eventhough logs most of the operations, reuses
the logspace
0
 

Author Comment

by:estanton
ID: 26202541
I was doing regular log backups every hour and full backups once a week to tape. We have move to replication and bare metal recovery. The entire machine gets backed up every hour now.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26202563
in that case your logs wont be really big unless you are doing some  bulk operations or running some big transactions . also make sure that the replication is in sync.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:estanton
ID: 26202583
Have you ever gong from full to simple? It seems uncommon. I am worried that I might break something. I will of course backup the database first. Will the logs automatically be truncated with I go to simple?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26202625
>Have you ever gong from full to simple?
I dont think this is possible without stopping the replication
0
 

Author Comment

by:estanton
ID: 26202639
Replication is stopped. I need to get the DB squared away before I start it back up.
0
 
LVL 10

Expert Comment

by:myrotarycar
ID: 26279372
::estanton::

This is a fairly simple task -whether you have replication ON or not. You can change your recovery model, on ANY database, at any time without disrupting much. Just about the only thing that will be angry is a maintenance plan including LOG backups or a flat out attempt to backup logs after you've gone to SIMPLE recovery model.

You can take whichever approach you feel more comfortable with -do it so from Enterprise Manager or from a Query Analyzer window.

Regarding the log files: DO NOT DELETE THEM! I don't know if you are clear or not on this based on your question but, log files will always be a part of your SQL DB instance. You MUST maintain at least one primary log file for each of your databases. When you switch to SIMPLE mode, you are basically allowing the SQL engine to get rid of transaction logs after they have been committed -whether you start an explicit transaction or you let the engine decide for you.

After you've switched to SIMPLE recovery, you won't be able to take further log backups -hence you won't be ale to unallocate space as you did before. Depending on how critical the application feeding off of the DB is, you could take the following approach:

1. Turn DB to SIMPLE recovery
2. Set your LOG files to auto-grow, and allow them to grow indefinitely in increments of 100MB -you will have to tweak this depending on the transaction volume you DB is subjected to.
3. Shrink your log files to their minimum size (1MB IIRC).

Let this run and monitor it during peak hours. Your log files surely have grown again by now from 1MB to XXX-size. This could be an indication of the typical size you may expect the log files to grow up to. You now have the option to cap the log file max-size to about 25% of this size, or simply leave them on auto-grow. You no longer have to worry about uncommitted transactions being shoved on your log files indefinitely. As soon as each transaction auto-commits, the DML-statements will be purged from the file.

If you need specific queries / commands, let us know.
0
 

Author Comment

by:estanton
ID: 26285718
I switched to simple, but I still have a huge log file. I did not do  log file backup and flush before I switched to simple. How do I reduce the size of the current log file.
0
 

Author Comment

by:estanton
ID: 26286443
I was thiking I could detach the database, rename the log file, then re-attach it. My hope would be that SQL would recreate the log file, then I could just delete the old one.
0
 
LVL 10

Accepted Solution

by:
myrotarycar earned 2000 total points
ID: 26311387
The following will flush the log file and release all unused space down to it's minimum.
USE [dbname]
GO
DBCC SHRINKFILE (N'db_log_logical_name' , 0)
GO

Open in new window

0
 

Author Comment

by:estanton
ID: 26314181
I detached the database, renamed the log file then re-attached the database. When I did that SQL asked me if I wanted it to re-create the log file. Mycotarycar's answer was what I was looking for, and assume that would work.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question