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.
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 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?
In SQL 2008 R2, if we clone an environment including SQL server, the maintenance plans retain a connection string to the source/original server they got cloned from and are not editable. But, I was able to use a work around by editing them in BIDS and saving them back on the server. But now with 2014, I am facing two issues:
1.I still can edit the package to correct the server connection, with SSDT; but the option to save back to the server is not available any longer!
2.I used to be able to see all my plans under SSIS in 2008 R2 but not in 2014 now. Although, they are listed in SSMS!
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?
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.
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.
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?
I'm using Win SBS R2 2003 with SQL Server 2005 Workgroup Edition, and the database have been up and runnning now.
I was wondering whether I can perform a backup database from the workstation computers or not? That would mean I login as domain users and using the SQL Server Management Studio (located in the server), I will perform the particular database that I wish to backup... Can this be done? If so, perhaps someone could tell me how (step by step procedure) or is there a document about this?
I have about 15, 100mb databases on my SQLserver and was wondering if my db maintenace plan was correct. I backup the db once a day, and the Transaction log gets backep up every four hours. I am also using the "Bulk-logging" option for each database. My main concern was if I ever do a manual backup of one of the dbs, will it corrupt my backup history and stop me from restoring a db sucessfully. Would simple logging be a "safer" option? I just want to have the most reliable plan in place. I would appreciate any wisdom.Thanks.
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?
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?
Can someone tell what could possibly be the problem with SQL7 not running backups that were created and scheduled using the maintance plan wizar. There is no job history, or any record of the backups running. However, I did notice that the SQL Server Agent was set to manual. Could that perhaps have something to do with the jobs not running. If possible could some give me possible solutions or answers as to why these jobs didn't run.
Can someone please explain what the tempdb database is responsible for in SQL Server 2000. The database and log file has grown extremely large and I cannot backup this table for some reason. Thanks.
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).
sql server 2000I am currently maintaining a table that contains 30 Million+ records,30 columns, and 11 indexes and will double within the next six mouths.The application that accesses this table, mainly for read onlypurposes, runs without any problems. We have begun using Crystalreports and are now having problems. When we create reports thataccesses the large table our server has significant performance dip.The application begins to time out and the reports take a very longtime, even with simple selects on indexed field.I have began looking into partitioning the large table on its key fieldand creating a partition view. But from what I have read this willonly help if we key on the partitioned field. And all other searcheswill actually take a little longer.Archiving old data is not an option. All the data is being usedAny suggestions will be appreciated. Thanks in advance.Rick
We have a SQL Server 2005 instance that I am running a few databases on, nothing too big though.
CRM-3.0
GFI-Network Monitor Database
Future Use
Solomon, and Sharepoint
The goal is to have all of our databases running on one server for backup and maintenace. Currently we are using VMWare to hose all of our servers and we use nightly scripts to backup all of the data. So backups are good to go.
What I am most concerned about is preventative maintenance on the databases. What would people recommend that I set-up for nightly, weekly, or monthly cleaning, integrity checks, etc? Are there third party tools that would do a better job? I am fairly naive on SQL server so any help would be greatly appreciated.
If your principal fails and your mirror assumes the main role and it is in Full recovery mode (which it has to be in Standard edition), then the log files will grow and grow until a transaction backup and shrink is executed.
I have maintenance plans running daily (and hourly for transaction log backup/shrink) on my principal, but nothing on my mirror.
Do I need to set up the same maintenance plans on my mirror server???
Could 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?
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 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
I am using SQL server 7.0. After restoring all the databses (Exceptdistribution) Everything seems to be working fine except the backupmaintenance plan. I put following detail in the maintenance plan"General Tab:Plan Name --- DB Maintenance Plan1() All DatabasesOptimizations tab:Reorganize Data and Index PageChange free space per page percentage to 10%Remove unused space from database filesShrink Database when it grows beyond 50 MBAmount of free space to remain after shrink 10% of the data spaceSchedule: Occurs every 1 week(s) on Thursday, at 1:30:00 PM.Integrity tab:Chack database integrityInclude indexes.Attempt to repair any minor problemSchedule : Occurs every 1 week(s) on Thursday, at 1:00:00 PM.Complete Backup tab:Backup the database as part of the maintenance planVerify the integrity of the backup upon completionDisk use this directory: d:SQL BackupsBackup file extension: BAKSchedule: Occurs every 1 day(s), at 2:10:00 PM.Transaction Log Backup tab:schedule: Occurs every 1 day(s), at 2:00:00 PM.Reporting tab:Write report a text file in Directory: D:SQL BackupsLOG "Problem:My databases(mroduction as well as systems) sre not at all gettingbacked up according to above plan.Error Message in the Log Directory:"Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO(ODBC SQLState: 42000)] Error 4062: [Microsoft][ODBC SQL ServerDriver][SQL Server]Cannot open user default database '<ID>'. Usingmaster database instead."In the SQL server registration properties the login through whichgetting logged into the EM for that, default database is "my productiondatabase".Is this problem because of the distribution db which I am not able torestore.Please helpDeepak Sinha
We have lately experianced a strange problem with our SQL Server 2005 x64 (SP2) that is NOT consistent but when it happens it happens on the same time.
Almost every night at 03:30 one of our databases (not all) seems to be down or locked. When i have a look at the order table in this database I can see that we have stopped recieving orders after 03:30. Two hours later (05:30) I can see the following error each minute in the error log until we reboot the server:
All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on LCK_M_IS. Process Utilization 0%%.
As we have a maintenance job running at 03:30 it feels like this is the problem. The job performs the following tasks: "Check Database Integrity -> Rebuild Index -> Reorganize Index"
When i look at the history of the job it looks like it's not completed and only the "Check Database Integrity" task was runned. No error message here either.
Also when i look in the error log i can see that the Maintenance job is started but never ended. Worth to notice is that I get the follwoing info in the log after the start-message:
Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.
Also, when i run this job manually daytime it works great!
Anyone having any idees on this? Is it possible to track this even more? I'm tired of restarting the server 03:30 in the morning =)
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
I'm a database newbie, trying to decide whether to create an SQL Server or Windows Application project that uses SQL Server 2005 Express. As I understand it, the advantage of an SQL Server project is that the app deploys somehow as part of the database. If that's the case, how would I handle the scenario where the database has been updating at customer site and I need to install just a new release of the software? Would I be better of creating a Windows Application project and connecting to the database explicitly? Thank you.
I've had problems trying to get a new maintenance plan on a new server to work.
I'm running a plan that does an integrity check, reindex, shrink, backup, the a maint cleanup to get rid of backups older than 2 weeks.
I'm getting failures that say:
Error Number -1073548784
Error Message: Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'BAK',N'2007-10-18T18:43:41',1 " 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.
Yes, this is a post SP2 server. Current version, with the patches I've applied looking to fix this is: 9.0.3054
Additionally, if you go in to edit a maintenance cleanup task, you cannot click on OK, or the T-SQL. Changing values within it does not help.
I want to prepare for the TS: Microsoft SQL Server 2005 - Implementation and Maintenance exam. I want to know that how can i tackle it in the proper way. Can somebody please give me tips & tricks or study guide?
Can anyone please provide me with some Best Practises for setting up Maintenance plans? IE what order should things be done in? What should be done daily, weekly, monthly, yearly... I am attempting to setup a new server and I am now 100% sure I have the maintenance plan setup correctly... expecially when I attempted to run it last night it and ran for over 8 hours and still didn't do the backup's yet. I had to reboot the server this morning to make it stop using 100% of the CPU. So please any help would be wonderful.
I've seen a few threads on this which seem to suppose the problem is on Express edition rather than Standard edition. Not so here. This is definitely Standard edition, SP3 as seen by running SELECT CONVERT(char(20), SERVERPROPERTY('Edition')) SELECT CONVERT(char(20), SERVERPROPERTY('ProductLevel')) In SSMS Under the Management folder there is no "Maintenance Plans" folder. There IS a "Maintenance Plans" folder under the Legacy Folder. I have another instance which is Standard and SP1 which looks just fine.
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
