Periodic Maintenance Like Sp_delete_backuphistory?
Jul 20, 2005
I'd like to remove any cruft that's built up in the system over the
last couple of years. Obviously there's old information no longer
needed, I'm just not sure what is there.
I've been cleaning up backup history in MSDB with
sp_delete_backuphistory (leaving the last year's data) - what else is
there?
P.S. to anyone else who needs to use sp_delete_backuphistory - if you
have a lot of backups, you really need to add some indexes, otherwise
it'll take days to remove the history. Go through the SP and figure
out which tables/fields you need to index, as there are several. The
biggies are (in multiple tables) media_set_id, backup_set_id, and
restore_history_id.
Is there an automated way of regenerating the stored procedures, views, and triggers? Currently I drop and recreate them periodically to ensure that the dependancies are all correct and SQL uses the best plan. Is there a way to have SQL Server do this for me???
Since we started using another sales program (1C Enterprise), SQL Server started "crashing" (connection between our program and SQL Server breaks) with this assertion error: Event ID: 17066 SQL Server Assertion: File: <"xrange.cpp">, line=399 Failed Assertion = '0 == pxteRangeHrow->m_pxteFetch'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
After this error DBCC CHECKDB doesn't find any priblems.
I can't find what exactly causes it, sometimes we get it only once a week, but today I got it four times in a row.
We had the same error on all versions of SQL Server 2005, SQL Server SP1, SQL Server SP1+Hot fixes, etc.
select @@version Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
Hi everyone, I am using this temporary data table which gets cluttered after certain time (table is used for registering data waiting for email confirmation). Is there a possibility to empty a data table automatically every day (at a certain moment)? Kind regards,Maxime
I need help from you data warehouse / SSIS experts out there! I have a Transaction Fact Table with dollar amounts as the measurements. The grain is one row per transaction. I want to roll this up into a Monthly Periodic Snapshot based on 5 keys. I am having no problem where there is transaction data for each month.
However, the problem I am having is - how do I gracefully insert the Monthly rows for the five keys where there was no activity in the transaction fact table - I am sure there is a slick way to do this with SSIS but I am definitely having a mental block on how to accomplish this. Any help would be appreciated!
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.
I am testing some maintenance tasks sql commands such as index rebuild, index reorg, update statistics and db integrity check on a SQL Server 2014 Database. This is a new non-production vendor database (DB Size 500 GBs, Log Size 25 GBs) which eventually will be created in production. Currently, it is in full recovery model and without log backups. The database has a whole lot of indexes. I am just trying to rebuild and reorganize all the indexes (that need it), in addition to trying to get an idea of how long these maintenance task will take and the space needed in the log file to complete these tasks/commands. I would like to execute these tasks manually (the first time) to gather the duration and space required information. Eventually, I would probably schedule a weekly job to perform this maintenance.
I ran the index rebuild task on the database and noticed that the log file grew by over 50 GBs. I killed the process and truncated and shrunk the log file back down.
1. Does the index rebuild, index reorg, update statistics and db integrity check commands all use the log file?
2. Does Indexs Reorg have less impact on log file then Index Rebuild?
3. Should a truncate log and shrink log file be performed after these maintenance commands?
4. Should a full database backup be performed after these maintenance commands? Or before the maintenance commands?
I have read and understand that shrinking is not good for the database (could lead to more fragmentation and more data file growth when data is added) and I know about rebuilding indexes when fragmentation is GT 30% and reorganizing indexes when fragmentation is GT 5% and LE 30%.
Since this is a non-production database maybe I should set the recovery model to simple, run the maintenance commands and leave the database in simple recovery model unless the vendor needs it in full recovery model for some unknown reason.
5. With the simple recovery model the log file should be reused in a circular manner and not grow during these maintenance tasks. Is this correct?
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!
My index maintenance job that was setup through Enterprise manager database maintenance fails with the following notice. It ran great for several weeks then it started failing. Any suggestions!!
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Hi, anyone who administering the pretty big database not less than 30 Gb with the average number of rows in a table about 2M and more, please share you experience with maintenace of such a db. Esspecially i'm interesting in:
1) Indexes maintenance (When and how - just regular dbcc, maint. plan or some script to split the job twice and so on.)
2) Remove unused space from db. (not major)
The serever works 24*7, and it's transactional environment. SQL 7 sp3 on claster.
I run the sp. to rebuild all the indexes it takes about 2-3 hrs to determin the objects withfragmentation less than 80% and actually rebuilding, during this process the users experience the performance (specially for update/insert) problem. It looks like I need to change the plan or strategy to do this. Any thoughts appreciated!
I have been given a SQL Server 2000 database to look after which has been set up with a Database maintenance plan. The plan is set to backup the complete database and the transaction log. The backups are written to the local disk correctly but the plan is also set to remove any backup files (both database .BAK and transaction log .TRN) that are over one week old. Complete database .BAK files are written daily and the .TRN are written every hour daily. The .BAK files are removed ok automatically but the .TRN files are not - they are just slowly filling the disk. There does not seen to be anything different between the way the main database and the transaction log is set up in the maintenance plan.
I 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.
For 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
I have a strange thing in one of our Maintenance plans.
On the first tab where you check which databases you're including in the plan I have (say my database name is CAT) a 'CAT' and 'cat' database listed and the one chosen is 'cat'. However my database in all other views shows up in all caps. (even when I do an sp_helpdb)
The backups look like they're working, etc. but it just seems weird. If I go to create a new plan it only gives me the one option 'CAT' which is really what's there. I'm new and I'm thinking the database at one time was 'cat' and this is when the maintenance plan was created. Then it was renamed to 'CAT' and there's the two db's showing in the old mainenance plan.
What would you do? Create a new plan with "CAT" and just get rid of the old one with the weird 'cat' and 'CAT'?
I've created a database maintenance plan to backup a database, but it just isn't happening, am i missing something. The maintenance plan appears to be created successfully.
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.....
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).
Help !! I am running a database of 500-600mb 20-30% of which is new data daily (5 day old data being deleted as part of the nightly maintenance) And my nightly maintenance is regularly taking an hour plus. CheckDB, New Alloc, Catalog, re-indexing and dumps are performed nightly (2am ish) and as the system is in constant use I cannot afford such a long task. I can't use weekly dumps/checkDB as we use transaction log replication and these are dumped every minute. I really need some suggestions on how I can improve matters. The deletion of old data in particular is taking a long time due to the use local variables but is there a faster way to do this :
OPEN tnames_cursor FETCH NEXT FROM tnames_cursor INTO @connectionid WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN Select @dRent = DeliveredRetention from ControlDB..connectiontable where ControlDB..connectiontable.Cid = @connectionid Delete from MyDB..Table where Cid = @cid and DateDelivered != NULL and Datediff(hh,MyDB..Table.DateDelivered,getdate()) >= (@dRent*24) END FETCH NEXT FROM tnames_cursor INTO @connectionid END DEALLOCATE tnames_cursor GO
These jobs have also started running out of locks and deadlocking on occaision which seems odd as the system has 10000 available (escalating at 2000) Any Suggestions would be very much appreciated
hi, I have SQL 2000 ProductionBox.It is in 24x7 environment. We need to maintain data only for 30 days. Even if I schedule deletetion of data on daily basis - SQL will take the lock as data we receive is too huge. Secondly,Since the indexes are heavily used - defrag don't work for them.the only option I think I am left with is to rebuild the indexes.Though in SQL2K - index creation is on fly - but here we are talking of table sizes of 8-10GB. I suggested my Boss to bring down the box for few hours for maintenance.but he insists that since this is 24x7 - Box can never be brought down. I am finding it hard to convice him and do my job. Any idea on how to rebuild the indexes & how to delete this many records(avg.50k per day-data of xml type)without creating a block is highly appreciated...or how to convince my boss to give me a window for maintenance...:) TIA
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
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?
I have been having an extremely annoying problem with SQL server. About 3 to 4 time a day, it starts running some job that takes 30+ minutes to finish. The problem is that it bogs the system down, and consumes so many resources, that it it is almost impossible to run anything while the job is running. Most of the time, this job runs when the server is idle. And, much of the time, it has been idle for at least 30 minutes, and often longer. Also, there is excessive hard drive activity while this task runs.
I am unable to find out what is going on because Enterprise manager times out trying to connect to it, and other tasks remotely connecting either time out or get a network error trying to connect. I have task manager running all the time and it shows task 'sqlservr.exe' hogging the system when this is happening.
Can anyone shed any light on what is happening, why, and how I can stop this?? If it is performing maintenance, is there a way to get it to schedule this for specific times rather than during normal idle system activity?
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' ??????
Hi. I'm totally new to this whole sql server world so bear with me on this. I tried creating a maintenance plan which consist of a backup of all of my db on the server and whenever I try to run the job it creates, it just hang there like I have done nothing. I have created the maintenance plan with the administrator account on the server and I have tried to run it like that but no dice. If any of you can give me any hint on what could be happening, I would be very appreciated.
I was interested in finding out if anyone knows any helpful websites, articles, and/or postings for recommendations on server maintenance.
Maintenance such as defrag, logging...and so on. Our company has a good backup system but we would like to improve on everything else (basic/advance maintenance).
If you have any best practices secrets or know of any good resources, it would be much appreciated to hear from you. If you need more info, just let me know. We have SQL Server 2005.
hi everyone.. this is a little bit weird .. i am trying to make a backup strategy. i am using sql2005. when i go to maintenance plan. right click >> new maintenance plan... nothing happens.. if i go with the maintenance plan wizard everything goes normally. after doing the backup, if i right click on it and press modify , nothing happens too. what i mean by nothing happens is that it doesn't open the "design view". the back up is doing normally.. but i need to set a range of 5 days before overwriting the oldest backup. any idea what is going on or what am i missing?! thank you
Created weekly (full backup) and a daily (differential backup) Maintenance Plans using the wizard. I formatted the server, installed the OS and SQL. Restored the full backup (No Recovery Mode), then restored the differential backup (Recovery Mode), tested and all worked well.
Then I noticed the original Maintenace Plans I created (Full and Differential) were gone; makes sense as I had formatted the server.
Is there a way to create a Maintenance Plan file or script that I can save and just add back to the server??
Hope that makes sense. Any help appreciated. Kerry
We have a training server and I've had a request that after each training session, we have the ability to quickly roll the training server back to its previous state so that the next group of people can be trained with the same examples.
Doing a restore requires that the maintenance (defrag, warm cache, etc..) also runs after that before the server performs fast enough.
So I was thinking of snapshots as an alternative. When you roll back to a snapshot, does that invalidate what's in the cache or have any adverse effect on query plans, stats, or indexes?
Could someone advise and/or correct me with my thoughts on how I would do my db maintenance plans?
(db's on SQL2000 as 'full' model)
Backups: 1) Daily Transaction log backups scheduled frequently enough. 2) Full Backup scheduled daily. Good way to start I presume ;)
Maintenance: Would be scheduled daily if possible, on non-production hours and if not colliding with daily full BU schedule.
3) Full DB reorg data&indexes. 4) Update Query Optimizer Stats (although 'Auto Update Stats' is on) 5) Shrink the logfile (ldf) as I presume this will have grown due to previous maintenance jobs. 6) If 5 ok, alter ldf filesize back to new allocated size.
