Orphaned .mdf File

Nov 3, 2004

I have a .mdf file that I would like to restore. However the log file has been destroyed. Is there any way to restore just an .mdf file?

View 1 Replies


ADVERTISEMENT

SQL 2012 :: Restoring Orphaned MDF File

Sep 23, 2014

Been asked to restore an orphaned MDF file leftover after a botched uninstall - no .bak file. Tried to reattach, but got an error, I don't think it had been detached. My initial answer was, "No, very likely can't be done".

Am I right? Or is there a way of attaching it that doesn't require it to have been detached?

View 4 Replies View Related

Orphaned Connections

Jan 15, 2002

On SQL 6.5 we have had a couple of instances recently where an orphan connection cannot be killed and the only way to get rid of it is to stop and start the service. Does anyone know of any other way to get rid of that orphan connection?

View 1 Replies View Related

Orphaned Indexes?

Jan 14, 2000

As I do analysis on a database I am finding multiple clustered indexes defined on a single table.
I know this is not possible since SQL Server only allows one clustered index per table.
With more research I found these suspected clustered indexes have a corresponding row in
sysindexes but without a correlating id match in either sysobjects or syscolumns.
Is this orphaning of indexes common and what is a recommended solution.
Thanks,
Randy Garland

View 2 Replies View Related

Fix Orphaned Users

Feb 4, 2002

/*************************************************************************************
This procedure should be created in the Master database. This procedure takes no
parameters. It will remap orphaned users in the current database to EXISTING logins
of the same name. This is usefull in the case a new database is created by restoring
a backup to a new database, or by attaching the datafiles to a new server.
*************************************************************************************/


IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_fixusers
IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_fixusers >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_fixusers >>>'
END

GO

CREATE PROCEDURE dbo.sp_fixusers

AS

BEGIN

DECLARE @username varchar(25)

DECLARE fixusers CURSOR
FOR

SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name

OPEN fixusers

FETCH NEXT FROM fixusers
INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END


CLOSE fixusers
DEALLOCATE fixusers
END
go
IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_fixusers >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_fixusers >>>'
go

View 20 Replies View Related

Orphaned SQL Server Connections

Jan 8, 2008

 I am not sure if this is actually a sql connection I didn't dispose of.The database the app use is franchise_search, login testHere  are the results of sp_who before I start the web server and run the page1    0    background                        sa                                                                                                                                        0        NULL    RESOURCE MONITOR    02    0    background                        sa                                                                                                                                        0        NULL    LAZY WRITER         03    0    suspended                         sa                                                                                                                                        0        NULL    LOG WRITER          04    0    background                        sa                                                                                                                                        0        NULL    LOCK MONITOR        05    0    background                        sa                                                                                                                                        0        master    SIGNAL HANDLER      06    0    sleeping                          sa                                                                                                                                        0        master    TASK MANAGER        07    0    background                        sa                                                                                                                                        0        master    TRACE QUEUE TASK    08    0    sleeping                          sa                                                                                                                                        0        NULL    UNKNOWN TOKEN       09    0    background                        sa                                                                                                                                        0        master    BRKR TASK           010    0    background                        sa                                                                                                                                        0        master    TASK MANAGER        011    0    suspended                         sa                                                                                                                                        0        master    CHECKPOINT          012    0    background                        sa                                                                                                                                        0        master    BRKR EVENT HNDLR    013    0    background                        sa                                                                                                                                        0        master    BRKR TASK           014    0    sleeping                          sa                                                                                                                                        0        master    TASK MANAGER        015    0    sleeping                          sa                                                                                                                                        0        master    TASK MANAGER        016    0    sleeping                          sa                                                                                                                                        0        master    TASK MANAGER        017    0    sleeping                          sa                                                                                                                                        0        master    TASK MANAGER        018    0    sleeping                          sa                                                                                                                                        0        master    TASK MANAGER        020    0    sleeping                          sa                                                                                                                                        0        master    TASK MANAGER        022    0    sleeping                          sa                                                                                                                                        0        master    TASK MANAGER        024    0    sleeping                          sa                                                                                                                                        0        master    TASK MANAGER        051    0    sleeping                          NT AUTHORITYSYSTEM    HPDEV                                                                                                                               0        msdb    AWAITING COMMAND    052    0    sleeping                          sa    HPDEV                                                                                                                               0        ReportServer    AWAITING COMMAND    053    0    sleeping                          sa    HPDEV                                                                                                                               0        master    AWAITING COMMAND    054    0    sleeping                          sa    HPDEV                                                                                                                               0        ReportServer    AWAITING COMMAND    055    0    runnable                          sa    HPDEV                                                                                                                               0        master    SELECT              0 then I run the page and all I do is                       using (m_sqlConn = new SqlConnection(m_strSQLConnect))                    {                        m_sqlConn.Open();                        //Log_History();                        //FillCompany_DropDown();                        //m_sqlConn.Open();                        //BuildCategoies();                    }I open the connection never use it and return the using should close the connection.But after I close the page sp_who add the line56    0    sleeping                          test    HPDEV                                                                                                                               0        Franchise_Search    AWAITING COMMAND    0Is this connection pooling or am I missing something?No other app or anything can use the test connection  I just made it and changed the password. Jon  

View 1 Replies View Related

Finding Orphaned Records

Mar 10, 2004

I am trying to find records that are orphaned in a data base after thier parent record (in another table) has been deleted.

Something happened to the constraints and I had no idea until now this had happened.

I have the faint rustle of a query in query sort of thing but my knowledge needs expansion.

PS: sorry for the cross post - just noticed that I was in the wrong forum before.

Thanks,
Matt

View 11 Replies View Related

Orphaned Scheduled Jobs

Mar 17, 2004

Had to rename a SQL 2000 box and now the scheduled maint jobs that were created under the previous name of the box cannot be deleted via EM. Can anyone offer help?

View 1 Replies View Related

Orphaned Transactions Problems

Mar 10, 2008

so i'm having a bit of a bad day today

i have an app that starts a transaction (some deletes, etc...)
in the middle of this transaction the connection drops (say we pull out our network cable) but this will leave the transaction still running and it will be orphaned.
i know i can kill them in sql server, but what i'm looking for this to somehow not happen at all.
I tried SET XACT_ABORT ON but no luck.

Is what i need even possible?


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

View 20 Replies View Related

Mirroring And Orphaned Users

May 29, 2006

Hi there!

There is still a problem with mirrored (mapped) SQL Users. If you mirror a database where an application connects with an sql user, the mapping (login / user) will be lost on the mirror server. After a failover occurs, it it not possible to log onto the new principal database because the database use will be an orphaned user and has to be remapped to the login (using sp_change_users_login 'update_one', 'user', 'user').

Is there any chance to do it in a system trigger? What is the firing event after the failover occurs? I've tried something like following, but it doesn't fire.

alter TRIGGER map_orphaned_users

ON ALL SERVER

FOR ALTER_DATABASE

AS

execute sp_change_users_login 'update_one', 'easyris_41', 'easyris_41';

Someone an idea how to automate these usermapping after failover on the (new) principal server?

View 10 Replies View Related

SQL 2000 Orphaned Connections

May 24, 2007

We have 4 sql servers which service 4 load balanced web servers (with sticky sessions) and we recently brought one of the four SQL servers online.



In the last week the new SQL Server 2000 Standard (SP4) has been exhibing orphaned connections and attempts to use invalid connections out of the connection pool on the app servers. This manifests itself in General network Errors and Timeouts (when the sql is not really doing much other than simple table requests).



Does anyone have any experience in what might be causing this on a new server. I've asked our networking people to verify that the network routing and firewall are set the same as the other three servers. The application is the same acrossed all of the app servers so I've ruled out any issues with the application not closing connections.

View 1 Replies View Related

Orphaned SQL Logins On The Mirror

Nov 20, 2006

Hi there,

I€™ve recently set-up database mirroring between two servers in the same domain: DMZSQL01 and DMZSQL02 with a witness of DMZSQL03.
The mirroring as all gone according to plan.

Set up all the users/databases on the Mirror (DMZSQL02) and then do a back-up/restore to sync the databases and then enable the mirroring, this has all gone fine and we have lots of synchronised/mirrored databases now. However, if I do a failover the logins on the mirror are not valid, they are in SQL Server and also the database but they don€™t work. I€™ve read through other posts and found links to this SQL Server article:
http://support.microsoft.com/kb/918992/

This has had no effect though. Do I need to remove everything off my mirror and start again setting up the users first and then doing a backup/restore or is there some other way. Both servers are reporting the SID€™s are the same for the logins which do not work. However the principle_ID is different, I'm quite sure if this is a problem or not??

Can anyone point me in the right direction of what to do next? Or have any ideas.

Thanks
Ed

View 7 Replies View Related

Orphaned/Hung User Process

Jul 21, 2000

Hi,

Looking through the archives, I didn't see any articles that specifically addressed the problem, so here it is:

SQL 7.0, NT4SP6, 2G ram, 4x

I've got a user process as follows:
Status: ROLLBACK
Command: SELECT
Application: Enterprise Manager
Wait Type: EXCHANGE
Login time: 06/25/00 4:07:05AM
Last batch: 06/25/00 4:07:20 AM
The last TSQL command batch is a correlated subquery with grouping...

Apparently it hung and the user quit ungracefully.

No other processes are blocking it, but periodically it blocks other processes, including some index maintenance I need to perform.

I have tried to kill it with Enterprise Manager and with the KILL [id] command. Neither have worked.

Mgmt is reluctant to bounce the database, as am I.

Any ideas?

Thanks in advance!
--Nassif

View 2 Replies View Related

Orphaned User Accounts When Restoring

Dec 29, 1999

I am copying a database to an alternate server by restoring a full backup onto the new server. However, whether I create the logins prior to the restoration or not the user accounts in the database no longer map to logins in the master.

Unfortunately it is not simply a case of dropping the acounts as they own objects in the database.

My best solution to date has been to use the sp_addalias to alias logins of the same name to the original user. However this is a far from ideal situation as one cannot readily tell that the logins are aliased and there must be an additional overhead in doing this.

View 3 Replies View Related

Replication - Orphaned Distribution Agent

Dec 9, 2002

I seem to have an orphaned Distribution Agent. There is no associated Publication and the agent is sending errors. The errors would be legitimate if only I had an associated publication. I also do not have an associated Snapshot Agent.

How can I get rid of this bogus agent? I already deleted the associated job and rebooted SQL and SQLAgent but it still persists.

Any help would be appreciated.

Colleen

View 1 Replies View Related

SQL 2012 :: Cannot Drop Orphaned Account

Dec 30, 2014

All is happened when a server crashed some weeks ago and it was removed from my network. After that, under my SQL Server 2012 I get an orphaned account which cannot be removed. This account is a computer account related to an old SCOM installation.

If I try to execute the command DROP USER [NETWORKSERVERNAME$] I get the following error message:

The database principal has granted or denied permissions to objects in the database and cannot be dropped. Msg 15284, Level 16, State 1, Line 1

The database principal has granted or denied permissions to objects in the database and cannot be dropped.But if I run the following command to know all permission granted to the account, I get an empty result:

select * from sys.database_permissions where grantee_principal_id = user_id ('NetworkSERVERNAME$');

Furthermore, following the instructions provided by the official KB for troubleshooting orphaned users, I get another error [URL].

sp_change_users_login 'update_one', 'NetworkSERVERNAME$', 'NetworkSERVERNAME$'
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114
Terminating this procedure. The User name 'NetworkSERVERNAME$' is absent or invalid.

The only thing I can retrieve is 15 permissions that this account granted to another account in the past:

select *
from sys.database_permissions
where grantor_principal_id = user_id ('NetworkSERVERNAME$'
--
class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc
17 SERVICE 65538 0 5 19 SN SEND G GRANT
And more 14 rows…

resolve my issue and safe delete the account SERVERNAME$? I need this because I have to reinstall SCOM with the same computer name on another server, but installation fails due to this behavior.

View 8 Replies View Related

Deleting Orphaned Data - Maintenance

Dec 10, 2007

Hi,

I really don't know how frowned upon my approach is here, but it was the only way I have been able I've been able to do it.

On my application, when users delete their account, it sometimes brings the db server to a COMPLETE crawl. The reason is some users who delete have many years of related data, and when the data deletes with them, its very slow.

To avoid this I've taken off many contstraints, and I do have some sprocs that deleted orphaned data at night.

thoughts on this approach ?

View 5 Replies View Related

XML Services And SQL Server Orphaned Processes

May 1, 2007

Hi

Wanting to know how do you detect and kill orphaned spid in SQL Server left behind by an XML service which was not completed normally. The client requests an XML service which open a connection, and due to network problem the client did not manage to call the another service which suppose to end the connection. Thus leaving behind in SQL Server orphaned spid which continue to hold locks. This created problem when the next user wants to access the resource.

The simplest way is to get all spid which are holding locks for more than a certan period and kill them. Is there a better way to actually detect orphaned spid?



Help much appreciated.



regards

benc

View 3 Replies View Related

Orphaned Users Afret Restor - SQL 2005

Aug 19, 2007

Hi,

I've restored my database from backup on another SQL 2005 server and can't login, could you tell me what is the best way to repair this on SQL 2005, please

--
Regards,
anxcomp

View 2 Replies View Related

Large Numbers Of Orphaned/expired Requests

Mar 30, 2007

We are experiencing a situation where the SRS (2000 SP2) report server will no longer render reports. In the log file, there are many instances of

w3wp!runningjobs!434!3/23/2007-10:12:57:: i INFO: Adding: 8 running jobs to the databasew3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired request

What could be causing this? The reports are making queries through an OLE DB provider. There are no scheduled jobs, and the load doesn't seem that heavy.

View 1 Replies View Related

Orphaned Publication Information Preventing Drop Of Database

Jul 7, 1999

I am attempting to drop a database (sales), however I receive this message: Error 3274 is "Cannot drop the database 'sales' because it is published for replication." Yet, I no longer have any publications in this database. It seems that there is some orphaned information related to a publication that existed. Any help would be great.

View 2 Replies View Related

SQL 2012 :: Transactional Replication - How To Remove Orphaned Articles

Jan 27, 2015

We are runnning on SQL Server 2012 SP1 + CU9.

I have found some articles with no publication in our transactional replication.

For example, running this:

select p.publication,
a.publication_id,
a.article
from dbo.MSArticles as a
left outer join dbo.MSpublications as p
on a.publication_id = p.publication_id

shows this:

NULL1org_Community
NULL3org_Community
Purchasing to EDW5org_Community
NULL1org_Division
NULL3org_Division
Purchasing to EDW5org_Division

How can I get rid of the articles that are not part of a publication?

I can't use sp_droparticle because it requires a publication which these articles do not have.

View 1 Replies View Related

How To Find Orphaned Value From Parent / Child Hierarchy Table

Aug 15, 2013

how to find the orphaned value from the below parent/child hierarchy Table.

create table dbo.Hier(parent varchar(100), child varchar(100))

insert into Hier
select 'subramanium','Manickam' union all
select 'subramanium','Munuswamy' union all
select 'Munuswamy','senthil' union all
select 'Munuswamy','sasi' union all
select 'Munuswamy','uma' union all
select 'manickam','vijay' union all
select 'manickam','bhavani' union all
select 'manickam','dhanam' union all
select 'uma','varsha'

Delete from Hier where child='uma'

I tried:

select parent from Hier
where parent not in(select Child from Hier)
and parent <> 'subramanium'
Getting resultset as:
parent
======
uma

I need to know whether my select statement is correct or not,if its correct,how to write the same in CTE?

View 2 Replies View Related

Is There A Hack That Would Allow You To Reuse Identity Numbers That Were Orphaned By Deleted Records?

Aug 24, 2006

I guess this is a fairly common topic but couldn't find the right words to find anything in a search.

What I'm getting at, is there any tsql functions or combination of commands for the following.

You have identity columns in your tables, if you set the a seed and autoincrement, I enter in rows 1 -10 and then I delete 4, 6, 7, 8.

My next new record uses 11. Is there any logic that allows you to check and reuse 4, 6, 7 & 8 described above? Not looking for something that consists of having to create an extra ID table for each table and handle configuring what the next available number is everytime an Insert or delete is called.

Thanks.

View 4 Replies View Related

Reporting Services :: Parsing SSRS Config File And Dynamically Changing File Path Of Config File In Code

Sep 2, 2015

Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url.  My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?

Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc. 

I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).

Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.

View 2 Replies View Related

File Will Not Attach To SQL Reason File Is Not Primary File

May 10, 2007

I have a customer they are running raid 5 on a windows 2000 server one of the drives went bad. The customer replaced the drive and raid rebuilt the drive, every thing seamed to be fine but there is one database file that cannot be attached to SQL. The file is 15G so I know there is information the error states that the file is not a Primary file. Any clue on how to fix this?

mdf file size 5,738,944 KB
ldf file size 10,176 KB

View 4 Replies View Related

An Attempt To Attach An Auto-named Database For File (file Location).../Database.mdf Failed. A Database With The Same Name Exists, Or Specified File Cannot Be Opened, Or It Is Located On UNC Share.

Sep 2, 2007

Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
 I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
 Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
 

View 3 Replies View Related

Attach Mdf File And Data File Without Log File

Mar 1, 2006

how can i attach the mdf file with a corrupted/deleted Log file
my log file is deleted how can i attach the mdf file

View 1 Replies View Related

Maintenance Commands Affect On Log File / Log File Maintenance Without Log File Backups?

Jun 18, 2015

I am testing some maintenance tasks sql commands such as index rebuild, index reorg, update statistics and db integrity check on a SQL Server 2014 Database. This is a new non-production vendor database (DB Size 500 GBs, Log Size 25 GBs) which eventually will be created in production. Currently, it is in full recovery model and without log backups. The database has a whole lot of indexes. I am just trying to rebuild and reorganize all the indexes (that need it), in addition to trying to get an idea of how long these maintenance task will take and the space needed in the log file to complete these tasks/commands. I would like to execute these tasks manually (the first time) to gather the duration and space required information. Eventually, I would probably schedule a weekly job to perform this maintenance.

I ran the index rebuild task on the database and noticed that the log file grew by over 50 GBs. I killed the process and truncated and shrunk the log file back down.

1. Does the index rebuild, index reorg, update statistics and db integrity check commands all use the log file?

2. Does Indexs Reorg have less impact on log file then Index Rebuild?

3. Should a truncate log and shrink log file be performed after these maintenance commands?

4. Should a full database backup be performed after these maintenance commands? Or before the maintenance commands?

I have read and understand that shrinking is not good for the database (could lead to more fragmentation and more data file growth when data is added) and I know about rebuilding indexes when fragmentation is GT 30% and reorganizing indexes when fragmentation is GT 5% and LE 30%.

Since this is a non-production database maybe I should set the recovery model to simple, run the maintenance commands and leave the database in simple recovery model unless the vendor needs it in full recovery model for some unknown reason.

5. With the simple recovery model the log file should be reused in a circular manner and not grow during these maintenance tasks. Is this correct?

View 3 Replies View Related

Help Please (Check File Exists/ Archive File/ Check If File Empty)

Mar 10, 2008



Hello World,

I'm new to SSIS and would like a little assistance getting started, if possible...


Here is what I want to do:


Check if file exist (C:DTS UpgradeFilexxx.txt) --->

Archive file (C:DTS UpgradeArchive) --->

Check if file has data (true or false)


AND/OR

If there are any good website that have good direction, let me know


Thanks in advance for your help!!!

View 5 Replies View Related

SQL Server 2012 :: Write A Process To Get File Size In Kb And Record Count In A File?

Jul 31, 2014

I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?

I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.

View 1 Replies View Related

SQL Server Admin 2014 :: Creating Additional Data File For A Particular File Group?

Jul 6, 2015

For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.

Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?

View 5 Replies View Related

Integration Services :: Flat File Error File Being Created In-spite Of No Errors

Jun 23, 2015

I have a package in which there are only one Data flow Task and it has only three components. 1) Source , which is a SQL db 2) destination and 3) OLE DB Destination flat file Error output file.   I want the error file to be created ONLY if there is any error while dumping the data into destination DB. But , the issue is, the error flat file is being created inspite of No error while dumping the  data from Source to Destination.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved