| Author |
Topic |
|
DBA007
Posting Yak Master
145 Posts |
 Posted - 2010-06-17 : 14:34:09
|
| Team,One of my maintenance plan shrink db has failed with below error.even though the server connection was goodMicrosoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 64-bit Could not load package "Maintenance Plans\ShrinkDBs" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Unable to complete login process due to delay in opening server connection). The SQL statement that was issued has failed. Source: Started: 2:00:11 AM Finished: 2:01:01 AM Elapsed: 49.656 seconds. The package could not be loaded. The step failed. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
DBA007
Posting Yak Master
145 Posts |
Posted - 2010-06-17 : 15:10:14
|
| This is not a production db,daily there are number of inserts,deletes,updates and jobs were running based on this db,so I have created a maintenanceplan to shrink that db and release unused space,this is scheduled to run everyday. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-06-17 : 15:32:32
|
| This is a very bad idea. You are causing performance issues by having this run on a scheduled basis. But at any rate, can you increase the timeout value for the connection? The error message indicates that it was too slow to connect. Have you considered just using DBCC SHRINKFILE directly in a SQL job rather than using a maintenance plan? You won't get that error with DBCC SHRINKFILE.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
DBA007
Posting Yak Master
145 Posts |
Posted - 2010-06-17 : 15:41:05
|
| thanks tara,can you help me how to increase the timeout value for the connection |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
DBA007
Posting Yak Master
145 Posts |
Posted - 2010-06-19 : 15:54:09
|
| Tara,this db has to be shrinked when ever it goes beyond 50mb,by using the "DBCC SHRINKFILE" i think we cant automatically shrink when it reaches 50mb |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-06-20 : 12:34:07
|
| Why does the database need to be shrunk at all? It's just going to grow again, and at some point - if you keep doing this - you won't have the space available to grow the file and your jobs/processes will fail.Not to mention, every time you shrink you are fragmenting the indexes and every time it grows you are fragmenting the files. This is going to cause performance issues on your larger databases (it probably won't cause issues on a 50MB database, which is really tiny). |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2010-06-21 : 16:44:15
|
| Are you simply trying to keep the log size down? If it's not production, why not set the mode to "simple" or simply create a backup strategy to keep the file size in check. Being a non-production db, I'd opt to change it to "simple" mode.Terry-- A word to the wise ain't necessary - it's the stupid ones that need the advice. -- Bill Cosby |
|
|
|
|
shrink db failed