SQL 2005 SP2 : Cannot See DB IN SQL 7.0 CMP MODE In Maintenance Plan Wizard
Jan 30, 2008
Hi,
I've small issue with SQL 2005 SP2. We have migrated database from another server with backup/restore procedure. Seems that db is properly set with cmp level 7.0. We are using standard maintenance plan for all SQL 2005 servers with option "backup all user databases", to have less work. Now it seems that dbs in SQL COMPATIBILITY LEVEL 7.0 cannot be backuped with Maintenance plan. They are not listed in the list of the databases and Maintenance plan is always reporting failure :
"Database '<DBNAME>' is not valid to be included in the maintenance plan."
Is there any way to fix this? Or can I safely switch db to CMP Level 8.0 at least?
Thanks for help
View 4 Replies
ADVERTISEMENT
Aug 17, 2007
Hello,
I have a question that I hope someone can clear up for me. I have come across a number of different suggestions on DB maintenance, for example reindexing with the following script:
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
My question is, doesn't the maintenance plan have this functionality inherent in it when you create the maintenance jobs to reindex? Is there a benefit to scripting things out vs just using the maintenance plan wizard for this sort of thing and any of the items it covers? I came from an Oracle background where this was a no-brainer but I am a bit confused on the choices with SQL Server.
Thanks.
View 1 Replies
View Related
Sep 14, 2000
I have created the backup plan for all the 40 user databases on one of the production servers.Scheduled to run the backup at Sunday 2.00AM every week on to a different drive on the same server.If all the databases are backed up at the same specified time,will it effect the performance or anything?Under the maintenance plan wizard,there is no option to select different times for different databases.Total size of all the databases will be 10GB.Does it take the backups one after another DB or all at the same time which is specified?Any idea on potential problems?
Thanks!!
View 1 Replies
View Related
Apr 8, 1999
I ran the Maintenance Plan Wizard and created 2 plans, one for the system databases and one for the User databases. While running the wizard I was asked if I wanted to configure an operator to e-mail reports to.
Once that operator is selected, how can you change it without recreating the maintenance plan?
This is separate from an operator that can be configured within each job.
Thanks,
C. Fischer
View 1 Replies
View Related
Apr 15, 1999
When i was going to run the Database Maintenance Plan Wizard on one of my databases, i received the following warning:
"Database 'HGB' is over 400MB in size. It is not recommended that you run the Database Maintenance Plan Wizard on databases exceeding this size. Do you still want to continue?"
My database has 1GB data space, with 757MB free and 341MB log, with 211MB free. What´s the matter on having too much free space? I thought that was good...
View 1 Replies
View Related
May 2, 2008
Hi everybody,
I need a way to create a database maintenance plan in SQL Server 2000, without using the wizard. So, I am looking for a way to create this plan either using TSQL or (even better) using VB6 code (maybe by using SQLDMO library). I want the end-user of my vb6 application to decide for the options of the plan (like scheduling or backup directory). Does anybody have any sample vb6 code for doing this?
Thanks in advance.
View 1 Replies
View Related
Jul 9, 2007
Under Management, all I have is SQL Server Logs and Activity monitor, no option to create a maintenance plan or run the wizard ??
View 4 Replies
View Related
Jul 20, 2005
I have a db maintenance plan which is set to backup (then truncate hopefully)the transaction log. In order to backup a transaction log the db must be insingle user mode so the maint. plan fails. How do you automatically set the db,in single user mode, for the transaction log to be backed up then truncated?Also, I manually set the db in single user mode and it shows itself to be insingle user mode yet the number of users in the db properties says there are 8users connected. What's this about?ThanksMark
View 5 Replies
View Related
Apr 25, 2008
I need to write my own application to start SQL Server Management Studio. What's more, when SSMS appears, the "SQL Server Maintenance Plan Wizard" is also required to be appeared. So could i get some suggest on implementing this scenario?
Thanks
View 1 Replies
View Related
Jan 23, 2006
Hi all,
I need to find out on runtime, a list of Maintenance plans and Jobs.
i dont know how to acquire the database name on which the plan/job are operating.
i also dont know from which table can i get the tasks ( backup,integrity,rebuild index etc.) that belongs to the plan.
its quite urgent,
10x,
Gil.
View 5 Replies
View Related
Jan 17, 2006
I'm trying to create a maintenance plan to backup a database, and when I go to select the database in the dropdown, I don't have any of the user databases any more. They were there several weeks ago when I first created the 'plan'.
Does anyone know how to get the user db's to show up in the dropdown?
View 7 Replies
View Related
Jul 9, 2007
Hi there
I feel like a complete nitwit. I've been using SQL 2000 for about 5 years now and regularly set up automatic backups using the wizard for many people. I'm in no way an advanced user and have enjoyed some of the more user-friendly features of SQL 2000 such as the backup wizard.
Well now we have SQL 2005 and I can't find the "wizard". I did a lot of googling and found very little on this. The frustration is mounting - let alone the fact that I had to edit the registry just to be able to perform a manual backup in the first place... :-(
Apparently their is a "maintenance plans" folder but the only place I can find this is under "Legacy" and you can't create jobs there.
Has anybody else had this issue? Is there a bug in SQL 2005? I reinstalled the full version with all options selected and still can't find this folder. Am I just being completely "blonde"?
View 4 Replies
View Related
Oct 4, 2007
Hello, everyone:
I have a strange trouble on SQL Server 2005 on XP/local and Win 2003/server. When I connect local database and create a backup plan named by "backup plan", I can see the name under "maintenance plan" and "Jobs" under SQL Server Agent. But if I connect to server database and do the samething, I cannot see the name under "maintenance plan". I can see the name under "Jobs" under SQL Server Agent. But cannot delete this job, and message:
Drop failed for job "Backup Plan".(Microsoft.SqlServer.Smo).
Did someone meet this bug and have suggestions?
Thanks
ZYT
View 4 Replies
View Related
Nov 16, 2007
I need to create sql 2005 maintenance plan for system and user databases which includes Database integrity check and Rebuild index tasks but these two things should happen at different times. Is it possible to schedule these two tasks at different times under single database maintenance plan, if so please let me know...
View 3 Replies
View Related
Aug 4, 2006
I successfully created a simple Backup Maintenance Plan under SSMS. But now that the plan is created I need to change the number of days the backups are kept. When I pull up the plan udner SSIS and lok at the tasks I can't locate that option. I couldn't find it under the sql agent either. Anyone know where it is? (Or do I need to recreate the plan from scratch?)
TIA,
barkingdog
View 24 Replies
View Related
Feb 16, 2007
In SQL 2005...
How do I specifty in the maintenance plan to only keep 3 days worth of backup.
Andre
View 4 Replies
View Related
Apr 16, 2008
I need to build a query that shows which databases on a sql 2005 server are NOT included in a maintenance plan.
So far I have been unable to find a matching column from msdb sys.databases with any column in the following msdb tables:
sysmaintplan_plans
sysmaintplan_subplans
sysmaintplan_log
sysmaintplan_logdetail
Has anyone else been able to solve this challenge?
Thanks!
.40
View 6 Replies
View Related
Nov 16, 2007
I want to create sql 2005 maintenance plan which includes Database integrity check and Rebuild index tasks but these two things should happen at different times. Is it possible to schedule these two tasks at different times under single database maintenance plan, if so please let me know...ASAP
View 4 Replies
View Related
Sep 19, 2007
I created a database maintenance plan on sql server 2005 (standard SP2, cluster environment). The plan created successfully on scheduled successfully. But when execute the plan, it fails. Here is the information in the log:
The last step to run was step 1 (TranLog backup).,00:00:01,0,0,,,,0
09/19/2007 12:06:38,Tranlog backups.TranLog backup,Error,1,LAIWWORKSITE1LA,Tranlog backups.TranLog backup,TranLog backup,,Executed as user: STROOCKSQLSRV. 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: 12:06:38 PM Warning: 2007-09-19 12:06:39.27 Code: 0x80012017 Source: Tranlog backups Description: The package path referenced an object that cannot be found: "PackageTranLog backup.Disable". This occurs when an attempt is made to resolve a package path to an object that cannot be found. End Warning DTExec: Could not set PackageTranLog backup.Disable value to false. Started: 12:06:38 PM Finished: 12:06:39 PM Elapsed: 0.688 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
Any ideas? Thanks.
View 1 Replies
View Related
Mar 25, 2007
Hi,We have a SQL-cluster with over 6300 databases, most of them prettysmall in size and usage.For this reason all these databases are on auto-close ( Otherwise SQLwould need many many gigabytes ).We've recently upgraded to SQL 2005 because of the Management Studio,because the EM is unusable on a SQL-Server with lots of databases.But whenever you choose 'all user databases' or 'all databases' in anyof the DB Maintenance task, MSSQL begins starting up every databasewhich takes about 30 minutes. Until then, you cannot use your Studioanymore.My question : is there a possibility to avoid this behavior, it shouldnot go and check all those db's, just add my task.PS : Detaching these databases is not an option ...Thanks in advance,Sven Peeters
View 5 Replies
View Related
Apr 21, 2008
Hello,
I use SQL Server Express 2005 SP2.(Microsoft SQL Server 2005 - 9.00.3042.00)
I want to make a maintenance plan, but I don't have a task called "maintenance plan"
I am user sa with "sysadmin"
How can I make a maintenance plan.
Thanks for your help
Thomas
View 3 Replies
View Related
Nov 8, 2006
I had created a maintenance plan and configure and scheduled my maintenace plan to run. I would like to save this package as a file into a source control. SO I use SSIS to export the package under stored packages->MSDB->Maintenace Plan. After that, I wanted to test my import process. So I deleted the packages under SSIS ->stored packages->MSDB->Maintenance plan and I use the import to add the package from my previously exported package stored in a file .dtxs extention.
So the problem is, after I imported my package. I lost the configured schedule and the job runs without doing anything. When I try to go in and make changes to the package, by adding a new schedule under SQL server -> management->maintenance plan. I receive a odd error message and it doesn't allow me to save the package.. The error message I got is...."GUID should contain 32 digits with 4 dashes (xxxxx-xxx-xx...)"
So my questions are: 1. why did the re-import loses the originally configure job run schedule. 2. why doesn't the re-import package works by backing up database as it was first setup. 3. why I cannot re-edit this package and saving the package error out?
Thank you for reading and for your helps! --Jon
View 3 Replies
View Related
Oct 10, 2006
I am finding difficult to set Expression Property "SelectedDatabases" in Check Databases Integrity Task in SSIS.
I keep getting error:
TITLE: Expression Builder
------------------------------
Expression cannot be evaluated.
ADDITIONAL INFORMATION:
The data type of variable "User::varDataset" is not supported in an expression.
Reading the variable "User::varDataset" failed with error code 0xC00470D0.
(Microsoft.DataTransformationServices.Controls)
I need the flexibilty to be able to control the Task behaviour during runtime through Variables.
Any suggestions .... or even a different approach will be helpful..
cheers
aigbor
View 1 Replies
View Related
Sep 22, 2006
Hello,
I have created a simple maintenance plan in SQL Studio. When I double-click on the task boxes (ie 'Rebuild Index', 'Backup') to just make changes or to look, I get the following message:
Cannot show editor for this task
Additional information:
Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)
I also cannot save any change that I make in the schedule of the plan.
I HAVE installed Integration Services, which at least enabled me to initially SAVE the plan. I just can't edit the tasks or change the schedule.
Any help is most appreciated!
Eunice Griffin
View 1 Replies
View Related
Nov 26, 2007
I need to programmatically create a SQL 2005 maintenance plan at hundreds of customer sites. Each plan will be a little different because of path names, database names, etc., so I can't use one canned plan without modifications. I know that SQL 2005 maintenance plans are XML packages and SQL jobs, so scripting isn't an option. I've seen mention of using the .Net API to create the plans, but I can't find any examples. SMO doesn't seem to have any objects for managing maintenance plans. Can someone point me to an example of using the API to perform this task?
Thanks,
Tom
View 1 Replies
View Related
Nov 15, 2007
Hi All
I€™m having a problem setting up maintenance plans on one of our SQL 2005 servers but first here€™s a bit of back ground.
The server is a Virtual machine running win2k3R2 SP2 server std and SQL 2k5, A supplier arrived to install and app on the server when discovering there app didn€™t work with 2k5 they seem to have set the 2 dbs on the server into 2k compatibility mode and set the entire SQL install to think its a 2000 install. (I€™m not sure how this is done all I can see is the wrong version number in management studio) The problem this is giving me it that the folder in management studio for Database Maintenance Plans is no longer under the management folder instead it under a sub folder called Legacy (which is how management studio seems to deal with all registered 2000 instances) and as such a can no longer right click and create new database maintenance plans is there any way around this or will I have to setup my backup job manually?
Ohh and have the supplier shot at dawn
Thanks All
Jon
View 6 Replies
View Related
May 5, 2008
Hi,
I have recently upgrade from MS SQL 2000 to MS SQL 2005. The maintenance plan disappeared. Although the jobs are still around and running. What issues might arise from this? Thanks..
View 1 Replies
View Related
Jul 5, 2007
Hi all,
We have a SQL Server 2005 64-bit, and recently I upgrade from build 3042 to 3054 and I try to do a maintenance plan for transaction logs(TL) backup, including cleanup for two days (have full backup every night).
Problem I have is that I want the TL files to dump in a different location(due to disk space), so I put in the UNC path in the "Create a backup file for every database - >Folder:\FileServerTLDBLogs"
NB: if using the local drives, it work
Check List
Security:
- The account that I used to create the plan is an sa account
- The location that I dump the TL files, I have full access to the folder
SQL Statement:
exec xp_cmdshell 'dir FileServerTLDBLogs' (it list all files)
Is this a bug for 64-bit? because I can do this on SQL Server 2005 32-bit and it's work perfectly
View 1 Replies
View Related
May 15, 2006
We are using SQL Server 2005 (SP1). I have created a maintenance plan that backs up up the datebase every night. The problem is that the transaction log is continuing to grow. I have been told that a full backup will automatically truncate and shrink the transaction log. However, this is not happening. How can I truncate and shrink the transaction log after a full backup as part of our maintenance plan. Thank you.
View 29 Replies
View Related
Apr 13, 2007
I installed SQL Server 2005 Enterprise, then SP1 and then SP2, Maintenance Plan worked. But if I installed SQL Server 2005 Enterprise and then SP2 directly (skipping SP1), the Check Database Integrity in Maintenance Plan was not working. The error message is as follows:
Executed as user: DomainSqlServiceAccount. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:56:21 AM Could not load package "Maintenance PlansTest Plan" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login failed for user ''. The user is not associated with a trusted SQL Server connection.). The SQL statement that was issued has failed. Source: Started: 10:56:21 AM Finished: 10:56:21 AM Elapsed: 0.047 seconds. The package could not be loaded. The step failed.
Other tasks like Rebuild Index seem to be fine. SP2 is supposed to be inclusive. Does anyone have any ideas on why this is happening.
Thanks
E.G.
View 10 Replies
View Related
Nov 2, 2015
Unable to save a maintenance plan in sql server 2005, SP3. Error is class not registered.
View 2 Replies
View Related
Oct 1, 2007
I get the following message when I execute a mantenance plan to delete files older than 1 day.
Error # -1073548784
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-09-30T07:56:09'
" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any help would be appreciated!!
View 3 Replies
View Related
Jul 20, 2005
I'm trying to backup several databases to UNC folders. I'm able to do itmanually for each database, but when I set up the maintenance plan it failsbecause SQL tries to use the SQL administrator account to push the data.The million dollar question is how does one change which account is runningthe maintenance?
View 2 Replies
View Related
