Hi,
Does anyone here know of any on-line references on how to optimize index maintenance in sql 2005? Also do you know of any good DBA books that will explain database maintenance and or best practices?
Thanks.
:D
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,I am writing an in house utility to attempt to compare differentaspects of databases.I am currently writing the queries to list all of the indexes in thedatabase (including primary key indexes at present - I may move theseand compare separately at some point).I would like the following information, in one result set if possible:Table NameIndex NameColumn NameColumn PositionUnique?Now on Oracle, this is easily done with the following query:SELECT IND.TABLE_NAME, IND.INDEX_NAME, IND.COLUMN_NAME,IND.COLUMN_POSITION, COL.UNIQUENESSFROM USER_IND_COLUMNS IND,USER_INDEXES COLWHEREIND.INDEX_NAME = COL.INDEX_NAMEORDER BY 1, 2, 3, 4, 5I have been trying for over an hour now to get the equivalent, and Ireally cannot figure it out. If anybody can come up with this then Iwould greatly appreciate it!Many Thanks,Paul
View 4 Replies View RelatedRebuild index maintenance plan is failed, since we don't have space in the C:Drive we have left the option as it is to sort the results in user databases respectively. These user databases are in E: with sufficient space to rebuild index.
Check the below details.
SQL Server 2005: Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10 2010 10:38:40 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Online reindexing supports in SQL Server 2005 Standard Edition? Job is failing because these options (sort results in tempdb & keep index online while reindexing) is not checked (enabled)?
Are there any examples of maintenance(ReBuild FULL or Incremental) for Full-Text indexes? Are there any index integrity checks that can be done? What is the best way to backup a full-text index?
View 2 Replies View RelatedWe are having very big tables in TBS and wanted to setup a strategy for index maintenance.
View 3 Replies View RelatedI am using Ola Hallengrens scripts for index and stats maintenance but I am wondering what most people to in terms of the maintenance schedules. At present we do an index rebuild reorg weekly, but do people also do update stats nightly?
I suppose there is an element of "it depends" here in that the data may be fairly static so the update stats may not be required, or if heavily updated then perhaps rebuilding indexes may be required more frequently.
Greetings
My Fuzzy Lookup task works beautifully when it generates the lookup index every time it runs, but as I'm planning on running this hundreds of times I'd like it to maintain the index via the trigger. However when it attempts to install the trigger via sp_FuzzyLookupTableMaintenanceInstall I get:
Description: "A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_FuzzyLookupTableMaintenanceInstall":
System.Data.SqlClient.SqlException:
Error number 8101 is invalid.
(I've not included the full stack trace as I figured this would be enough)
The table currently has an After Insert and After Update trigger. CLR integration is enabled in this database instance. Is there some other option I need to set somewhere?
Thanks!!
Hello,
For one of my SSIS projects that does a fuzzy lookup on a table, I opted to create an index and
to maintain the stored index. The index got created and subsequent project execution was able to
use that index.
Now I want to update certain rows in that table. When I run the update statement I get the following error.
How can I retain the index and still be able to update the table?
update location_stage set batchid = 'APR07N'
where batchid is null and eventid = '20070528020041';
Msg 6549, Level 16, State 1, Procedure sp_FuzzyLookupTableMaintenanceInvoke, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_FuzzyLookupTableMaintenanceInvoke':
System.Data.SqlClient.SqlException: Transaction is not allowed to roll back inside a user defined routine, trigger or aggregate because the transaction is not started in that CLR level. Change application logic to enforce strict transaction nesting.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnectionSmi.ExecuteTransaction(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction)
at System.Data.SqlClient.SqlInternalTransaction.Rollback()
at System.Data.SqlClient.SqlTransaction.Rollback()
at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.TranWrap(DataCleaningOperation c)
. User transaction, if any, will be rolled back.
The statement has been terminated.
Recently upgraded SQL Server 2005 x64 to SP2 and upto Build 3159. Since then the Maintenance Plan for Index Reorgs has failed with a System.OutOfMemoryException error. No other errors are logged anywhere. The plan report file has no information either.
Has anybody come across a similar issue?
Thank you.
We have a new database with cdc enabled on all of its tables. This causes the index maintenance task to fail with following message:
"Executing the query "EXEC DBName.dbo.IndexDefrag_sp" failed with the following error: "The unique index 'PK_TableName' on source table '[dbo].[TableName]' is used by Change Data Capture. To alter or drop the index, you must first disable Change Data Capture on the table. The transaction ended in the trigger. The batch has been aborted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly" We would like to run the index maintenance without losing the cdc data. We plan on installing SP2 on SQL Server 2008 R2 soon, would that solve the issue? Disabling the cdc prior to index maintenance and then re-enabling back upon completion; would delete the data as I found in most discussions, but we would like to retain it.
I've been fixing some issues lately where weekly maintenance has been causing logs to grow and filling disks.
Is there any rule of thumb for allocating log space for doing reorgs and rebuilds in a worst case scenario? I'm thinking 3x the largest database size?
I've been watching them run on databases in the range of 50GB where the logs are growing well over that for rebuilds or even reorgs. Once you have a few databases like this on a server, you can suddenly eat through a lot of disk space just for holding logs during maintenance.
Hi all,
I need to find out on runtime, a list of Maintenance plans and Jobs.
i dont know how to acquire the database name on which the plan/job are operating.
i also dont know from which table can i get the tasks ( backup,integrity,rebuild index etc.) that belongs to the plan.
its quite urgent,
10x,
Gil.
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.
The error happened on SQL 2005 sp2 32bit servers. Can someone please help. Thanks very much.
Brian
Server Name: CDNPCCVS3CDNPCCPRODDB3, Job Name: DatabaseMaintenance.Subplan_1, Step Name: Subplan_1, Message: Executed as user: CDNPCCPRODzpcc_sqlsvc. ....3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:00:00 PM Error: 2007-12-22 00:56:32.60 Code: 0xC0024104 Source: Rebuild Index Description: The Execute method on the task returned error code 0x8007000E (Exception of type 'System.OutOfMemoryException' was thrown.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Error: 2007-12-22 03:11:01.83 Code: 0xC002F210 Source: Update Statistics Execute SQL Task Description: Executin
Hi, I'm a novice user of SQL Server.
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?
Thanks!
I want to run a maintenance for a database in MS SQL Express 2005 running on Windows 2003 SP2 server.
I know by default it is not there in Microsoft SQL Server Management Studio Express. Is there a way or any procidure to run maintenance for MS SQL Express 2005 ?
Information Schema is the part of the SQL-92 standard which exposes the metadata of the database. I have written a small article that exposes the same. Let me know your comments on the same. http://aspalliance.com/1380_Information_Schema_and_SQL_Server_2005
Uday Denduluri
Software Engineer
Refer my articles at http://aspalliance.com/author.aspx?uId=62740
Hi All,
I've written a scalar function in C# for the MS SQL Server 2005 that has to have access to two databases in one instance of a server ("database1" and "database2"). I got information from database1, calculate the name of the database2 and got information from user's tables in the database2. But when I'm trying to get informatin from "sys.tables", "sys.columns", "sysobjects" I always get an error "System.Data.SqlClient.SqlException: This statement has attempted to access data whose access is restricted by the assembly."
All databases are inside one server, I can run this quieries in SQL Server management Studio - could anybody help me what should I set/change to get metainformation from another database in the same server (in fact I need a list of fields of a curtain table).
Thanks,
Alex Gerasimov
P.S. Atrribute [SqlFunction(DataAccess = DataAccessKind.Read)] is in the applicaiton.
Hi,
I have scheduled a job that runs every minute.
If the job does not succeed, then I would like my front end application to be notified.
I am not sure if this is a reasonable way but I am thinking of somehow
populating a table (Only if the scheduled job fails) in sql server and then read that table every minute from the front end application.
So, is it possible to populate a table if a job fails?
I do not see any options for this in the properties of the job.
Thanks
How do we determine on a SQL 2005 server what license type was selected during the install? Where is the informationkept and how it is tracked by SQL Server 2005.
Thanks in Advance.
Admindba
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?
Hi there
I feel like a complete nitwit. I've been using SQL 2000 for about 5 years now and regularly set up automatic backups using the wizard for many people. I'm in no way an advanced user and have enjoyed some of the more user-friendly features of SQL 2000 such as the backup wizard.
Well now we have SQL 2005 and I can't find the "wizard". I did a lot of googling and found very little on this. The frustration is mounting - let alone the fact that I had to edit the registry just to be able to perform a manual backup in the first place... :-(
Apparently their is a "maintenance plans" folder but the only place I can find this is under "Legacy" and you can't create jobs there.
Has anybody else had this issue? Is there a bug in SQL 2005? I reinstalled the full version with all options selected and still can't find this folder. Am I just being completely "blonde"?
Hello, everyone:
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).
Did someone meet this bug and have suggestions?
Thanks
ZYT
I need to create sql 2005 maintenance plan for system and user databases which includes Database integrity check and Rebuild index tasks but these two things should happen at different times. Is it possible to schedule these two tasks at different times under single database maintenance plan, if so please let me know...
View 3 Replies View RelatedI successfully created a simple Backup Maintenance Plan under SSMS. But now that the plan is created I need to change the number of days the backups are kept. When I pull up the plan udner SSIS and lok at the tasks I can't locate that option. I couldn't find it under the sql agent either. Anyone know where it is? (Or do I need to recreate the plan from scratch?)
TIA,
barkingdog
Afternoon All,
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.
Joe
In SQL 2005...
How do I specifty in the maintenance plan to only keep 3 days worth of backup.
Andre
I need to build a query that shows which databases on a sql 2005 server are NOT included in a maintenance plan.
So far I have been unable to find a matching column from msdb sys.databases with any column in the following msdb tables:
sysmaintplan_plans
sysmaintplan_subplans
sysmaintplan_log
sysmaintplan_logdetail
Has anyone else been able to solve this challenge?
Thanks!
.40
I want to create sql 2005 maintenance plan which includes Database integrity check and Rebuild index tasks but these two things should happen at different times. Is it possible to schedule these two tasks at different times under single database maintenance plan, if so please let me know...ASAP
View 4 Replies View Related
I have created a simple maintenance plan in SQL Studio. When I double-click on the task boxes (ie 'Rebuild Index', 'Backup') to just make changes or to look, I get the following message:
Cannot show editor for this task
Additional information:
Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)
I cannot rename, edit nor delete this task, Do you know what might be the problem?
Hi Friends,
I was in the phase of switching over to Sql Server Reporting from the normal reports. I plan to go with the SQL server 2005 standard version. Do i need visual studio 2005 to design and deploy report. If I am using only SQL server standard alone for my reporting and publishing, is it possible to assign the user rights to reports, user can just login and can view all the reports that belongs to that user. Since I wont be using the visual Studio 2005, so I want to implement this using the SQL server 2005. So pls help me with your kind information how to proceed ahead.
Thanks,
Sreekoo.
Hello, I'm completly new to SQL server 2005 express and wanted to see if somebody can answer a few questions for me.
I recently had a small application developed to import CSV files into a SQL database. The application imports roughly 200,000 records a day and takes roughly 4 hours or longer to import the data. I greatly under estimated the amount of time that it would take to import the data and I'm looking for some solutions to help speed up the process. I'm currently just using my laptop to hold the data. Would it help speed things up by dedicating a seperate machine to hold the data? If so, what type of machine would be best. I'm looking for a cost effective solution because the data is for personal use.
Any information would be appreciated.
Thanks,