April 19, 2006 at 9:09 am
I am getting a report like DB1,File1 has grown by -2869MB (shrunk if negetive) .
What is that negetive value means? Also how can I shrunk the database?
April 20, 2006 at 3:26 am
this might occur when your database has autoshrink enabled.
If this is a production db, disable autoshrink, unless you like to have no control regarding when the shrink will occur !
alter database xxx set AUTO_SHRINK Off
check DBCC ShrinkFile or DBCC shrinkdb in BOL.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 20, 2006 at 5:06 am
Thanks..
I didnot understand why it is showing negetive value,what actually is the meaning of that?
also do I need to truncate the log file before shrinking the log file?
Waht is the default target size does it use If don,t mention the target-size in DBCCSHRINKFILE?
What is the best option to use Truncateonly or Notruncate, which one is default?
April 20, 2006 at 5:42 am
you databasesize has been shrunk.
it will try to shrink to it's original size, unless specified other size.
there is a procedure (which may need to be executed a number of times) to shrink the transactionlog. By design, the trx-log is devided into active section(s). The log can only be truncated when the active section is not the last section in the log. It will only truncate to the active section !
http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD is for SQL7 , but also works for sql2000
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 20, 2006 at 12:18 pm
In one of my server there is 32Gb of log file for a database. when I run DBCC SQLPERF (LOGSPACE) log space usage is 94% and the status is not yet 0.
Can I truncate the log file to 1GB, If so what procedure do I need to follow?
April 21, 2006 at 12:00 am
-- this one works on SQL7 and SQL2000
You may have to run this script a number of times, because of the reasons I mentioned in previous the post.
-- Shrink_TrxLog.SQL
--INF: How to Shrink the SQL Server Transaction Log
-- http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD
-- select db_name()
-- select * from sysfiles
USE Your_Database_Name -- This is the name of the database for which the log will be shrunk.
go
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***
SELECT @LogicalFileName = 'Your_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 = 1000 -- 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
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply