SQLExpress, Locked Files, And Sp_detach

Oct 10, 2006

I have a situation where I want to copy a SQLExpress mdf file, after having read some metadata from it, but I find that an exclusive lock persists on the file for about 5 minutes after closing... the lock is held by "sqlservr.exe" This lock prevents me from copying the file. It seems that I may be able to fix this by calling sp_detach_db after closing the connection (does this seem reasonable?) but I am unsure what the database name (dbname) is that I need to pass to sp_detach_db.

To make this a little more concrete, here is the connection string I use... "Data Source=.SQLEXPRESS;AttachDbFilename=C: estdataDVDCollectionDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True'" so in that case what would be the dbname?

Note that I need to do this through API (in a .NET application)

View 7 Replies


ADVERTISEMENT

Detached NAS Mdf/ndf Files Locked And Won't Reattach

Oct 22, 2007



Hi



I have a database consisting of multiple files on a remote server.

Both servers have the same user (bob) which is in the administrator group.

The directory that the mdf/ndf files exist on is shared as "nas" with full rights given to the user bob.

They are both in the same workgroup.

SQL server is running as this user (not ideal I know but server and services will be locked down later)



The script I used to create the database uses the following line to enable remote database files:



-- Enable the use of network drive for DB files

DBCC TRACEON(1807)

GO



I used SSMS to detatch the database and then modified my create script by removing database sizes and adding "FOR ATTACH" at the end. (I have also tried using sp_attach_db but no use)



So the script looks like this:



CREATE DATABASE [dbname] ON PRIMARY

( NAME = N'DBName', FILENAME = N'\123.45.67.89asDBName_FG.mdf' , MAXSIZE = UNLIMITED, FILEGROWTH = 10GB ),

-- Filegroups for the Alerts Table and Indexes.

FILEGROUP [FG_DBFile2]

( NAME = N'DBFile2_FG', FILENAME = N'\123.45.67.89asDBFile2_FG.ndf' , MAXSIZE = UNLIMITED, FILEGROWTH = 5GB ),

FILEGROUP [FG_AlertsIndexes]

( NAME = N'DBFile3_FG', FILENAME = N'\123.45.67.89asDBFile3_FG.ndf' , MAXSIZE = UNLIMITED, FILEGROWTH = 5GB )



LOG ON

( NAME = N'DBName_log', FILENAME = N'\123.45.67.89asDBName_log.ldf' ,MAXSIZE = 13GB , FILEGROWTH = 0%)

FOR ATTACH

GO



When I run the script I get the following error:



DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Msg 5120, Level 16, State 101, Line 5

Unable to open the physical file "\123.45.67.89asDBName_FG.mdf". Operating system error 5: "5(error not found)".



I know the permissions are ok as I can map a drive without being prompted.

SQL server is running as the user bob which is the same user I log onto the server as.



I am running Windows 2003 Server R2 Standard Edition on server with SQL Server 2005 SP2.

The NAS box is running SQL Server R2 Storage Edition.



One thing I noticed was that the mdf/ndf files on the NAS box are locked as if SQL is still using them, i.e. I cannot rename them, move them, alter any properties etc. I just get "Access Denied".



I've tried rebooting both servers but still no luck. I've also tried stopping SQL server then renaming the files, but again "Access Denied".



I know there has been a problem in the past detaching and attaching db files on a NAS, but the Hotfix is included in SP2, which I am using.



I hope I have included enough info but if I've missed anything, it can be provided.

(Event Log basically replicates what the error message says.)



Any help or ideas would be greatly appreciated.



Thanks



Steve


View 2 Replies View Related

Unlock Locked Database Files In SQL Express ?

Mar 24, 2008

When I develop I sometimes forget and try to open the database file with the MS SQL Express Manager even though the web application I am devloping is still using it ... error lock occurrs and thats fine .... but what is the best way to unlock the files again? .... often I find myself having to restart the workstation because of this and thats sounds a bit like overdoing it .... there should be an easier way to unlock locked db files ...without having to restart the workstations.... is there ?

/Johan

View 3 Replies View Related

BackUp SQLEXPRESS Mdf Files

Sep 28, 2006

Hi All,I searched the archives but I could not find any useful stuff for me.I can backup a database on SQLExpress like USE masterEXEC sp_addumpdevice 'disk', 'Store_1', 'c:Store_1.dat'BACKUP DATABASE Store TO Store_1Here is my problem: If I use logins,roles etc. in an application automatically a mdf file is created. Or I can create a new sql database.  SQLExpress dynamically use the mdf file when connection string defined. This mdf file is not defined as a database on SQLExpress. Because SQLExpress has not a gui, I cannot see the registered database.  How can I backup any sqlexpress mdf file without deattaching or attaching?Thanks

View 2 Replies View Related

Can't Start SQLEXPRESS So That I Can't Install Latest SQLEXPRESS Security Updates.

Mar 13, 2007

I can't start SQLEXPRESS.

The SQL ERRORLOG shows: Error is 3414, Severity 21, State 2 and says: "An error occurred during recovery, preventing the database 'model' (database ID 3) from restarting." Just prior to this, I get a warning: "did not see LP_CKPT_END".

Any thoughts why this might be and how I can fix this?

View 3 Replies View Related

Installing SqlExpress (Advanced Services) Will This Break Existing SqlExpress?

Sep 21, 2006

hiya,

I have sqlExpress and sqlServerManagementStudio on my XP pro box.

Will the installation of sqlExpress (Advanced Services) cause any problems?IS thereanything that I shold be aware of in advance?

many thanks,

yogi

View 3 Replies View Related

How To Install Windows Application(C#) With SQLExpress In A System Which Is Having SQLExpress Already?

Jan 19, 2007

Hi All,

I have created an installation application which will install the application with SQL Express and .NET Framework 2.0.

If i install this application in a Fresh system(i.e which is not having SQL Express), it is installing the application with new database instance successfully.

But if i try to install the same in a system which is already having SQL Express, throwing "Object reference exception" because it is not able to create the new database instance.

Can anybody help me out .

Regards,

Doppalapudi.

View 2 Replies View Related

Sqlexpress Installer Doesn't Believe Sqlexpress Has Been Uninstalled

Mar 21, 2006

Because of numerous problems trying to get sqlexpress working, I uninstalled it with the intention of reinstalling (via Add or Remove Programs). However, now when I try to reinstall it, I get a message that the I am not making a changes so it won't let me install it.

I do have sql server 2005 developer's edition installed; is that the reason? and does that mean I cannot have both installed on the same machine?

View 1 Replies View Related

Convert A SQLExpress File Based DB Project To SQLExpress Server DB Project

Jul 1, 2006

Hi All,I've been struggling with this for hours...Could someone please advise me on how to convert my current File based SQL Server Express website to a Server based SQL Express one.Particularly interested in what I need to do in the SQL Express management tool, changes I need to make the projecvt itself and changes needed to get IIS to understand things have been changed.Thanks,Martin.

View 1 Replies View Related

Locked Out

Apr 10, 2007

I am using the desktop sqlexpress 2005 and I was experimenting with the account setting and I accidentally disabled my access. I am using windows authentication and there is no password, just the windows login name.

How can I reactivate the login for this connection. Any help is greatly appreciated.

View 1 Replies View Related

How To Tell If A Row Is Locked?

Apr 14, 2008

I am writing in regard to this post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52798
Which I found while searching for a best-practice way to implement record locking in my application.
In the above referenced post the user asks “How can I tell if a row is locked?�, and the reason for asking is that the user wants to implement logic similar to this:

Select a row for editing.
If the row has been locked by a different user, display the information as read only.
Otherwise, lock the row and allow the user to edit (allow others to read).

My questions are exactly the same as the original poster of the above thread. I was surprised to see that the general response to the post was the user was taking a wrong approach and that sql server is unable to work as the user requested. I think the approach is both correct and desirable (I used it extensively as a foxpro programmer) and I would like further confirmation that sql server does not support it.

One of the things I don’t understand is the interaction between transactions and locks. Is it not possible to lock a row without initiating a transaction? Bottom line, my goal is this:

Select a row for editing.
If the row has been locked by a different user, display the information as read only.
Otherwise, lock the row and allow the user to edit (allow others to read).


However I get there is fine with me.

View 12 Replies View Related

Mdf Locked

May 12, 2008

Hi to all,
I need help about access to mdf file. I don't know when, but sometime my application, build with vbnet, return an error on mdf database access, that is access denied, also winxp return on the mdf file the message "process cannot access the file because it is being used by another process" if I try to delete the mdf file. I can delete the file only if I stop the procss sqlservr.exe from taskmanager. I don't use instruction to open and close database, I use dataset, datatable and sql build in datatable.
Someone can say me why sqlservr.exe don't unlock the mdf file when I close my application?
Thanks..

View 5 Replies View Related

Table Is Getting Locked

Jan 10, 2002

Hi,

When I am deleting records from a table, I am not able to select or insert records from the table. This particular table needs to be used by more users and very frequently used.

Could anyone please help with ideas.

Thanks in advance
John Jayaseelan

View 5 Replies View Related

Tables Act Like They Are Locked For Dbo Only

Feb 23, 2001

I am having an interesting problem that has got me completely baffled. I restored a database from the .bak file of another database on my server to use as a test DB for an App. upgrade before upgrading the live side. The problem I have run into is that the application will not allow login into the DB unless the Trusted Connection user has dbo privs. The DB is not set for dbo use only acoording to sp_dboption. It is not this way on the live side and tech support for the app. and I both think it has to strictly do with something in the SQL permissions. Any Ideas on what could have happened? Is the restored DB hanging onto something from the original DB that could be causing this? The connection works great on the live side on the older version, and I have seen it work in the newer version as well. Any takers?--Dallas

View 2 Replies View Related

Account Locked Out

Jul 6, 2006

I have a problem that i can't log on sql server as the account is lock out. I don't know why it is locked out as this is the first time i log on. Anybody has this experience? Thank you very much!

View 4 Replies View Related

User 'sa' Is Locked Out

Sep 6, 2006

I just brought SQL Server 2005 up on Windows Server 2003 a few days ago. Everything was going great up until a few minutes ago. I tried to connect using the Management Console and get the message

Login failed for user 'sa' because the account is currently locked out. Your administrator can unlock it.

It's the administrator that's locked out!

Do I have to reinstall SQL Server all over again?

View 2 Replies View Related

'sa' Account Is Locked Out

Jun 19, 2008

My 'sa' account is locked out and the BUILTINAdministrators account does not have the sysadmin role. I removed the sysadmin role from BUILTINAdministrators for security reasons and before I could create a new account with the sysadmin role I fat fingered the sa password and locked it out. How can I get myself out of this mess.

Any help would be welcomed.

View 10 Replies View Related

Mdf File Is Locked

Dec 18, 2007

My mdf and ldf files were locked by windowsXP. Iam not using these files in anywhere of my two instances of databases.

Even after stopping SQL SERVER 2005 iam not able to cut or copy those files.

How to recover this files.

View 5 Replies View Related

Tables Get Locked

Jul 23, 2005

HelloI have a stand alone Java application that uses SQL Server 2000 on thesame machine. Sometimes, when we run the applications and follow acertain steps, the SQL server locks some of the table. At that point oftime, if we try to query Select * on those table, in Query Analyzer, itjust keep spinning and doesn't return anything. That tells me that thetables are locked. If I close the application then it frees up theconnection and those tables are accessible again.My question is how do I find out how and what query/update causes thetables to be locked. Is there a tool (some kind of a profiler) that cantell me what was the last insert/update/delete quesry was run before itgot locked?Please help me.

View 2 Replies View Related

My Sql Server Locked Everyone Out

Aug 27, 2007

My sql server just dissalowed every connection attempt made. All the allowed users were denied permission, including the computer's admins. I changed the permissions and tryed everything I could think of but it wont let me back in. Can someone help ASAP?

View 1 Replies View Related

Help! I Am Locked Out From My SQL2005

Jul 7, 2006

Hi Champs,
Is there a way to recover from a account lockout in SQL2005?
I cannot connect to "Database Engine" in "SQL Server Management Studio",
error: login failed...18456. Access denied.

And I also cannot start the "SQL Server agent service"

Any possebility to recover without reinstall?


/Many thanks

View 3 Replies View Related

SA Account Locked Out

Mar 20, 2006

I have been using SQL2000 for a number of years and the company I work for needs a new system so I decided to install SQL2005.

I installed SQL2005 without any problems and set a complex password for SA. I tried to log on and had to make several atempts as I remmebered the password after the SA account got locked out with the following message:

Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it. (Microsoft SQL Server, Error: 18486).

The problem I have is I cannot log onto SQL at all as it is not logging on with windows authentication either!

How do I unlock the SA account or do I have to reinstall SQL?

Thanks

Brian

View 28 Replies View Related

Why Are Buffer Locked...

Oct 22, 2007

We had a package fail when trying to get 3 buffers. I also saw another message saying "18430 buffers were considered and 18430 were locked. "

1. Why are buffers locked in SSIS
2. How can i reduce the number of locked buffers.

This one in question was a very large lookup.

Thanks
Glenn

View 1 Replies View Related

Locked Out Of Enterprise Manager

Apr 16, 1999

I am a beginner working on a webclass project using VBLive as a model. Today I have lost any ability to enter Enterprise Manager for SQL7 although my SQL7 Server is still running and operational with the webclass application.

When I try to start the enterprise manager, i get a mmc.exe-application error
Have I locked myself out somehow? How to get back in control?

Thanks.

View 2 Replies View Related

Locked DBCC Process

Aug 17, 1999

Hi,
I've started 2 dbcc processes using SQL Scheduler overnight. and I've got
one is flagged rollback and the other is still running, they are blocked
together. I've tried to kill them using kill <spid> without success.
How can I delete this processes ? Shall I shutdown the server ?

Thanks

Herve

View 1 Replies View Related

Please, HELP!!! OLAP Cube Locked

Aug 26, 2004

When I try to process a cube on olap I receive this error message:

Could not lock object (user xxx on computer yyy has locked 'xxxxxxxx'

I already tried to find an option to unlock the cube and I do a restart of SQL service.
I'm worryed about my job !!!! :eek:
Please, HELP ME !!!! :(

View 2 Replies View Related

Locked Out Of Enterprise Manager

Mar 7, 2007

Hi all

First post - please be gentle :)

I have had a search of the forum and couldn't find a solution to this one.

On a clean install of SQL Server 2000 on a W2003 server we setup our database as normal, selected appropriate priveldges for everyone and all was well.

Until an adminsitraor accidently turned builtinadministors account to DENY access. We are now locked out of EM and I can't think of a way back in.

We can't access the database via the sa account as it is set to Windows Autentication, and we can't re-register the database for the same reason.

No other user has sufficent priveldges to update the master db or change authenticaion, or even log into EM for that matter.

I think we are snookered, but any thoughts are welcomed.

Cheers

K2

View 3 Replies View Related

SQL Table Locked Up. How Do I Unlock It?

Nov 18, 2005

I can't open a table that has 5 records. Is there any kill processes that can unlock the table for me?

View 2 Replies View Related

Merge Replication Locked

Jul 11, 2007

Hi All



New to sql server .



Have setup several pilot installations using merge replication between sql server mobile and sql server 2005. All has been perfect for over a week in all locations. last night one of the ocations stopped updating. Upon further investigation all devices kept throwing errors: "The database is currently synchronizing with the server". So I left for a further 2 hours with no change. The replication process usually takes abot 1 second and never take more than 3 or 4 seconds. There was no change to the data at all in this case either. I fixed the issue by restarting the whole server.



It would seem that there was some kind of lock on the database. Being new to sql server my question is:



Is there a way to properly diagnose the cause (I looked in the system event log and found nothing).
Is there a way to get notified if this occures again.

We are plannng hundreds of similar installations and cannot constantly monitor all of them in the hope of finding this issue again.



Regards



Nick

View 1 Replies View Related

SQL Stored Procedures Locked

Feb 8, 2008



I am facing a problem in SQL SERVER 2005, 3 stored procedures seem to have a padlock on them and when i right click and the modify option is disabled.

I have tried logging in as SA and it seems to be the same problem, it is a database which someone else developed, but I need to modify it.

Any help would be appreciated.

Thanks
Harp

View 7 Replies View Related

Problems With 'locked' MDF File

Nov 16, 2006

Hi Folks,

When I create a Database and try to copy the MDF file, I get a message to say that the Database is locked or in use by another user.

Seems like maybe I am not doing a 'Save' operation - although I can't find one ?

If I shut down the PC and restart it, then the MDF File is freed up, although when I try to open it, there is something wrong with it.

I'd appreciate any suggestions.

Thanks a lot.



Barry







View 4 Replies View Related

Locked Stored Procedures

May 29, 2007

Hi



I am reasonably new to this so bear with me.



I have a copy of a SQL Server 2000 Database, and would like to see what the stored procedures are doing (as I think it is wrong).



in Server Management Studio the stored procedures have a lock symbol on them and when I try and view them I get the message:



TITLE: Microsoft SQL Server Management Studio
------------------------------

Script failed for StoredProcedure 'dbo.sp_procedure'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+StoredProcedure&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Property TextHeader is not available for StoredProcedure '[dbo].[sp_procedureprocedure]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)





This was developed by someone else, but I need to see it.



My question is, is there any way i can see the text of this stored procedure?



Thanks



Will

View 4 Replies View Related

Test For Locked Rows

Aug 31, 2007

Hi
I want to know if is there any way to check a locked row for another process using a ID row in a table. I was looking for a query over sys.dm_tran_locks, but didnt get the ID for the locked row.
My app must send a message to user if another is procesing the same row, not only wait for unlock.

Any suggestions?

Thank you

View 5 Replies View Related







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