Help me find the problem, please

Question

Post reply

Help me find the problem, please

Tara-1044200

SSCoach

Points: 15951
More actions
January 4, 2010 at 8:09 am

#228872

I have weekly backup job(using LiteSpeed) which runs every friday on sql server which consists of 360 databases.
Configuration:
OS- Win 2003
SQL Server 2005
The wiered thing i am observing since a year is , it doesnt not backup all databases every week, i have to manually start the job from the point where it stopped.
The job will run order by database name and just finishes successfully without comepleting backups of all databases. Mostly it stops backing up and i have to exec the backup script from the "model" database.
I have no idea why it stops only at that point ie; model database. I dont have any error logs from windows side or sql server side, how would i know the problem.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sunny-138471 Ten Centuries Points: 1162 More actions · Answer 1

Could you please share the backup creation script?

Thanks

Satish More

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

Here is the script i use

declare @dbname as varchar(80)
declare @msgdb as varchar(80)
declare @dbbkpname as varchar(80)
declare @datepart as char(1)
declare rs_cursor CURSOR for select name from master.sys.databases where state_desc = 'ONLINE' and is_read_only = 0 order by name
open rs_cursor
Fetch next from rs_cursor into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
select @msgdb= 'database backup in progress: ' + @dbname
PRINT @msgdb
select @dbbkpname='\\OBSTORE\RevProd2\full\' + @dbname + '.bak'
BEGIN TRY
exec master.dbo.xp_backup_database
@database = @dbname,
@filename = @dbbkpname,
@compressionlevel = 5,
@init = 1,
@logging = 2,
@maxtransfersize= 512000,
@threads = 1
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
deallocate rs_cursor
GO

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 3

Have you tried changing the script to filter out the model database

Try adding and name ! = 'Model'

If this works, then you can try backing up Model only as a separate job.

Also, have you checked out the possibility of disk space on the drive that might stop the backup job.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

but how would i know the reason for causing this problem.

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 5

Have the SQL Agent write information message into a text file and you should be able to see the output of the execution in that file.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Your best bet is to configure the job to write its output to a text file as Bru suggested. Without the job erroring out, there might be something stated in the log (output) that would clue us in to what is causing the failure.

Also, have you considered breaking up the job into multiple jobs? X number databases per job, or 1 job per database?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Sunny-138471 Ten Centuries Points: 1162 More actions · Answer 7

Just a wild guess. Could you pelase check the output of this query in the script?

select name from master.sys.databases where state_desc = 'ONLINE' and is_read_only = 0 order by name

The variable that holds the database names is limited to length 80. Could you please confirm that none of the database names have length more than 55?

Thanks

Satish More