SQL Limits and Suspect databases

Question

SQL Limits and Suspect databases

Meredith Mayes

SSC Enthusiast

Points: 174
More actions
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

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

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

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 1

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.

Meredith Mayes SSC Enthusiast Points: 174 More actions · Answer 2

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

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 3

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

Daljit-208782 SSChasing Mays Points: 623 More actions · Answer 4

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

SteveHall SSChasing Mays Points: 633 More actions · Answer 5

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.

barsuk SSChampion Points: 12280 More actions · Answer 6

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.

Meredith Mayes SSC Enthusiast Points: 174 More actions · Answer 7

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....

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 8

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.

barsuk SSChampion Points: 12280 More actions · Answer 9

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.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

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]

Meredith Mayes SSC Enthusiast Points: 174 More actions · Answer 11

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.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 12

"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]

barsuk SSChampion Points: 12280 More actions · Answer 13

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.

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 14

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