September 16, 2011 at 11:13 am
On a development database, SQL Server 2008, how would I remove the data in the transaction log without backing it up. The database is in full recovery mode. The current Transaction Log file is currently larger that the amount of free disk space on the database server. The Process I will follow is:
1.Change database to Simple Recovery mode.
2.Remove the transaction log data????
3.Shrink the Transaction Log using DBCC SHRINKFILE.
4.Change the database back to full recovery mode.
5.Take a full backup of the database and transaction logs.
After I perform this process, I will implement regular backups of the database and transaction logs.
Thanks,
New SQL Server DBA
September 16, 2011 at 11:21 am
If it's a dev database, do you need to be able to restore it to any point in time? If not, just switch to simple recovery, run a checkpoint and then shrink the log to a sensible size (not 0)
Only if you need the ability to restore the DB to any time should you switch it back to full recovery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 16, 2011 at 1:42 pm
Thanks for the reply.
The only reason I am going to change it back to full recovery mode is to keep it like our production database. I know it is unnecessary, but I am also using it for learning SQL Server.
September 16, 2011 at 1:50 pm
I'd recommend not, unless you have the need to restore the dev database to point-in-time or you plan to mirror the dev database.
Install a dev instance on your local machine and use that for playing with SQL Server. That way when you break something (and I say when, not if), you won't stop others (the developers) from working while you figure out how to fix whatever you broke.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 16, 2011 at 2:02 pm
That was a point (Breaking the DB) I had not considered.
Thanks for the answers and advice.
September 17, 2011 at 3:00 am
hi
I think you already know that, If you are using simple Reco Model, You cannot Configure database for mirroring OR Logshipping also you can't take the transactional backup and also unable to recover database in point- in -time,
Play any thing on a side server not on a live server, as Gail said.
Ali
MCTS SQL Server2k8
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply