Need help ASAP 3414 error SQL 2005 Developer Edition

Hello ,

This morning I was running a report in my ssrs and I had a power failure. My Machine rebooted and I can not connect to my database below is the error log can some one tell me what I need to do to recover I really do not want to have to reinstall everything if possible.

2007-06-15 07:59:49.88 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2007-06-15 07:59:49.88 Server (c) 2005 Microsoft Corporation.
2007-06-15 07:59:49.88 Server All rights reserved.
2007-06-15 07:59:49.88 Server Server process ID is 1600.
2007-06-15 07:59:49.88 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2007-06-15 07:59:49.90 Server This instance of SQL Server last reported using a process ID of 880 at 6/14/2007 5:36:25 PM (local) 6/14/2007 9:36:25 PM (UTC). This is an informational message only; no user action is required.
2007-06-15 07:59:49.90 Server Registry startup parameters:
2007-06-15 07:59:49.90 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2007-06-15 07:59:49.90 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2007-06-15 07:59:49.90 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2007-06-15 07:59:50.01 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-06-15 07:59:50.01 Server Detected 1 CPUs. This is an informational message; no user action is required.
2007-06-15 07:59:50.36 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-06-15 07:59:50.46 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-06-15 07:59:51.16 Server The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
2007-06-15 07:59:51.25 Server Database Mirroring Transport is disabled in the endpoint configuration.
2007-06-15 07:59:51.29 spid5s Starting up database 'master'.
2007-06-15 07:59:51.70 spid5s WARNING: did not see LOP_CKPT_END.
2007-06-15 07:59:51.70 spid5s Error: 3414, Severity: 21, State: 2.
2007-06-15 07:59:51.70 spid5s An error occurred during recovery, preventing the database 'master' (database ID 1) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
2007-06-15 07:59:51.70 spid5s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

The error message pretty much tells you what to do. It tellsyou to look at the SQL Server books online, it gives you an error code to look up.

A quick search of Google gives a link to Microsoft's support page, which should lead you in the right direction:

http://support.microsoft.com/kb/306366

sql

Need Help 1505 Error

Hello All
I have a SQL 6.5 Server running on Service Pack 5a with a hotfix.
select @.@.version returns:
Microsoft SQL Server 6.50 - 6.50.451 (Intel X86)
Jul 1 1999 01:11:25
Copyright (c) 1988-1997 Microsoft Corporation

I am getting an error something like this:
Error 1505 Create unique index aborted on duplicate key. Primary key is '847474 '

When this is happening is after copying some data and during the creation of an index. I have 2 tables one called ITEM_INVENTORY the other ITEM_INVENTORY_WORK. The only difference between them is that the WORK table has an identity column.

I use a stored procedure to perform the following steps:
1. Truncate the table ITEM_INVENTORY
2. Drop all the indexes on ITEM_INVENTORY
3. Copy the data from the ITEM_INVENTORY_WORK table to the ITEM_INVENTORY table in 10000 row chunks using:
BEGIN TRAN
INSERT INTO ITEM_INVENTORY
SELECT <(COLUMN LIST)>
FROM ITEM_INVENTORY_WORK
COMMIT TRAN
4. Recreate all the indexes.

During step 4 (recreate indexes) the process fails and returns a 1505 error while creating the UNIQUE index: CREATE UNIQUE INDEX ITEM_INVENTORY_idx1 ON dbo.ITEM_INVENTORY(IC_ITEM_NO, CORP_CODE)

Now here is the kicker. There are no duplicates. I've checked and re-cheked and there are no duplicate records. I think this may be a bug, but I don't know. Has anyone encountered this problem and have any ideas on how to solve it?It's not about duplicare records - it's about duplicate IC_ITEM_NO, CORP_CODE.

Run this:

select IC_ITEM_NO, CORP_CODE
from ITEM_INVENTORY
group by IC_ITEM_NO, CORP_CODE
having count(*) > 1

See what It returns. There are no miracles|||Hi I ran the query:
select IC_ITEM_NO, CORP_CODE
from ITEM_INVENTORY
group by IC_ITEM_NO, CORP_CODE
having count(*) > 1

and I get nothing back. Like I said no duplicates.
The other crazy thing is the WORK table is truncated and recreated each day in a similar way but it does not error when recreating the index.

Regards
CaptainEstock|||Try this:
drop table ITEM_INVENTORY
select * into WORK

drop IDENTITY column
recrate indexes

Do not forget to set DB option to allow Bulk copy/select into|||Sorry,

select * into item_inventory
from work|||I can't drop columns in SQL 6.5

I might be able to do a SELECT with the column names...sql

Need hardware suggestions for SQL 2000 server

I am running McAfee ePO and SQL Server 2000 on a dual Xeon 900Mhz box
currently. It is slow. I am going to be ordering 2 new servers, 1 for
ePO and 1 for SQL Server 2000. Anybody have any suggestions for the
SQL Server's hardware specs. Thanks,
Chris"czidak" <czidak75@.gmail.com> wrote in message
news:1115925307.753935.292720@.z14g2000cwz.googlegr oups.com...
> I am running McAfee ePO and SQL Server 2000 on a dual Xeon 900Mhz box
> currently. It is slow. I am going to be ordering 2 new servers, 1 for
> ePO and 1 for SQL Server 2000. Anybody have any suggestions for the
> SQL Server's hardware specs. Thanks,

Not w/o knowing a lot more.

Where's your bottleneck? I/O? CPU? Not enough memory?

Have you checked the code? Using cursors? etc.

> Chris|||Currently whenever I Navigate through ePO to make changes or apply
changes it hangs. Checking Task Manager the following processes are
hogging processor and memory.

mmc.exe (epo app) Processor - 50% Memory -28 MB
sqlservr.exe Processor - 1% - 50% (only high for a second at the end
of a transaction Memory-1.5GB currently (starts off at 60 MB when
server is 1st booted up.)

The server has 2 GB of RAM and the virtual memory settings are set at
2046 - 4096

Thanks,
Chris|||Also, the ePO application does not run any better right after a reboot,
so I don't think the issue is the amount of memory that sqlservr.exe
can use.

Hope this helps,
Chris

Need assistance restoring MS SQL 6.5 databases

We had a server running NT 4 and SQL 6.5, it was running an RMS database. We
attempted to move the databases to SQL 7.0 using the upgrade wizard and then
move them to SQL 2000 on another server. This all worked correctly, however
the client app that accesses the data cannot do so on the SQL 2000 box
(probably not compatible). So now we want to revert back to the old SQL 6.5
box however we can longer get SQL 6.5 to start, it is giving the error "
Cannot start SQL, cannot find the path specified'. Our next move was to build
a NT 4 box, install SQL 6.5 and attempt to restore, we have the .DAT file.
What we need is step by step instructions on how to accomplish this.
Alternatively, our first choice would be to restore the original server
however as stated we cannot get SQL 6.5 to start. Any assistance would be
appreciated
Hi,
With SQL 2000, set the database compatibility mode to SQL 6.5 and try your
application.
Execute the below command from query analyzer
sp_dbcmptlevel 'DBNAME',65
replace the dbname with actual.
If you still have issues then you can Install sql6.5 in Winnt and restore
from backup.
If you do not have backup then With DAT files you have use :-
Use the DISK REINIT and DISK REFIT commands.
Steps:-
1. Restart in Single-user mode
2. DISK REINIT and DISK REFIT against each device
3. Restart in normal mode
See books onlne for command syntax
Thanks
Hari
SQL Server MVP
"webby" <webby@.discussions.microsoft.com> wrote in message
news:6B14351A-B3E5-4130-8CBD-C16A5B80240A@.microsoft.com...
> We had a server running NT 4 and SQL 6.5, it was running an RMS database.
> We
> attempted to move the databases to SQL 7.0 using the upgrade wizard and
> then
> move them to SQL 2000 on another server. This all worked correctly,
> however
> the client app that accesses the data cannot do so on the SQL 2000 box
> (probably not compatible). So now we want to revert back to the old SQL
> 6.5
> box however we can longer get SQL 6.5 to start, it is giving the error "
> Cannot start SQL, cannot find the path specified'. Our next move was to
> build
> a NT 4 box, install SQL 6.5 and attempt to restore, we have the .DAT file.
> What we need is step by step instructions on how to accomplish this.
> Alternatively, our first choice would be to restore the original server
> however as stated we cannot get SQL 6.5 to start. Any assistance would be
> appreciated

Need assistance restoring MS SQL 6.5 databases

We had a server running NT 4 and SQL 6.5, it was running an RMS database. We
attempted to move the databases to SQL 7.0 using the upgrade wizard and then
move them to SQL 2000 on another server. This all worked correctly, however
the client app that accesses the data cannot do so on the SQL 2000 box
(probably not compatible). So now we want to revert back to the old SQL 6.5
box however we can longer get SQL 6.5 to start, it is giving the error "
Cannot start SQL, cannot find the path specified'. Our next move was to build
a NT 4 box, install SQL 6.5 and attempt to restore, we have the .DAT file.
What we need is step by step instructions on how to accomplish this.
Alternatively, our first choice would be to restore the original server
however as stated we cannot get SQL 6.5 to start. Any assistance would be
appreciated :)Hi,
With SQL 2000, set the database compatibility mode to SQL 6.5 and try your
application.
Execute the below command from query analyzer
sp_dbcmptlevel 'DBNAME',65
replace the dbname with actual.
If you still have issues then you can Install sql6.5 in Winnt and restore
from backup.
If you do not have backup then With DAT files you have use :-
Use the DISK REINIT and DISK REFIT commands.
Steps:-
1. Restart in Single-user mode
2. DISK REINIT and DISK REFIT against each device
3. Restart in normal mode
See books onlne for command syntax
Thanks
Hari
SQL Server MVP
"webby" <webby@.discussions.microsoft.com> wrote in message
news:6B14351A-B3E5-4130-8CBD-C16A5B80240A@.microsoft.com...
> We had a server running NT 4 and SQL 6.5, it was running an RMS database.
> We
> attempted to move the databases to SQL 7.0 using the upgrade wizard and
> then
> move them to SQL 2000 on another server. This all worked correctly,
> however
> the client app that accesses the data cannot do so on the SQL 2000 box
> (probably not compatible). So now we want to revert back to the old SQL
> 6.5
> box however we can longer get SQL 6.5 to start, it is giving the error "
> Cannot start SQL, cannot find the path specified'. Our next move was to
> build
> a NT 4 box, install SQL 6.5 and attempt to restore, we have the .DAT file.
> What we need is step by step instructions on how to accomplish this.
> Alternatively, our first choice would be to restore the original server
> however as stated we cannot get SQL 6.5 to start. Any assistance would be
> appreciated :)

Need assistance restoring MS SQL 6.5 databases

We had a server running NT 4 and SQL 6.5, it was running an RMS database. W
e
attempted to move the databases to SQL 7.0 using the upgrade wizard and then
move them to SQL 2000 on another server. This all worked correctly, however
the client app that accesses the data cannot do so on the SQL 2000 box
(probably not compatible). So now we want to revert back to the old SQL 6.5
box however we can longer get SQL 6.5 to start, it is giving the error "
Cannot start SQL, cannot find the path specified'. Our next move was to buil
d
a NT 4 box, install SQL 6.5 and attempt to restore, we have the .DAT file.
What we need is step by step instructions on how to accomplish this.
Alternatively, our first choice would be to restore the original server
however as stated we cannot get SQL 6.5 to start. Any assistance would be
appreciated Hi,
With SQL 2000, set the database compatibility mode to SQL 6.5 and try your
application.
Execute the below command from query analyzer
sp_dbcmptlevel 'DBNAME',65
replace the dbname with actual.
If you still have issues then you can Install sql6.5 in Winnt and restore
from backup.
If you do not have backup then With DAT files you have use :-
Use the DISK REINIT and DISK REFIT commands.
Steps:-
1. Restart in Single-user mode
2. DISK REINIT and DISK REFIT against each device
3. Restart in normal mode
See books onlne for command syntax
Thanks
Hari
SQL Server MVP
"webby" <webby@.discussions.microsoft.com> wrote in message
news:6B14351A-B3E5-4130-8CBD-C16A5B80240A@.microsoft.com...
> We had a server running NT 4 and SQL 6.5, it was running an RMS database.
> We
> attempted to move the databases to SQL 7.0 using the upgrade wizard and
> then
> move them to SQL 2000 on another server. This all worked correctly,
> however
> the client app that accesses the data cannot do so on the SQL 2000 box
> (probably not compatible). So now we want to revert back to the old SQL
> 6.5
> box however we can longer get SQL 6.5 to start, it is giving the error "
> Cannot start SQL, cannot find the path specified'. Our next move was to
> build
> a NT 4 box, install SQL 6.5 and attempt to restore, we have the .DAT file.
> What we need is step by step instructions on how to accomplish this.
> Alternatively, our first choice would be to restore the original server
> however as stated we cannot get SQL 6.5 to start. Any assistance would be
> appreciated

Need advice

Hi,
I have a few offices accross the country running MSDE and my HQ SQL 2000. I
need to port the data nightly to the HQ. Should I consider Replication?
Whatever is done on the MSDE at the offices are not needed after copied to
the HQ. I was thinking of creating an app (running at all offices) to copy
the data nightly to the HQ but I am worried if the connection goes down
during the copy?
Please advice.
Thanks
I would do this with DTS instead of replication since you don't have a
requirement to maintain the data in MSDE after it is transferred and it
doesn't appear you are sending anything from HQ back to any of the offices.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:574D914C-55A2-48B3-B606-55DD5D9E02BC@.microsoft.com...
> Hi,
> I have a few offices accross the country running MSDE and my HQ SQL 2000.
> I
> need to port the data nightly to the HQ. Should I consider Replication?
> Whatever is done on the MSDE at the offices are not needed after copied to
> the HQ. I was thinking of creating an app (running at all offices) to copy
> the data nightly to the HQ but I am worried if the connection goes down
> during the copy?
> Please advice.
> Thanks

Need a way to use Windows NT intgrated security between two servers

Reporting Services is on its own Win2k3 server , running a report that
does a query against our Production database using Windows NT
Integrated Security.
The query never returns any data, yet when I run the query in Query
Analyzer it works fine...I have another report that uses "Credentials
stored securely in the report server" and that Query woks fine in RS
and in Query Analyzer...Has anybody found a way to use Windows NT
intgrated security between two servers accessed by IIS.RS is on one
server and the database is another..I don't have a direct answer for you but I have a suggestion. I think that
using the credentials of the calling user to hit the database is a bad idea.
The reason is that you will lose all the benefits of connection pooling. I
suggest having a readonly account that is used for the credentials of the
report. Then you use the role based security (with integrated security) to
only allow access to the reports the user should have. If you have sensitive
data that the user should only be able to see certain data then use the
global variable user!userid either as a query parameter or as a filter
parameter. The end result will be the same but your performance under load
will be much much better.
Bruce L-C
"Tom" <tom_barnard@.aotx.uscourts.gov> wrote in message
news:1096405401.694519.113440@.k17g2000odb.googlegroups.com...
> Reporting Services is on its own Win2k3 server , running a report that
> does a query against our Production database using Windows NT
> Integrated Security.
> The query never returns any data, yet when I run the query in Query
> Analyzer it works fine...I have another report that uses "Credentials
> stored securely in the report server" and that Query woks fine in RS
> and in Query Analyzer...Has anybody found a way to use Windows NT
> intgrated security between two servers accessed by IIS.RS is on one
> server and the database is another..
>

Need a sysadmin login

We’re running mixed mode authentication on our SQL Servers.To make the server “safer” builtin\admininstrators no longer have sysadmin role on the sql server.If there is only one login with sysadmin role, and we lose track of the password, is there any way to recover it?How could we reset the password or create a new sa account with a new password?This situation has not occurred, but I’m worried about how to recover from it should it occur.This question relates to SQL 2000 and SQL 2005.

David Zokaites,

DBA & Software Engineer

If you deleted the users and have no available sysadmin user you will be locked out of your server for the sysadmin work. Therefore I always suggest to create a user which is not used and only available for these scenarios. Depending on the kind of security level the password can be stored somewhere in a safe or at a secure place.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

Thanks for the information. I thought the scenario might lock me out of sysadmin on the sql server. I will certainly make sure we have a secondary sysadmin account to prevent such a disaster.

David

|||

Jens is right as far as SQL Server 2000 is concerned. For SQL Server 2000, you can get locked out; if that would happen, you can recover other databases than master by reattaching them on a different SQL Server instance.

However, in SQL Server 2005, by restarting the server in single-user mode, a local administrator can get access to SQL Server as a sysadmin, so you cannot get yourself locked out of the SQL Server instance.

Thanks
Laurentiu

Neebie needing help asap restoring sql

I am having problem which is getting worst by the moment. I had a power failure which my battery backup fail while running a large report in my SSRS all of a sudden I started getting reportservertempdb.dbo.persistedstream error. I could not get my reports to run through the iis webservice. I could get them to run from within my reportbuilder. I was told to reload my sql and restore it but I can not get my sql to successfully reinstall I am using the sql2005 dev ed. It either gives me a name instance issue or fails the database, report server and notifaction portion of the reload. I am not sure why it will not reload. any assistance would be great.

You dont have to reinstall SQL Server just for a power failure? Try restarting all the services - SQL server as well as IIS.

NEAR syntax in Full-Text Search

In tuning our search engine which is running against SQL 2005, I'm trying to understand the "near" operator in my CONTAINSTABLE query. I'm doing a query like the following:

select * FROM CONTAINSTABLE(Catalog, *,
'FORMSOF(INFLECTIONAL,"class") OR
FORMSOF(INFLECTIONAL,"calendar") OR
("class" near "calendar")', 1000)

Entries that have class and calendar directly next to eachother are being ranked higher, as would be expected. But entries that have Class <word> calendar, are being ranked the exact same as they would be with just the two Inflectional ORs and no near syntax.

I thought the near syntax was supposed to be useful up to 50 words?

The near syntax is useful up to 50 words means if the two words are more than 50 words apart, the ranking score (of that OR clause) will be 0.

Ranking is rather complicated. In your example, the ranking will be affected by all 3 OR clauses. The ranking also depends on other factors, like document length, average document length of the corpus, frequency of the terms in the document and across corpus. So, word distance in the near clause may or may not be visible in your final ranking score.

NEAR syntax in Full-Text Search

In tuning our search engine which is running against SQL 2005, I'm trying to understand the "near" operator in my CONTAINSTABLE query. I'm doing a query like the following:

select * FROM CONTAINSTABLE(Catalog, *,
'FORMSOF(INFLECTIONAL,"class") OR
FORMSOF(INFLECTIONAL,"calendar") OR
("class" near "calendar")', 1000)

Entries that have class and calendar directly next to eachother are being ranked higher, as would be expected. But entries that have Class <word> calendar, are being ranked the exact same as they would be with just the two Inflectional ORs and no near syntax.

I thought the near syntax was supposed to be useful up to 50 words?

The near syntax is useful up to 50 words means if the two words are more than 50 words apart, the ranking score (of that OR clause) will be 0.

Ranking is rather complicated. In your example, the ranking will be affected by all 3 OR clauses. The ranking also depends on other factors, like document length, average document length of the corpus, frequency of the terms in the document and across corpus. So, word distance in the near clause may or may not be visible in your final ranking score.