June 30, 2008 at 5:53 am
Hi,
I have scheduled a maintianance plan to shrink database in sql server 2005 using maintainance plan wizard but it is failing due to the error
"Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed."
I checked from my side.Any suggestion or solution to fix the error.
June 30, 2008 at 5:56 am
You created the maintenance plan for remote machine or local one????
June 30, 2008 at 6:11 am
I created for remote machine through remote login access
its working for other sql instances also,but only for one instance its not working.
June 30, 2008 at 7:03 am
Do not, do not, do not regularly shrink your data files! It causes lots of fragmentation and the data files will just have to grow again. Databases need some free space inside to work properly.
If you shrink the file, it will probably just grow again. You'll have spent a lot of CPU time and IOs to achieve nothing, you will have caused internla fragmetnation and possiblly also file-level fragmentation.
See - Shrinking databases[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2008 at 7:12 am
I am shrinking database once in a week.
The job is scheduled to run once in a week.
pls tell me how to fix this error wat i have mentioned previously.
June 30, 2008 at 7:18 am
anjan.ashok (6/30/2008)
I am shrinking database once in a week.
It is not recommended to shrink databases on a regular basis. If you insist on doing it, make sure that you rebuild all of your indexes after shrinking, or your database performance may be reduced after a shrink.
The instance that's not working, check what version of SQL it is, and what the compatability mode of the databases on it are.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2008 at 7:24 am
Gila,
Version and compatilbity levels are same?any other thing to check.
one more question if the data file is growing rapidly wat the solution its taking more space.
alteast log files we can shrink right.
June 30, 2008 at 7:29 am
anjan.ashok (6/30/2008)
one more question if the data file is growing rapidly wat the solution its taking more space.alteast log files we can shrink right.
Find out why the database is growing. Data imports? Index rebuilds?
Logfile shrinks don't cause fragmentation but the recomendation is still not to shrink them regularly. You should figure out what size you need the log file to be based on activity and frequency of log backups, set th log to that size and leave it. The ideal situation is when it never needs to grow and never gets shrunk.
Can you run profiler against the instance that's giving you problems and see exactly what statement from the maint plan is giving an error?
What does SELECT @@version return for the failing instance?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2008 at 7:53 am
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
is the ouptut.
June 30, 2008 at 7:57 am
Do the instances that do work have SSIS installed on them?
I seem to recall that in 2005 RTM (which is what you're running) maint plans required SSIS.
You may want to consider applying service pack 2. There were bugs in RTM that were fixed in the service packs
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2008 at 8:03 am
I think the issue is with the version the instance showiing error version is 9.0.1399 and other instance in which maintainance job ran fine version is 9.0.3054
need to download latest service pack and check.
Any tips to download and install service pack.
June 30, 2008 at 8:07 am
anjan.ashok (6/30/2008)
I think the issue is with the version the instance showiing error version is 9.0.1399 and other instance in which maintainance job ran fine version is 9.0.3054
I thought you said all the versions were the same?
need to download latest service pack and check.
Any tips to download and install service pack.
Definitly get the latest service pack. I don't have a link handy, but it should be easy enough to find on the mcrosoft download site.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2008 at 8:11 am
install SP2 + at least Cumulative update 2 (CU)
We install up to CU5.
(CU8 is current, but you'll need to test if that one works for you !)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply