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
Showing posts with label dbcc. Show all posts
Showing posts with label dbcc. Show all posts
Monday, March 19, 2012
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.
14 2 4096472 63 4096472 56
--
Message posted via http://www.sqlmonster.comCan 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.com...
> 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.com...
> 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
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.comCan 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.com...
> 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.com...
> 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
Newbie needs help with DBCC Shrinkfile
Help SQLServer newbie..
Hello. I have a 32gb transaction log that I just backedup with truncate opti
on. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is used. Wh
en 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.droptable.comCan 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 droptable.com" <forum@.droptable.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQ
droptable.com...
> 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.droptable.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 droptable.com" <forum@.droptable.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQ
droptable.com...
> 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.droptable.com
Hello. I have a 32gb transaction log that I just backedup with truncate opti
on. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is used. Wh
en 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.droptable.comCan 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 droptable.com" <forum@.droptable.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQ
droptable.com...
> 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.droptable.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 droptable.com" <forum@.droptable.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQ
droptable.com...
> 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.droptable.com
Subscribe to:
Posts (Atom)