Database marked as suspect please HELP!

Hi,

I restored my Windows XP system and discovered that I cannot access a
MSDE DB that was saved in D: drive (I restored my primary partition in
C). How can I restore it?
Thanks a lot.did you try reattaching it ?|||On 6 Jan 2005 10:25:09 -0800, "getinked" <glenn@.imsocial.com> wrote:

>did you try reattaching it ?

How can I do that? I have the MDF and LDF files but I've never done a
backup. I'm using the trial version of Teratrax Database Manager and
DbaMGR2K.
Thanks a lot for your help.|||On 6 Jan 2005 10:25:09 -0800, "getinked" <glenn@.imsocial.com> wrote:

>did you try reattaching it ?

I noticed the attach command in Teratrax but don't know how to use it.
It tells me

sp_attach_db 'database_name', 'file_name_and_path_relative_to_server'

Is the database_name the filename? What's the relative path? I've my
DB saved in D:\database while the server is in C:\InetPub I think.
Thanks.|||yea the relative path would be where you are storing the data...thus
being on your D drive.|||On 6 Jan 2005 13:23:38 -0800, "getinked" <glenn@.imsocial.com> wrote:

>yea the relative path would be where you are storing the data...thus
>being on your D drive.

Could you be more specific, what string should I enter? Thanks.|||kurotsuke (kurotsuke@.yahoo.it) writes:
> On 6 Jan 2005 13:23:38 -0800, "getinked" <glenn@.imsocial.com> wrote:
>>yea the relative path would be where you are storing the data...thus
>>being on your D drive.
> Could you be more specific, what string should I enter? Thanks.

The exact path to where the database file is located. (The Send To Cliboard
as Name from the Win95 Powertoys comes in extremely handy here.) For
instance D:\Program Files\Microsoft SQL Server\MSSQL\Data\yourdatabase.mdf.
Note that you have two files to enter: the .MDF and the .LDF.

However, since your database is marked as suspect, it still attached
to SQL Server in some sense. You may have to drop it first. Then again,
you could attach the database under a new name.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Database log file

hi all

my database log file has been full and the H.D partition not have any space

what's the procedures to remove or empty the log file

thanks & regards

Are you running SQL Server 2000 or SQL Server 2005? What recovery model is your database using (Full or Simple)?

If you are using the Full Recovery model, you have to periodically backup and truncate the log. Otherwise, it will fill up. Most likely, you have a long-running transaction that is causing this.

If you are running SQL 2005, you can run this query to find out why the transaction log cannot be backed up and truncated.

-- Find the problem with the transaction log

SELECT name, database_id, log_reuse_wait_desc

FROM sys.databases

|||

I guess the question to be asked is, do you need to recover your database to a point in time or to the last full backup.

1. Point in time

a. Your recovery model needs to be set to 'Full' - which it is already because it's filling up.

b. You need to backup you log file routinely, the frequency is determined by how much data you are prepared to lose in the event of a failure i.e. if you only want a maximum 15 minutes of data lost then you need to backup your log file every 15 minutes. By backing up your log file the inactive portion of the log file will be truncated hence it will free up space within the log file for new transactions (note however this will not decrease the log file size on the disk) and reduce growth in the log file.

2. Recover to the last full database backup

a. Set your recovery model to 'simple', your log file will not fill up anymore and sql won't fall over because of disk space issues relating to the log file..

b. Backup you database each day (or as per your backup procedures/policies)

The first step is to reduce the size of your log file to make it more manageable:

1. Backup your log file to disk (or just truncate it if you're not interested in retaining the back file)

To Disk:

BACKUP LOG { database_name } TO DISK='your backup location'

Truncate:

BACKUP LOG { database_name } WITH TRUNCATE_ONLY

Both methods will remove the inactive portion of the log file. Once this has completed you need to reduce the size of the file on the disk, to do this have a look at the DBCC SHRINKFILE command in BOL, there are a few options that may be of interest.