New to SQL server. I have to truncate log on printing database as the server is almost out of space:(other stuff on server too) I know I must :
USE master GO ALTER DATABASE DB_Name SET RECOVERY SIMPLE WITH NO_WAIT GO DBCC SHRINKFILE (TransactionLogFileName, 1) GO ALTER DATABASE DB_Name SET RECOVERY FULL WITH NO_WAIT GO
My question is. Can I do it while database in use? It's used all the time being the printer database.
First thing is after changing recovery model from full to simple you loose point in time recovery. Is this database production database(which i feel it is)? if so you are not doing correct thing. If database fails at this point in time you would loose point in time recovery and would loose data are you ok with it.
If you switch from the full or bulk-logged recovery model to the simple recovery model, you break the backup log chain. Therefore, its strongly recommend that you back up the log immediately before switching, which allows you to recover the database up to that point. After switching, you need to take periodic data backups to protect your data and to truncate the inactive portion of the transaction lo
My question is. Can I do it while database in use? It's used all the time being the printer database.
Yes you can do it. But is it not possible for you to take transaction log backup on some other drive. Transaction log backups will truncate logs(if no long running transaction still requires it) and then you can shrink the log file. You must also note that shrinking logs also cause performance issue if you are doing it for just one time its ok.
Why did log file grew out of proportion have you thought about it ? I guess because you did not took transaction log backups. Please schedule proper transaction log backup for the database
if you don't require point in time recovery why not let database in simple recovery mode and you can still take full and differential backups.
Yes you can change the db recovery model while it's in use.
Although the Transaction Log file may be large is it full? have you run the DBCC SQLPERF(LOGSPACE) command to see what percentage of the file is in use. it could be that the backups have cleared the data out of the file and you could shrink the file to make space. This would still need you to find out why the log had grown so large.
