December 8, 2005 at 9:14 am
We searched the internet and found the following procedure (supposedly) that will reduce the log file size of our SQL databases. This job runs once a week. Initially, we thought the job was running successfully, but upon further review it appears that it has not worked. On top of that, we lost our employee who had been working on this project.
Here is the SQL Statements we have been running inside of a DTS to reduce the log file size:
use OII_GENFABSYSTEM
DBCC SHRINKFILE (OII_GENFABSYSTEM_log,TRUNCATEONLY)
BACKUP LOG OII_GENFABSYSTEM WITH TRUNCATE_ONLY
DBCC SHRINKFILE (OII_GENFABSYSTEM_log,TRUNCATEONLY)
GO
The DTS job history says it was “successful”, but upon closer examination, it has been producing the following message upon completion (one message for each database attempted to be shrunk):
Cannot shrink log file 2 (OII_GenFabSystem_Log) because all logical log files are in use. [SQLSTATE 01000] (Message 9008) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528). The step succeeded.
Any help on this would be grateful.
December 8, 2005 at 1:14 pm
The following Stored Proc will Shrink the Log file to it's minimum value possible for the Database passed in as a Parameter to it.
exec dbo.Run_DBCCWithParam 'Target Database'
I have scheduled this as a job and is triggered if the "Log File Size in (KB) " rises above 1GB.
In my 2 Step scheduled job, First Step is to Truncate the Log and Second Step is this Stored Procedure which Shrinks the File.
*******************
Stored Procedure:
CREATE procedure Run_DBCCWithParam
@dbname varchar(32)
as
declare @dname varchar(32)
declare @LSZ real
declare @LSp real
declare @Sts varchar(10)
declare @ShrinkSize nvarchar(10)
declare @cmd varchar(100)
SET NOCOUNT ON
CREATE TABLE #tmplg
(
DBName varchar(32),
LogSize real,
LogSpc real,
Sts varchar(10)
)
create table #spdescc
(
dbn varchar(30),
fid varchar(5),
fname varchar(500),
fgrp varchar(20),
dbsz varchar(20),
mzsz varchar(20),
grth varchar(10),
usg varchar(20)
)
insert into #tmplg execute (' DBCC SQLPERF(LOGSPACE)')
DECLARE db_curs INSENSITIVE CURSOR FOR
select * from #tmplg where dbname = @dbname
OPEN db_curs
FETCH NEXT FROM db_curs INTO @dname, @LSZ, @LSp, @Sts
WHILE (@@FETCH_STATUS = 0)
BEGIN
select @cmd = N'use ' + quotename(@dbname) + N' insert into #spdescc exec sp_helpfile'
exec (@cmd)
Set @dname = (select dbn from #spdescc where usg = 'log only')
select @cmd = N'use ' + quotename(@dbname) + N'BACKUP LOG ' + @dbname + ' with truncate_only'
exec (@cmd)
Set @ShrinkSize = (@lsz * @lsp) / 100
set @ShrinkSize = round(@ShrinkSize, 0)
select @cmd = N'use ' + quotename(@dbname) + N'DBCC SHRINKFILE (' + @dname + ', ' + @ShrinkSize + ')'
exec (@cmd)
FETCH NEXT FROM db_curs INTO @dname, @LSZ, @LSp, @Sts
END
CLOSE db_curs
DEALLOCATE db_curs
drop table #tmplg
GO
***********************
I suggest you review this before putting it to use.
December 9, 2005 at 6:38 am
Thanks so much for responding to my inquiry.
What does the stored procedure look like that you use to truncate the file? You said the procedure given was only to shrink the file, so I am curious how you go about successfully shinking the log file (or am I misunderstanding something).
Thanks once again for your assistance.
December 11, 2005 at 10:44 am
Not sure if I understand ur ?
The above mentioned proc will calculate how much the log file can be shrunk and then shrink it to that value.
This proc is run after Log file is truncated.
Hope this helps.
December 11, 2005 at 12:41 pm
Craig, why are you trying to shrink the log file? If you don't need the space, you might want to leave it alone. Otherwise, it will just have to grow the file throughout the day as it expands, which can cause performance issue. After you run a transaction log backup or full backup, the file will have free space in it which can be utilized by the next set of transactions to be logged without growing the physical file.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
December 12, 2005 at 6:07 am
Derrick,
It is a good question you ask, but like I mentioned in my original post, I am not a SQL DBA, but a manager trying to make due until another person is hired.
What would be the suggested command(s) that we should run to properly manage this thing?
April 13, 2006 at 4:42 am
Will the above stored proc work in SQL Server 2000?
April 13, 2006 at 10:40 am
I'm running this proc in SQL 2000 env and it works.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply