Same SQL; Different Exec Plans; Different Run Times
Mar 27, 2008
I have a problem that is starting out in Reporting Services but I believe also involves basic SQL issues.
In SSRS, I have a report created that will execute in 30 seconds when the default parameters are entered. If I change the parameters, the report will take 9 minutes to execute. If I reverse the values of the parameters, I'll get the same results. The default will run in 30 seconds and the entered values will run in 9 minutes.
So to recap the scenarios. First test:
Default set to US - run time 25 seconds
Change country to UK - runs in 9 minutes
Then change the programming of the defaults and:
Default set to UK - run time 25 seconds
Change default to US - runs in 9 minutes
Upon doing some further investigation, I've discovered that the SQL is being executed with two different execution plans. WIth the default paramters (regardless of what they are) execution Plan A is used. When the parameters are changed, execution Plan B is used. I do no know enough about execution plans to read through them and understand the differences, however, the plans are not really the issue. The issue is why does SQL Server create two different execution plans?
In Plan A, default set to US, the execution plan does not see the passed parameters. The values are directly entered into the SQL. In this example, the query will run in about 30 seconds.
WHERE (REGION IN (N'US',N'Canada')) AND (SUB_REGION IN (N'Canada',N'US')) AND (COUNTRY IN (N'Canada',N'United States of America'))
In Plan B, changed to UK, the parameters are passed and are seen in the SQL. In this example, the query will take about 9 minutes to run.
WHERE (REGION IN (@Region_Selected)) AND (SUB_REGION IN (@Sub_Region_Selected)) AND (COUNTRY IN (N'Great Britain',N'Ireland'))
For some reason, the parameter @Country_Selected isn't passed but the values are entered. In this example, Great Britain and Ireland.
Why does SSRS substitute the parameters for the values in Plan A but not in Plan B?
Why does the SQL engine care and create two different execution plans?
Rob
View 1 Replies
ADVERTISEMENT
Aug 5, 2004
Hi All...
if i had the following sp...
*******************************************************
create procedure my_insert (param1 int, param2 int, paramx int)
as
...
complicated insert routine
...
return
*******************************************************
and then i wanted to exec this sp in another procedure i would have
exec my_insert( 1_value, 2_value, 3_value )
My question is how could i exec this will the result set of a select.... something like this
exec my_insert (select 1_value, 2_value, 3_value from another_table).
I know i could have this in an insert result type statement ie...
insert into dest_table (select 1_value, 2_value, 3_value from another_table)
but my insert routine is quite complicated and carries out some other functions so I would like to call (exec) a sp rather than repeating the complication in the select statement
Many Thanks
Gary T
View 4 Replies
View Related
Dec 31, 2006
Hi,
If i have an SP called mySP that accepts one parameter @param
If I have a table of paramaters with only one column like this:
Param1
Param2
..
ParamN
How do I do if I want to execute the SP on all the table fields:
some thing like this:
Exec my SP 'Param1'
Exec mySP 'Param2'
...
Exec mySP 'ParamN'
I want that automatically since the parameters are going to be in a table called myTblParams
Notice that I don t want to pass all the parameters to the SP just once but only one value each time I execute the SP since mySP ccepts only one parameter.
Thanks a lot for guidelines
View 8 Replies
View Related
Dec 7, 2005
Hi I am slowly getting to grips with SQL Server. As a part of this, I have been attempting to work on producing more efficient queries. This post is regarding what appears to be a discrepancy between the SQL Server execution plan and the actual time taken by a query to run. My brief is to produce an attendance system for an education establishment (I presume you know I'm not an A-Level student completing a project :p ). Circa 1.5m rows per annum, testing with ~3m rows currently. College_Year could strictly be inferred from the AttDateTime however it is included as a field because it a part of just about every PK this table is ever likely to be linked to. Indexes are not fully optimised yet. Table:CREATE TABLE [dbo].[AttendanceDets] ([College_Year] [smallint] NOT NULL ,[Group_Code] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Student_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Session_Date] [datetime] NOT NULL ,[Start_Time] [datetime] NOT NULL ,[Att_Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GO CREATE CLUSTERED INDEX [IX_AltPK_Clust_AttendanceDets] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [All] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Start_Time], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [IX_AttendanceDets] ON [dbo].[AttendanceDets]([Att_Code]) ON [PRIMARY]GOALL inserts are via an overnight sproc - data comes from a third party system. Group_Code is 12 chars (no more no less), student_ID 8 chars (no more no less). I have created a simple sproc. I am using this as a benchmark against which I am testing my options. I appreciate that this sproc is an inefficient jack of all trades - it has been designed as such so I can compare its performance to more specific sprocs and possibly some dynamic SQL. Sproc:CREATE PROCEDURE [dbo].[CAMsp_Att] @College_Year AS SmallInt,@Student_ID AS VarChar(8) = '________', @Group_Code AS VarChar(12) = '____________', @Start_Date AS DateTime = '1950/01/01', @End_Date as DateTime = '2020/01/01', @Att_Code AS VarChar(1) = '_' AS IF @Start_Date = '1950/01/01'SET @Start_Date = CAST(CAST(@College_Year AS Char(4)) + '/08/31' AS DateTime) IF @End_Date = '2020/01/01'SET @End_Date = CAST(CAST(@College_Year +1 AS Char(4)) + '/07/31' AS DateTime) SELECT College_Year, Group_Code, Student_ID, Session_Date, Start_Time, Att_Code FROM dbo.AttendanceDets WHERE College_Year = @College_YearAND Group_Code LIKE @Group_CodeAND Student_ID LIKE @Student_IDAND Session_Date <= @End_DateAND Session_Date >=@Start_DateAND Att_Code LIKE @Att_CodeGOMy confusion lies with running the below script with Show Execution Plan:--SET SHOWPLAN_TEXT ON--Go DECLARE @Time as DateTime Set @Time = GetDate() select College_Year, group_code, Student_ID, Session_Date, Start_Time, Att_Code from attendanceDetswhere College_Year = 2005 AND group_code LIKE '____________' AND Student_ID LIKE '________'AND Session_Date <= '2005-11-16' AND Session_Date >= '2005-11-16' AND Att_Code LIKE '_' Print 'First query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds' Set @Time = GetDate() EXEC CAMsp_Att @College_Year = 2005, @Start_Date = '2005-11-16', @End_Date = '2005-11-16' Print 'Second query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds'GO --SET SHOWPLAN_TEXT OFF--GOThe execution plan for the first query appears miles more costly than the sproc yet it is effectively the same query with no parameters. However, my understanding is the cached plan substitutes literals for parameters anyway. In any case - the first query cost is listed as 99.52% of the batch, the sproc 0.48% (comparing the IO, cpu costs etc support this). BUT the text output is:(10639 row(s) affected) First query took: 596 milli-Seconds (10639 row(s) affected) Second query took: 2856 milli-SecondsI appreciate that logical and physical performance are not one and the same but can why is there such a huge discrepancy between the two? They are tested on a dedicated test server, and repeated running and switching the order of the queries elicits the same results. Sample data can be provided if requested but I assumed it would not shed much light. BTW - I know that additional indexes can bring the plans and execution time closer together - my question is more about the concept. If you've made it this far - many thanks.If you can enlighten me - infinite thanks.
View 10 Replies
View Related
Oct 31, 2007
I have two SQL Server 2000 (one is localhost, one is remote with VPN IP 192.168.5.4).
I can select * from [192.168.5.4].db.dbo.test but I can't exec [192.168.5.4].db..spAdd in localhost.
These select and sp is OK for 1 or 2 week without any problem,but it didn't work one day.
Can some one explain why?
View 5 Replies
View Related
Nov 16, 2001
I have two schematically identical databases on the same MS SQL 2000 server. The differences in the data are very slight. Here is my problem: the identical query has totally different execution plans on the different databases. One is (in my opinion) correct, the other causes the query to take 60 times as long. This is not an exaggeration, on the quick DB the query takes 3 seconds, on the other DB it takes 3 minutes. I have tried the following to help the optimizer pick a better execution plan on the slow db:
rebuild the indexes
dbcc indexdefrag
update statistics
I CAN put in a hint to cause the query to execute faster, but my employer now knows about the problem and he (and I) want to know WHY this is happening.
Any ideas would be greatly appreciated.
Thanks.
-Scott
View 1 Replies
View Related
Feb 20, 2003
SQL7: I have added a Maintenance Plan to backup to 4mm dat tape the master and msdb SQL databases as well as another database relative to our application called WISE. This works fine; however, it appears to always append to the media as opposed to overwriting (preferred). Any help would be appreciated.....
View 1 Replies
View Related
Aug 22, 2005
Hi,
I am going to set up maintenance plans on all our SQL servers (7.0 and 2000). I have found several 'tutorials' on how to do this, but no one is describing the options in detail. Can you guys/gals please help me out? We have alot of small databases and some medium (1-2GB).
Thanks//Stefan
View 3 Replies
View Related
May 14, 2002
I am new to SQL Server 2000 & need ya all's help!!
I am trying to set up a database maintenance plan to back up databases & transactional logs. If I do a full backup once a week & a transactional backup every day...will I be safe enough to have enough backups to be able to restore to any point of time by restoring the full backup & the transactional logs upto that point?
In other words, I am asking what are the points to consider & what should be a decent backup plan? Do transactional logs take stored procedureal changes also?
Thanks for ur help,
Edward
View 3 Replies
View Related
Feb 15, 2005
We have Veritas' Backupexec running in our Enterprise and the Veritas Install actually installs MS SQL Server MSDN on each Server in the Enterprise.
It looks like it also sets up a default Maintenance plan within each of the MSDN Instances.
I guess my question is.. Can I manage the Maintenance Plans on these MSDN Instances via the SQL Server EM GUI from my desktop?? Seems like when I look at the Maintenance plans alot of the options are greyed out or not available. What I am trying to do is modify one of the maintenance plans to have the backups deleted after one week (One of the Instances has been running a complete backup on the Backupexec Databases for a year and there are a years worth of backups on the Server) but the option to "remove files older than" is 'greyed out' ??????
View 6 Replies
View Related
Feb 20, 2008
Eh uhhhh where did the old '-DelBkUps 7DAYS' (delete files older than 7 Days) option go?
Is this a future product enhancement?
I fee like such a dirty noob.
View 3 Replies
View Related
Dec 1, 2007
Does anyone get any issues creating "Backup" jobs as a Maintenance Plan when specifying the backup location as a UNC path (e.g. "\backup_bladeBACKUPS")?
For some reason, if i try using the UNC path for a 1-time backup, it works, but when I am trying to put it into a scheduled job, it does not 'seem' to perform the Backup step.
Help please...
seethem
View 4 Replies
View Related
Jul 23, 2005
HiCan you give me sone pointers to where I can get more information aboutthe various operations like index seeks,Bookmark Lookups,ClusteredIndex Scan in an execution plan.ThanksRagu
View 2 Replies
View Related
Apr 13, 2006
I have 2 SQL databases which are the same and are giving me differentquery plans.select s.* from hlresults hinner join specimens s on s.specimen_tk = h.specimen_tkwhere s.site_tk = 9 and s.location in ('ABC','WIAD')and s.date_collected between '2/1/2003' and '2/3/2006'order by s.location, s.date_collectedBoth boxes have the same configuration, the only difference is that oneof them is a cluster.The Acluster box is taking twice as long to run the query.I have run statistics on both, and the cluster is still creating abitmap and running some parallelism which the other box is not.Also, the the first step, the A1 box estimates the rows returned to bearound 80K and the actual rows returned is about 40K - subtree cost =248. The Acluster box estimates 400K - subtree cost=533!After running statistics, how can it be so off?I've also reindexed to no avail . . .any insight would be very much appreciated. We just moved to this newsystem and I hate that the db is now slower -A1:affinity mask -2147483648 2147483647 0 0allow updates 0 1 0 0awe enabled 0 1 1 1c2 audit mode 0 1 0 0cost threshold for parallelism 0 32767 0 0Cross DB Ownership Chaining 0 1 0 0cursor threshold -1 2147483647 -1 -1default full-text language 0 2147483647 1033 1033default language 0 9999 0 0fill factor (%) 0 100 90 90index create memory (KB) 704 2147483647 0 0lightweight pooling 0 1 0 0locks 5000 2147483647 0 0max degree of parallelism 0 32 4 4max server memory (MB) 4 2147483647 14336 14336max text repl size (B) 0 2147483647 65536 65536max worker threads 32 32767 255 255media retention 0 365 0 0min memory per query (KB) 512 2147483647 1024 1024min server memory (MB) 0 2147483647 4096 4096nested triggers 0 1 0 0network packet size (B) 512 32767 4096 4096open objects 0 2147483647 0 0priority boost 0 1 0 0query governor cost limit 0 2147483647 0 0query wait (s) -1 2147483647 -1 -1recovery interval (min) 0 32767 0 0remote access 0 1 1 1remote login timeout (s) 0 2147483647 0 0remote proc trans 0 1 0 0remote query timeout (s) 0 2147483647 0 0scan for startup procs 0 1 1 1set working set size 0 1 0 0show advanced options 0 1 1 1two digit year cutoff 1753 9999 2049 2049user connections 0 32767 0 0user options 0 32767 0 0Acluster:affinity mask -2147483648 2147483647 0 0allow updates 0 1 0 0awe enabled 0 1 1 1c2 audit mode 0 1 0 0cost threshold for parallelism 0 32767 0 0Cross DB Ownership Chaining 0 1 0 0cursor threshold -1 2147483647 -1 -1default full-text language 0 2147483647 1033 1033default language 0 9999 0 0fill factor (%) 0 100 90 90index create memory (KB) 704 2147483647 0 0lightweight pooling 0 1 0 0locks 5000 2147483647 0 0max degree of parallelism 0 32 4 4max server memory (MB) 4 2147483647 14336 14336max text repl size (B) 0 2147483647 65536 65536max worker threads 32 32767 255 255media retention 0 365 0 0min memory per query (KB) 512 2147483647 1024 1024min server memory (MB) 0 2147483647 4095 4095nested triggers 0 1 0 0network packet size (B) 512 32767 4096 4096open objects 0 2147483647 0 0priority boost 0 1 0 0query governor cost limit 0 2147483647 0 0query wait (s) -1 2147483647 -1 -1recovery interval (min) 0 32767 0 0remote access 0 1 1 1remote login timeout (s) 0 2147483647 0 0remote proc trans 0 1 0 0remote query timeout (s) 0 2147483647 0 0scan for startup procs 0 1 1 1set working set size 0 1 0 0show advanced options 0 1 1 1two digit year cutoff 1753 9999 2049 2049user connections 0 32767 0 0user options 0 32767 0 0
View 1 Replies
View Related
Feb 28, 2007
Does anyone know if Sql Server 2005 Express SP2 support scheduled backups and maintenance plans?
Looking at http://msdn2.microsoft.com/en-us/library/bb283536.aspx , it appears to...
Thanks.
View 1 Replies
View Related
Jan 25, 2008
Does any body have any good recommendations for maintenace plans?
Here are few questions I have.
When should indexes be re-indexed?
What should be done first? Reorganize indexes or rebuild indexes?
How often backups should be done and what kind?
How often should database statistics be updated?
Do database statistics need need to be updated on system databases?
How often should a database integrity check be done?
How long should history be kept for?
What is a good order for tasks should be done?
Any input would be great
Eytan
View 3 Replies
View Related
Mar 5, 2007
I created several Maint.Plans before installing SP2. Now I need to modify them and I get the following error. I cannot even Create new ones, because of the Enumerate error. Please advice if this error is due to the same issues mentioned on this blog.
When replying please cc me at Camilo.Torres@bellsouth.com
Thanks
TITLE: Microsoft SQL Server Management Studio
------------------------------
Enumerate target servers failed for Job 'Daily Maintenance Plan 1'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Enumerate+target+servers+Job&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
String or binary data would be truncated. (Microsoft SQL Server, Error: 8152)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476
View 2 Replies
View Related
Jul 24, 2001
We have been asked by a customer who uses our software product whether they should upgrade from SQL 6.5 to SQL 7 or SQL 2000. They have one server with 250 clients accessing SQL Server.
I used to think that upgrading to SQL 7 would be cheaper in light of the complaints I've heard from administrators about the cost of going to SQL 2000's new pricing model, so I took a look at Microsoft's web site. Navigating around their web site looking for licensing info is always an adventure!
I saw only SQL 2000 (non-upgrade) pricing on their web site, so I'm assuming that buying SQL 2000 and then upgrading to SQL 7 would result in the same pricing structure whether a customer upgrades to SQL 7 or SQL 2000 (minus the discount for upgrading). I see that a processor based license ($2499 per processor standard edition) with its unlimited cals would be cheaper for a company with 250 clients, since the cost of cals for 250 clients with the standard edition server-based pricing would be prohibitive.
So if the pricing is the same, the decision to upgrade to SQL 7 or SQL 2000 would rest upon features available and when SQL 7 will be retired.
Does anyone know how long Microsoft will support SQL 7? (I'm defining support as until they stop providing the patches and hotfixes, since the product will no longer be actively supported. The information I saw on Microsoft's web site made it seem like SQL 7 will be supported for a long time yet.
Does anyone know how long Microsoft will support SQL 6.5? That will give me an idea how long our customers will have to upgrade?
What would you recommend to your customers so as to benefit them most?
Thanks!
Barbara
View 1 Replies
View Related
Sep 18, 2000
Is there a way to find out what Trans-SQL is being executed by a Maintenance Plan. More specifically, if I select Attempt to Repair on the Check Database Integrity screen, I know it is executing DBCC CheckDB, but which repair option is it using (repair_fast, repair_allow_data_loss, repair_rebuild)?
View 3 Replies
View Related
Jun 8, 2000
I defined a maintenance plan to do a backup, scheduled it for midnight, but no job ever ran. Is there some other step to do?
thanks!
View 2 Replies
View Related
Feb 18, 2000
A year ago one of our SQL Server 6.5 servers was upgraded
to SQL Server 7.0 sp1. My compatibility level still shows
6.5, however. The SQLAgent has been using the 'localsystem'
account up until earlier this week. I changed the login
to be a domain account with System Administrator
permissions and removed the SA permissions from the
BuiltinAdministrator group. (My ultimate goal is to limit
the access NT Administrators have within my SQL databases.) All of my scheduled jobs run without error except the maintenance plans. (All
jobs have an owner of sa.)
The errors that I receive are permission errors-not being able
to get into tables on the MSDB database. However, if I open
Query Analyzer with the SQLAgent domain account and perform
a select on one of the tables in MSDB, it is successful. If I give
the BuiltinAdministrator account the SA permissions again
while still keeping the SQLAgent using the new domain account,
the maintenance jobs succeed.
Is this an upgrade problem since I do have other SQL 7.0 servers
that don't have this problem? How can I correct this?
Thank you!
Toni
View 1 Replies
View Related
Sep 15, 1999
Hi, I'm new to SQL 7 (and fairly new to SQL Server), can anyone help with these basic questions on database maintenance plans generated by the wizard:
(1) Scheduling - can SQLServer handle say a REORG running at the same time as a backup against the same database? This should never happen, but what will happen if say a backup is due to start before a REORG has finished? To try & simulate this 'problem' I've run REORGs and backups at the same time & have yet to encounter errors I presume SQL locking handles this OK.
(2) Database integrity checks - (a) any comments on the wisdom of checking 'repair any minor problems'? Anyone had any problems with this? (b) While integrity checks are running do they take enforce a consistent view of the data? (I think this is probably the case as my reading of books online indicates that DBCC takes shared locks for the duration)
(3) Backups - does the 'VERIFY INTEGRITY' option have any impact on the live database? (My reading of RESTORE VERIFYONLY indicates it doesn't)
Thanks....
View 1 Replies
View Related
Mar 18, 2003
What is the recommended schedule for SQL Server maintenance plans that perform database integrity checks and optimizations?
Also, more specifically, how often should we be re-building our indexes?
Thanks for your help.
View 4 Replies
View Related
Nov 6, 2003
Is there a way to script out maintenance plans (and their corresponding jobs) from a server ?
View 2 Replies
View Related
Apr 25, 2001
I used the wizard to create maintenance plans on my SQL 2000 servers. Part of the plan fails (checking data and index linkeage) when the job runs in off hours. It fails because it says the DB is not in single user mode. Shouldn't something in the plan take care of this. The wizard gives you no options. Or is this a bug. These jobs ran fine in SQL 7
View 1 Replies
View Related
Mar 17, 2006
Hello everyone,
I'm new to DB Maint Plans, so let me apologize upfront. I've taken over a system from a DBA who is no longer working here, and he set up Maint Plans for all of the existing DBs. The plans show up in the Enterprise Manager under "Management->Database Maintenance Plans" like they should, but there are also entries in the "Management->SQL Server Agent->Jobs" area. When I set up a new DB Maint Plan for a new DB, it seems to be working fine, but I don't have any corresponding entries in Jobs. Did the other DBA set these up manually? Does anyone know why he might have done this? Is it needed? The jobs and job steps look like the following:
[DBName]Full:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "W:sqldataMSSQL$P001Backup" -DelBkUps 1WEEKS -CrBkSubDir -BkExt "BAK"'
[DBName]Maint Integrity Checks:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -CkDB '
[DBName]Maint Optimizations:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '
Any help or insight would be greatly appreciated!
Thanks in advance,
Cat
View 12 Replies
View Related
Jan 23, 2004
Gurus,
I'm trying to get an application finished that works like Query Analizer in
terms of returning query plans and statistics.
Problem the co-author is having:
>In using ADO to connect to SQL Server, I'm trying to retrieve multiple
>datasets AND statistics that are usually returned via the OnInfoMessage
>event. For those that are familiar with SQL Server, I need the results
>returned by the SET STATISTICS IO ON and SET STATISTICS PROFILE ON options.
>Anyone had any luck doing this before?
Can anyone shed any light on this please?
Thanks.
BTW if anyone wants to take a look at the tool so far - to see what I'm
delving into:
http://81.130.213.94/myforum/forum_posts.asp?TID=78&PN=1
Much Appreciated!!
View 3 Replies
View Related
Apr 15, 2008
Hi Gurus,
What permissio0ns one should have to view execution plans on SQL SERVER 2005.
Thanks,
ServerTeam
View 1 Replies
View Related
Jun 17, 2008
Hi,
Whenever I create a maintenance plan either manualy or via the wizard, I get the following error message when saving it.
Does anyone know what might be wrong?
Best regards
===================================
Fehler beim Speichern des Wartungsplanes.
(Error when saving the maintenance plan.)
===================================
Mindestens ein Argument ist ungültig.
(At least one argument is invalid.)
------------------------------
Speicherort des Programms:
(Save location of programme:)
bei Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package package, IDTSEvents events, String packagePath, String serverName, String serverUserName, String serverPassword)
bei Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlan.Save()
bei Microsoft.SqlServer.Management.MaintenancePlanWizard.MaintenancePlanWizardForm.PerformActions()
View 7 Replies
View Related
Aug 16, 2006
I am currently using sql server 2005 standard edition. I just completed a maintenance plan using the wizard to perform a full backup nightly of a user database. I noticed there was no option to delete backups greater then a number of days old like we have with plans in sql server 2000. Is there a way of doing this through the plan in sql server 2005?
View 1 Replies
View Related
Jan 24, 2007
Ok, I must be blowing my mind here.
I created a maintenance plan to run on Sundays at 10PM.
This plan failed due to Tempdb running out of space.
I rectified this and scheduled the maintenance plan to run the next day. This did not work either. The underlying jobs just did not kick off. Nothing in the event viewer or SQL Logs.
The reason, the jobs that this plan created also need to be changed.
Has anybody encountered this?
Further, I have a SQL Express on my laptop, which I use for maintaining our db servers(SQL 2005 Standard Version). When I alter the portions of the Maintenance plans(e.g. I changed the selection of the databases for "Index rebuilding" part) using my work station connected to a Production/QA server,when I try to save the changes it gives me an error: "Failed GUID........and some 16 digit number most of which are all XXXXX". But if I change the schedule it does not give an error but it does not change it either.....
I am really lost. I never had such problems on 7 or 2000.
I am not sure if these are co-related.
View 4 Replies
View Related
Mar 13, 2007
how can i move the database maintanence plans from one server to another server ?
Thanks
View 1 Replies
View Related
Mar 7, 2008
I am trying to set up a maintenance plan is SQL Server. I set one up to query one of my very small tables for testing purposes. I made sure that I have the server entered in correctly and also verified that I needed to make sure that I have both TCP/IP and Named Pipes both enabled. However, everytime I set something up and try to execute it. The plans ends up failing. This isn't a hard process but something else isn't setup that needs to be. Does anyone have any ideas pertaining to this.
Is there a special credential that needs to be setup for this?
Any help on this would be great. Thanks
View 7 Replies
View Related
