January 4, 2008 at 6:21 am
Kishore.P (1/4/2008)
use DBCC Updateusage, DBCC PROCCACHE, DBCC SHRINKDATABASE command along with DBCC Shrinkfile get better performance as well as good for backup also. Just try it.
Unless you plan to rebuild your indexes then shrinking the database i.e using DBCC SHRINKDB (as you ar using sql 2000 not 2005)is REALLY not a good idea as it will case extensive index fragmentation and result in poor performance.
As stated before don't truncate the transaction log unless you plan to take a full backup afterwards.
just view your actual transaction log usage against your actual allocation transaction log using task manger or use
DBCC SQLPERF(logspace) from query analyzer
Then if you must shrink the .LDF file but be aware that its a good idea to monitor your transaction log usage as a busy transactional system will use up your allocated transaction log space and when full the transaction log will grow by the amount or percentage growth factor.
CodeMinkey
January 4, 2008 at 6:39 am
I know this particular case mentioned is posted in the wrong forum, but I had this problem last week with a sql 2005
database where the log had blossomed to 134Gb for 40GB data, Tried multiple times to shrink the log but to no avail.
after much searching I came up with the below little script, which works a gem in sql2005 database to shrink errant logs.
I tinkered with the original script to create a stored proc, apply it in any database you want to shrink then execute it. ta da works 100% of the time (well On the 20 or so dbs I've tried it on so far anyways!)
I think script will need tinkering with if you use multiple file groups for your logs... (I don't have any so didn't test it)
-- =============================================
-- Stored Proc that forces a shrink of the database log file
-- Will free up log file space, when other methods fail.
-- Written by Yussuf Khan 2007-12-31, but I cannae take all the credit
-- Most of the script taken from an MSDN forum
-- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=621684&SiteID=1
--
-- Parameters:
-- @MaxMins maximum minutes to try and get to desired size
-- @TargetSizeMB new log file size target in MB
-- DBCC SQLPERF(LOGSPACE) is a nice thing to see how much of the log is actually being used.
-- =============================================
-- Drop stored procedure if it already exists
IF EXISTS ( SELECT
*
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
SPECIFIC_SCHEMA=N'dbo'
AND SPECIFIC_NAME=N'ForceLogShrink' )
DROP PROCEDURE dbo.ForceLogShrink
GO
CREATE PROCEDURE dbo.ForceLogShrink
@MaxMins int=1 ,
@TargetSizeMB INT=500
AS
SET NOCOUNT ON
DECLARE @OriginalSize INT
DECLArE @LogicalFileName sysname
SELECT
@LogicalFileName=name
FROM
sys.database_files
WHERE
type_desc='log'
SELECT
@OriginalSize=size -- in 8K pages
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, @TargetSizeMB) WITH NO_INFOMSGS
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMins>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)>@TargetSizeMB -- 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
DROP TABLE DummyTrans
SELECT
db_name() AS dbName ,
--CONVERT(VARCHAR(30),size) as pagesSize,
CONVERT(VARCHAR(30) , (size*8/1024)) AS NewSizeMB ,
CONVERT(VARCHAR(30) , (@OriginalSize*8/1024)) AS OriginalSizeMB
FROM
sysfiles
WHERE
name=@LogicalFileName
--PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
GO
-- =============================================
-- Example to execute the stored procedure
-- =============================================
-- EXECUTE dbo.ForceLogShrink 1, 50
GO
January 4, 2008 at 2:31 pm
It takes 3 commands to dump the log and shrink the database and log files.
You may wish to change the DBCC commands to specify a target percentage to leave. Otherwise, you can be plagued by frequent data and log file expansions which will most likely cause fragmentation. It's all about planning and implementation.
If there has been no maintenance on this database for 2 years, I'd suggest you look into setting up a database maintenance plan (make sure to auto start SQL Agent) to routinely reorganize data and indexes, integrity check, backup db, backup logs, etc.
--=====================================================
--TRUNCATING A DATABASE TRANSACTION LOG
--=====================================================
--The following script will:
-- 1. Truncate a database transaction log.
-- 2. Shrink the database to free up space.
-- 3. Release any extra space to the operating system.
--=====================================================
--IMPORTANT NOTES:
--This command will discard the inactive portion of the transaction log.
--You should immediately backup the full database after running this script.
--INSTRUCTIONS FOR USE:
--Replace the word PutDatabaseNameHere with the name of the database.
--(The name of the database must be enclosed in single quote marks.)
declare @SQLString nvarchar(512)
declare @DatabaseName nvarchar(128)
set @DatabaseName = 'PutDatabaseNameHere'
--------------------------------------
--Step 1: Truncate the transaction log
--------------------------------------
--notify user
print N''
print N'Truncating the transaction log...'
--construct SQL string
set @SQLString = N'backup log [' + @DatabaseName + N'] with no_log'
--execute the SQL string
exec master.dbo.sp_executesql @SQLString
--------------------------------------------------
--Step 2: Shrink the database (data and log files)
--------------------------------------------------
--notify user
print N''
print N'Shrinking the database...'
--construct SQL string
set @SQLString = N'dbcc shrinkdatabase([' + @DatabaseName + N'], notruncate)'
--execute the SQL string
exec master.dbo.sp_executesql @SQLString
---------------------------------------------------------
--Step 3: Release any extra space to the operating system
---------------------------------------------------------
--notify user
print N''
print N'Releasing any extra space to the operating system...'
--construct SQL string
set @SQLString = N'dbcc shrinkdatabase([' + @DatabaseName + N'], truncateonly)'
--execute the SQL string
exec master.dbo.sp_executesql @SQLString
January 5, 2008 at 8:50 am
Step 4: Rebuild all your indexes to undo the damage that ShrinkDatabase did to them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2008 at 1:20 am
Ok
Thank you sir.
Regards
Majid
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply