scheduled TRN backups failing (sql2000)

I have created a backup that does once weekly BAK and every night it does TRN backup. This is in the same maintaince plan, the jobs appear as seprate jobs as they should. I have noticed my TRN is always failing, after looking into the event logs i see this.

please note this is lastest time to early:

4:22pm
this is the warning that the event log gives:

SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'xxxxxxxx TRN'' (0xB87B2FA07E4FB74CA01A1D38134C9C4F) - Status: Failed - Invoked on: 2007-03-09 16:19:40 - Message: The job failed. The Job was invoked by User domain\Administrator. The last step to run was step 1 (Step 1).

the next is
4:22pm
18265 :
Log backed up: Database: Staging, creation date(time): 2007/01/30(09:35:34), first LSN: 24624:14008:1, last LSN: 24624:14021:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'f:\program files\microsoft sql server\MSSQL\BACKUP\Staging\Staging_tlog_200703091622.TRN'}).

last is 4:21pm
18264 :
Database backed up: Database: Staging, creation date(time): 2007/01/30(09:35:34), pages dumped: 93982, first LSN: 24624:14008:2, last LSN: 24624:14018:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'f:\program files\microsoft sql server\MSSQL\BACKUP\Staging\Staging_db_200703091618.BAK'}).

So for some very strange reason its running a full backup then a Transactional, for this reason i would say its failing but why is it even doing this backup? the job is only for TRN?

A manual TRN backup works like a treat

Thanks

BRAD

CHECK what is the recovery model of your database... make sure it is not Simple... If it is simple it is normal to have this behaviour

Madhu

|||

ran sp_helpdb all of my databases are at full recovery apart from the tmpdb and another system DB but these are not in the backup plan only user databases are.

any other ideas?

|||

never mind you were right there was the northwind database still active (by default set to simple) this is classed as a user DB.

Thanks

Brad

Scheduled SSIS

Hello all,

I was very familiar in creating quite complex DTS packages in SQL2000. Now with 2007 that is all gone. I looked at the command line utility and it's UI counter part but these are just for executing. I keep getting references to the SSIS designer but I can't find where it is.

Can someone point me in the right direction?

Thanks,

Jesse

So, dtsrun.exe has now become dtexec.exe and it's counterpart on the UI is dtexecui.exe. The UI you see in the UI there is the same type of ui you'll see in the SQL Server Agent job. You'll just simply need to select SQL Server Integration Services for the step type and you'll get the same screens.

Brian

|||The designer components is installed when you select Client/Workstation components during SQL Setup. Run SQL Server Business Intelligence Development Studio, create new Integration Services project, now you have a package to design.|||

Yes I did that but when I open SQL Server Business Intelligence Development Studio it just looks like a standard visual basic studio that there is nothing under new projects that says intergration services project.

Do I just have a bad install maybe?

|||BIDS is a standard Visual Studio, but in the New Project dialog it should contain Business Intelligence Projects folder, with Integration Services Project in it.|||

I found it!

I feal incredably greatfull to everyone that helped and incredably stupid for not spoting earlier.

Thank you all.

Scheduled SSIS

Hello all,

I was very familiar in creating quite complex DTS packages in SQL2000. Now with 2007 that is all gone. I looked at the command line utility and it's UI counter part but these are just for executing. I keep getting references to the SSIS designer but I can't find where it is.

Can someone point me in the right direction?

Thanks,

Jesse

So, dtsrun.exe has now become dtexec.exe and it's counterpart on the UI is dtexecui.exe. The UI you see in the UI there is the same type of ui you'll see in the SQL Server Agent job. You'll just simply need to select SQL Server Integration Services for the step type and you'll get the same screens.

Brian

|||The designer components is installed when you select Client/Workstation components during SQL Setup. Run SQL Server Business Intelligence Development Studio, create new Integration Services project, now you have a package to design.|||

Yes I did that but when I open SQL Server Business Intelligence Development Studio it just looks like a standard visual basic studio that there is nothing under new projects that says intergration services project.

Do I just have a bad install maybe?

|||BIDS is a standard Visual Studio, but in the New Project dialog it should contain Business Intelligence Projects folder, with Integration Services Project in it.|||

I found it!

I feal incredably greatfull to everyone that helped and incredably stupid for not spoting earlier.

Thank you all.

sql

scheduled dts fails but runs when done manually

I'm a newbie to sql2000. i have some jobs that copy stuff from a non microsoft database(titanium) to SQL2000. i use the propriety odbc drivers provided by titanium. when run manually in enterprize manager,it works. but scheduled as a job it fails with the message "System cannot find the specified File" i've read up and tried microsofts suggestions ie ensuring that the sql server agent has rights to the folders used etc. i've also tried puttin in the the dts owner and user passwords. each time it fails with the same error.
any one out there who can help?:confused:I'm a SQL2K newbie and I've experienced a similar problem.

When you run the DTS pkg manually, it runs on your client machine;
when you run it as a job it runs on the SQL server.

If you can run the DTS pkg manually on the SQL server (from its console), do that. You should get more details on the error. Most likely
something is missing from the PATH environment variable on the SQL
server (and you can compare to what you have for this on your client
machine).

Good luck!

Jeff|||Jeff's on the right track. When you develop a DTS package remotely, it remembers the paths based on where you developed it. You would either need to
- develop the package locally (on the server), in which case it would not run interactively from a remote machine, or

- use a unc path, ie. \\servername\sharename\filename. In this case, it should run interactively or scheduled.

Steve|||I'd also add to check permissions. You need to keep in mind that the job on the server is not going to run as "you" but as the sql server agent, so it maynot have the same authorization to access drives and shares as you do. That's the one that always catches me.

schedule a restart for SQL2000

hoping to gather all the options here on the different methods you can use
to schedule a restart of SQL2000 for maintenance.
found one here:
http://vyaskn.tripod.com/restart_sql_server_service.htmYou may also
1. Use the T-SQL shutdown command from Query Analyzer or Osql or your own
program or
2. use the object.STOP method in SQL DMO or WMI
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pk" <pk@.> wrote in message news:OgoSvmwEEHA.2524@.TK2MSFTNGP09.phx.gbl...
> hoping to gather all the options here on the different methods you can use
> to schedule a restart of SQL2000 for maintenance.
> found one here:
> http://vyaskn.tripod.com/restart_sql_server_service.htm
>