Shrinkfile problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Shrinkfile problem

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-07-11 : 02:38:46

Hi, I have a shrink log procedure which will be called schedully. I have experience that when execute the procedure, it just stuck there for 7 hours. Anyone has idea in what situation this will happen? Thanks.


CREATE PROCEDURE [dbo].[Proc_Shrink_File] 
@DBName VARCHAR(100)
AS
SET NOCOUNT ON;	
DECLARE @i AS INT;
DECLARE @targetSize AS INT;

SET @i = 1;
SET @targetSize = 100

--retrieve current log size used
EXEC dbo.Proc_Find_Log_Space_Usage @DBName 

--Check whether the log space used is more than targeted size, if yes, backup log and shrink log

IF ((SELECT Max(LogSize_MB) as LogSize_MB FROM dbo.tbl_Log_Space_Usage) > @targetSize)
BEGIN
	BACKUP LOG Database1 WITH TRUNCATE_ONLY
	DBCC SHRINKFILE (Database1_LOG, 1);	
END
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-11 : 02:59:35

You should not be running a script like this as you are breaking your transaction log chain which means you are losing your ability to restore to a point in time. You might as well use SIMPLE recovery model if you are going ot use a script like this.

Use this script instead for shrinking:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-07-11 : 03:14:47

quote:
Originally posted by tkizer

You should not be running a script like this as you are breaking your transaction log chain which means you are losing your ability to restore to a point in time. You might as well use SIMPLE recovery model if you are going ot use a script like this.

Use this script instead for shrinking:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Hi, I purposely truncate the log as there is a big issue on the availabe log size, this proc is called during database maintenance, and at the time, the log will increase very very huge.

But the condition I mentioned above is not due to this right?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-12 : 00:00:41

Shrinking file does take time, better to either backup log more frequently or set db to simple recovery model.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-12 : 00:21:48

quote:
Originally posted by juicyapple

quote:
Originally posted by tkizer

You should not be running a script like this as you are breaking your transaction log chain which means you are losing your ability to restore to a point in time. You might as well use SIMPLE recovery model if you are going ot use a script like this.

Use this script instead for shrinking:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Hi, I purposely truncate the log as there is a big issue on the availabe log size, this proc is called during database maintenance, and at the time, the log will increase very very huge.

But the condition I mentioned above is not due to this right?

You can not recover to a point in time then. You might as well switch to simple recovery model.

And if your log is growing big for some reason, then run more frequent tlog backups. Do not truncate it and then shrink it. You are causing a performance issue and a recoverability issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-07-12 : 00:33:37

OK. Thanks for all the info. But I am still not understand why shrinkfile can just hang without return error...Please advise.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-12 : 00:58:16

Because it didn't finish shrinking yet.

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-07-12 : 01:56:07

quote:
Originally posted by rmiao

Because it didn't finish shrinking yet.

Usually the portion of codes will only take few seconds or minutes to shrink log, because the shrinkfile command will be called after one transaction commited. Shouldn't until 7 hours long.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-12 : 02:08:29

And that's why you need to shrink in small increments.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-07-12 : 04:11:23

quote:
Originally posted by tkizer

And that's why you need to shrink in small increments.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

emm...It shouldn't has difference if shrink in small increments or shrink it to smallest size of it? I mean in terms of the time it will use.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-12 : 12:18:20

Yes it can.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-12 : 13:41:22

And it's possible been blocked by other processes.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-12 : 14:06:49

quote:
Originally posted by rmiao

And it's possible been blocked by other processes.

Which is exactly why you must shrink in small increments.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources