Hi,
I'm researching on how to maintain RS SQL server database or what is the best way to maintain ReportServer and ReportServerTempDB? Should I go for the automatic shrink facility of SQL Server? Any suggestion? Thanks.
After looking through the archives at database maintenance steps used by others, I have decided on the following steps for each of my SQL Server 7.0 databases. I have set up a maintenance plan where they would execute in the following order for each database, if the preceding step is successful. If the preceding step fails, the job quits.
dbcc checkcatalog ('master')
dbcc checkdb ('master')
backup database master to master_dump with init, name = 'ASP Master dump'
dbcc dbreindex ('master', '', 70)
dbcc shrinkdb ('master', 10)
Now my questions...
1. The checkcatalog, checkdb, backup database, and shrinkdb commands appear in blue in SQL Server 7.0 to seemingly indicate they are commands. However, the dbreindex command is black. Does this mean that it is not recognized as a valid command? If so, why not? It is shown as a valid command in the Microsoft Knowledge Base.
2. The value of 70 in the dbreindex command was the value shown in sample code in the Microsoft Knowledge Base. However, the Knowledge Base does not give any guidance on whether this is a good value or not. How should I make a decision on what value to use? The value of 10% for the shrinkdb command is also just a sample value from Microsoft. How does one know what value to choose?
If anyone thinks this is a poor selection of maintenance steps, please let me know that also.
Thanks!
Joe Meyer
I've heard from several sources that there are issues using the maintenance wizard with 7.0 & 2000 databases. I'm looking for information to either support or refute this. Can anyone help?
View 2 Replies View RelatedCould someone here clarify the difference between replication and mirroring?
What I want to do is to be able to make a back-up of my database to a different computer, what are the things I need to do? Is it replication or mirroring?
TIA
I learned how to make SQL database BACKUPs through Maintenance Plans in SQL server 2005 in my own computer.
So my question is: When I finally would be deploying my ( asp.net 2.0) web site in a distant real server, how could I set a Maintenance Plan there ?
I have a query. Here are the steps:
1. I created a maintenance plan using Visual Studio 2013 (nothing fancy pretty basic)
2. Using ssms 2014 I imported it (the dtsx file) under the Integration Services and it appeared there successfully
3. I connected to the Database Engine again using ssms 2014 - my expectation was to see it under the Management > Maintenance Plans folder but it was not present.
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.
One of my programmers changed their database from full to Simple recovery. Saw that my job that backs up the Full Recovery mode databases failed, so I moved that database to my Simple database backup job plan and removed it from the Full Recovery job. I am unable to remove the db from the Transaction Log task on the Full Plan because when I try to edit that job "Databases with Simple Recovery will be excluded"
My transaction log backups are still failing with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Just want to remove that database so my Full Recovery backup job does not try to back it up.
I have inherited a server on which is a maintenance plan with two subplans on different schedules. In each subplan there are Execute T-SQL tasks with scripts for index rebuilds. Each task is set up with a Completion arrow to the next task and a Failure arrow to a Notify Operator Task. I was asked to add a task for index rebuilds to a specific subplan for a specific database, which is what the other tasks also do. I discovered that my task was failing but the others were fine. No notification was sent about my task failing even though the job is marked in MSDB as a failed job. I have sent a test email using the "Send Test Email..." option when right clicking Database Mail in SSMSand I receive an email so I know Database Mail works.
I set up a test job to model the index job that I can't get notifications from. I have two T-SQL tasks that just select the top row from a small table. The first task has a syntax error that I did so it would fail. I have a failure arrow to a Notify Operator Task and a Completion arrow to another T-SQL task with no syntax error which has a Success arrow to a Notify Operator task. As expected, when I execute this job I receive one failure email and one success email.
The only other troubleshooting step I know to try is to add a Notify Operator task before my failing task. That Notify Operator task will hopefully fire to tell me that the previous step was successful. I am not having problems with the other steps so I was just thinking I would try to get the subplan to send me a success email about one of the steps that has been working fine.
I was trying to configure maintenance plan to take nightly full database backup and Log backup. I was trying to configure it like in attached file. Any links that i can follow and configure as in attached file.
View 7 Replies View RelatedI am looking for opinions of setting up a database maintenance plan. I want to know if it is safe to trust the wizard and let it set up all of the jobs, or if it is better to write your own procedures to handle backups and maintenance as in 6.5. All sugestions and opinions are welcome. Thanks.
View 2 Replies View RelatedFor SQL server 7.0, is it necessary to schedule database maintenance plan on a regulare basis? I know it is necessary for SQL server 6.5.
Thanks.
Su Ge
SQLMaint is run once a week for the following a database on SQL 6.5. The following is the information for the database when I see it through the Enterprise Manager:
Data Size 650 MB
Data Space Available 0.00
Log Size 360
Log Space Available 359.99
The following is the syntax built by the DATABASE Maintenance Wizard:
SQLMAINT.EXE -D CATS -CkDB -CkAl -CkTxtAl -CkCat -UpdSts -RebldIdx 100 -Rpt E:MSSQLLOGCATS_maint.rpt
It runs once a week and takes about 40 mins and runs successfully. Last it run was 4/11/99 at 2:00 AM
The result set I get from sp_spaceused is as follows:
database_name database_size unallocated space
CATS 1010.00 MB 273.96 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
753710 KB 280360 KB 426494 KB 46856 KB
What I don’t understand is how come the data space available shows 0 in Enterprise Manager? Shouldn't SQLMAINT, which is run once a week, allow for correct information to be reported?
Could someone please explain.
Thanks
Shashu
Running SQL Server 2000 Enterprise Edition SP3. The database is alsoused by Microsoft Project Server 2002 and also has OLAP views, so thedatabase is being used to view/run cubes in the Analysis Manager.What is the best way of shrinking the database size and its log file too?Is there an automatic way to do this with a maintenance plan or i haveto manually run a SQL statement periodically?What are the best practices?I have a database that the size of it is 260megs and now the log file isover 800megs...Everytime i run the following:BACKUP LOG DBName WITH TRUNCATE ONLYDBCC SHRINKDATABASE (DBName, 10, TRUNCATEONLY)When running it, i get the following 2 records returned:DbId FieldId CurrentSize MinimumSize UsedPages EstimatedPages7 1 32304 128 28152281527 2 160 128 160128I don't know what the above 2 records mean. I am also concerned if i shouldbe running that statement or not.Also concerned as to why the Windows Server 2003 (Enterprise Edition)always creates a CRITICAL error in the Application event viewer with EventIDnumber 17055, source being MSSQLSERVER and the description of the event:18278:Database log truncated: Database: DBName.If it's a critical error message, then what i am doing is bad? Am i damagingmy database?Within like 7-10 days of running the above statement, the log file becomescloseto 1 GB again! How can i find out what causes the log file to grow that big?Is it beingcaused by running some OLAP views or what? The db has about 20 usersconnected to itusing an ASP web aplication as the front-end, and MSP Professional 2002 alsoto connectto the Project Server 2002 (which uses the same database as the aboveDBName).I would appreciate any help.Thank you very much
View 4 Replies View Related
I have been looking through the data files directory for my databases and noticed that the log (ldf) files of some of the databases are from 2GB to 4.5GB where as the data files are under 500MB. If I shrink the log files what affect does this have on the database? Should I be setting up regular shrink jobs on the log files?
Any help would be very much appreciated.
Hi there,
We are using a database maintenance plan to backup and reindex our db's. Up until the end of last month this was working perfectly - however now it has stopped deleting the old backups (even though we have checked 'Delete files over 1 day old').
Does anyone have any ideas as to why they are now being deleted - and how we can remove them automatically - has something been corruped? Would it be a case of creating a new maintenance plan?
thanks in advance,
Matt
SQL Server 7.0 with SP 1
Database maintenance job is failing
with error about being unable to delete old backup file.
I can manually delete the file with no problem.
This occurs randomly, maybe once a week.
Any ideas on what's wrong, and how to fix it?
I want to know database maintenance issue when there are many databases in a machine. The number of database will be 50~80. We'll use SQL 7.0. In Sql 7.0, I heard that there is not so much maintenance issue.
Wisepost Inc.
Chris Song
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....
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.
I have several Maintenance jobs that keep failing. They
are , Integrity Checks, Index rebuild, and Update
statistics. I keep getting a similar error on all three. I
have pasted it below. None of my other SQL 2000 servers
have any problems. I have tried changing the database
options in question. The error I keep getting is as
follows:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'DAPHNE' as 'CDCNorm' (trusted)
Starting maintenance plan 'FSPRD84A Integrity Checks' on
4/21/2003 10:20:59 AM
[1] Database FSPRD84A: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'.
** Execution Time: 0 hrs, 0 mins, 8 secs **
End of maintenance plan 'FSPRD84A Integrity Checks' on
4/21/2003 10:21:07 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Any suggestions?
Hello, everyone:
I have a Database Maintenance Plan that back up some databases and delete old backup files that more than two day. Can any one have idea which action is first, backup or delete old files?
Thanks
ZYT
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
Overview of my database: size - 45 GB, Full Recovery Mode, Log shipping ever 15 minutes; Full Backup nightly; Optimization and integrity checks on Sunday night.
The Database takes a transactional log every 15 minutes and ships it to our failover database. Usually these are small logs and are executed in matter of seconds.
On sunday night there is a problem. The optimization job (part of DB maintenance plan) runs for 3 hours and during this time the transactional log backup continues to runs and takes close to 4 hours of complete. When the tran log completes its close to a 30 GB file and this is causing problems with space and shipping. This also causes the failover server to be out of sync and at times we have to re setup log shipping.
Does anyone know if there is a way to get around this problem. Appreciate all help and comments.
I need to create sql 2005 standard maintenance plan for system and user databases and database maintenance plan should include Reorganize index task, full backup task, maintenance cleanup task(for backup files), history cleanup task.
What should be the tasks order ? please let me know....I have to complete this on 8 servers by 11/16/2007 10 AM PST
In sql 2000 database maintenance plan, any rebuild index task is there ??
I have in production, the database maintenance plan that runs the nightly backups. I want to restore this to a completly different server I am setting up for testing. What is the best way to restore a backup on one server (that was done using the maintenance plan) to another server? I tried just mapping a drive and then forcing a restore, however, I got some error message about an ID or something. Maybe I need to do it through query analyzer and there is an easier way to force it? thanks!
View 5 Replies View RelatedI 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
I am using Database Maintenance Plan to run backups. But I am quite short in disk space. So I declared to remove previous backups to release disk space. Still have problem when writing backup file onto the hard drive. I suspect that SQL first tries to complete backup and put in on the disk and only after that removes old file. Could not find any tips in BOL or elsewhere in what order this all goes - backup - then removal or removal - then backup. If anyone know I would be very happy for the rest of my life.
Thanks.
Hi all-
I have a database maintenance plan set up to run for user databases. The integrity checks job for a user database failed today with the error 'error 7919 Repair cannot be processed, db must be in single user mode' . I have scheduled the job to run once every week. Is this a bug in sql server 2000? I thought that the db is placed into single user mode when the integrity job starts. Please advise.
Thanks,
ndba
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...
Hi there,
I have a Database maintenance plan (DMP) that always fails!
The plan reorganises data and index pages and checks database integrity.
The plan covers several databases, and it always fails on the same database.
The error message (in the DMP history) is the following: "[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0: This server has been disconnected. You must reconnect to perform this operation."
The odd thing is that the DMP is locally executed, so I don't see why the network could be an issue here.
Thank you for your time!
gilles25
Hello, everyone:
How to check the function of a Database Maintenance Plan? For example, I want to check how how many databases are backed up in this plan. Thanks
ZYT
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.