July 6, 2008 at 5:32 am
I use this code to shrink a log. When I execute this (R2008 being the database) in my stored procedure the text message it creates must interfere with the rest of the procedure.
After I execute it, is there a way I can clear the message it generates? Thanks.
Backup LOG R2008 WITH NO_LOG
DBCC SHRINKFILE ('R2008_log',1,TRUNCATEONLY)
July 6, 2008 at 6:27 am
Ok, I have to ask. Why are you truncating and shrinking your log file?
If you don't care about being able to restore to a point-in-time, set the database into simple recovery mode and you won't have to worry about the log at all. If you are concerned with been able to restore to a point-in-time, set up regular log backup to keep the size of the log under control.
Either way, the log file should not be shrunk as it can lead to file-level fragmentation and lots and lots of little virtual log files. Set the size of the log to however big it needs to be depending on the frequency of changes in your DB (and log backups if you stay in full recovery) and leave it alone.
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
July 6, 2008 at 9:20 pm
I figured it out, I need to add WITH NO_INFOMSGS.
Backup LOG R2008 WITH NO_LOG
DBCC SHRINKFILE ('R2008_log',1,TRUNCATEONLY) WITH NO_INFOMSGS
BUT your point is a good one. I had an issue running SQL Server 2000 on XP ... I was on service pack 3 xp and still could not get the properties of the database without enterprise manager crashing.
I reloaded service pack 4 for sql server ... and was able to get to the properties window. I set the database to the simple recovery model.
Thanks
July 7, 2008 at 12:08 am
swtrans (7/6/2008)
I reloaded service pack 4 for sql server ... and was able to get to the properties window. I set the database to the simple recovery model.Thanks
ALTER DATABASE <DB Name> SET RECOVERY SIMPLE
🙂 For next time.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply