Post reply

Shrink Database problem

  • April 15, 2003 at 12:57 pm

    #58957

    Hello,

    Our company runs a SQL Server 7.0 production database.

    I created a Maintenance Plan to shrink this database. In its turn this Maintenance Plan automatically created a job that was scheduled to run every Sunday early in the morning.

    This job is always run successfully. The only problem, it runs only for few seconds and doesn't do anything to the database.

    These are the settings.

    Optimizations:

    Remove unused space from database files - checked

    Shrink database when it grows beyond - 50mb

    Amount of free space to remain after shrink - 10%

    In the options tab of the database properties:

    Auto shrink - checked

    Truncate log on checkpoint - unchecked.

    Any help would be appreciated.

    Thanks in advance.

    Alex

  • April 15, 2003 at 1:14 pm

    #454292

    What size is your database now?

  • April 15, 2003 at 1:17 pm

    #454293

    13GB; 3GB of which is unused space.

  • April 15, 2003 at 1:39 pm

    #454298

    And what was the originally size of the DB when you first create it?

  • April 15, 2003 at 1:51 pm

    #454299

    I beleive, it used to be 4-5 GB

  • April 15, 2003 at 2:43 pm

    #454305

    It's not read-only is it? You can't auto shrink read only databases. What is the minimum size of the initial data/log files? Remember it can't shrink below that level.

    Darren


    Darren

  • April 15, 2003 at 2:56 pm

    #454309

    Do you have any transactions running? Are you shrinking the transaction log?

    Patrick

    Quand on parle du loup, on en voit la queue

  • April 16, 2003 at 7:25 am

    #454348

    Thank you guys for all your replies. I'll try to answer your questions.

    The database is not read only. I don't know the minimum size of the files (I'm not sure if I can figure it out).

    Both files are set to automatically grow by 10% with unrestricted file growth.

    At the time this job is scheduled I don't have any transactions running.

    Yes, I beleive this maintenance plan should shrink the whole database (i.e. both files)

    Any ideas?

  • April 16, 2003 at 10:38 pm

    #454466

    I had a simalar problem on two of our SQL 2000 Servers. When I tried to shrink the databases through the EM it appeared to do nothing.

    I created a job that runs DBCC Shrinkfile and it shrinks down fine. Maybe give this a shot.

    Angela

  • April 16, 2003 at 11:03 pm

    #454467

    Hi,

    I have tried to shrink the DB with DBCC shrinkfile and SHrinkdatabase as well and in both cases the log file shrunk properly but the data file didn't. although the size of teh DB has grown to 4 GB and i know for sure that it does not contain that much data. any idea why the data file is so fat?

    Kind Regards,

    Affan

  • April 17, 2003 at 5:17 am

    #454481

    I am having a similar problem except cannot shrink the log or database for our SQL 7.0 box.

    I did find some reading through the 7.0 BOL and from this forum that talks about flags not getting set properly (in the right order) with SQL 7.0 which causes the code to run and not do anything.

    Our solution was to migrate to SQL 2k (Which was planned and implemented) prior to this issue becoming critical.

    I am very curious to find if you do get a resolution.

    Thanks,

    AJ Ahrens

    SQL DBA

    Custom Billing AT&T Labs



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • April 17, 2003 at 5:21 am

    #454483

    AER,

    Currently there is another topic open regarding similar issue TOPIC 10543. Think it might be useful

    AJ Ahrens

    SQL DBA

    Custom Billing AT&T Labs



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • April 17, 2003 at 6:30 am

    #454503

    AER,

    can you Please paste the link as i could not find the way to search the Topic ID.

    Regards,

    Affan

  • April 17, 2003 at 6:31 am

    #454504

    Sorry AER

    Actually whoteegan Please paste the topic id link you mentioned.

    REgards,

    AFfan

  • April 17, 2003 at 7:02 am

    #454512

    Thanks Angela,

    I think your idea would work.

    I have a remote access to my database. I'm checking it's size every Sunday.

    If it didn't shrink, I'm shrinking it manually using a DBCC srinkfile method.

    I think I can create a package which will be looking for a percent of unused space in the database and if it is, let's say, > 10% then I could tell it to shrinkfile.

    And then of course I could schedule a job from this package. I don't know why I didn't come to this idea earlier. This would definitely save my time.

    This probably is a perfect way around. But why the hell a maintenance plan is not working?

    I'm also curious about Topic 10543 AJ is mentioning, but I cannot find it on this site. Could you please submit a link?

    Thanks for all replies.

    Alex

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply