SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup


Backup

Author
Message
tt-615680
tt-615680
Right there with Babe
Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)

Group: General Forum Members
Points: 715 Visits: 1231
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88975 Visits: 45284
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, MVP, M.Sc (Comp Sci)
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


tt-615680
tt-615680
Right there with Babe
Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)

Group: General Forum Members
Points: 715 Visits: 1231
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
tt-615680
tt-615680
Right there with Babe
Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)

Group: General Forum Members
Points: 715 Visits: 1231
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88975 Visits: 45284
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, MVP, M.Sc (Comp Sci)
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


tt-615680
tt-615680
Right there with Babe
Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)

Group: General Forum Members
Points: 715 Visits: 1231
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88975 Visits: 45284
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, MVP, M.Sc (Comp Sci)
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


tt-615680
tt-615680
Right there with Babe
Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)

Group: General Forum Members
Points: 715 Visits: 1231
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.
tt-615680
tt-615680
Right there with Babe
Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)

Group: General Forum Members
Points: 715 Visits: 1231
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.
john.campbell-1020429
john.campbell-1020429
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 336
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search