How can I repair a sudden increase in Database size SQL Server 2005

My database has recently suddenly increased in size from 237,985 KB to 1,685,030 KB (August 12th to 13th). The system has been working perfectly for some 8 years.
A "properties" selection on "master" gives Size 25.06 MB, Space Available 1.22 MB
A "properties" selection on "Live data" gives Size 1959.56 MB, Space Available 1559.52 MB

Every night a Maintenance Plan is executed - a log of this shows failure every day apart from 17th and 25th August! The time taken to fail is anywhere between 3 minutes and 9 minutes.
The operations carried out in this are:- Backup, Check Integrity, History Cleanup, Maintenance Cleanup,Rebuild Index, Reorganize Index, Shrink.
When it fails - everything is ticked apart from "Shrink".
I have increased the timeout value from 600 to 900 secs in case it were taking too long - however the on demand maintenance task then ran for 10m 25sec before failing.
I do have some .trc files in the Program files\Microsoft Sql Server\MSSQL.1\MSSQL\LOG which I have tried looking at but "it's all greek!"

Examination of the Maintenance plan logs (in the LOG directory above) shows that the order in which the jobs are run appears to be completely random (and concurrent). The failure message reads "backup and maintenance operations .... must be serialized"  - there appears to be no means of specifying either "completion" or "order"

Anyone able to shed some light?
Regards
keith
keith66minersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Probably the LOG files or possibly TEMPDB.

First thing to do is to check the actual disk space.

SQL is a strange beast - it tries to allocate physical disk space and then proceed to fill up that space. So we see physical files with free space internally.

What can happen after a big batch run, or a missed transaction log is the consumed space within the logs start off where it thinks it is up to. It doesn't necessarily re-use empty space, it has pointers where to go.

if it is so large that there isn't physical disk space then you can be in a bit of trouble.

Easy enough to fix but you need to take complete control for a while.

For example TEMPDB is recreated every time you restart SQL server.
Transaction logs can be set to simple recovery and then back again.

Important that you do full backups and separate transaction log backups.

Have a read of : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_657-Managing-the-Transaction-Log-for-the-Accidental-DBA.html

And check those individual files on disk (also use SSMS to right click on DB and look at properties, You can also right click on DB go into Tasks , Shrink, Files and use the drop down to check the LOG)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
I will start with a recommendation:
- Don't use MSSQL Server Maintenance Plan. Use instead Ola Hallengren's maintenance solution.
Shrink database should not be part of any maintenance plan. You shouldn't shrink a database if you have the correct maintenance plan.

About your issue, check the data file size and the transaction log file size for the database that's having size issues. I bet it's the transaction log that growed. If so, execute a transaction log backup and shrink ONLY the transaction log file.
David ToddSenior Database AdministratorCommented:
Hi,

Depending on the version of SQL different index maintenance operations do different things to the free space in your datafiles. Back in the day on SQL 2000 there would be 1.4 times the finished size of the biggest index left as free space in the database, as it need this space to work in.

Something to check is if the index padding and free space etc have changed to silly numbers, and you have the equivalent of a small novel( your data) but written out with only one word on each page ...

HTH
  David

PS I second Vitor's comments about the inbuilt maintenance plans. I use Ola's and have a lot of success with them
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

keith66minersAuthor Commented:
Thank you Victor and David, but I am not going to change the supplier at this stage.
Mark I have read with interest your paper - I believe it will help.
I have stopped and restarted the SQL services, so I believe Tembdb should have been reset
I have now sorted the problem of establishing sequential jobs - they all run successfully now albeit that the size of the database.mdf file is still 1.4 GB. I am not actually working again until next Monday - I will be able to get some exclusive time on the system then.
Regards
Keith
Vitor MontalvãoMSSQL Senior EngineerCommented:
the size of the database.mdf file is still 1.4 GB
What's the size used by data?
keith66minersAuthor Commented:
The position has been overtaken by the forced migration to server 2014
Thanks for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.