Hello All,
I need to migrate our databases from one server to another. Now since these
databases have DTS packages/scheduled jobs associated with them, should i
just restore the MSDB database on the target server to recover all settings?
Or is there any alternate way that will be best for my scenario?
Thanks
MannyI would script the job (right-click in EM). And Save DTS packages as files and from those files open
and save them in the new instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:70B7B8A8-DC90-4C7E-B484-B88EABD23F92@.microsoft.com...
> Hello All,
> I need to migrate our databases from one server to another. Now since these
> databases have DTS packages/scheduled jobs associated with them, should i
> just restore the MSDB database on the target server to recover all settings?
> Or is there any alternate way that will be best for my scenario?
> Thanks
> Manny
Showing posts with label scheduled. Show all posts
Showing posts with label scheduled. Show all posts
Wednesday, March 21, 2012
Monday, March 19, 2012
Database Marked as Single User Pls Help
Hi All,
Sql Server 7
I have database called ecatalog
i have a scheduled job which shrinks the database every day once at 12 am
today the job got failed
In the view Job history its showing the below contents
-----------------
Database 'ecatalog' is already open and can only have one user at a time. [SQLSTATE 42000] (Error
924) DBCC execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE 01000] (Message 2528). The step failed.
And in Application Log of event viewer its showing the below contents
------------------------
The description for Event ID ( 208 ) in Source ( SQLServerAgent$ABCSQL ) cannot be found. The
local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: Shrink Database
- ecatalog, 0xA0C4F8157A744244A61A4ECABE8C6056, Failed, 4/19/2004 12:00:03 AM, The job failed.
The Job was invoked by Schedule 27 (Shrink Database - ecatalog). The last step to run was step 1 (Shrink Database - ecatalog)..
I ran the job manually it worked fine
but now when i go and see the database ecatalog in my Enterprise Manager
it is showing ecatalog(Single User)
What is the meaning of this, will this make any problem to my database
Please help me in this.
Waiting for Reply
AdilIt means only one connection can be allowed in this db. use sp_dboption 'ecatalog', 'single', 'false' to turn it off.|||Hi Thanks for reply,
I wanted to know that does this happens on its own or has some one has done it.
Thanks waiting for reply
Adil|||No it won't do this on its own. check all jobs to see if any sp_dboption command would have done that. Also check sql error logs and look for a series of Closing file, Starting up database and Opening file. that may represent an event to put the db in single user mode.
Note some operatins do require single user mode, such as using sp_rename to change a db name.|||I think that many of the maintenance jobs will put the database into SINGLE USER mode because it is required for some kinds of maintenance. Those jobs normally take the database back out of SINGLE USER automagically when they complete.
-PatP
Sql Server 7
I have database called ecatalog
i have a scheduled job which shrinks the database every day once at 12 am
today the job got failed
In the view Job history its showing the below contents
-----------------
Database 'ecatalog' is already open and can only have one user at a time. [SQLSTATE 42000] (Error
924) DBCC execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE 01000] (Message 2528). The step failed.
And in Application Log of event viewer its showing the below contents
------------------------
The description for Event ID ( 208 ) in Source ( SQLServerAgent$ABCSQL ) cannot be found. The
local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: Shrink Database
- ecatalog, 0xA0C4F8157A744244A61A4ECABE8C6056, Failed, 4/19/2004 12:00:03 AM, The job failed.
The Job was invoked by Schedule 27 (Shrink Database - ecatalog). The last step to run was step 1 (Shrink Database - ecatalog)..
I ran the job manually it worked fine
but now when i go and see the database ecatalog in my Enterprise Manager
it is showing ecatalog(Single User)
What is the meaning of this, will this make any problem to my database
Please help me in this.
Waiting for Reply
AdilIt means only one connection can be allowed in this db. use sp_dboption 'ecatalog', 'single', 'false' to turn it off.|||Hi Thanks for reply,
I wanted to know that does this happens on its own or has some one has done it.
Thanks waiting for reply
Adil|||No it won't do this on its own. check all jobs to see if any sp_dboption command would have done that. Also check sql error logs and look for a series of Closing file, Starting up database and Opening file. that may represent an event to put the db in single user mode.
Note some operatins do require single user mode, such as using sp_rename to change a db name.|||I think that many of the maintenance jobs will put the database into SINGLE USER mode because it is required for some kinds of maintenance. Those jobs normally take the database back out of SINGLE USER automagically when they complete.
-PatP
Sunday, March 11, 2012
Database Maintenance Plans - query?
Is there an easier way to get a listing of all your
maintenance plans, what they do, and when they are
scheduled to occur - in a multiserver environment - aside
from pulling up enterprise manager for each server and
writing it all down? I would think these would be defined
in the system information and I could query it out but I
can't find anything about it in BOL or in searching KB and
newsgroups. Help please?
Thanks,
KevinJust one of the many problems with maintenance plans is that some of the
data is stored in the MP system tables, some is stored in Job system tables
and some is actually parsed from the command strings in the job steps. Here
is a query to list all jobs with their schedules, descriptions etc. You
could expand it a bit to join with the MP system tables to get just the ones
associated with the MP's.
Maintenance Plan system tables
--
sysdbmaintplan_databases
sysdbmaintplan_history
sysdbmaintplan_jobs
sysdbmaintplans
select a.[Name],a.[Enabled] AS [J_E],
b.[Enabled] AS [S_E],
case b.freq_type
when 1 then 'Run Once'
when 4 then 'Runs Daily'
when 8 then 'Every Week'
+ case freq_interval & 2 when 2 then ' on Mondays' else '' end
+ case freq_interval & 4 when 4 then ' on Tuesday' else '' end
+ case freq_interval & 8 when 8 then ' on Wednesday' else '' end
+ case freq_interval & 16 when 16 then ' on Thursday' else '' end
+ case freq_interval & 32 when 32 then ' on Friday' else '' end
+ case freq_interval & 64 when 64 then ' on Saturday' else '' end
+ case freq_interval & 1 when 1 then ' on Sunday' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval)
when 32 then 'Mthly ' + case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case b.freq_interval
when 1 then ' on Monday'
when 2 then ' on Tuesdsay'
when 3 then ' on Wednesday'
when 4 then ' on Thursday'
when 5 then ' on Friday'
when 6 then ' on Saturday'
when 7 then ' on Sunday'
when 8 then 'Day'
when 9 then 'Week day'
when 10 then 'Weekend day'
end
when 64 then 'Startup'
when 128 then 'Idle'
else 'Err'
end as schedule,
case b.freq_subday_type
when 1 then 'Runs at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + '
hours'
end as frequency,
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6), 1, 2) + ':' +
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6) ,3 ,2) + ':'+
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_start_time),6) ,5 ,2)
as start_at,
case freq_subday_type
when 1 then NULL
else
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_end_time), 6), 1, 2) + ':' +
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6), active_end_time),6) ,3 ,2) + ':' +
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6), active_end_time),6) ,5 ,2)
end as end_at,
a.[Description]
from [msdb].[dbo].[sysjobs] AS a INNER JOIN [msdb].[dbo].[sysjobschedules]
AS b
ON a.[Job_ID] = b.[Job_ID]
ORDER BY a.[Name]
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:12ecb01c44355$738f2a60$a001280a@.phx.gbl...
> Is there an easier way to get a listing of all your
> maintenance plans, what they do, and when they are
> scheduled to occur - in a multiserver environment - aside
> from pulling up enterprise manager for each server and
> writing it all down? I would think these would be defined
> in the system information and I could query it out but I
> can't find anything about it in BOL or in searching KB and
> newsgroups. Help please?
> Thanks,
> Kevin
maintenance plans, what they do, and when they are
scheduled to occur - in a multiserver environment - aside
from pulling up enterprise manager for each server and
writing it all down? I would think these would be defined
in the system information and I could query it out but I
can't find anything about it in BOL or in searching KB and
newsgroups. Help please?
Thanks,
KevinJust one of the many problems with maintenance plans is that some of the
data is stored in the MP system tables, some is stored in Job system tables
and some is actually parsed from the command strings in the job steps. Here
is a query to list all jobs with their schedules, descriptions etc. You
could expand it a bit to join with the MP system tables to get just the ones
associated with the MP's.
Maintenance Plan system tables
--
sysdbmaintplan_databases
sysdbmaintplan_history
sysdbmaintplan_jobs
sysdbmaintplans
select a.[Name],a.[Enabled] AS [J_E],
b.[Enabled] AS [S_E],
case b.freq_type
when 1 then 'Run Once'
when 4 then 'Runs Daily'
when 8 then 'Every Week'
+ case freq_interval & 2 when 2 then ' on Mondays' else '' end
+ case freq_interval & 4 when 4 then ' on Tuesday' else '' end
+ case freq_interval & 8 when 8 then ' on Wednesday' else '' end
+ case freq_interval & 16 when 16 then ' on Thursday' else '' end
+ case freq_interval & 32 when 32 then ' on Friday' else '' end
+ case freq_interval & 64 when 64 then ' on Saturday' else '' end
+ case freq_interval & 1 when 1 then ' on Sunday' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval)
when 32 then 'Mthly ' + case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case b.freq_interval
when 1 then ' on Monday'
when 2 then ' on Tuesdsay'
when 3 then ' on Wednesday'
when 4 then ' on Thursday'
when 5 then ' on Friday'
when 6 then ' on Saturday'
when 7 then ' on Sunday'
when 8 then 'Day'
when 9 then 'Week day'
when 10 then 'Weekend day'
end
when 64 then 'Startup'
when 128 then 'Idle'
else 'Err'
end as schedule,
case b.freq_subday_type
when 1 then 'Runs at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + '
hours'
end as frequency,
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6), 1, 2) + ':' +
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6) ,3 ,2) + ':'+
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_start_time),6) ,5 ,2)
as start_at,
case freq_subday_type
when 1 then NULL
else
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_end_time), 6), 1, 2) + ':' +
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6), active_end_time),6) ,3 ,2) + ':' +
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6), active_end_time),6) ,5 ,2)
end as end_at,
a.[Description]
from [msdb].[dbo].[sysjobs] AS a INNER JOIN [msdb].[dbo].[sysjobschedules]
AS b
ON a.[Job_ID] = b.[Job_ID]
ORDER BY a.[Name]
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:12ecb01c44355$738f2a60$a001280a@.phx.gbl...
> Is there an easier way to get a listing of all your
> maintenance plans, what they do, and when they are
> scheduled to occur - in a multiserver environment - aside
> from pulling up enterprise manager for each server and
> writing it all down? I would think these would be defined
> in the system information and I could query it out but I
> can't find anything about it in BOL or in searching KB and
> newsgroups. Help please?
> Thanks,
> Kevin
Wednesday, March 7, 2012
Database Maintenance Plan
I used the Database Maintenance Plan Wizard,
I have scheduled it once a day at a certain time.
And nothing happen. No program run at that time.
Is there something else to do to run the plan ?
And is it possible to run it immediatly ?
Thanks for help.
Pierre.The problem comes from a Loggin failure.
I am very confused with all these loggin !
My SQLdatabase have SQL login (not window).
When I run the SQL job, it takes my window login.
What should I do to be coherent.
(SQLserver 2000, and Windows 2000)
Thanks, Pierre.|||First of all check from EM --> Management --> JObs and specified job is enabled or not.
And use Local or domain account with admin privileges for SQL services account.
Try to execute the job manually and see the results.|||Ok, my problem is that I do not understand well Windows2000.
I understand that a User belong to a group.
And this group have priviledges.
But I do not see where to modify the group properties for assigning rights (priviledges).
Thanks for any help, if possible.|||On Win2K from Administrative tools --> Services and select MSSQLSErver & MSSqlAgent to add this user from that group/domain.
Restart the service to take the effect.|||I add the user I use to loggin Windows2000.
(MSSQLSERVER Service properties : Log On)
I restart the Server and run the job.
But the SQL job did fail again.
I have scheduled it once a day at a certain time.
And nothing happen. No program run at that time.
Is there something else to do to run the plan ?
And is it possible to run it immediatly ?
Thanks for help.
Pierre.The problem comes from a Loggin failure.
I am very confused with all these loggin !
My SQLdatabase have SQL login (not window).
When I run the SQL job, it takes my window login.
What should I do to be coherent.
(SQLserver 2000, and Windows 2000)
Thanks, Pierre.|||First of all check from EM --> Management --> JObs and specified job is enabled or not.
And use Local or domain account with admin privileges for SQL services account.
Try to execute the job manually and see the results.|||Ok, my problem is that I do not understand well Windows2000.
I understand that a User belong to a group.
And this group have priviledges.
But I do not see where to modify the group properties for assigning rights (priviledges).
Thanks for any help, if possible.|||On Win2K from Administrative tools --> Services and select MSSQLSErver & MSSqlAgent to add this user from that group/domain.
Restart the service to take the effect.|||I add the user I use to loggin Windows2000.
(MSSQLSERVER Service properties : Log On)
I restart the Server and run the job.
But the SQL job did fail again.
Database Main. Plan
Hello,
I have set-up a new SQL 2000 server. Created a Database Mainteance Plan. I get the following error when backup is run:
"SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'System DB's'' (0xAD16C5E02F3C0348B51471AB7BB3DD96) - Status: Failed - Invoked on: 2002-10-21 00:00:01 - Message: The job failed. The Job was invoked by Schedule 11 (Schedule 1). The last step to run was step 1 (Step 1). "
Can anyone assist?
ThanksThe only thing that I can think of is to create and run a backup seperate from the maintenance plan to isolate the error better, becuase u are right that error doesnt mean anything to me eaither.
Originally posted by akvag
Hello,
I have set-up a new SQL 2000 server. Created a Database Mainteance Plan. I get the following error when backup is run:
"SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'System DB's'' (0xAD16C5E02F3C0348B51471AB7BB3DD96) - Status: Failed - Invoked on: 2002-10-21 00:00:01 - Message: The job failed. The Job was invoked by Schedule 11 (Schedule 1). The last step to run was step 1 (Step 1). "
Can anyone assist?
Thanks|||RE: Hello, I have set-up a new SQL 2000 server. Created a Database Mainteance Plan. I get the following error when backup is run:
"SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'System DB's'' (0xAD16C5E02F3C0348B51471AB7BB3DD96) - Status: Failed - Invoked on: 2002-10-21 00:00:01 - Message: The job failed. The Job was invoked by Schedule 11 (Schedule 1). The last step to run was step 1 (Step 1). " Can anyone assist? Thanks
Q1 Can anyone assist?
A1 Note that in regard to 'System Databases', e.g. Master:
You can only perform a full backup of the master database. A 'Transaction Log Backup Job' therefore should not be included as part of a maintenance plan for such 'System Databases'.
I have set-up a new SQL 2000 server. Created a Database Mainteance Plan. I get the following error when backup is run:
"SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'System DB's'' (0xAD16C5E02F3C0348B51471AB7BB3DD96) - Status: Failed - Invoked on: 2002-10-21 00:00:01 - Message: The job failed. The Job was invoked by Schedule 11 (Schedule 1). The last step to run was step 1 (Step 1). "
Can anyone assist?
ThanksThe only thing that I can think of is to create and run a backup seperate from the maintenance plan to isolate the error better, becuase u are right that error doesnt mean anything to me eaither.
Originally posted by akvag
Hello,
I have set-up a new SQL 2000 server. Created a Database Mainteance Plan. I get the following error when backup is run:
"SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'System DB's'' (0xAD16C5E02F3C0348B51471AB7BB3DD96) - Status: Failed - Invoked on: 2002-10-21 00:00:01 - Message: The job failed. The Job was invoked by Schedule 11 (Schedule 1). The last step to run was step 1 (Step 1). "
Can anyone assist?
Thanks|||RE: Hello, I have set-up a new SQL 2000 server. Created a Database Mainteance Plan. I get the following error when backup is run:
"SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'System DB's'' (0xAD16C5E02F3C0348B51471AB7BB3DD96) - Status: Failed - Invoked on: 2002-10-21 00:00:01 - Message: The job failed. The Job was invoked by Schedule 11 (Schedule 1). The last step to run was step 1 (Step 1). " Can anyone assist? Thanks
Q1 Can anyone assist?
A1 Note that in regard to 'System Databases', e.g. Master:
You can only perform a full backup of the master database. A 'Transaction Log Backup Job' therefore should not be included as part of a maintenance plan for such 'System Databases'.
Subscribe to:
Posts (Atom)