Maintenance Plan File Deletion Bug

I have a maintenance plan set to delete files after 1 week. Instead it is deleting everthing older then 1 day. When I view the tsql in the designer, the xp_delete_file string looks correct. Is there anyway to see the exact xp_delete_file string that is actually being executed at run time?

I found the problem.

There is an "off by one" bug using the time unit of measure in "Maintenance" and "Cleanup" tasks.

If you select "Weeks" you get "Days", with "Days" you get "Hours", and "Hours" blows the index.

The patching procedure is a little complicated. Read this:

http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

What service pack you are on?

There were maintenane related bugs in SP2 so that MS released hot fix on 03/05/2007.

Check the hot fix if you are already on SP2 applied before the hot fix release. If you are on SP1 apply the SP2 down loaded after 03/06/2007 which is hot fix included.

|||

I found out what the problem is.

There is an "off by one" bug where the wrong time unit of measure is being used.

"Hours" are being converted to "Days", Days" are being converted to "Hours", and "Hours" will blow with an invalid index.

This problem exists for "Maintenance Cleanup" tasks and "Clean Up History" tasks.

The fix is a little complicated because there are two versions of sp2 out there.

Read this to figure out the proper fix:

http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

maintenance plan and sub-directory

Hello there,
We have a maintenance plan to backup five user databases, we select "create
a sub-directory" and backup file to "d:\sql\backup", one of database backup
files did go to subdirectory of "d:\sql\backup", system put backup files in
"d:\sql\backup",
Anybody has idea? I appreciate your reply
Thanks very much
LionelHi
It's not clear if these are different database in the same job or different
locations for the same database!!
I would expect the location to be consistent for each database within a
given job, therefore all full backups are either in one directory or each
sub-directory and the transaction log backups are either in one directory or
one sub-directory for each database.
You may also want to check the dates and times one the files.
John
"lionel" wrote:

> Hello there,
> We have a maintenance plan to backup five user databases, we select "creat
e
> a sub-directory" and backup file to "d:\sql\backup", one of database backu
p
> files did go to subdirectory of "d:\sql\backup", system put backup files i
n
> "d:\sql\backup",
> Anybody has idea? I appreciate your reply
> Thanks very much
> Lionel
>|||Does the folder exist for that specific db? I have seen issues where when
the MP can not get access to the specified folder it uses the next level up
instead. Another reason why I hate MP's.
Andrew J. Kelly SQL MVP
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:CFB6734A-EEC3-4707-A47E-F4F399E12746@.microsoft.com...
> Hello there,
> We have a maintenance plan to backup five user databases, we select
> "create
> a sub-directory" and backup file to "d:\sql\backup", one of database
> backup
> files did go to subdirectory of "d:\sql\backup", system put backup files
> in
> "d:\sql\backup",
> Anybody has idea? I appreciate your reply
> Thanks very much
> Lionel
>

maintenance plan and sub-directory

Hello there,
We have a maintenance plan to backup five user databases, we select "create
a sub-directory" and backup file to "d:\sql\backup", one of database backup
files did go to subdirectory of "d:\sql\backup", system put backup files in
"d:\sql\backup",
Anybody has idea? I appreciate your reply
Thanks very much
Lionel
Hi
It's not clear if these are different database in the same job or different
locations for the same database!!
I would expect the location to be consistent for each database within a
given job, therefore all full backups are either in one directory or each
sub-directory and the transaction log backups are either in one directory or
one sub-directory for each database.
You may also want to check the dates and times one the files.
John
"lionel" wrote:

> Hello there,
> We have a maintenance plan to backup five user databases, we select "create
> a sub-directory" and backup file to "d:\sql\backup", one of database backup
> files did go to subdirectory of "d:\sql\backup", system put backup files in
> "d:\sql\backup",
> Anybody has idea? I appreciate your reply
> Thanks very much
> Lionel
>
|||Does the folder exist for that specific db? I have seen issues where when
the MP can not get access to the specified folder it uses the next level up
instead. Another reason why I hate MP's.
Andrew J. Kelly SQL MVP
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:CFB6734A-EEC3-4707-A47E-F4F399E12746@.microsoft.com...
> Hello there,
> We have a maintenance plan to backup five user databases, we select
> "create
> a sub-directory" and backup file to "d:\sql\backup", one of database
> backup
> files did go to subdirectory of "d:\sql\backup", system put backup files
> in
> "d:\sql\backup",
> Anybody has idea? I appreciate your reply
> Thanks very much
> Lionel
>

maintenance plan and sub-directory

Hello there,
We have a maintenance plan to backup five user databases, we select "create
a sub-directory" and backup file to "d:\sql\backup", one of database backup
files did go to subdirectory of "d:\sql\backup", system put backup files in
"d:\sql\backup",
Anybody has idea? I appreciate your reply
Thanks very much
LionelHi
It's not clear if these are different database in the same job or different
locations for the same database!!
I would expect the location to be consistent for each database within a
given job, therefore all full backups are either in one directory or each
sub-directory and the transaction log backups are either in one directory or
one sub-directory for each database.
You may also want to check the dates and times one the files.
John
"lionel" wrote:
> Hello there,
> We have a maintenance plan to backup five user databases, we select "create
> a sub-directory" and backup file to "d:\sql\backup", one of database backup
> files did go to subdirectory of "d:\sql\backup", system put backup files in
> "d:\sql\backup",
> Anybody has idea? I appreciate your reply
> Thanks very much
> Lionel
>|||Does the folder exist for that specific db? I have seen issues where when
the MP can not get access to the specified folder it uses the next level up
instead. Another reason why I hate MP's.
--
Andrew J. Kelly SQL MVP
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:CFB6734A-EEC3-4707-A47E-F4F399E12746@.microsoft.com...
> Hello there,
> We have a maintenance plan to backup five user databases, we select
> "create
> a sub-directory" and backup file to "d:\sql\backup", one of database
> backup
> files did go to subdirectory of "d:\sql\backup", system put backup files
> in
> "d:\sql\backup",
> Anybody has idea? I appreciate your reply
> Thanks very much
> Lionel
>sql

Maintenance Plan Fails

I created a maintenance plan to back up a database. It fails and I get the follow error in the log file.

Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4064: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.

What user is it referring to? What user does a Maintenance plan use to login as? SA on the server has full access to all databases. SAs default database is master and I can read and write to it with the enterprise manager and query analyzer.

All replies appreciatedWhat's your security model?

Are you logged on and manually running it, or is it failing during a scheduled run?

That doesn't sound like a maint plan error though...|||It fails during a scheduled run.

In the Enterprise Manager if I right click on the database > All Tasks > Backup Database it works fine.

In the Enterprise Manager if I go into Management > New Maintenance Plans, schedule it. It fails.

The SQL Server use allows Windows Authentication or SQL Server Authentication. The server in question was registered in the Enterprise Manager with SQL Server Authentication. I don't know if that has any bearing on how Maintanence Plans are queued or what login is used when a plan executes.

Maybe I'm looking at a ODBC Driver problem. I just don't know how to get the details on which driver is being so I can compare it to my other servers where all Plans work fine.

Thanks for the reply.

mail task

how to attach a xls file that will be created at run time using mail task. I get a file does not exist error if the file is not there.

If the file is not there, then obviously you would expect the task to fail.

If your are just having issues because at the start of execution the file is not there, but it will exist by the time the mail task actually runs, use the DelayValidation property. This will stop the mail task validating at the beginning of package execution, it will only validate immediately prior to the task execution, by which time you should have written the file.

Mail failure in DTS package

Hi, Im trying to do a DTS that exports data to a excel file and then
mail it. It runs fine all way throw until the mail object get an
error. Like this:
Logon failed: Unable to get the default MAPI Message Store due to MAPI
error 273: Det g=E5r inte att =F6ppna informationsarkivet.
Does any one know what to do?
Cheers! /Karinkarin.w74@.hotmail.com wrote:
> Hi, Im trying to do a DTS that exports data to a excel file and then
> mail it. It runs fine all way throw until the mail object get an
> error. Like this:
> Logon failed: Unable to get the default MAPI Message Store due to MAPI
> error 273: Det går inte att öppna informationsarkivet.
> Does any one know what to do?
> Cheers! /Karin
Hi,
It sounds like the user that runs the SQLServer service hasn't got
access to logon to either the mailbox or to the Exchange server you are
using. You might have to check the the user that runs SQL Server also
is the one that has an Outlook profile setup on the server - otherwise
you might run into the problem.
Regards
Steen Schlüter Persson
CRM System Specialist / DBA
Denmark

Mail failure in DTS package

Hi, Im trying to do a DTS that exports data to a excel file and then
mail it. It runs fine all way throw until the mail object get an
error. Like this:
Logon failed: Unable to get the default MAPI Message Store due to MAPI
error 273: Det g=E5r inte att =F6ppna informationsarkivet.
Does any one know what to do?
Cheers! /K"liteblandat@.hotmail.com" wrote:
> Hi, Im trying to do a DTS that exports data to a excel file and then
> mail it. It runs fine all way throw until the mail object get an
> error. Like this:
> Logon failed: Unable to get the default MAPI Message Store due to MAPI
> error 273: Det går inte att öppna informationsarkivet.
> Does any one know what to do?
> Cheers! /K
>
Hi
Are you running this package from the designer or a job? The issue is that
the account under which it is running, does not have access to the MAPI
profile that you have configured for SQLMail. Check the accounts that SQL
Server and SQL Agent have been configured to run as and that they can access
the profile you have set up. Try logging in on the server as these accounts
and sending a test email.
HTH
John

LUN attachment and moving temp db

F:\ is a RAID 0 LUN attached to the server (it's on a SAN)
After running
ALTER DATABASE tempdb
MODIFY FILE (name=tempprod, filename='f:\tempdb\tempdb_data.mdf')
I get the error
Server: Msg 5184, Level 16, State 1, Line 1
Cannot use file 'f:\tempdb\tempdb_data.mdf' for clustered server. Only
formatted files on which the cluster resource of the server has a
dependency can be used.
What do i need to change in my configuration? Seems I can only see a
g:\ drive who is also on the san.Apparently, the F drive is from your locally attach
drives, not those shared by the cluster nodes. You cannot
create a database file on a non-shared drive if your SQL
instance runs in a cluster.
Create the tempdb file on the G drive.
Linchi
>--Original Message--
>F:\ is a RAID 0 LUN attached to the server (it's on a
SAN)
>After running
>ALTER DATABASE tempdb
> MODIFY FILE (name=tempprod,
filename='f:\tempdb\tempdb_data.mdf')
>I get the error
>
>Server: Msg 5184, Level 16, State 1, Line 1
>Cannot use file 'f:\tempdb\tempdb_data.mdf' for clustered
server. Only
>formatted files on which the cluster resource of the
server has a
>dependency can be used.
>What do i need to change in my configuration? Seems I
can only see a
>g:\ drive who is also on the san.
>.
>|||Ahh the F:\ drive needed to be a dependancy of the sql cluster service.
I was able to create temp on that drive after it was added.
Linchi Shea wrote:
> Apparently, the F drive is from your locally attach
> drives, not those shared by the cluster nodes. You cannot
> create a database file on a non-shared drive if your SQL
> instance runs in a cluster.
> Create the tempdb file on the G drive.
> Linchi
>
>>--Original Message--
>>F:\ is a RAID 0 LUN attached to the server (it's on a
> SAN)
>>After running
>>ALTER DATABASE tempdb
>> MODIFY FILE (name=tempprod,
> filename='f:\tempdb\tempdb_data.mdf')
>>I get the error
>>
>>Server: Msg 5184, Level 16, State 1, Line 1
>>Cannot use file 'f:\tempdb\tempdb_data.mdf' for clustered
> server. Only
>>formatted files on which the cluster resource of the
> server has a
>>dependency can be used.
>>What do i need to change in my configuration? Seems I
> can only see a
>>g:\ drive who is also on the san.
>>.

LTM help file

Sorry in advance if this is off topic...please advise

In the past, I've used LTM (Local Test Manager) for testing a OLE DB providers conformance and had much success with it. The tool shipped in older versions of the MDAC SDK.

I've obtained v 2.70 of LTM.EXE from the MSDN platform SDK but there is no online help (LTM.CHM)

Anyone know where I can down load a copy?

Have you tried downloading Microsoft Data Access SDK 2.8 from http://www.microsoft.com/downloads/details.aspx?FamilyID=5067faf8-0db4-429a-b502-de4329c8c850&DisplayLang=en ?

Thanks

Suroor

|||

Thanks Suroor,

The MDAC SDK download appears to be much more complete than what's on the Platform SDK

Kind regards,

Terry

LSN Sequence Issue

HI
I have full backup of 17th & lost the file of full backup
of 13th. I have transaction log backups from their
onwards. I need to restore data from 15th & 16th from
transaction logs. Its very critical. When I apply the
transaction log on 17th restore in loading mode it says
apply current transaction log having so & so lsn number.
What is the alternative to get that critical data.
Thanks,
ManojAfter you restore the backup from the 17th, you can only restore log backups
taken after that point.
SQLServer doesn't provide any tool to extract the data from the log only.
There may be tools, from companies such as "Lumigent" which would allow you
to extract the inserts/deletes/updates from the log.
"Manoj" <mgmanoj@.hotmail.com> wrote in message
news:0e7501c3846c$6b60b460$a101280a@.phx.gbl...
> HI
> I have full backup of 17th & lost the file of full backup
> of 13th. I have transaction log backups from their
> onwards. I need to restore data from 15th & 16th from
> transaction logs. Its very critical. When I apply the
> transaction log on 17th restore in loading mode it says
> apply current transaction log having so & so lsn number.
> What is the alternative to get that critical data.
> Thanks,
> Manoj|||Do you have the "next latest" db backup? And all subsequent log backups? If so, you can restore from
that thanks to SQL Server not truncating the log when you do db backup.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Manoj" <mgmanoj@.hotmail.com> wrote in message news:0e7501c3846c$6b60b460$a101280a@.phx.gbl...
> HI
> I have full backup of 17th & lost the file of full backup
> of 13th. I have transaction log backups from their
> onwards. I need to restore data from 15th & 16th from
> transaction logs. Its very critical. When I apply the
> transaction log on 17th restore in loading mode it says
> apply current transaction log having so & so lsn number.
> What is the alternative to get that critical data.
> Thanks,
> Manoj