November 20, 2002 at 3:16 am
The DB in question here is running on SQL7.
I have a couple of questions, and would be very grateful if somebody could help to point me in the right direction.
1/ I have a database maintanence plan that does a back up of a txlog every hour between a certain time frame. According to the SQL Server Agent Job, the job succeeds with no errors - however no backup (.trn) file appears in the specified folder, and the tx log is not truncating at the same time?! Any ideas?
2/ Because of the first problem the total allocated space for the txlog has now reached approx 4368Mb, shocking I know!!! Can someone help, and give advice on how I should go about shrinking this? Where do i start, what T-SQL do I need, what can be done in EM, what should I look out for??
Anyones help appreciated.
TIA.
November 20, 2002 at 7:08 am
1/ 3 things, check the MSDB database for the backup set this should help but still needs some work.
select
database_name,
[name] backup_name,
[description] backup_description,
case device_type
WHEN 5 then 'Tape'
WHEN 2 Then 'Drive'
WHEN 102 THEN 'Drive'
END device_type,
device_type x,
case type
when 'L' Then 'Log'
when 'D' then 'Full'
when 'I' then 'Differential'
end Backup_Type,
physical_device_name
from
backupmediafamily bumf
inner join
backupset bus
ON
bumf.media_set_id = bus.media_set_id
WHERE
type = 'L'
Also, check the Event Viewer logs for issues pertaining to te backup.
Otherwise drop and recreate the maintainence plan to see if corrects.
2/ Do a backup log yourself thru T-SQL like so
BACKUP LOG [dbName]
TO DISK='Drive:\path\file.ext'
WITH
INIT,
NAME = 'dbName Log Backup'
Then run this script after the file is created and backedup to force the file to shrink.
Then set size of the Transaction log to the value you want as this may have shrunk below that, there EM.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply