MS SQL Server :: SQL Maint. Plan Help

Maint. Plan

May 31, 2001

In maint. plan when reorg is selected for a user db, it will also work on
sys tables in user database. Is it a good thing to do ? How do I avoid sys tables in a user database not to be part of maint optimization plan ? Some script would be helpful !

Thanks,
Ivan

View 3 Replies View Related

Maint. Plan Help

Aug 8, 2006

I need to set up a Maintenance Plan on a 6.5 DB. I know the version is old but the customer cannot upgrade at this time. When I try and run the wizard to set it up I get a warning about running a plan on a DB that is larger that 400MB. Whats the deal? Thanks for the help!!

View 1 Replies View Related

Maint Plan Issues

Aug 16, 2005

I have a maint plan for a single database to backup the database, thats it, very simple. It fails every time.

When I do a view history of the maint plan, it is empty as if it has never run. When I do a view history of the job, it shows it has failed after 13 seconds. With this message.
"Executed as user: sa. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."

Any help would be great as I cannot get any backups to run. I am worried to say the least right now.

View 3 Replies View Related

SQL 2005 Maint Plan

Dec 8, 2007

I am creating a sql 2005 maintenance plan backup job. How can i specify 'write log files to some particular drive'. I am new to sql server 2005
Please advice

View 3 Replies View Related

SQL 2005 Maint Plan

Dec 25, 2007

I need to create a maintplan for user databses,scheduled everyday 8pm
includes
Reoraganize index
fullbackup
Histoty clean up

What should be tasks order? is it okay reorganize index before full backup?
Please advice

View 1 Replies View Related

Sql 2005 Maint Plan

Dec 26, 2007

I need to create a maintplan for user databses,scheduled everyday 8pm
includes
Reoraganize index
fullbackup
Histoty clean up

What should be tasks order? is it okay reorganize index before full backup?
Please advice

View 8 Replies View Related

Backups: SQL Vs Maint Plan Vs BkpExec

Aug 14, 2001

It seems there are at least 3 ways to back up my databases in SQL2000

1) Veritas BackupExec
2) SQL Sever Backup
3) SQL Maintenance Plan

I don't quite see what the difference is between 2 & 3.

I'm currently using Veritas to do a full backup to tape, and a separate full backup to disk via a Maint Plan (a complete backup & trans log backup). It looks as though I've finally got my trans log to stop getting bigger (it started a new one) .... can I delete the huge one I don't need anymore. Does "truncate" mean the log is deleted, or just inactive as a new log gets created. I been reading up on this, but apparently it hasn't sunk in yet.

Please give me any long winded, but clear help you might have

View 1 Replies View Related

Tempdb Included In Systemdb Maint Plan

Jan 10, 2008

I have just done an in-place upgrade of a sql2000 SP4 instance to SQL 2005. After the upgrade I applied SP2 and cumulative hotfix rollup 4, plus the SQL 2000 DTS designer components for backward compatibility.

all went well with the upgrade.

I then migrated the maintenance plan for the system databases to 2005 SSIS version, and find it includes tempdb! This causes the jobs to fail.

Have I come across anther maintenance plan issue or have I done something wrong? Should I have applied GDR2 before the cumulative roll up?

View 5 Replies View Related

Baffled. How To Purge Old Backups With Maint Plan

Apr 13, 2006

I'm missing something basic...

Trying to create a new job from sql server 2005 Management Studio. The nifty wizard under Management provides options to create backups, and clean history, but I cant seem to find out how to create a job to purge old backups.

Looking at the commnand line Sql Server Agent Job shows:

/SQL "Maintenance PlansFull Backups" /SERVER "JFSLIB-DEV" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

But as you can see, doesn't help a novice like myself, as it appears to be command options to run the "integration" package.

Any help is appreciated. Thanks!

View 3 Replies View Related

Maint. Plan Single User Mode Issue?

Apr 12, 2001

Dear All,
With SQL Server 7 SP2 Maint. Plan, Integrity,
Attempt to repair minor problems - there was a feature
that sometimes left databases in single user mode.

I have recently updated to SQL Server 2000 with a
production database (option set OFF) - does this
feature still apply to SQL Server 2000?

Best Wishes,
Andrew

View 2 Replies View Related

Maint Plan - Failed To Acquire Connection Local Server Connection

Feb 15, 2008

I created a single step plan that does integrity checks. It fails with the error below. I created a new connection using our clusters virtual sql name.

Executed as user: ACCTCOMsqlagent. ...n 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:10:06 PM Error: 2008-02-15 13:10:49.02 Code: 0xC00291EC Source: {0CF32F3D-A8D1-492A-9C0F-AD4E0FC67D14} Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2008-02-15 13:10:49.02 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2008-02-15 13:11:31.26 Code: 0xC0024104 Source: Check Database Integrity Ta... The package execution fa... The step failed.

The agent and db are running under domain accounts and the job is owned by sa.

Thanks for your help.

View 13 Replies View Related

Backup - Maint. !!!

Mar 26, 2001

In SQL 7, a single table cannot be restored. What if a week later one found out that a table was corrupted few days and no one realized, then how to address this ? Restoring entire db from a tape few days old would't be a solution since all other tables have changed meantime.

Your feed-backs are greatly appreciated !!!

-ISB

View 1 Replies View Related

SQL Maint Optimization

Apr 19, 2000

I am using the Database maintenance on a database that is about 4gb. The database optiiztion is running about an hour. Does this job only do an update stats? If I run the stored procedure sp_updatestats on the database it only takes a couple of minutes. Are thes two processes doin the same thing? Do I need them if the create, update statistics are turned on?

Thanks,
Glen

View 4 Replies View Related

Maint. Not Running

Dec 7, 2007

Just two days ago, I noticed none of the scheduled maintenance jobs (backups) are running. I can run them manually successfully, but they are not running according to the maintenance schedule. There are no errors thrown.

Just prior to this happening, our anti-virus detected and deleted what it described as a virus [trojan]. The file was named sqlmaint.rll. I have done extensive searching and have not found anything definitive for this file. Obviously, this has something to do with maintenance not running, but how can I resolve this?

Thanks in advance for any help.
Deacon

Windows Server 2003 Enterprise
SQL Server 2000

View 6 Replies View Related

Maint Views

Jan 18, 2007

When I use Enterprise Manager to Maint views it basically brought up the view in simple text editor, where I could put in comments as to what the view was for.

I did this by just double clicking the view, but now in SQL Studio express when I modify the view it just brings it up in a query editor. I don't see my comments for my old SQL2000 views.

Is this just the way it is, and I need to get used to it. Or I am just missing something.

View 1 Replies View Related

SQL Server 2008 :: Is Only One Plan Is Kept For One Query In Plan Cache

Mar 14, 2015

Is only one plan is kept for one query in plan cache?

i heard generally hash is created for a query and plan is search with this hash.

View 2 Replies View Related

Maint Cleanup Task

Mar 19, 2007

Ques; can "both" files the db backup (.bak) file "and" the (.txt)report file in a maint plan object be cleaned up at same time?

The object is working ok but trying to setup and match backup text reports to the db, I have way more .bak files than text files.

Created from/after db maint plan (which is working ok) from/in the object Maintenance Cleanup Task and the object selections are

Delete files of the following type:
_backup files
_maintenance plan text reports

File location:
_delte specific file
File name: __
_search folder and delete files based on an extension
Folder:__
File extension:__
File age:
_Delete files based on the age of the file at task run time...
delete files oder than the following. . . .

View 1 Replies View Related

Transaction Log Backup Via SQL Maint Fails

Jul 5, 2002

I have setup a SQL maintenance plan to backup the transaction log file to be backup every one hour M-F.

But now I have been getting the following error:

sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

Any clues what does this error mean ?

Thanks.

View 2 Replies View Related

Auto-Creating DB Maint Plans?

Aug 3, 2005

Hi Guys,

Does anyone know an easier way to create DB Maint Plans instead of having to create each one manually?
i.e. Copy Maint Plans from 1 server to another automatically.
Does anyone know of a quick export method.

thnx in advance.

View 4 Replies View Related

Error 22029 On Maint Jobs

Apr 27, 2006

OK this is a new error on me.

I have a SQL server 2000 on Windows Server 2003.

I have setup a Maint plan that backs up the user and system databases
and the transaction Logs on my user databases.

Does optimises and integrity checks on those databases as well.
I am not attempting to repair any minor problems.
I am not performing integrity checks before backing up the database
and transaction logs.

All of the databases that I am doing a transaction log backup on have
recovery mode of full.

When the jobs run they fail instantly with error code 22029.
Sqlmaint.exe failed to run. This was working up until the end of the
day on the Thursday before Good Friday. (last week) The transaction
log backup ran fine at 4:00 PM the same job failed at 8:00 PM. No
changes were made to the server during that time.
Another issue that started at the same time. I have a web page used to
grab data from a text file export and pull it into an AS400. I use
this server to house the DTS packages and stored procedures that the
process needs. I suddenly quit working when the jobs quit running.

We are in a mixed enviroment with most clients on AD and this server
and most others still in a NT 4 domain. We have been like this for at
least 3 months and everything has worked fine. I setup the various
users and systems the way the need to be, to work.

Originally the SQL Agent was running as a generic NT 4 Domain user with
local administrator rights to the local box and server role of system
administrator. The MSSQLSERVER and DTC services were running as the
built in system account.

Everything was happy then it broke.

When I reboot the server the jobs and the website work fine. Then
after a period of time that I have not figured out yet, but more than
15 minutes and less than 2 hours. Everything quits working.

I have changed all the services to run as a domain admin with SQL
system adminsitrator server role. Still same thing.

I have changed all the services to run as the built in system account.
Still same thing.

I run the sqlmaint from command line with the switches that I am using
in the maint jobs and it runs fine.

I have run dbcc checkdb on all the databases and they are fine.

The DTS packages and the stored procedures used by the website run fine
from with in Query analyzer.

Any help you can provide would be greatly appreciated.

View 2 Replies View Related

SQL 2005 - How Do You Refresh Db Backup Maint Job?

Apr 30, 2007

We have an application that uses MS SQL 2005 to stored information related to user internet usage. It is storing a large amout of data on a daily bases. The application is set to create a new logging database, when the current database reaches 10GB, then it will start logging to the new database.

A new database is getting created at the rate of about 1 new db every 48hrs and the backup maintenance plan for the user databases, is not picking up the new database. I have to remember to refresh the maintenance job so, the new database gets picked up and is included in the daily backup.

Is there a way to have the backup maintenance job refesh itself? Or possibly a stored procedure or some sort of scripting option to do this?

Please provide information if you have a way of doing this. Thanks.

View 7 Replies View Related

Cached SQL Plan Vs. Stored Proc Plan

Dec 12, 2002

We have a debate in our team about embedded SQL vs. Stored Procs.

The argument is why use SP's if you can embed the SQL in the code and SQL2K will cache it on the fly?

I can't find any definitive information on pros and cons between the two methods.

If there are no major performance issues, or gotchas, I guess it comes down to developer preference.

SP Pros:
- Great SQL support in VS.NET (dev, debug, integration)
- Seperation of database specific code from middle tier.
- Less lines of code in middle tier
- VS.NET support for .xsd dataset definitions.
- Logic closer to data for more demanding processes.

Embedded SQL Pros:
- Less artifacts for version control
- Better encapsulation of logic

Any info would be appreciated.

thanks

Kevin

View 4 Replies View Related

Named Backups Using Database Maint Wizard

Aug 20, 1999

Has anyone been able to find a way to name the files within your backup using the Database Maintenance Wizard in SQL 7.0?

Thanks for your input?

View 2 Replies View Related

2005 Maint Plans .txt Files Not Deleting?

Aug 16, 2007

I am running 2005 SP2 (It is the SP2 refresh as I downloaded the SP2 on 3/15/07) and I can't get the .txt maint plans to delete. The job completes successfully; however, the old .txt files are still there. I found this technote: http://support.microsoft.com/kb/938085
although I don't receive the error message in the KB. The report does have "New Component Output" as the 1st line in the report. I still don't see how deleting the 1st line of an already run job (the report) will affect a new run of the job.

Also, I checked the version of the Microsoft.SqlServer.MaintenancePlanTasks.dll and it is at 9.00.3043.00 (which is the SP2 Refresh) in KB 933508 ( http://support.microsoft.com/kb/933508 ).

I have several SQL/2005 servers and they all have this problem. Any help would be appreicated...

Thanks,
DeWayne

View 3 Replies View Related

Best Practice/recommendation Dev Data Maint Plans

Feb 9, 2007

We are working on converting to SQL 2005 database. During the conversion we are having to rewrite a lot of code and doing a lot of intital testing and development on development data. This is causing our transaction logs to really big. I have created a maint plan that runs nightly that does a back up of database and tran log but throughtout the day the tran logs are getting really big and eating up a ton of disk space. Does anyone have suggestions on what sort of maint plan I can setup to run on my developement data where as at this point I am not concened about being able to roll back the database just keep is small as possible and "healthly"

All ideas are appreciated

Thanks

Chris

View 5 Replies View Related

SQL 2000 - Changing Maint Plans; Any Gotchas?

Oct 16, 2007

Hi fellow techs! I am getting to know SQL 2000 intimately and it's been a fun ride with the top down!

My question is, are there any gotchas that I should be aware of in SQL 2000 if I want to change 3 of my databases running on one 2000 SQL server from simple to full recovery model? I want to begin backing up the log file for point in time purposes, but don't want anything to break by disregarding the gotchas, if there are any.

Thanks!

View 4 Replies View Related

SQL 2005 Sp2 Works But SQL Maint Studio And The Instance Still Report As 9.0.1399

Jan 25, 2008

SQL 2005 SP2 install succeeds but in SQL Maintenance Studio the instance is still 9.0.1399. Also when I interogate using TSQL it is reported as 9.0.1399

Here's the log (I did try it a couple of times)

Time: 01/25/2008 13:25:05.338
KB Number: KB921896
Machine: ------------- (I took this out)
OS Version: Microsoft Windows Server 2003 family, Standard Edition Service Pack 2 (Build 3790)
Package Language: 1033 (ENU)
Package Platform: x86
Package SP Level: 2
Package Version: 3042
Command-line parameters specified:
Cluster Installation: No
**********************************************************************************
Prerequisites Check & Status
SQLSupport: Passed
**********************************************************************************
Products Detected Language Level Patch Level Platform Edition
Setup Support Files ENU 9.2.3042 x86
Reporting Services (MSSQLSERVER) ENU SP2 9.00.3042.00 x86 STANDARD
SQL Server Native Client ENU 9.00.3042.00 x86
Client Components ENU SP2 9.2.3042 x86 STANDARD
MSXML 6.0 Parser ENU 6.10.1200.0 x86
SQLXML4 ENU 9.00.3042.00 x86
Backward Compatibility ENU 8.05.2004 x86
Microsoft SQL Server VSS Writer ENU 9.00.3042.00 x86
**********************************************************************************
Products Disqualified & Reason
Product Reason
**********************************************************************************
Processes Locking Files
Process Name Feature Type User Name PID
**********************************************************************************
Product Installation Status
Product : Setup Support Files
Product Version (Previous): 3042
Product Version (Final) : 3042
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB921896_SqlSupport.msi.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
Product : Reporting Services (MSSQLSERVER)
Product Version (Previous): 3042
Product Version (Final) : 3042
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRS9_Hotfix_KB921896_sqlrun_rs.msp.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
Product : SQL Server Native Client
Product Version (Previous): 3042
Product Version (Final) : 3042
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB921896_sqlncli.msi.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
Product : Client Components
Product Version (Previous): 3042
Product Version (Final) : 3042
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQLTools9_Hotfix_KB921896_sqlrun_tools.msp.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
Product : MSXML 6.0 Parser
Product Version (Previous): 1200
Product Version (Final) : 6.10.1129.0
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB921896_msxml6.msi.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
Product : SQLXML4
Product Version (Previous): 3042
Product Version (Final) : 3042
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB921896_sqlxml4.msi.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
Product : Backward Compatibility
Product Version (Previous): 2004
Product Version (Final) : 2004
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB921896_SQLServer2005_BC.msi.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
Product : Microsoft SQL Server VSS Writer
Product Version (Previous): 3042
Product Version (Final) : 3042
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB921896_SqlWriter.msi.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
**********************************************************************************
Summary
Success
Exit Code Returned: 0

View 4 Replies View Related

Prepared SQL Plan Vs. Procedure Plan

Nov 23, 2005

I am working on tuning the procedure cache hit ratio for my server. We haveadded 4 Gb of memory to the server, which has helped. In addition, I have runthe DBCC FREEPROCACHE, which helped for a couple of days to get the hit ratioup to about 84% (from 68%).When I use the performance monitor on the server and look at SQL Server CacheManager:Buffer Hit Ratio, I see that the Prepared SQL Plan is around 97%, butthe Procedure Plan hit ratio is down around 55%. I've done some research ondifferent tuning techniques, but can't seem to find 1. a clear definition ofthe difference between the prepared sql plan and the procedure plan and 2.other than adding memory and running dbcc freeprocache, how can I get theprocedure plan cache raised? I do know that there are some procedures thatneed to be modified to be called fully qualified (e.g. exec dbo.sp_###instead of exec sp_###), but I don't think that those will increase theprocedure plan by 30% or more.Any insight you can give would be greatly appreciated.Thanks,Michael--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200511/1

View 1 Replies View Related

Actual Execution Plan Vs Estimated Execution Plan

Jul 7, 2006

The benefit of the actual execution plan is that you can see the actual number of rows passing through each step - compared to the estimated number of rows.But what about the "cost percentages" ?I believe I've read somewhere that these percentages is still just an estimate and is not based on the real execution.Does anyone know this and preferable have a link to something that documents it?Thanks

View 1 Replies View Related

SQL Maintenance Plan

Mar 5, 2001

I have deleted a database from SQL Enterprise Manager. Anyone know a way to clear that database from my maintenance plan? I do not wish to just uncheck the deleted database or create a new database plan.
Thanks!

View 1 Replies View Related

Maitenance Plan

May 1, 2001

Hi All,

I have around 10 maintenance Plans on my server and all the plans are running fine except for one which takes for ever....Yesterday I scheduled it to run at 1 AM and it was still running when I came in this morning...Usually this job used to take only 2 minutes and now it takes forever....

I tried to delete the maintenance Plan and created a new one , but no luck..

But If I try to back up the database by right clicking the database --
All Tasks --- Backup database , it takes less then 2 minutes..But If I try to run the job created by the Maintenance Plan to back up the database it takes forever and all I can see the status of the Job as ' Executing Job Step 1"..

I created a test database and restored the database in Issue onto the test database and created a maintenance Plan for the test database and it runs fine and takes less than 2 minutes.. and I don't see any security issues on this...

B'cause the job neither fails nor executes ..So there are no error messages to look in to ...

Please let me know if any one has encountered the same problem and how to resolve it..

Thanks in Advance
Venu

View 1 Replies View Related

Exectution Plan

Jun 22, 2001

When looking at the Est. Execution Plan of a query in QA, what should I be looking for? What numbers are acceptible? Does anybody have a link to any information on this subject?

TIA,

Chris

View 1 Replies View Related