Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Posts
    3

    Unanswered: Auto grow enabled but DB won't grow

    I've been through the forum and read a number of threads on people's DBs not growing and the answer usually is they don't have auotmatically grow data file. Unfortunately I have this on, but when I look at the properties of the database it reports the space available is 0.00 MB? Up until about two weeks ago I was showing appx 48% space utilization. When I ran an SP to show growth, it tells me that it was expanded by 20% yesterday, but SQL Server is still telling me the space available is zero.

    The log file is also set for auto growth. The DB is 14.5 GB in size and the drives still have around 92 GB of space.

    Has anyone experienced this before? Any ideas? Does anyone know of an SPs that can give me detailed info on internal data file size compared to stated size (i.e. wasted space in data file)? Is SQL Server doing something funny in the way it is seeing the database or data files individually? Any help is appreciated.

  2. #2
    Join Date
    Sep 2004
    Posts
    15
    Hi,
    Have you tried DBCC SHRINKFILE? It can move the pages to beginning of the data file and freeing unused space.
    Regards,
    Leila

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are you getting any messages in the errorlog? Are you loosing any data? My suspicion is that the database is functioning according to the parameters you've set, and that it is perfectly content.

    The first thing I'd suggest that you check is the MS-SQL errorlog (either via Enterprise Mangler or by simply printing out the file). If there are no error messages there, I'd be really surprised if you've lost any data at all.

    The next thing I'd check would be the NT Event log (either via "Manage my computer" or the Control Panel | Administrative Tools | Event Viewer). Look for the red icons for errors and the yellow ones for warnings... Other messages are just status information.

    If those come up clean (no serious error messages), you haven't lost any data yet. At that point, you might want to think about performance issues which might exist, but integrity issues have to come first.

    -PatP

  4. #4
    Join Date
    Jan 2005
    Posts
    3

    Status

    I had not run a DBCC SHRINKFILE yet as I was unsure about the validity of the DB and didn't want to cause more headaches than I have. My initial concern was not necessarily that it was not using its existing space effectively as much as it was why the data files weren't grwing when they should. I checked the logs at the time, and again just now; sorry I hadn't put that in the thread. Everything looks good as far as the NT event logs and the SQL logs, no errors and everything looks to be in order.

    I will run the SHRINKFILE tonight and see how it goes. Pat, what kind of performance issues do you think could be involved in this?

    Thanks to both of you, I'll keep you posted as to what I find.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    sp_spaceused will give a more detailed info on space utilization. And I don't see anything that can be posted in Windows event log that is not recorded by SQL Server error log that can indicate "a data loss." Pat is probably day-dreaming again
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jan 2005
    Posts
    3

    Thanks

    That sp_spaceused comes in pretty handy, thanks. I ended up running
    DBCC UPDATEUSAGE and that resolved the problem. Still not sure why this occured, but I'm hoping this resolves it.

    Thanks again for all of your help, it was very good to bounce this off others.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    SQL Server has historically been very bad about keeping size data in the system tables up to date. The DBCC UPDATEUSAGE(0) command checks these numbers against what is actually used, and makes corrections. Even so, I doubt it is worth setting up a job to run UPDATEUSAGE on a regular basis, so long aas you are not getting errors and such.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •