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.
I am new to always on management, I would like to know if exist a best practice for configuring maintenance plan on a alwayson system (two nodes) or is just running the maintenance plan wizard on both nodes.
View 8 Replies View RelatedI have a table with primary key and also clustered index on that primary key column. I need almost all columns from that table. When I wrote the select column names, it showing that Index scan occurred. How can I avoid that Index scan and change to index seek? When I check the fragmentation of that Index it is showing more than 34%. Is that fragmentation is ok or do I need to reorg the Index?
View 5 Replies View Related
Getting this error when running a maintenance plan step. The backup steps work fine.
" 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-04-15 09:15:03.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-04-15 09:15:45.02 Code: 0xC0024104 Source: Reorganize Index Task ... The package execution fa... The step failed."
The step is run under the sql service agent account.
We are at SP2 on a 64 bit machine.
Thanks.
Sam
Hi I have an issue while attempting to clean/build my Integration Services solution which prevents me creating my deployment SSIS deployment package.
The solution comprises of around 73 packages totally a physical file size of 103 MB. Most packages are ~1/2 MB with a ~1/2 packages around the 4/5/6 MB
I cannot seem to clean or build/rebuild the solution as I keep getting the following exception message
Error 1 Error loading €˜PACKAGE_NAME.dtsx' : Exception of type 'System.OutOfMemoryException' was thrown.. C:FOLDER1PACKAGE_NAME.dtsx 1 1
This randomly occurs with different packages but the commonality is it occurs with the package above 4MB.
My workstation has Windows XP Professional with SP2 and 2GB RAM and Visual Studio 2005 with SP1 and SQL Server 2005 with SP2.
A look at task manager in Windows shows the devenv.exe process to be ~675MB when the above exception occurs. There are no other applications running in the background.
Can anybody kindly suggest any possible resolutions or any known fixes for this problem? Maybe the possible to build SSIS packages from a command prompt?
Many Thanks in advance
Hi,
when I´m trying to save a SSIS Package in Visual Studio I´m having the following problem:
===================================
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft Visual Studio)
------------------------------
Program Location:
at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
at System.Text.StringBuilder.Append(Char value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BufferTextWriter.Write(Char ch)
at System.IO.TextWriter.Write(Char[] buffer, Int32 index, Int32 count)
at System.IO.TextWriter.Write(Char[] buffer)
at System.IO.TextWriter.Write(String value)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializePackage(IDesignerSerializationManager manager, Package package, TextWriter textWriter)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializeComponent(IDesignerSerializationManager manager, IComponent component, Object serializationStream)
at Microsoft.DataWarehouse.Serialization.DesignerComponentSerializer.Serialize(IDesignerSerializationManager manager, Object value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseDesignerLoader.Serialize()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush(Boolean forceful)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseContainerManager.OnBeforeSave(UInt32 docCookie)
The dtsx is around 8MB size, I have installed the service pack, what else could be?
thanks
Are there anyway I can check my last database reorg date and time using Tsql ?
View 5 Replies View RelatedI am trying to load a XML file into a string variable using a XML Task. The file is 67,936 KB. When I excecute the package in the dev environment I get the following error:
[XML Task] Error: An error occurred with the following error message: "Exception of type 'System.OutOfMemoryException' was thrown.".
Has anybody run into this before? In the next step I pass the XMLData string to a stored procedure which loads it into a table with a XML data type column.
All,
I have a large package (I know the recommendation is to have one package per data flow.) It has 20+ data flows in it. Personally I have found it much too complex to manage dozens of packages just because I want to have more than one data flow in my package.
I have been working on this package in an iterative manner over the past several months. Recently, I noticed I started getting the error message: "Exception of type 'System.OutOfMemoryException' was thrown" when I went to save my package. This is _extremely_ frustrating, because when this happens, all the changes I have made are generally lost. Occasionally, I have noticed if I close some other BIDS windows, or just wait a bit, I will be able to click save again, and it will actually save. Usually, I am forced to just "end-task" Visual Studio.
Other than splitting the package up into 20 separate packages, is there a way around this problem? I would rather put up with the lost changes than switch to dealing with 20 separate packages. It just makes things to difficult to manage when everything is split up into so many packages - particularly when I am passing variables around from parent to child packages.
Please help!
Thanks,
David Baldauff
Hi,
My server configuration is
Intel Xeon(R) X5355 @ 2.66GHz, 3.75 GB RAM
Win 2k3
I have been receiving the above mentioned error when I try to use the out of the box feature of "Export To Excel" provided in the Reporting Services, the reports are hosted on MOSS 2007 using the Reporting Services Add-In. The number of pages generated by the report is 500+, so I don't think exporting a report of around 500 - 1000 should give out a memory exception since we have sufficient RAM on the server.
Please advice a solution to solve the error.
Thanks in Advance.
Vikas Mestry.
Hello,
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.
Thanks.
when I call ((System.Collections.ICollection)resultSet.ResultSetView).Count multitime I get out of memory exception
does the calling of this statement reserve memory and how can I release it?
This is my error text... how do I get this nonsense to stop?!
===================================
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft Visual Studio)
------------------------------
Program Location:
at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
at System.Text.StringBuilder.Append(Char value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BufferTextWriter.Write(Char ch)
at System.IO.TextWriter.Write(Char[] buffer, Int32 index, Int32 count)
at System.IO.TextWriter.Write(Char[] buffer)
at System.IO.TextWriter.Write(String value)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializePackage(IDesignerSerializationManager manager, Package package, TextWriter textWriter)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializeComponent(IDesignerSerializationManager manager, IComponent component, Object serializationStream)
at Microsoft.DataWarehouse.Serialization.DesignerComponentSerializer.Serialize(IDesignerSerializationManager manager, Object value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseDesignerLoader.Serialize()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush(Boolean forceful)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseContainerManager.OnBeforeSave(UInt32 docCookie)
Hi,
I got an strange problem with one of my packages.
When running the package in VisualStudio it runs properly, but if I let this package run as part of an SQL-Server Agent job, I got the message "The script threw an exception: Exception of type 'System.OutOfMemoryException' was thrown." on my log and the package ends up with an error.
Both times it is exactly the same package on the same server, so I don't know how the debug or even if there is anything I need to debug?
Regards,
Jan
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!
Hello All
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 would be very grateful for any ideas
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'?
Any other suggestions or ideas on what happened..
ann
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.
responses appreciated.
thanks
Todd Minifie
Can you generate script for a maintenance plan?
I know how to script a job, I was wondering about a plan.
If not, whats the best way to record the configuration?
Thanks
Lystra
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
Windows Server 2003R2 w/ Sp2
SQL 2005 w/ Sp2
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
Hello 2 all,
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.
Rgds,
T.
Hi
As per my backup policy I need to do a full backup dialy & transactional backup every 30 minutes , can i use the Maintenance plan to do this
or should I write T-sql statements & create jobs.....
I mean whats the difference between the two methods.
Thanks
Hi folks.
Our products are VB6-based interacting with MSDE2000 i.e. none of our clients have EM. We have auto-backups performed twice a day by default and we encourage people to keep it to a least 2 per day.
Yesterday, one of our clients reported a problem. Upon investigation, I did a DBCC CheckDB WITH ALL_ERRORMSGS which returned the following:
Object ID 1461580245, forward row page (1:159), slot 50 points to page (1:234), slot 43. Did not encounter forward row. Possible allocation error.
So after reading a lot of posts and blogs from Paul Randal, I proceeded cautiously to copy the DB then perform a DBCC CheckDB REPAIR_REBUILD which had no effect, then a DBCC CheckDB REPAIR_ALLOW_DATA_LOSS which also had no effect. Then, I determined which table was at fault via DBCC CheckTable and I exported its data to a blank table where I discovered the missing data row and corrected for it manually. End of story.
Not a funny situation. The worst part is that this defect may have been there for a very long time, meaning that restoring the latest backup would not have helped the situation. I now realize that relying on backups alone is a huge no-no.
So, having been scarred into reality, I would like to install an automated maintenance plan. I'm used to doing it on my personal station using EM but, as stated above, I can't do that for the clientele. I was thinking about simply shrinking the databases and then doing a DBCC CheckDB WITH ALL_ERRORMSGS on all of them before performing a backup. Would this be a complete enough procedure or should I be doing something else? My understanding is that this will verify index structure and data integrity, and not attempt to repair anything which is uncovered.
Any thoughts of wisdom would be greatly appreciated.
Thanks!
Hi,
I have created maintenance plan. It was working fine. When I tried to edit it gives me error. How to sollve this error?
Please help me.
Cannot show the editor for this task.
ADDITIONAL INFORMATION:
Value of '11/8/2007 12:00:00 AM' is not valid for 'Value'. 'Value' should be between 'MinDate' and 'MaxDate'.
Parameter name: Value (System.Windows.Forms)
Hi All,
As a part of creating a maintenance plan i want to copy the backfiles from the server to another machine.How can i achieve this .Please help its very urgent.
Thanks in Advance
RKNAIR
Hello everyone!
I was wondering if there were any best practices for creating maintenance plans?
Im just getting started into the DBA world and have been delegated the task of creating maintenance plans for our 8 SQL servers.
Right now, our backup policy is Fulls on Saturday, differentials Monday-Friday.
Also, since im new, if you defragment the database and rebuild the index, does that have the possibility of "breaking" anything?
Just looking for some good articles, or anything to get me started on best practices.
Much appreciated.
TCG
I am relatively new to SQL Server 2005. I have gone ahead and created a maintenance plan which backs up all our databases. This plan is scheduled to run every night. The problem is that each time the plan runs, new backup files are created which quickly uses up valuable disk space.
How do I set up SQL Server 2005 to only create one set of backup files and overwrite any existing files when the maintenance plan is run? I tried playing with the "backup set will expire" settings, this did not seem to do anything.....
Thanks
I have a SQL 2000 server that has a small but very important database (about 5GB). The current maintenance plan does trans logs every hour and full every day. Currently they are to file on the same drive array. I would like to send them to a share on another server just to be really safe.
Would it be better to
1. Redirect the maintenance plan so that trans logs and backups go directly to the share
or
2. Keep the maintenance plan back ups to the current location and write a script that runs every hour and copies the .bak files to the share.
Also, since the database is so small should I just do full backups every hour instead of transaction?
In the Enterprise Manager of SQL Server 2000 I have set up a maintenance plan which rebuilds my indexes. I've stuided the documentation, and from what I've learned what happens behind the curtain is that several DBCC REINDEX commands are being issued.
Question:
If I have 20 tables and 40 indexes: Will SQL Server do the maintance plan in 1 single transaction, or will it divide the it up to eg. 20 or 40 transactions?
-h
Are there any known issues using Maintenance Plans to backup DBs in SQL Server 7.0
Thanks
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
Hi,
I am getting the following message from one of my scheduled Maintenance plan, anyone know what this mean.
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029)
Martin