Post reply

SQL Limits and Suspect databases

  • March 15, 2005 at 8:52 am

    #185497

    Is there a limit on how big a SQL database can be? Everytime my database reaches 15.5 GB  it turns into a "Suspect" database.  BTW is there an easy way to recover from a suspect database?

    Thanks in Advance, Meredith

  • March 15, 2005 at 9:05 am

    #547083

    Hi Meredith,

    I can't remember what the upper limit is on a SQL database but we're currently around the 1TB size with one of ours and it's still going strong (actually I just found it and we're no where near the limit). The maximum database size in SQL 7.0 & 2000 is 1,048,516 TB (or 2GB if using MSDE)

    I think you should be looking elsewhere for a reason your database might be getting made suspect, is all of the hardware working correctly, was it on the HCL? is your virus software up to date?  If you've checked all of that have you done any DBCC checks or maintenance on the database lately, if not then it's probably time to do some.

    To try and reset the status you can use the sp_resetstatus stored procedure (usage details in books online), it may work or then again it may not, but at least you'll know one way or another

    Feel free to ask if you need any additional help.

  • March 15, 2005 at 12:28 pm

    #547146

    Thanks for anserwing my question

    I've tried the sp_resetstatus procedure but I get an error saying I cann't Alter my database because it is "Suspect" when I do the Alter database procedure. I'm following the steps outlined in help, sp_cofigure and sp_resetstatus both appereard to work fine.

    Thanks

  • March 16, 2005 at 3:56 am

    #547222

    Hi,

    is there enough free disk space?

    What kind of file system do you use?

    How many data files for your database?

    karl

    Best regards
    karl

  • March 16, 2005 at 3:59 am

    #547223

    your database can go in suspect mode if

    1. there is power outage

    2. the drive on which your log file or data file is placed, it is full

    3. some missing files if you have recently attached or detatched your database.

    and there can be many other reasons for that. and there is no problem with the liits of sql database. we are handling 1000 Gig of Db at this moment without any problems.

    Thanks

  • March 16, 2005 at 5:20 am

    #547229

    What does the SQL error log say about the suspect database, and why it went suspect?

    We've had a spate of such errors here which have been caused by a Log scan failing. In these cases I detached the database, deleted the log file and attached as a single file. A new log file is created and works fine.

  • March 16, 2005 at 11:57 am

    #547337

    Check if you have enough space for tempdb as well as: There is a database option called  "torn page detection" that can be turned on and off at the database level. Each database much be set separately. Keep in mind that this option does not prevent torn pages, it only tells you if you have one. Once it discovers one, your database is marked as corrupt, and you have little choice but to restore your database with your latest backup.

  • March 16, 2005 at 12:59 pm

    #547356

    Torn page detection is on, I'll turn it off and see how things go...

    When you mention the tempdb do you mean the file \\Program Files\Microsoft SQL Server\ServerName\Data\tempdb.mdf file?

    Thanks....

  • March 17, 2005 at 2:14 am

    #547475

    Hi Meredith, I'm fairly certain that's what he means, basically make sure that there's plenty of free space on the drive where the database's are living.

     

  • March 17, 2005 at 12:02 pm

    #547638

    it is correct for the tempdb. Besides, if you have a lot of tempdb t/a's you might want to consider adding an extra .mdf ( or.ndf) file to it.

  • March 21, 2005 at 2:21 am

    #547958

    At a size of 15.5 GB you're nowhere near any limit provided by SQL Server. See "Maximum capacity specifications" in BOL for such informations. And, just for fun, consider this: http://www.microsoft.com/sql/techinfo/administration/2000/rosetta.asp

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 21, 2005 at 11:21 am

    #548052

    What's a "t/a" ?

    Would having a second .mbf make the database run more effeciently. Also when I looked at the Server error log I got a bunch of log errors, do you think having a second .ldf would help.

    BTW - Turning of the "torn page detection" worked at least I don't have  to do a restore which is a time saver.

  • March 22, 2005 at 12:42 am

    #548128

    "t/a"

    Do you mean N/A = not available?

    do you think having a second .ldf would help

    No, SQL Server treats log files (no matter how much you have) as one single virtual log file. You have no influence what is written to which file. And since writing is done sequentially, there is not much use for a second file. Placing the log on a different pairs of drives (ideally RAID 1) would help however.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 22, 2005 at 8:51 am

    #548215

    t/a stands for transaction(s).

    To reduce the allocation resource contention for tempdb that is experiencing heavy usage you might want to increase the number of tempdb data files with equal sizing.

  • March 22, 2005 at 8:58 am

    #548219

    I tend to create one MDF file for each processor, and just the one LDF because it's written sequentially

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

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