HELP!!!! Transaction Log Automatically Shrinking
-
Im using sql server version 7.0 sp4 the only thing a have in my job scheduling is the BACKUP LOG %db_name% WITH TRUNCATE_ONLY which is set at around 11:50PM my backup is set at 11:00PM but what bothers me is that the script not only truncates but also shrinks the file ( which i do not know why?)
when i checked the backup as of 11:00PM (whic i restored in my dummy database) the result says that the transaction log shrinks AFTER the database backup but i think my script (as mentioned above) is not affecting the shrinking of the T-Log what could be the problem please help me.. thanks in advance 
Lester A. Policarpio
"-=Still Learning=-"
Lester Policarpio
-
Are the database properties setup to automatically shrink the database?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for the response Jeff the only database properties that have check are the "Auto update statistics" and "Auto create Statistics"
"-=Still Learning=-"
Lester Policarpio
-
Is there a maintenance plan (with remove free space ? ) ?
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
-
Or any other JOB/Script doing it ?
* Noel -
-the only maintenance that i have is the database backup maintenance plan
- the scripts that i have in my job schedule are:
BACKUP LOG <databasename> WITH TRUNCATE_ONLY script (no shrink script)
and
usp_deleteoldbackupfile stored procedure that only removes backup files which is not related in shrinking the transaction log
thanks,
Lester A. Policarpio
"-=Still Learning=-"
Lester Policarpio
-
Hi Lester,
Can you let us know a bit more about the issue ?
1) From when u have noticed about this issue ?
2) How do u confirm that the Transaction logs are Shrinked ?
3) If so, at what time the transaction logs are getting shrinked ?
4) Can you post the usp_deleteoldbackupfile Stored Procedure and the time which is scheduled to run ?
So that we could narrow down, how the shrinking happens automatically with out running any Maintenance job.
Regards
Kumar
-
1) From when u have noticed about this issue ?
- about a week ago when my boss noticed that the transaction log shrunk
2) How do u confirm that the Transaction logs are Shrinked ?
- by monitoring the transaction log in the EM i noticed that it shrunk every night
3) If so, at what time the transaction logs are getting shrinked ?
-This is the schedule of what im running everyday
10:00PM - usp_deleteoldbackupfile
11:00PM - Database Maintenance Plan (Backup all database)
11:50PM - BACKUP LOG <database> WITH TRUNCATE_ONLY
thats it... only 1 database shrink every night hmm.. dont know the exact time but I think it is after the database maintenance plan
4) Can you post the usp_deleteoldbackupfile Stored Procedure and the time which is scheduled to run ?
CREATE PROCEDURE usp_DeleteOldBackup
AS
DECLARE @path varchar(1024)
DECLARE @extension varchar(3)
DECLARE @pathension varchar(1000)
DECLARE @DeleteBeforeDate datetime
DECLARE @FName varchar(1024)
DECLARE @delete varchar(1024)
DECLARE @error varchar(1000)
DECLARE @msg varchar(1000)
DECLARE @count int
-- Drop tables if they exist --
IF OBJECT_ID('dbo.cmdshell') IS NOT NULL
DROP TABLE cmdshell
IF OBJECT_ID('dbo.Errors') IS NOT NULL
DROP TABLE Errors
CREATE TABLE cmdshell (Fentry varchar(1024),FDate VARCHAR(25), FName VARCHAR(50))
CREATE TABLE Errors (Results VARCHAR(1000))
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
SET @path = 'C:\lester\'
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
SET @extension = 'BAK'
SET @pathension = 'dir /OD '+@Path+'*.'+@Extension
insert into cmdshell(fentry) exec master..xp_cmdshell @pathension
delete from cmdshell where fentry not like '%.BAK%'
delete from cmdshell where fentry is NULL
UPDATE cmdshell set fdate = substring(fentry,1,10) , fname = substring(fentry,40,50)
-- Set @DeleteBeforeDate --
IF @DeleteBeforeDate is null
select @DeleteBeforeDate = (Select top 1 FDate
from cmdshell
where right(FName,3) = @Extension
order by FDate DESC)
--delete record for most recent backup
delete from cmdshell where FDate >= @DeleteBeforeDate
SET @count = 0
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT FName
FROM cmdshell
WHERE FDate < @DeleteBeforeDate
OPEN curDir
FETCH NEXT FROM curDir INTO @FName
WHILE (@@fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @Delete = 'DEL "'+ @Path + @FName + '"'
INSERT INTO Errors (Results)
exec master..xp_cmdshell @Delete
-- select @Delete as asdfdelete
IF @@RowCount > 1
BEGIN
SET @Error = -1
SET @Msg = 'Error while Deleting file ' + @FName
GOTO On_Error
END
-- PRINT @Delete
PRINT 'Deleted ' + @FName + ' at ' + CONVERT(VARCHAR(28),GETDATE(),113)
set @count = @count+1
FETCH NEXT FROM curDir INTO @FName
END
print ''
print 'The total number of Backups deleted is: ' print @count
CLOSE curDir
DEALLOCATE curDir
DROP TABLE cmdshell
DROP TABLE Errors
On_Error:
BEGIN
IF @Error <> 0
BEGIN
SELECT @Msg + '. Error # ' + CAST(@Error AS VARCHAR(10))
RAISERROR(@Msg,12,1)
RETURN @Error
END
END
GO
Thanks for the help guyss
Lester A. Policarpio
Asia United Bank - DB Administrator
"-=Still Learning=-"
Lester Policarpio
-
The only thing I can see that might be doing this is your 11PM DB Maintenance job... check it and see if you are "regorganizing" the data... there's at least one setting that will "remove the space from the end". That means "shrink" in geekenese
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi,
Jeff is right, As per my knowledge if the auto shrink option is also uncheck in your scenerio, then auto shrink of the log file cannot be done automatically.
The possible guess is that, open the maintenance plan and check with any additional job for shrinking the log is added in the backup plan.
Otherwise you can run the trace in the profile and see what happens exactly after 11PM, the backup completes. It would give out the clear the picture, what happening after the backup.
Regards
Kumar
-
I've checked the things that you've said and those options are not activated in my database maintenance plan. As of September 6, 2007 8:00AM the logsize did not shrink the only thing i did is to delete my job schedule BACKUP LOG <dbname> WITH TRUNCATE_ONLY and poof... logsize is stable however i have another question. DOES BACKUP LOG <dbname> WITH TRUNCATE_ONLY affects the shrinking of the log? because based on what i've red it doesn't affect the shrinking of the log size?? im so confused....
@@OFF TOPIC@@
Anyone used dbcc sqlperf(logspace) command? is there a bug in it? The reason is that i have 15 databases (including master,model etc..) but everytime i use this dbcc command it always displays 12 out of 15 databases. The solution i made with this is to create a cursor to run this command in every databases then get the average of each database ouput so that i can achieve 15 out of 15 database output.. any insights???
Thanks...
Lester A. Policarpio
Asia United Bank - DB Admin
"-=Still Learning=-"
Lester Policarpio
-
Anyone used dbcc sqlperf(logspace) command? is there a bug in it? The reason is that i have 15 databases (including master,model etc..) but everytime i use this dbcc command it always displays 12 out of 15 databases. The solution i made with this is to create a cursor to run this command in every databases then get the average of each database ouput so that i can achieve 15 out of 15 database output.. any insights???
Lester
Whats the status of the databases that are missing? To check the status for every database run this,
exec sp_msforeachdb 'select ''?'', databasepropertyex(''?'', ''status'')'
--------------------
Colt 45 - the original point and click interface
-
All databases are online sir. Have you tried using this command? In your x databases where x = no. of database did it output all databases?
"-=Still Learning=-"
Lester Policarpio
-
Yes it did output all databases and showed two as OFFLINE.
--------------------
Colt 45 - the original point and click interface -
What could be the problem in my databases when i run this command?
"-=Still Learning=-"
Lester Policarpio
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply