Thursday, March 29, 2012
Database Naming
company, they named the database '123v10'.
When I run some queries (mostly admin stuff, checking the fragmentation,
space used by the data and log files, etc) in the Query Analyzer, I often
get the error message:
Line 1: Incorrect syntax near '123'.
My guess is that SQL doesn't particularly like database names that start
with numbers, am I correct? If so, will this create any real problems down
the line? I think we are at an early enough stage that the database could
be renamed if this would help.
TIA,
Nancy L> My guess is that SQL doesn't particularly like database names that start
> with numbers, am I correct? If so, will this create any real problems
> down
> the line? I think we are at an early enough stage that the database could
> be renamed if this would help.
Yes, you are correct. However, it should not cause problems if you code
defensively (which you should be doing anyways). The problem is that the
creator of the name did not follow the rules for regular identifiers. If
you don't follow the rules, then you must take extra steps to properly
delimit the identifier name (this includes all object names - databases,
triggers, procedures, tables, etc.). Obviously, the easiest approach is to
follow the rules.|||"Scott Morris" <bogus@.bogus.com> wrote in message
news:eUt9vPN$FHA.360@.TK2MSFTNGP09.phx.gbl...
> Yes, you are correct. However, it should not cause problems if you code
> defensively (which you should be doing anyways). The problem is that the
> creator of the name did not follow the rules for regular identifiers. If
> you don't follow the rules, then you must take extra steps to properly
> delimit the identifier name (this includes all object names - databases,
> triggers, procedures, tables, etc.). Obviously, the easiest approach is
> to follow the rules.
>
To add to that. Ensure that you use square brackets [ ] whenever you
reference the db name.
Rick Sawtell|||You may get it to work by using brackets around the db name like [123xxx],
however, somewhere down the road there will perhaps be a query or
application that will choke on it. It would be best to nip it in the bud
now.
"Nancy Lytle" <word_diva@.hotmail.com> wrote in message
news:O0YWoDN$FHA.216@.TK2MSFTNGP15.phx.gbl...
> am doing some testing on a database created by the programming dept of the
> company, they named the database '123v10'.
> When I run some queries (mostly admin stuff, checking the fragmentation,
> space used by the data and log files, etc) in the Query Analyzer, I often
> get the error message:
> Line 1: Incorrect syntax near '123'.
> My guess is that SQL doesn't particularly like database names that start
> with numbers, am I correct? If so, will this create any real problems
> down
> the line? I think we are at an early enough stage that the database could
> be renamed if this would help.
> TIA,
> Nancy L
>
DATABASE NAME SHOWS MY USERNAME
I am not so familiar with SQL Server 2005, but the problem is that when i created a vies and try to save it, instead of the name starting with the dbo. prefix, its starting with my username.
And i just found out that this is not happening only in the view its happening where ever i created an object and try to save it.
How can i change this from my username to make it display dbo. instead.
I think this is why i'm getting permission error when i try to use DTS package.
IF you want the dbo schema to 'own' the object (VIEW, TABLE, etc.) then use 'dbo.' as part of the name when you create the object.
CREATE VIEW dbo.MyView
AS ...
In order to correct the existing 'mis-named' objects, you will have to 'transfer' them into the correct schema.
ALTER SCHEMA dbo TRANSFER YourName.MyView
When that is complete, each item will then be in the [dbo] schema, and can be used by all users of the database, and referred to as dbo.MyView, dbo.MyFunction, dbo.MyTable, etc..
|||Thanks for the reply
We dont create them coding. This has to do with Cranberry, so when we create rules, we use them in cranberry.
The way we create a view is just to right click on the View, select new rule. Then after adding table and doing all modification then we click the save button. A box pop showing View1 that is what we change to the new name we want to use then after i save and went back to the view list, i see that it has added my username as a prefix instead of dbo.
So its not that I'm coding anything manualy, so i dont know where to put all the codes above.
Thanks.
N.B. Sorry, this is my 1st time using SQL.
|||Add the 'dbo.' to the name when you name the view.
For the existing 'mis-named' views, if you can't execute the code I presented previously, you will have to delete the existing views and re-create them and use the correct names (including the prefix).
|||Hi Arnie,
When i created another view and add the 'dbo.' to the name, it gave an error message
"Invalid Indentifier dbo.tr_XXXX_XXX_XxxXxx"
Thanks.
|||Hi Kaybal,
Modify the user that your application uses to have default schema of dbo. and make sure your user account has permissions on the dbo schema.
try this and let us know how you got on.
regards
Jag
|||Hi Jag,
Thanks for the reply, can you put through how to modify the user and have default schema of dbo. Where do i go or what link should i use
thanks
|||Hi Kaybal,
here you go. Let us know how you got on.
USE database_name; ALTER USER user_name WITH DEFAULT_SCHEMA = dbo; GOregards
Jag
Database name not found in the Edit job step in SQL Server Enterprise Manager
I need help. I transferred my database from one server to another by
using attach detach process.I created all the jobs which existed in the
old server in the new server. Everything looked fine but a couple of
jobs is a giving me some problems. When I try to edit the job step I
could see that the database dropdown list doesn't contain the database
name(which I transferred from the old server)
Does anyone have any idea about it.
Thanks
Arun(arunkumar_m2001@.yahoo.com) writes:
> I need help. I transferred my database from one server to another by
> using attach detach process.I created all the jobs which existed in the
> old server in the new server. Everything looked fine but a couple of
> jobs is a giving me some problems. When I try to edit the job step I
> could see that the database dropdown list doesn't contain the database
> name(which I transferred from the old server)
>
> Does anyone have any idea about it.
Have you tried simply closing down Enterprise Manager and starting it
again? I did a quick test, and it appears that the job-step dialogue
caches the database list, so it may not have seen the new database yet.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Database moved - How to change subscriptions
Thanks
Benjamin
I found out, that the problem appears when I move the database from one machine to another. All works fine, but the owner of the SQL-Server-Agent Job seems to cause the problems. On the source-machine it is ASPNET and on destination-machine its SYSTEM, when I manually change it back to ASPNET I can edit/save the subscriptions.
So how do I set the owner of the SQL-Server Agent Jobs programaticly for all Jobs?
Or is my concept of moving the database via backup/restore not the best solution?
Thanks
Benjamin
After you have moved the database to another machine, you could actually go ahead and just delete all the RS generated SQL Agent Jobs before you start up the report server windows service.
On startup of the RS windows service it will automatically recreate the agent jobs if they were deleted.
-- Robert
|||Restarting the Service creates the jobs, thats right, but they have still the wrong owner..On both machines the owner is ASPNET if I create a new subscription (or look at a existing subscription on the test-server). Deleting the Agent-Job and starting the Report-Server Service creates the Agent-Jobs with System as owner!
Even on the test-machine(source) deleting the Agent-Job and restarting ReportingServices produces this error..
I've made a little stored procedure which changes the owners, after running it all works as expected..
Create PROCEDURE [dbo].[sp_AlterOwnerOfAgentJob] ( @.username nvarchar(128))
AS
BEGIN
SET NOCOUNT ON;
declare @.jobname nvarchar(128),@.servername nvarchar(128),@.UserID uniqueidentifier
DECLARE job_cursor CURSOR FOR select name from msdb.dbo.sysjobs
OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @.jobname
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_update_job @.job_name=@.jobname, @.owner_login_name=@.username
FETCH NEXT FROM job_cursor INTO @.jobname
END
CLOSE job_cursor
DEALLOCATE job_cursor
END
GO
exec sp_AlterOwnerOfAgentJob 'SERVERNAME\ASPNET'
GO
Is there any sp to list all jobs? That would make my sp work in further versions..
|||
Thanks for reporting this issue. We will look into this in more detail.
-- Robert
sqlDatabase moved - How to change subscription of another user.
Thanks
Benjamin
I found out, that the problem appears when I move the database from one machine to another. All works fine, but the owner of the SQL-Server-Agent Job seems to cause the problems. On the source-machine it is ASPNET and on destination-machine its SYSTEM, when I manually change it back to ASPNET I can edit/save the subscriptions.
So how do I set the owner of the SQL-Server Agent Jobs programaticly for all Jobs?
Or is my concept of moving the database via backup/restore not the best solution?
Thanks
Benjamin
After you have moved the database to another machine, you could actually go ahead and just delete all the RS generated SQL Agent Jobs before you start up the report server windows service.
On startup of the RS windows service it will automatically recreate the agent jobs if they were deleted.
-- Robert
|||Restarting the Service creates the jobs, thats right, but they have still the wrong owner..On both machines the owner is ASPNET if I create a new subscription (or look at a existing subscription on the test-server). Deleting the Agent-Job and starting the Report-Server Service creates the Agent-Jobs with System as owner!
Even on the test-machine(source) deleting the Agent-Job and restarting ReportingServices produces this error..
I've made a little stored procedure which changes the owners, after running it all works as expected..
Create PROCEDURE [dbo].[sp_AlterOwnerOfAgentJob] ( @.username nvarchar(128))
AS
BEGIN
SET NOCOUNT ON;
declare @.jobname nvarchar(128),@.servername nvarchar(128),@.UserID uniqueidentifier
DECLARE job_cursor CURSOR FOR select name from msdb.dbo.sysjobs
OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @.jobname
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_update_job @.job_name=@.jobname, @.owner_login_name=@.username
FETCH NEXT FROM job_cursor INTO @.jobname
END
CLOSE job_cursor
DEALLOCATE job_cursor
END
GO
exec sp_AlterOwnerOfAgentJob 'SERVERNAME\ASPNET'
GO
Is there any sp to list all jobs? That would make my sp work in further versions..
|||
Thanks for reporting this issue. We will look into this in more detail.
-- Robert
Wednesday, March 21, 2012
Database Migration
was created by installation of an application. On that SQL
server we have created Logins for this particular "Acts"
database so users can use the application. Management has
now asked us to move this database to another server with
a different server name. My question is can I just
backup "Acts" database on the old server and restore it on
the new server. Will that bring over the logins ID and
their info or if it doesn't what would I have to do to get
that info. Or would I have to backup the entire SQL2000
databases(master, acts, model,msdb,norhtwind,pubs,tempdb)
from my old server and than restore all these databases on
my new server.
Any help is appreciated...
thanks,
JayBackup and restore is a good method to migrate a database. You can also use
sp_detach_db and sp_attach_db and copy the actual database files.
Regardless of which method you choose, you will need to create the logins
for the 'Acts' users on your new server and then link them to the users in
your newly migrated database. Look up sp_change_users_login in BOL for
details.
I strongly discourage migrating system databases between servers. If there
are any scheduled jobs, you can script them and apply them to the new server
much easier than you can fix a migrated MSDB database.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Jay" <desiguys@.hotmail.com> wrote in message
news:0df101c35769$5b6d53e0$a301280a@.phx.gbl...
> I have a SQL database called "Acts" on SQL2000 server that
> was created by installation of an application. On that SQL
> server we have created Logins for this particular "Acts"
> database so users can use the application. Management has
> now asked us to move this database to another server with
> a different server name. My question is can I just
> backup "Acts" database on the old server and restore it on
> the new server. Will that bring over the logins ID and
> their info or if it doesn't what would I have to do to get
> that info. Or would I have to backup the entire SQL2000
> databases(master, acts, model,msdb,norhtwind,pubs,tempdb)
> from my old server and than restore all these databases on
> my new server.
>
> Any help is appreciated...
> thanks,
> Jay
>|||These should help:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"Jay" <desiguys@.hotmail.com> wrote in message
news:0df101c35769$5b6d53e0$a301280a@.phx.gbl...
> I have a SQL database called "Acts" on SQL2000 server that
> was created by installation of an application. On that SQL
> server we have created Logins for this particular "Acts"
> database so users can use the application. Management has
> now asked us to move this database to another server with
> a different server name. My question is can I just
> backup "Acts" database on the old server and restore it on
> the new server. Will that bring over the logins ID and
> their info or if it doesn't what would I have to do to get
> that info. Or would I have to backup the entire SQL2000
> databases(master, acts, model,msdb,norhtwind,pubs,tempdb)
> from my old server and than restore all these databases on
> my new server.
>
> Any help is appreciated...
> thanks,
> Jay
>
Monday, March 19, 2012
Database Master Key Error
Hi,
In sys.transmission_queue is showing the transmission_status with error. "The session keys for this conversation could not be created or accessed. The database master key is required for this operation."
But I do have a Master Key in the database, and many External Assemblies depend on that. Is there an option to use the existing master key. How to use the existing key ?
Thanks in advance.
Regards
Babu
In case your conversation spans between two databases, both databases need to have a master key. Also, the database master key has to be encrypted with the service master key. If the service master key encryption of the database master key is missing, you can add it like this:
alter master key add encryption by service master key
HTH,
~ Remus
Hi Remus,
It worked.. Thank you..
Regards
Babu
|||Also had this problem on moving databases between servers using restore. Had to run this after the restore and re enable broker on each restored database.Database Master Key Error
Hi,
In sys.transmission_queue is showing the transmission_status with error. "The session keys for this conversation could not be created or accessed. The database master key is required for this operation."
But I do have a Master Key in the database, and many External Assemblies depend on that. Is there an option to use the existing master key. How to use the existing key ?
Thanks in advance.
Regards
Babu
In case your conversation spans between two databases, both databases need to have a master key. Also, the database master key has to be encrypted with the service master key. If the service master key encryption of the database master key is missing, you can add it like this:
alter master key add encryption by service master key
HTH,
~ Remus
Hi Remus,
It worked.. Thank you..
Regards
Babu
|||Also had this problem on moving databases between servers using restore. Had to run this after the restore and re enable broker on each restored database.Sunday, March 11, 2012
Database Maintenance Plans for Backup
Hello
We created maintenance plans for Backup, we configured as:
1. Backup set expires after 2 days. (but we still see backup files are at the location from day one)
2. There is Overwrite and Append in backup file settings. what eaxactly overwrites means, in case we set up expire the backup set after 2 days.
please advice. Thanks, Jay
If you are using a single backup device (whether it's a tape, file location...it's the same thing), overwrite means it will replace the backup on that device with the current backup. So that device will just hold the one backup. Append means it will add the current backup to the device. That device will hold multiple backups.
If you use a single device, set it to overwrite and the backup has not expired and you have selected the option to check the expiration date of the backup, you will get an error. It is designed to prevent you from accidentally overwritting a backup that you want to keep.
-Sue
Database Maintenance Plans - Not removing Files Older than x days
database on the server and have created a database maintenance plan that
backups up all the database nightly. I have also configured the maintenance
plan to delete back files older than 3 days old but this isn't currently
happening.
I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
seen this problem first. Or if anyone can confirm SP4 fixes this issue.
You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
SP4 on another system to make sure your apps work correctly first, of course.
Have you checked the various logs SQL Server uses? There's a SQL Server Log,
a SQL Server Agent Log, and the Windows Application Event Log. They might
point out what is happening.
That being said, there is an extended stored procedure that Microsoft uses
to delete the files. If the Agent service doesn't have the proper rights to
the subdirectory you might find that to be the issue.
"Gary S" wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
>
|||What is the extended store procedure that's used so that I can check it
hasn't been removed.
"Buck Woody - Microsoft SQL Server Team" wrote:
[vbcol=seagreen]
> You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> SP4 on another system to make sure your apps work correctly first, of course.
> Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> a SQL Server Agent Log, and the Windows Application Event Log. They might
> point out what is happening.
> That being said, there is an extended stored procedure that Microsoft uses
> to delete the files. If the Agent service doesn't have the proper rights to
> the subdirectory you might find that to be the issue.
> "Gary S" wrote:
|||It's sp_delete_file - and it's undocumented, meaning unsupported for direct
use by users. Check this thread:
https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
"Gary S" wrote:
[vbcol=seagreen]
> What is the extended store procedure that's used so that I can check it
> hasn't been removed.
> "Buck Woody - Microsoft SQL Server Team" wrote:
|||Slight correction - that's XP_delete_file. Had an S in there!
"Buck Woody - Microsoft SQL Server Team" wrote:
[vbcol=seagreen]
> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
|||On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
There are a couple of common causes for this:
1. The maintenance plan is configured to do transaction log backups,
but one of the databases included is in Simple mode. The plan fails
when it hits this database, and never reaches the "cleanup" step.
2. One of the files to be deleted is "in use" by another process,
such as a tape backup, causing the cleanup process to fail.
Right-click on the maintenance plan itself (not the Agent job), look
at the history, you should be able to get an actual error message from
there.
|||You have to be a sysadmin to run this stored proc, even in SP2 CTP. I have
been told a fix will be raised for a later service pack.
Chris
"Buck Woody - Microsoft SQL Server Team"
<BuckWoodyMicrosoftSQLServerTeam@.discussions.micro soft.com> wrote in message
news:0751636B-0F16-40EA-BAE8-8ADE57217570@.microsoft.com...[vbcol=seagreen]
> It's sp_delete_file - and it's undocumented, meaning unsupported for
> direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
|||Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
big fan of database mainatenance plans (perfer setting up the jobs
individual) so as I'm not 100% certain why the databases are set to simple
I'll create seperate backup jobs.
"Tracy McKibben" wrote:
> On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> There are a couple of common causes for this:
> 1. The maintenance plan is configured to do transaction log backups,
> but one of the databases included is in Simple mode. The plan fails
> when it hits this database, and never reaches the "cleanup" step.
> 2. One of the files to be deleted is "in use" by another process,
> such as a tape backup, causing the cleanup process to fail.
> Right-click on the maintenance plan itself (not the Agent job), look
> at the history, you should be able to get an actual error message from
> there.
>
|||On Feb 15, 5:13 am, Gary S <G...@.discussions.microsoft.com> wrote:
> Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
> big fan of database mainatenance plans (perfer setting up the jobs
> individual) so as I'm not 100% certain why the databases are set to simple
> I'll create seperate backup jobs.
>
Here's a script of mine that might help you out:
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html
Database Maintenance Plans - Not removing Files Older than x days
database on the server and have created a database maintenance plan that
backups up all the database nightly. I have also configured the maintenance
plan to delete back files older than 3 days old but this isn't currently
happening.
I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
seen this problem first. Or if anyone can confirm SP4 fixes this issue.You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
SP4 on another system to make sure your apps work correctly first, of course.
Have you checked the various logs SQL Server uses? There's a SQL Server Log,
a SQL Server Agent Log, and the Windows Application Event Log. They might
point out what is happening.
That being said, there is an extended stored procedure that Microsoft uses
to delete the files. If the Agent service doesn't have the proper rights to
the subdirectory you might find that to be the issue.
"Gary S" wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
>|||What is the extended store procedure that's used so that I can check it
hasn't been removed.
"Buck Woody - Microsoft SQL Server Team" wrote:
> You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> SP4 on another system to make sure your apps work correctly first, of course.
> Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> a SQL Server Agent Log, and the Windows Application Event Log. They might
> point out what is happening.
> That being said, there is an extended stored procedure that Microsoft uses
> to delete the files. If the Agent service doesn't have the proper rights to
> the subdirectory you might find that to be the issue.
> "Gary S" wrote:
> > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > database on the server and have created a database maintenance plan that
> > backups up all the database nightly. I have also configured the maintenance
> > plan to delete back files older than 3 days old but this isn't currently
> > happening.
> >
> > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> >
> >|||It's sp_delete_file - and it's undocumented, meaning unsupported for direct
use by users. Check this thread:
https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
"Gary S" wrote:
> What is the extended store procedure that's used so that I can check it
> hasn't been removed.
> "Buck Woody - Microsoft SQL Server Team" wrote:
> > You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> > SP4 on another system to make sure your apps work correctly first, of course.
> >
> > Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> > a SQL Server Agent Log, and the Windows Application Event Log. They might
> > point out what is happening.
> >
> > That being said, there is an extended stored procedure that Microsoft uses
> > to delete the files. If the Agent service doesn't have the proper rights to
> > the subdirectory you might find that to be the issue.
> >
> > "Gary S" wrote:
> >
> > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > > database on the server and have created a database maintenance plan that
> > > backups up all the database nightly. I have also configured the maintenance
> > > plan to delete back files older than 3 days old but this isn't currently
> > > happening.
> > >
> > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> > >
> > >|||Slight correction - that's XP_delete_file. Had an S in there!
"Buck Woody - Microsoft SQL Server Team" wrote:
> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
> > What is the extended store procedure that's used so that I can check it
> > hasn't been removed.
> >
> > "Buck Woody - Microsoft SQL Server Team" wrote:
> >
> > > You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> > > SP4 on another system to make sure your apps work correctly first, of course.
> > >
> > > Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> > > a SQL Server Agent Log, and the Windows Application Event Log. They might
> > > point out what is happening.
> > >
> > > That being said, there is an extended stored procedure that Microsoft uses
> > > to delete the files. If the Agent service doesn't have the proper rights to
> > > the subdirectory you might find that to be the issue.
> > >
> > > "Gary S" wrote:
> > >
> > > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > > > database on the server and have created a database maintenance plan that
> > > > backups up all the database nightly. I have also configured the maintenance
> > > > plan to delete back files older than 3 days old but this isn't currently
> > > > happening.
> > > >
> > > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > > > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> > > >
> > > >|||On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
There are a couple of common causes for this:
1. The maintenance plan is configured to do transaction log backups,
but one of the databases included is in Simple mode. The plan fails
when it hits this database, and never reaches the "cleanup" step.
2. One of the files to be deleted is "in use" by another process,
such as a tape backup, causing the cleanup process to fail.
Right-click on the maintenance plan itself (not the Agent job), look
at the history, you should be able to get an actual error message from
there.|||You have to be a sysadmin to run this stored proc, even in SP2 CTP. I have
been told a fix will be raised for a later service pack.
Chris
"Buck Woody - Microsoft SQL Server Team"
<BuckWoodyMicrosoftSQLServerTeam@.discussions.microsoft.com> wrote in message
news:0751636B-0F16-40EA-BAE8-8ADE57217570@.microsoft.com...
> It's sp_delete_file - and it's undocumented, meaning unsupported for
> direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
>> What is the extended store procedure that's used so that I can check it
>> hasn't been removed.
>> "Buck Woody - Microsoft SQL Server Team" wrote:
>> > You'll want to apply SP4 anyway, for lots of other reasons. Test the
>> > setup of
>> > SP4 on another system to make sure your apps work correctly first, of
>> > course.
>> >
>> > Have you checked the various logs SQL Server uses? There's a SQL Server
>> > Log,
>> > a SQL Server Agent Log, and the Windows Application Event Log. They
>> > might
>> > point out what is happening.
>> >
>> > That being said, there is an extended stored procedure that Microsoft
>> > uses
>> > to delete the files. If the Agent service doesn't have the proper
>> > rights to
>> > the subdirectory you might find that to be the issue.
>> >
>> > "Gary S" wrote:
>> >
>> > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a
>> > > number of
>> > > database on the server and have created a database maintenance plan
>> > > that
>> > > backups up all the database nightly. I have also configured the
>> > > maintenance
>> > > plan to delete back files older than 3 days old but this isn't
>> > > currently
>> > > happening.
>> > >
>> > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone
>> > > else has
>> > > seen this problem first. Or if anyone can confirm SP4 fixes this
>> > > issue.
>> > >
>> > >|||> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users.
Buck,
Above is for 2005. For 2000, the files are removed by sqlmaint.exe (which is called by xp_sqlmaint
if the plan is created by the wizard).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Buck Woody - Microsoft SQL Server Team" <BuckWoodyMicrosoftSQLServerTeam@.discussions.microsoft.com>
wrote in message news:0751636B-0F16-40EA-BAE8-8ADE57217570@.microsoft.com...
> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
>> What is the extended store procedure that's used so that I can check it
>> hasn't been removed.
>> "Buck Woody - Microsoft SQL Server Team" wrote:
>> > You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
>> > SP4 on another system to make sure your apps work correctly first, of course.
>> >
>> > Have you checked the various logs SQL Server uses? There's a SQL Server Log,
>> > a SQL Server Agent Log, and the Windows Application Event Log. They might
>> > point out what is happening.
>> >
>> > That being said, there is an extended stored procedure that Microsoft uses
>> > to delete the files. If the Agent service doesn't have the proper rights to
>> > the subdirectory you might find that to be the issue.
>> >
>> > "Gary S" wrote:
>> >
>> > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
>> > > database on the server and have created a database maintenance plan that
>> > > backups up all the database nightly. I have also configured the maintenance
>> > > plan to delete back files older than 3 days old but this isn't currently
>> > > happening.
>> > >
>> > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
>> > > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
>> > >
>> > >|||Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
big fan of database mainatenance plans (perfer setting up the jobs
individual) so as I'm not 100% certain why the databases are set to simple
I'll create seperate backup jobs.
"Tracy McKibben" wrote:
> On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > database on the server and have created a database maintenance plan that
> > backups up all the database nightly. I have also configured the maintenance
> > plan to delete back files older than 3 days old but this isn't currently
> > happening.
> >
> > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> There are a couple of common causes for this:
> 1. The maintenance plan is configured to do transaction log backups,
> but one of the databases included is in Simple mode. The plan fails
> when it hits this database, and never reaches the "cleanup" step.
> 2. One of the files to be deleted is "in use" by another process,
> such as a tape backup, causing the cleanup process to fail.
> Right-click on the maintenance plan itself (not the Agent job), look
> at the history, you should be able to get an actual error message from
> there.
>|||On Feb 15, 5:13 am, Gary S <G...@.discussions.microsoft.com> wrote:
> Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
> big fan of database mainatenance plans (perfer setting up the jobs
> individual) so as I'm not 100% certain why the databases are set to simple
> I'll create seperate backup jobs.
>
Here's a script of mine that might help you out:
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html
Database Maintenance Plans - Not removing Files Older than x days
f
database on the server and have created a database maintenance plan that
backups up all the database nightly. I have also configured the maintenance
plan to delete back files older than 3 days old but this isn't currently
happening.
I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
seen this problem first. Or if anyone can confirm SP4 fixes this issue.You'll want to apply SP4 anyway, for lots of other reasons. Test the setup o
f
SP4 on another system to make sure your apps work correctly first, of course
.
Have you checked the various logs SQL Server uses? There's a SQL Server Log,
a SQL Server Agent Log, and the Windows Application Event Log. They might
point out what is happening.
That being said, there is an extended stored procedure that Microsoft uses
to delete the files. If the Agent service doesn't have the proper rights to
the subdirectory you might find that to be the issue.
"Gary S" wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number
of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenanc
e
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else h
as
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
>|||On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number
of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenanc
e
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else h
as
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
There are a couple of common causes for this:
1. The maintenance plan is configured to do transaction log backups,
but one of the databases included is in Simple mode. The plan fails
when it hits this database, and never reaches the "cleanup" step.
2. One of the files to be deleted is "in use" by another process,
such as a tape backup, causing the cleanup process to fail.
Right-click on the maintenance plan itself (not the Agent job), look
at the history, you should be able to get an actual error message from
there.
DataBase Maintenance Plan(DMP) Questions
I have few Qs..
1) I created DMP and chose all database for backup. BUt master db doesnt get
backup. Any idea?
2) if in each and every database i checked the "update statistics" options,
do i need to check "reorganize data and index pages" in the DMP wizard? are
they different? what way they are different?
3) what's the best method to use for email.SQLMail or Exchange? Any good
link to configure the exchange and outlook to send emails?
4) Finally, is there any good links that shows step by step on restore
process? i am blur , esp, at the backup the transaction log and restoring it
back when the database is corrupted... (looking for up to the time restore)
Thanks a lot guys!
Hi
I don't really use maintenance plans as I prefer to write my own, but I will
try to answer inline:
"rupart" wrote:
> Hello guys,
> I have few Qs..
> 1) I created DMP and chose all database for backup. BUt master db doesnt get
> backup. Any idea?
I would expect the "All Databases" option to do what it says.
> 2) if in each and every database i checked the "update statistics" options,
> do i need to check "reorganize data and index pages" in the DMP wizard? are
> they different? what way they are different?
I believe the reorganize data and index pages does a DBCC DBREINDEX
(profiling the plan when it is running will show what it does). This is very
important as it will take out fragmentation in the pages and therefore reduce
the number of reads you need to get the data.
SQL Server keeps statistics about the distribution of the key values in each
index and uses these statistics to determine which index(es) to use in query
processing. Query optimization depends on the accuracy of the information,
therefore keeping these up-to-date is important.
> 3) what's the best method to use for email.SQLMail or Exchange? Any good
> link to configure the exchange and outlook to send emails?
http://msdn.microsoft.com/library/de...rverE-mail.asp
More links:
http://www.aspfaq.com/show.asp?id=2403
Alternatively you may want to use SMTP:
http://www.sqldev.net/xp/xpsmtp.htm
> 4) Finally, is there any good links that shows step by step on restore
> process? i am blur , esp, at the backup the transaction log and restoring it
> back when the database is corrupted... (looking for up to the time restore)
>
The best place start when requiring information on SQL server is usually
Books online. Time spent reading this will usually pay itself back
substantially in the future.
The latest version can be downloaded from:
http://www.microsoft.com/sql/techinf...2000/books.asp
Online copies can be found at
http://msdn.microsoft.com/library/de...asp?frame=true
e.g the restore command and examples at:
http://msdn.microsoft.com/library/de...asp?frame=true
> Thanks a lot guys!
>
HTH
John
|||Before turning out automatic fragmentation remoal in a maintenance plan, I
recommend you read the whitepaper below which explains when and how it is
best to remove fragmentation, and when it simply doesn't matter.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:92DC9AC7-705F-4444-B84B-793BBF4F5250@.microsoft.com...
> Hi
> I don't really use maintenance plans as I prefer to write my own, but I
will[vbcol=seagreen]
> try to answer inline:
> "rupart" wrote:
get[vbcol=seagreen]
> I would expect the "All Databases" option to do what it says.
options,[vbcol=seagreen]
are
> I believe the reorganize data and index pages does a DBCC DBREINDEX
> (profiling the plan when it is running will show what it does). This is
very
> important as it will take out fragmentation in the pages and therefore
reduce
> the number of reads you need to get the data.
> SQL Server keeps statistics about the distribution of the key values in
each
> index and uses these statistics to determine which index(es) to use in
query
> processing. Query optimization depends on the accuracy of the
information,
> therefore keeping these up-to-date is important.
>
>
http://msdn.microsoft.com/library/de...rverE-mail.asp[vbcol=seagreen]
> More links:
> http://www.aspfaq.com/show.asp?id=2403
> Alternatively you may want to use SMTP:
> http://www.sqldev.net/xp/xpsmtp.htm
restoring it[vbcol=seagreen]
restore)
> The best place start when requiring information on SQL server is usually
> Books online. Time spent reading this will usually pay itself back
> substantially in the future.
> The latest version can be downloaded from:
> http://www.microsoft.com/sql/techinf...2000/books.asp
> Online copies can be found at
>
http://msdn.microsoft.com/library/de...asp?frame=true
> e.g the restore command and examples at:
>
http://msdn.microsoft.com/library/de...asp?frame=true
>
> HTH
> John
DataBase Maintenance Plan(DMP) Questions
I have few Qs..
1) I created DMP and chose all database for backup. BUt master db doesnt get
backup. Any idea?
2) if in each and every database i checked the "update statistics" options,
do i need to check "reorganize data and index pages" in the DMP wizard? are
they different? what way they are different?
3) what's the best method to use for email.SQLMail or Exchange? Any good
link to configure the exchange and outlook to send emails?
4) Finally, is there any good links that shows step by step on restore
process? i am blur , esp, at the backup the transaction log and restoring it
back when the database is corrupted... (looking for up to the time restore)
Thanks a lot guys!Hi
I don't really use maintenance plans as I prefer to write my own, but I will
try to answer inline:
"rupart" wrote:
> Hello guys,
> I have few Qs..
> 1) I created DMP and chose all database for backup. BUt master db doesnt g
et
> backup. Any idea?
I would expect the "All Databases" option to do what it says.
> 2) if in each and every database i checked the "update statistics" options
,
> do i need to check "reorganize data and index pages" in the DMP wizard? ar
e
> they different? what way they are different?
I believe the reorganize data and index pages does a DBCC DBREINDEX
(profiling the plan when it is running will show what it does). This is very
important as it will take out fragmentation in the pages and therefore reduc
e
the number of reads you need to get the data.
SQL Server keeps statistics about the distribution of the key values in each
index and uses these statistics to determine which index(es) to use in query
processing. Query optimization depends on the accuracy of the information,
therefore keeping these up-to-date is important.
> 3) what's the best method to use for email.SQLMail or Exchange? Any good
> link to configure the exchange and outlook to send emails?
http://msdn.microsoft.com/library/d...erverE-mail.asp
More links:
http://www.aspfaq.com/show.asp?id=2403
Alternatively you may want to use SMTP:
http://www.sqldev.net/xp/xpsmtp.htm
> 4) Finally, is there any good links that shows step by step on restore
> process? i am blur , esp, at the backup the transaction log and restoring
it
> back when the database is corrupted... (looking for up to the time restore
)
>
The best place start when requiring information on SQL server is usually
Books online. Time spent reading this will usually pay itself back
substantially in the future.
The latest version can be downloaded from:
http://www.microsoft.com/sql/techin.../2000/books.asp
Online copies can be found at
http://msdn.microsoft.com/library/d...asp?frame=true
e.g the restore command and examples at:
http://msdn.microsoft.com/library/d...asp?frame=true
> Thanks a lot guys!
>
HTH
John|||Before turning out automatic fragmentation remoal in a maintenance plan, I
recommend you read the whitepaper below which explains when and how it is
best to remove fragmentation, and when it simply doesn't matter.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:92DC9AC7-705F-4444-B84B-793BBF4F5250@.microsoft.com...
> Hi
> I don't really use maintenance plans as I prefer to write my own, but I
will
> try to answer inline:
> "rupart" wrote:
>
get[vbcol=seagreen]
> I would expect the "All Databases" option to do what it says.
>
options,[vbcol=seagreen]
are[vbcol=seagreen]
> I believe the reorganize data and index pages does a DBCC DBREINDEX
> (profiling the plan when it is running will show what it does). This is
very
> important as it will take out fragmentation in the pages and therefore
reduce
> the number of reads you need to get the data.
> SQL Server keeps statistics about the distribution of the key values in
each
> index and uses these statistics to determine which index(es) to use in
query
> processing. Query optimization depends on the accuracy of the
information,
> therefore keeping these up-to-date is important.
>
>
http://msdn.microsoft.com/library/d...ail.asp
> More links:
> http://www.aspfaq.com/show.asp?id=2403
> Alternatively you may want to use SMTP:
> http://www.sqldev.net/xp/xpsmtp.htm
>
restoring it[vbcol=seagreen]
restore)[vbcol=seagreen]
> The best place start when requiring information on SQL server is usually
> Books online. Time spent reading this will usually pay itself back
> substantially in the future.
> The latest version can be downloaded from:
> http://www.microsoft.com/sql/techin.../2000/books.asp
> Online copies can be found at
>
http://msdn.microsoft.com/library/d...me=tr
ue
> e.g the restore command and examples at:
>
http://msdn.microsoft.com/library/d...me=t
rue
>
> HTH
> John
DataBase Maintenance Plan(DMP) Questions
I have few Qs..
1) I created DMP and chose all database for backup. BUt master db doesnt get
backup. Any idea?
2) if in each and every database i checked the "update statistics" options,
do i need to check "reorganize data and index pages" in the DMP wizard? are
they different? what way they are different?
3) what's the best method to use for email.SQLMail or Exchange? Any good
link to configure the exchange and outlook to send emails?
4) Finally, is there any good links that shows step by step on restore
process? i am blur , esp, at the backup the transaction log and restoring it
back when the database is corrupted... (looking for up to the time restore)
Thanks a lot guys!Hi
I don't really use maintenance plans as I prefer to write my own, but I will
try to answer inline:
"rupart" wrote:
> Hello guys,
> I have few Qs..
> 1) I created DMP and chose all database for backup. BUt master db doesnt get
> backup. Any idea?
I would expect the "All Databases" option to do what it says.
> 2) if in each and every database i checked the "update statistics" options,
> do i need to check "reorganize data and index pages" in the DMP wizard? are
> they different? what way they are different?
I believe the reorganize data and index pages does a DBCC DBREINDEX
(profiling the plan when it is running will show what it does). This is very
important as it will take out fragmentation in the pages and therefore reduce
the number of reads you need to get the data.
SQL Server keeps statistics about the distribution of the key values in each
index and uses these statistics to determine which index(es) to use in query
processing. Query optimization depends on the accuracy of the information,
therefore keeping these up-to-date is important.
> 3) what's the best method to use for email.SQLMail or Exchange? Any good
> link to configure the exchange and outlook to send emails?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnovba01/html/SQLServerE-mail.asp
More links:
http://www.aspfaq.com/show.asp?id=2403
Alternatively you may want to use SMTP:
http://www.sqldev.net/xp/xpsmtp.htm
> 4) Finally, is there any good links that shows step by step on restore
> process? i am blur , esp, at the backup the transaction log and restoring it
> back when the database is corrupted... (looking for up to the time restore)
>
The best place start when requiring information on SQL server is usually
Books online. Time spent reading this will usually pay itself back
substantially in the future.
The latest version can be downloaded from:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Online copies can be found a
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp?frame=true
e.g the restore command and examples at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp?frame=true
> Thanks a lot guys!
>
HTH
John|||Before turning out automatic fragmentation remoal in a maintenance plan, I
recommend you read the whitepaper below which explains when and how it is
best to remove fragmentation, and when it simply doesn't matter.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:92DC9AC7-705F-4444-B84B-793BBF4F5250@.microsoft.com...
> Hi
> I don't really use maintenance plans as I prefer to write my own, but I
will
> try to answer inline:
> "rupart" wrote:
> > Hello guys,
> > I have few Qs..
> >
> > 1) I created DMP and chose all database for backup. BUt master db doesnt
get
> > backup. Any idea?
> I would expect the "All Databases" option to do what it says.
> >
> > 2) if in each and every database i checked the "update statistics"
options,
> > do i need to check "reorganize data and index pages" in the DMP wizard?
are
> > they different? what way they are different?
> I believe the reorganize data and index pages does a DBCC DBREINDEX
> (profiling the plan when it is running will show what it does). This is
very
> important as it will take out fragmentation in the pages and therefore
reduce
> the number of reads you need to get the data.
> SQL Server keeps statistics about the distribution of the key values in
each
> index and uses these statistics to determine which index(es) to use in
query
> processing. Query optimization depends on the accuracy of the
information,
> therefore keeping these up-to-date is important.
> >
> > 3) what's the best method to use for email.SQLMail or Exchange? Any good
> > link to configure the exchange and outlook to send emails?
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnovba01/html/SQLServerE-mail.asp
> More links:
> http://www.aspfaq.com/show.asp?id=2403
> Alternatively you may want to use SMTP:
> http://www.sqldev.net/xp/xpsmtp.htm
> >
> > 4) Finally, is there any good links that shows step by step on restore
> > process? i am blur , esp, at the backup the transaction log and
restoring it
> > back when the database is corrupted... (looking for up to the time
restore)
> >
> The best place start when requiring information on SQL server is usually
> Books online. Time spent reading this will usually pay itself back
> substantially in the future.
> The latest version can be downloaded from:
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
> Online copies can be found at
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp?frame=true
> e.g the restore command and examples at:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp?frame=true
> > Thanks a lot guys!
> >
> >
> HTH
> John
Thursday, March 8, 2012
Database maintenance plan backup
set to back up the transaction logs of several specified databases. I open
up the backup database task, then choose <select one or more> from the
Database(s) prompt. When the drop down window opens, I select the radio box
for "these databases", and I check the databases that I want. I save the
database maintenance plan and run it and it works fine. If I open up the
backup database task again, and go to see what databases I have selected,
none are showing as selected. If I save the database maintenance plan and
run it, it fails with the following message, which does not tell me much:
Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
100% complete End Progress DTExec: The package execution returned
DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
1.437 seconds. The package execution failed. The step failed.
This message contrasts with the text file report that shows:
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
Report was generated on "pdcnt23".
Maintenance Plan: MaintenancePlan
Duration: 00:00:00
Status: Succeeded.
Details:
If I go back into the backup task and reselect the databases and save the
maintenance plan and run it, everything works fine.
This doesn't work as I would expect it to. Is there something I need to do?
Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
Thanks,
Paul
Paul,
Losing the database is a known problem. MS is looking into it for a later
build of SQL2005.
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
> I've created a database maintenance plan with backup database task which
> is
> set to back up the transaction logs of several specified databases. I
> open
> up the backup database task, then choose <select one or more> from the
> Database(s) prompt. When the drop down window opens, I select the radio
> box
> for "these databases", and I check the databases that I want. I save the
> database maintenance plan and run it and it works fine. If I open up the
> backup database task again, and go to see what databases I have selected,
> none are showing as selected. If I save the database maintenance plan and
> run it, it fails with the following message, which does not tell me much:
> Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
> Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
> Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
> 2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
> Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
> 100% complete End Progress DTExec: The package execution returned
> DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
> 1.437 seconds. The package execution failed. The step failed.
> This message contrasts with the text file report that shows:
> NEW COMPONENT OUTPUT
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
> Report was generated on "pdcnt23".
> Maintenance Plan: MaintenancePlan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> If I go back into the backup task and reselect the databases and save the
> maintenance plan and run it, everything works fine.
> This doesn't work as I would expect it to. Is there something I need to
> do?
> Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
> Thanks,
> Paul
>
|||Thanks Chris.
I had a look around to try and find information about it. Where would I
find more information about it?
Thanks,
Paul
"Chris Wood" wrote:
> Paul,
> Losing the database is a known problem. MS is looking into it for a later
> build of SQL2005.
> Chris
> "PT" <PT@.discussions.microsoft.com> wrote in message
> news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
>
>
|||Paul,
Signon to Microsoft Connect:- https://connect.microsoft.com/default.aspx and
register for SQL
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:4C86DAAF-11E8-4CA7-B740-A3CF4BF32C69@.microsoft.com...[vbcol=seagreen]
> Thanks Chris.
> I had a look around to try and find information about it. Where would I
> find more information about it?
> Thanks,
> Paul
> "Chris Wood" wrote:
Database maintenance plan backup
set to back up the transaction logs of several specified databases. I open
up the backup database task, then choose <select one or more> from the
Database(s) prompt. When the drop down window opens, I select the radio box
for "these databases", and I check the databases that I want. I save the
database maintenance plan and run it and it works fine. If I open up the
backup database task again, and go to see what databases I have selected,
none are showing as selected. If I save the database maintenance plan and
run it, it fails with the following message, which does not tell me much:
Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
100% complete End Progress DTExec: The package execution returned
DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
1.437 seconds. The package execution failed. The step failed.
This message contrasts with the text file report that shows:
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
Report was generated on "pdcnt23".
Maintenance Plan: MaintenancePlan
Duration: 00:00:00
Status: Succeeded.
Details:
If I go back into the backup task and reselect the databases and save the
maintenance plan and run it, everything works fine.
This doesn't work as I would expect it to. Is there something I need to do?
Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
Thanks,
PaulPaul,
Losing the database is a known problem. MS is looking into it for a later
build of SQL2005.
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
> I've created a database maintenance plan with backup database task which
> is
> set to back up the transaction logs of several specified databases. I
> open
> up the backup database task, then choose <select one or more> from the
> Database(s) prompt. When the drop down window opens, I select the radio
> box
> for "these databases", and I check the databases that I want. I save the
> database maintenance plan and run it and it works fine. If I open up the
> backup database task again, and go to see what databases I have selected,
> none are showing as selected. If I save the database maintenance plan and
> run it, it fails with the following message, which does not tell me much:
> Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
> Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
> Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
> 2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
> Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
> 100% complete End Progress DTExec: The package execution returned
> DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
> 1.437 seconds. The package execution failed. The step failed.
> This message contrasts with the text file report that shows:
> NEW COMPONENT OUTPUT
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
> Report was generated on "pdcnt23".
> Maintenance Plan: MaintenancePlan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> If I go back into the backup task and reselect the databases and save the
> maintenance plan and run it, everything works fine.
> This doesn't work as I would expect it to. Is there something I need to
> do?
> Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
> Thanks,
> Paul
>|||Thanks Chris.
I had a look around to try and find information about it. Where would I
find more information about it?
Thanks,
Paul
"Chris Wood" wrote:
> Paul,
> Losing the database is a known problem. MS is looking into it for a later
> build of SQL2005.
> Chris
> "PT" <PT@.discussions.microsoft.com> wrote in message
> news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
> > I've created a database maintenance plan with backup database task which
> > is
> > set to back up the transaction logs of several specified databases. I
> > open
> > up the backup database task, then choose <select one or more> from the
> > Database(s) prompt. When the drop down window opens, I select the radio
> > box
> > for "these databases", and I check the databases that I want. I save the
> > database maintenance plan and run it and it works fine. If I open up the
> > backup database task again, and go to see what databases I have selected,
> > none are showing as selected. If I save the database maintenance plan and
> > run it, it fails with the following message, which does not tell me much:
> >
> > Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
> > Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
> > Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
> > 2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
> > Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
> > 100% complete End Progress DTExec: The package execution returned
> > DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
> > 1.437 seconds. The package execution failed. The step failed.
> >
> > This message contrasts with the text file report that shows:
> >
> > NEW COMPONENT OUTPUT
> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
> > Report was generated on "pdcnt23".
> > Maintenance Plan: MaintenancePlan
> > Duration: 00:00:00
> > Status: Succeeded.
> > Details:
> >
> > If I go back into the backup task and reselect the databases and save the
> > maintenance plan and run it, everything works fine.
> >
> > This doesn't work as I would expect it to. Is there something I need to
> > do?
> >
> > Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
> >
> > Thanks,
> >
> > Paul
> >
>
>|||Paul,
Signon to Microsoft Connect:- https://connect.microsoft.com/default.aspx and
register for SQL
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:4C86DAAF-11E8-4CA7-B740-A3CF4BF32C69@.microsoft.com...
> Thanks Chris.
> I had a look around to try and find information about it. Where would I
> find more information about it?
> Thanks,
> Paul
> "Chris Wood" wrote:
>> Paul,
>> Losing the database is a known problem. MS is looking into it for a later
>> build of SQL2005.
>> Chris
>> "PT" <PT@.discussions.microsoft.com> wrote in message
>> news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
>> > I've created a database maintenance plan with backup database task
>> > which
>> > is
>> > set to back up the transaction logs of several specified databases. I
>> > open
>> > up the backup database task, then choose <select one or more> from the
>> > Database(s) prompt. When the drop down window opens, I select the radio
>> > box
>> > for "these databases", and I check the databases that I want. I save
>> > the
>> > database maintenance plan and run it and it works fine. If I open up
>> > the
>> > backup database task again, and go to see what databases I have
>> > selected,
>> > none are showing as selected. If I save the database maintenance plan
>> > and
>> > run it, it fails with the following message, which does not tell me
>> > much:
>> >
>> > Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server
>> > Execute
>> > Package Utility Version 9.00.3042.00 for 32-bit Copyright (C)
>> > Microsoft
>> > Corp 1984-2005. All rights reserved. Started: 10:58:14 AM
>> > Progress:
>> > 2007-11-16 10:58:15.67 Source:
>> > {0EDA7F25-ED67-4796-9399-668FB2506E5F}
>> > Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE
>> > msdb..sp".:
>> > 100% complete End Progress DTExec: The package execution returned
>> > DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM
>> > Elapsed:
>> > 1.437 seconds. The package execution failed. The step failed.
>> >
>> > This message contrasts with the text file report that shows:
>> >
>> > NEW COMPONENT OUTPUT
>> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
>> > Report was generated on "pdcnt23".
>> > Maintenance Plan: MaintenancePlan
>> > Duration: 00:00:00
>> > Status: Succeeded.
>> > Details:
>> >
>> > If I go back into the backup task and reselect the databases and save
>> > the
>> > maintenance plan and run it, everything works fine.
>> >
>> > This doesn't work as I would expect it to. Is there something I need
>> > to
>> > do?
>> >
>> > Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
>> >
>> > Thanks,
>> >
>> > Paul
>> >
>>
Database maintenance plan backup
set to back up the transaction logs of several specified databases. I open
up the backup database task, then choose <select one or more> from the
Database(s) prompt. When the drop down window opens, I select the radio box
for "these databases", and I check the databases that I want. I save the
database maintenance plan and run it and it works fine. If I open up the
backup database task again, and go to see what databases I have selected,
none are showing as selected. If I save the database maintenance plan and
run it, it fails with the following message, which does not tell me much:
Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5
F}
Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
100% complete End Progress DTExec: The package execution returned
DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
1.437 seconds. The package execution failed. The step failed.
This message contrasts with the text file report that shows:
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
Report was generated on "pdcnt23".
Maintenance Plan: MaintenancePlan
Duration: 00:00:00
Status: Succeeded.
Details:
If I go back into the backup task and reselect the databases and save the
maintenance plan and run it, everything works fine.
This doesn't work as I would expect it to. Is there something I need to do?
Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
Thanks,
PaulPaul,
Losing the database is a known problem. MS is looking into it for a later
build of SQL2005.
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
> I've created a database maintenance plan with backup database task which
> is
> set to back up the transaction logs of several specified databases. I
> open
> up the backup database task, then choose <select one or more> from the
> Database(s) prompt. When the drop down window opens, I select the radio
> box
> for "these databases", and I check the databases that I want. I save the
> database maintenance plan and run it and it works fine. If I open up the
> backup database task again, and go to see what databases I have selected,
> none are showing as selected. If I save the database maintenance plan and
> run it, it fails with the following message, which does not tell me much:
> Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
> Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
> Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
> 2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506
E5F}
> Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
> 100% complete End Progress DTExec: The package execution returned
> DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
> 1.437 seconds. The package execution failed. The step failed.
> This message contrasts with the text file report that shows:
> NEW COMPONENT OUTPUT
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
> Report was generated on "pdcnt23".
> Maintenance Plan: MaintenancePlan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> If I go back into the backup task and reselect the databases and save the
> maintenance plan and run it, everything works fine.
> This doesn't work as I would expect it to. Is there something I need to
> do?
> Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
> Thanks,
> Paul
>|||Thanks Chris.
I had a look around to try and find information about it. Where would I
find more information about it?
Thanks,
Paul
"Chris Wood" wrote:
> Paul,
> Losing the database is a known problem. MS is looking into it for a later
> build of SQL2005.
> Chris
> "PT" <PT@.discussions.microsoft.com> wrote in message
> news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
>
>|||Paul,
Signon to Microsoft Connect:- https://connect.microsoft.com/default.aspx and
register for SQL
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:4C86DAAF-11E8-4CA7-B740-A3CF4BF32C69@.microsoft.com...[vbcol=seagreen]
> Thanks Chris.
> I had a look around to try and find information about it. Where would I
> find more information about it?
> Thanks,
> Paul
> "Chris Wood" wrote:
>
database maintenance plan - differential backup
1. Full database backup once each night
2. Transaction Log backups every 15 minutes
However, I would also like to implement a differential backup perhaps 4 times per day. This would save me from having to restore dozens of transaction Log backups in the event of a failure towards the end of a day
I know that the database maintenance plan does not support differential backups. Is there any plans to add this in the future?
Also, if I create a separate job for differential backups, how do avoid the T-Log backup and differential backup executing at the same time without increasing the T-Log interval?
What happens if both the T-Log and differential backup occur at the same time?
Scott,
I basically asked the same question on 07/09 with subject "backup sets to
named device". I solved my own problem like this:
Job A runs once daily at zero hours.
Step 1 creates a backup device pointing to local file with sp_addumpdevice.
Step 2 performs full backup with BACKUP DATABASE.
Step 3 removes old backup devices with sp_dropdevice.
Job B runs daily every 4 hours.
Step 1 performs diff backup with BACKUP DATABASE.
Job C runs daily every 10 minutes.
Step 1 performs log backup with BACKUP DATABASE.
Each job uses datetime and string functions to create the device and file
names, such as NorthWind_YYYYMMDD and NorthWind_db_YYYYMMDD.bak. For step 3
of Job A, I query sysdevices to get the names of old devices. You can
determine how many devices and associated backups remain based on the query.
I end up with complete backups for each day encapsulated in a single file
which gets written to tape and stored offsite.
It's working like a charm. I'll post the detailed t-sql if you need it.
Anthony
|||Scott,
Sorry about not really answering your questions in the previous post. I
have had backup jobs executing at the same time and didn't see any problems.
But for my previous suggestion, I have full at zero hours, logs every 10
minutes starting at 00:05 to 23:59, and diffs every 4 hours starting at
04:00 to 23:59. Now none of the jobs intersect.
I haven't looked at Yukon. But since it's .Net based, you will probably
be able to code you're own maint plans.
Anthony
|||Thanks Anthony, I would love to see the T-SQL. It sounds very creative.
My only concern is if both the transaction log and the differential backups execute at the same time, will it cause conflicts somehow.
"Anthony" wrote:
> Scott,
> Sorry about not really answering your questions in the previous post. I
> have had backup jobs executing at the same time and didn't see any problems.
> But for my previous suggestion, I have full at zero hours, logs every 10
> minutes starting at 00:05 to 23:59, and diffs every 4 hours starting at
> 04:00 to 23:59. Now none of the jobs intersect.
> I haven't looked at Yukon. But since it's .Net based, you will probably
> be able to code you're own maint plans.
> Anthony
>
>
database maintenance plan - differential backup
e database maintenance plan I created 2 jobs:
1. Full database backup once each night
2. Transaction Log backups every 15 minutes
However, I would also like to implement a differential backup perhaps 4 time
s per day. This would save me from having to restore dozens of transaction
Log backups in the event of a failure towards the end of a day
I know that the database maintenance plan does not support differential back
ups. Is there any plans to add this in the future?
Also, if I create a separate job for differential backups, how do avoid the
T-Log backup and differential backup executing at the same time without incr
easing the T-Log interval?
What happens if both the T-Log and differential backup occur at the same ti
me?Scott,
I basically asked the same question on 07/09 with subject "backup sets to
named device". I solved my own problem like this:
Job A runs once daily at zero hours.
Step 1 creates a backup device pointing to local file with sp_addumpdevice.
Step 2 performs full backup with BACKUP DATABASE.
Step 3 removes old backup devices with sp_dropdevice.
Job B runs daily every 4 hours.
Step 1 performs diff backup with BACKUP DATABASE.
Job C runs daily every 10 minutes.
Step 1 performs log backup with BACKUP DATABASE.
Each job uses datetime and string functions to create the device and file
names, such as NorthWind_YYYYMMDD and NorthWind_db_YYYYMMDD.bak. For step 3
of Job A, I query sysdevices to get the names of old devices. You can
determine how many devices and associated backups remain based on the query.
I end up with complete backups for each day encapsulated in a single file
which gets written to tape and stored offsite.
It's working like a charm. I'll post the detailed t-sql if you need it.
Anthony|||Scott,
Sorry about not really answering your questions in the previous post. I
have had backup jobs executing at the same time and didn't see any problems.
But for my previous suggestion, I have full at zero hours, logs every 10
minutes starting at 00:05 to 23:59, and diffs every 4 hours starting at
04:00 to 23:59. Now none of the jobs intersect.
I haven't looked at Yukon. But since it's .Net based, you will probably
be able to code you're own maint plans.
Anthony|||Thanks Anthony, I would love to see the T-SQL. It sounds very creative.
My only concern is if both the transaction log and the differential backups
execute at the same time, will it cause conflicts somehow.
"Anthony" wrote:
> Scott,
> Sorry about not really answering your questions in the previous post. I
> have had backup jobs executing at the same time and didn't see any problem
s.
> But for my previous suggestion, I have full at zero hours, logs every 10
> minutes starting at 00:05 to 23:59, and diffs every 4 hours starting at
> 04:00 to 23:59. Now none of the jobs intersect.
> I haven't looked at Yukon. But since it's .Net based, you will probably
> be able to code you're own maint plans.
> Anthony
>
>