Changing recovery model to simple 2

[shaminda]

I have a growing transaction log. To change the recovery model to simple do I need exclusive access to the database? Can I change it while users are accessing the database?

Environment: Windows 2003 and SQL Server 2000

 

[mrdenny]

You should be able to change the recovery mode while users are using the database.

The query may take a minute to run while it waits to take a lock on the database.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLBill]

Why are you changing to SIMPLE mode? Just because the transaction log is growing isn't really a good enough reason. Once you change to SIMPLE mode, you no longer have the ability to restore to a point-in-time.

If you are doing reindexing, the transaction log will grow large regardless of what mode you are in. Why? Consider the reindex as a transaction, until the reindex is done (committed), it needs to be able to rollback the transaction.

Are you set for FULL Recovery mode and doing frequent transaction log backups or have you just been doing full backups?

-SQLBill

Posting advice: FAQ481-4875

 

[shaminda]

The log file grew upto 35GB and database crashed. We disconnected the database and tried to restore a backup from Sunday. But the transaction file of the backup was 35GB and we couldn’t restore it because it was too big. The tables in the database contain data from the AS400. Periodically we copy data from the AS400 to the SQL Server. What matters is the structure of the database not the data it self. If we loose the data we have jobs that can copy the data pretty fast. But yesterday we lost database itself and we couldn’t recover it.

 

[PugTheBlack]

Full backup of the database

Backup LOG Databasename WITH TRUNCATE_ONLY

DBCC SHRINKFILE (N'Database_Log', wanted size)

Full backup of the database

Been using this way of doing things when things have been getting a little hairy.. Guess it is a little late for you if the database has already crashed though. Not a big fan of putting the database in simple mode myself..

-Pug