August 4, 2009 at 8:49 am
Right now I have a stored prodecure in the master database as follows:
ALTER PROCEDURE [dbo].[SP_TRUNCATE_LOG] as--This is a non-recursive preorder traversal.
SET NOCOUNT ON
DECLARE @DB_NAME VARCHAR(200)
DECLARE @DB_LOG VARCHAR(200)
DECLARE DB_CUR CURSOR FOR
SELECT [NAME] FROM dbo.sysdatabases
WHERE dbid > 4
AND STATUS <> 66056 -- OFFLINE DATABASES
ORDER BY dbid
OPEN DB_CUR
FETCH NEXT FROM DB_CUR INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DB_LOG = @DB_NAME + '_log'
BACKUP LOG @DB_NAME WITH truncate_only
DBCC SHRINKFILE (@DB_LOG , 0, TRUNCATEONLY)
FETCH NEXT FROM DB_CUR INTO @DB_NAME
END -- DB_CUR Cursor
CLOSE DB_CUR;
DEALLOCATE DB_CUR;
This procedure fails when attempting to run DBCC Shrink command saying it cannot find file.
If I add the USE[@DB_NAME] before the DBCC command, I get an error saying you cannot use in a stored procedure.
How can I shrink the log files of all the db's from a stored prodecure?
August 4, 2009 at 9:31 am
You'd need to build a dynamic SQL statement for each database and use that.
But don't do it!
Automatically truncating and shriking log files is a really, really bad idea. It just results in them having to grow again, and that results in massive file fragmentation, and kills performance.
If you have log files that you don't want to keep data in, set the database to Simple recovery. Don't schedule a truncate proc. Keep in mind that doing either can result in data loss, since you won't be able to do point-in-time recovery.
Basically, if you care about performance and about preventing data loss, don't use a proc like this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 4, 2009 at 10:12 am
Here are some resources about why you should not shrink:
http://www.sqlskills.com/BLOGS/PAUL/post.aspx?id=a2b9f8a7-ec99-43a5-b365-40143cbb9180
http://www.sqlskills.com/BLOGS/PAUL/post.aspx?id=08f8fcc0-8ec7-4403-8257-2d6bf6293e73
http://www.sqlservercentral.com/articles/64582/
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply