Newbie needs help with DBCC Shrinkfile

Help SQLServer newbie..
Hello. I have a 32gb transaction log that I just backedup with truncate option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is used. When I run
use projectserverdev
go
dbcc shrinkfile (ProjectServer_log, 600)
I get the following output.
142409647263409647256
Message posted via http://www.sqlmonster.com
Can you take a look into Database properties and see what is a initial size
of Transaction Log?
If it is 4000, then this out put is normal as you cannot shrink under
initial size.
Danijel
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.c om...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com
|||Hi,
If that doesnt work, you may need to "wrap" the transaction log file. Run
this script on the database in question, then runn your DBCC command again:
(make sure to verify the database parameters on lines 6 and 8)
SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE ProjectServer -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'ProjectServer_Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@.MaxMinutes = 10, -- Limit on time allowed to wrap log.
@.NewSize = 10 -- in MB
-- Setup / initialize
DECLARE @.OriginalSize int
SELECT @.OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @.LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @.Counter INT,
@.StartTime DATETIME,
@.TruncLog VARCHAR(255)
SELECT @.StartTime = GETDATE(),
@.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
EXEC (@.TruncLog)
-- Wrap the log if necessary.
WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
not expired
AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name =
@.LogicalFileName) -- the log has not shrunk
AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @.Counter = 0
WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @.Counter = @.Counter + 1
END -- update
EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.c om...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com