Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Backup Expand / Collapse
Author
Message
Posted Tuesday, August 24, 2010 7:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:09 AM
Points: 249, Visits: 925
The hard drive is formatted in NTFS and the only job that runs every half hour throughout the day is the "Truncate Transaction log" job but when I re-run the job again tomorrow, I will be able to see whether another process is blocking the job from running.

Thank you.
Post #974096
Posted Tuesday, August 24, 2010 7:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
tt-615680 (8/24/2010)
the only job that runs every half hour throughout the day is the "Truncate Transaction log" job


Truncate or back up? If truncate, why?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #974119
Posted Tuesday, August 24, 2010 8:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:09 AM
Points: 249, Visits: 925
This is the stored procedure that runs the 'Truncate Transaction log'

ALTER PROCEDURE [dbo].['name of stored procedure']

AS

BACKUP LOG 'Databasename'
WITH
TRUNCATE_ONLY

Post #974136
Posted Tuesday, August 24, 2010 8:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:09 AM
Points: 249, Visits: 925
Ray Mond (8/24/2010)
That script won't work on SQL Server 2000. You did post on a SQL Server 2005 forum after all.

Run sp_who2, find if the BACKUP processes are being blocked, if so, by which process.

Do note that given the backup command you are using i.e.

BACKUP DATABASE 'Databasename' TO DISK = 'E:\'filename'.BAK' 

you are appending backup sets to the file, so after 4 days of backing up the 4 databases totaling 108 GB, and assuming most of the database space is used, you will run out of space on that external drive. Also, do check if the external drive has been formatted using FAT32 or NTFS.


The hard drive is formatted in NTFS and the only job that runs every half hour throughout the day is the "Truncate Transaction log" job but when I re-run the job again tomorrow, I will be able to see whether another process is blocking the job from running.

Thank you.
Post #974150
Posted Tuesday, August 24, 2010 8:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
tt-615680 (8/24/2010)
BACKUP LOG 'Databasename'
WITH
TRUNCATE_ONLY


Why?

If you need point-in-time recovery, then you need log backups. If you don't need point in time recovery, set the recovery model to simple and leave it alone.
Please read through this - Managing Transaction Logs



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #974155
Posted Thursday, August 26, 2010 2:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:09 AM
Points: 249, Visits: 925
Ray Mond (8/24/2010)
That script won't work on SQL Server 2000. You did post on a SQL Server 2005 forum after all.

Run sp_who2, find if the BACKUP processes are being blocked, if so, by which process.

Do note that given the backup command you are using i.e.

BACKUP DATABASE 'Databasename' TO DISK = 'E:\'filename'.BAK' 

you are appending backup sets to the file, so after 4 days of backing up the 4 databases totaling 108 GB, and assuming most of the database space is used, you will run out of space on that external drive. Also, do check if the external drive has been formatted using FAT32 or NTFS.


I have run the backup again just with one of the Databases last night but the same thing happend, where the Backup states that it is still executing but nothing is being backed up.
I did run the following scripts to find out if anything is blocking the job:
sp_who2 and also
SELECT * FROM Master.dbo.sysprocesses WHERE blocked <> 0 and nothing seems to be blocking the job. I have been trying to backup the Databse for sometime now but it doen't seem to work, I've researched for the problem everywhere and I'm not having any luck. Is there anything I'm doing wrong?

Thank you.
Post #975427
Posted Thursday, August 26, 2010 2:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
What's the wait type for the process while that backup is running?

Edit: Is that extra quote in your statement a typo?
Should be
BACKUP DATABASE 'Databasename' TO DISK = 'E:\'filename.BAK' 

not
BACKUP DATABASE 'Databasename' TO DISK = 'E:\'filename'.BAK' 




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #975442
Posted Thursday, August 26, 2010 3:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:09 AM
Points: 249, Visits: 925
GilaMonster (8/26/2010)
What's the wait type for the process while that backup is running?

Edit: Is that extra quote in your statement a typo?
Should be
BACKUP DATABASE 'Databasename' TO DISK = 'E:\'filename.BAK' 

not
BACKUP DATABASE 'Databasename' TO DISK = 'E:\'filename'.BAK' 



I will run the job again tomorrow morning and see what the wait type for the process is and also I will check to see if there are any other processes that is blocking the job.

Thank you.
Post #975460
Posted Friday, August 27, 2010 4:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:09 AM
Points: 249, Visits: 925
tt-615680 (8/26/2010)
GilaMonster (8/26/2010)
What's the wait type for the process while that backup is running?

Edit: Is that extra quote in your statement a typo?
Should be
BACKUP DATABASE 'Databasename' TO DISK = 'E:\'filename.BAK' 

not
BACKUP DATABASE 'Databasename' TO DISK = 'E:\'filename'.BAK' 



I will run the job again tomorrow morning and see what the wait type for the process is and also I will check to see if there are any other processes that is blocking the job.

Thank you.

I've run the Job again with diffrent times but it still doing the same thing, it's not backing up. I run the script to see the wait type for the process and also if there are any other processses that are blocking the job and here are the results that I got:

kpid block waittype waittime lastwaittype
220 0 0x0000 0 ASYNC_DISKPOOL_LOCK
4808 0 0x0000 0 MISCELLANEOUS
5188 0 0x0000 0 ASYNC_DISKPOOL_LOCK

Thank you.
Post #976261
Posted Tuesday, September 14, 2010 6:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:57 AM
Points: 56, Visits: 335
Your backup command does not look correct. Try

backup database pubs to disk='e:\pubs.bak'

In your log files job adjust the schedule to stop before your backup begins and start again after the backup finishes.

Work with specific files until you figure out the problem, backup the system databases and get it right there, then do all of the databases.

Run sp_who to see if there are any old backup jobs hanging around causing problems. It there are any, kill them and start fresh.

John.
Post #985430
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse