DATABSE IS SUSPECT BECAUSE OF MISSING FILES

My SQL 7 database is missing it's LDF file and is now
tagged as suspect. I have tried many things to solve this
problem but I always get error msg 945, level 16.
I am trying to restore the database but this takes a long
time. By the way the reason that I deleted the LFD file
because it had grown beyond the capacity of the harddrive.
Is there anything else I can do that does not include
major surgery? Any help that you can give me is
appreciated, thanks.
James Colbert
See if this helps:
http://www.sqlservercentral.com/scri...p?scriptid=599
Deleting a log file should never be an option.
Andrew J. Kelly SQL MVP
"James Colbert" <jcolbert30@.yahoo.com> wrote in message
news:248b801c45f83$a205f370$a501280a@.phx.gbl...
> My SQL 7 database is missing it's LDF file and is now
> tagged as suspect. I have tried many things to solve this
> problem but I always get error msg 945, level 16.
> I am trying to restore the database but this takes a long
> time. By the way the reason that I deleted the LFD file
> because it had grown beyond the capacity of the harddrive.
> Is there anything else I can do that does not include
> major surgery? Any help that you can give me is
> appreciated, thanks.
> James Colbert
>
|||Sorry but no...
The exact problem that I am having is that files are
missing and your reply does not address how to recover
from this problem. In other words how do I replace the
missing files that SQL needs in order to remove the DB
from the suspect mode?
Any further suggestions would be appreciated, thanks.
James
|||Hi,
Instead of deletion it is always recommended to shrink the files using DBCC
SHRINKFILE.
When you lost the LDF and you need to recover the database
if you have the FULL database backup and Transaction log backups it is
recommeded to apply the backups in sequence to recover the database.
This provide the data integrity.
Incase if you do not have the backups you can do below:-
1. Set the database to emergency mode
2. Create a new database and USE DTS to transfer data and objects.
-- Setting emergency mode
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Since the transaction log file was not in the startup the data
integrity/consistency may not be assured.
Thanks
Hari
MCDBA
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James
|||Uhhh, but did you actually read it? It details exactly what to do in this
situation including resetting the suspect status. There are no supported
methods that will work 100% of the time when you delete the log. Your best
bet is to restore from know good backups. If that's not an option you can
try sp_attach_single_file_db and this method. You can also call MS PSS and
let them walk you trough it.
http://support.microsoft.com/default...d=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James

DATABSE IS SUSPECT BECAUSE OF MISSING FILES

My SQL 7 database is missing it's LDF file and is now
tagged as suspect. I have tried many things to solve this
problem but I always get error msg 945, level 16.
I am trying to restore the database but this takes a long
time. By the way the reason that I deleted the LFD file
because it had grown beyond the capacity of the harddrive.
Is there anything else I can do that does not include
major surgery? Any help that you can give me is
appreciated, thanks.
James ColbertSee if this helps:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Deleting a log file should never be an option.
Andrew J. Kelly SQL MVP
"James Colbert" <jcolbert30@.yahoo.com> wrote in message
news:248b801c45f83$a205f370$a501280a@.phx
.gbl...
> My SQL 7 database is missing it's LDF file and is now
> tagged as suspect. I have tried many things to solve this
> problem but I always get error msg 945, level 16.
> I am trying to restore the database but this takes a long
> time. By the way the reason that I deleted the LFD file
> because it had grown beyond the capacity of the harddrive.
> Is there anything else I can do that does not include
> major surgery? Any help that you can give me is
> appreciated, thanks.
> James Colbert
>|||Sorry but no...
The exact problem that I am having is that files are
missing and your reply does not address how to recover
from this problem. In other words how do I replace the
missing files that SQL needs in order to remove the DB
from the suspect mode?
Any further suggestions would be appreciated, thanks.
James|||Hi,
Instead of deletion it is always recommended to shrink the files using DBCC
SHRINKFILE.
When you lost the LDF and you need to recover the database
---
if you have the FULL database backup and Transaction log backups it is
recommeded to apply the backups in sequence to recover the database.
This provide the data integrity.
Incase if you do not have the backups you can do below:-
1. Set the database to emergency mode
2. Create a new database and USE DTS to transfer data and objects.
-- Setting emergency mode
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Since the transaction log file was not in the startup the data
integrity/consistency may not be assured.
Thanks
Hari
MCDBA
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx
.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James|||Uhhh, but did you actually read it? It details exactly what to do in this
situation including resetting the suspect status. There are no supported
methods that will work 100% of the time when you delete the log. Your best
bet is to restore from know good backups. If that's not an option you can
try sp_attach_single_file_db and this method. You can also call MS PSS and
let them walk you trough it.
http://support.microsoft.com/defaul...id=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx
.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James

Databse is lost

Hi,
I am using windows server STD 2003, with SP1, with SQL server 2000 with SP1.
The database stops working almost every 24 hours. I have to stop/kill
services and then start again to get it back up.
Kindly advise.
ThanksYou would want to start by checking the SQL Server error
logs and the Windows event logs. There should be something
logged to give you some clues as to what is going on with
your SQL Server.
-Sue
On Mon, 15 May 2006 22:35:01 -0700, Al Gates
<AlGates@.discussions.microsoft.com> wrote:

>Hi,
>I am using windows server STD 2003, with SP1, with SQL server 2000 with SP1
.
>The database stops working almost every 24 hours. I have to stop/kill
>services and then start again to get it back up.
>Kindly advise.
>Thanks

Databse in Use

I am attempting to schedule a Transaction log update to our reports server.
the transaction log completes fine as long as no one is in the DB. How can
I restart SQL or drop the users before I run the log restore so I do not get
the Database in use error?How about executing ALTER DATABASE and set it in single user with the
ROLLBACK IMMEDIATE option?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Will Westfall" <willwjr@.nospamyahoo.com> wrote in message
news:%23o6jcT8pDHA.1948@.TK2MSFTNGP12.phx.gbl...
> I am attempting to schedule a Transaction log update to our reports
server.
> the transaction log completes fine as long as no one is in the DB. How
can
> I restart SQL or drop the users before I run the log restore so I do not
get
> the Database in use error?
>|||Thanks for the quick response. I will give that a shot.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:exy65V8pDHA.372@.TK2MSFTNGP11.phx.gbl...
> How about executing ALTER DATABASE and set it in single user with the
> ROLLBACK IMMEDIATE option?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Will Westfall" <willwjr@.nospamyahoo.com> wrote in message
> news:%23o6jcT8pDHA.1948@.TK2MSFTNGP12.phx.gbl...
> > I am attempting to schedule a Transaction log update to our reports
> server.
> > the transaction log completes fine as long as no one is in the DB. How
> can
> > I restart SQL or drop the users before I run the log restore so I do not
> get
> > the Database in use error?
> >
> >
>

databse in DMZ

We provide datawarehousing reports.
Our datbases are currenlty in the DMZ.
Why? Wouldn't just the webserver be in the DMZ and the db's reside on the private network?RE:
Q1 Our datbases are currenlty in the DMZ. Why?
Q2 Wouldn't just the webserver be in the DMZ and the db's reside on the private network?

A1 That is a good question for your DB developers, IT / business management, etc.,.

A2 Configured well, that may be a somewhat more secure arrangement than having all (?) corporate DBs in a DMZ :( . Some corporations may have reasons to isolate (low risk DBs) in a DMZ. For example, if 'main' internal DBs are completly isolated from any intenet connectivity for security or policy reasons, there may be periodic small loads to the DMZ DBs of a working dataset, (while the core of the historical data is physically secured from any potential internet exposure). Or it may have just been for developer convenience.|||What is very curious is that datawarehouse data is usually highly confidential.

Normally, database servers are restricted from being accessed directly from the Internet due to security/confidential/hacking issues. Also, make sure that the ports that are open are ones absolutely necessary. The database server should only be accessible from the application/web server.

If the database server has to be available in the DMZ then replicate/copy the information from the master database, which is on the intranet behind firewall 2, to the DMZ database - this way if any damage does happen to the DMZ database server your master is still protected.|||When you say "master" db you mean the systme db, right? How do I use the master db to recover from a user db failure. I remember that when I was studying for my exams but I have never had to use that.

-K|||No - The master is the sql server instance running on the intranet (not the dmz).

Databse Design problem

Hello all,
I have a database design problem
I have a hierarchy that includes 5 levels and each level have a table
EX :
TABLE_L1
L1_ID INT AUTO
L1_CODE nvarchar(50)
L1_NAME nvarchar(255)
TABLE_L2
L2_ID INT AUTO
L1_ID INT
L2_CODE nvarchar(50)
L2_NAME nvarchar(255)
etc
The primary key is an id auto. (can be replaced by a GUID if it is
necessary)
The problem :
I have a user table and must affect rights on some members than can be a
different level of the hierarchy.
For example :
User 1 can access to the member A of level one and all the level A
children's but he can also access to member B4 of level 2
I try to implement integrity so when a member is deleted all rights are
deleted too.
My first design is to have one security definition table per level but i
think i am not the first person to have to give rights on different levels
of a hierarchy and they're must be a "best practice" to design it!
anoyone knows an "ideal" solution?
Thanks
cymryrPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
table .. <<
Then this is not a hierarchy. It should be in one table. You should
not use any kind of auto numbering in a relational database and a GUID
is the worst choice.
How much research did you do when you decided to use NVARCHAR(50) and
NVARCHAR(255). Thise "magic numbers" are a sign of no design work at
all.
children's but he can also access to member B4 of level 2 <<
I have implemented a security scheme with a nested sets model in which
privileges were inherited down the tree from a superior to a
subordinate.
You seem to have no rule for determining privileges, so you will have
to list all combinations.
I have an entire book on TREES & HIERARCHIES IN SQL which might help.|||> How much research did you do when you decided to use NVARCHAR(50) and
> NVARCHAR(255). Thise "magic numbers" are a sign of no design work at
> all.
This is not "Magic numbers" there are the result of an extraction. i am not
responsable of the other database and my design is subbordinate by the other
apps

> children's but he can also access to member B4 of level 2 <<
> I have implemented a security scheme with a nested sets model in which
> privileges were inherited down the tree from a superior to a
> subordinate.
> You seem to have no rule for determining privileges, so you will have
> to list all combinations.
> I have an entire book on TREES & HIERARCHIES IN SQL which might help.
Wich book?I am very interested|||>> Which book? I am very interested <<
TREES & HIERARCHIES IN SQL (Morgan-Kaufmann, 2004)
--CELKO--

Data-Bound Images

I can't seem to figure out how to add data-bound images to a report when the
database only holds the url for the images, not the binary images
themselves. Do images really need to be stored in the database in order to
be data-bound? If so, I wonder if it is possible to create a sp that can
dynamically pull the image from the image-path. Any suggestions would be
appreciated. Thanks.Elmer:
I am trying to work out the exact same thing. I've posted my question here
twice and not gotten a reply.
If you happen to figure it out, I'd greatly appreciate a reply here.
"Elmer Miller" <elmermiller@.empireco.com> wrote in message
news:ObrMUxhbGHA.3352@.TK2MSFTNGP03.phx.gbl...
>I can't seem to figure out how to add data-bound images to a report when
>the database only holds the url for the images, not the binary images
>themselves. Do images really need to be stored in the database in order to
>be data-bound? If so, I wonder if it is possible to create a sp that can
>dynamically pull the image from the image-path. Any suggestions would be
>appreciated. Thanks.
>|||I ended up using SSIS to batch the images into a table. I thought about
binding the report to an SSIS package, but this solution may take me longer
to implement and may not perform as well.
"Elliot Rodriguez" <elliotrodriguezatgeemaildotcom> wrote in message
news:ukRDa9hbGHA.4672@.TK2MSFTNGP04.phx.gbl...
> Elmer:
> I am trying to work out the exact same thing. I've posted my question here
> twice and not gotten a reply.
> If you happen to figure it out, I'd greatly appreciate a reply here.
> "Elmer Miller" <elmermiller@.empireco.com> wrote in message
> news:ObrMUxhbGHA.3352@.TK2MSFTNGP03.phx.gbl...
>>I can't seem to figure out how to add data-bound images to a report when
>>the database only holds the url for the images, not the binary images
>>themselves. Do images really need to be stored in the database in order to
>>be data-bound? If so, I wonder if it is possible to create a sp that can
>>dynamically pull the image from the image-path. Any suggestions would be
>>appreciated. Thanks.
>