[sims] DBAttach Issue

[fiza]

Does anyone know why when I try to use DBAttach to detach my SIMS DB I get the message that "This Database is currently in use. You cannot detach this database until all users have logged out".

 

No users are logged in (currently all user accounts are disabled to allow me to work in peace). I have rebooted the server.

The Dialogue box has the title "FMS Database Installation". As far as I know we dont use FMS.

 

Anyone have any ideas? I need to detach this DB so I can rebuild the server to 2008 R2.

 

I have successfully used DBattach in the past.

[creese]

Which version have you got?

[fiza]

DBattach version 1.24.24.0

[creese]

Try upgrading. Mine is 1.25.23.0

 

Are you on Summer 2011?

[fiza]

no still on Spring. I was hoping to migrate to 2008 R2 from 2003 before implementing the Summer release.

Where can i get 1.25.23.0? I search on supportnet for dbattach but got nothing

[creese]

1.25.23.0 would have come with the summer update. So probably not the problem.

 

Have you tried stopping and starting the SQL service?

[fiza]

Have you tried stopping and starting the SQL service?

 

Just tried that but no joy

 

Will raise a call with Supportnet.

[Banjo]

Try running SQLApplication setup, from memory FMS has it's own dbattach, I think you may be using that one.

[jinnantonnixx]

If you're OK with SQL queries, this will show the current logged-in users for your database.

 

declare @mySIMSdb as nvarchar(100)

set @mySIMSdb = ''



-- drop table #sp_who2

create table #sp_who2 
(
spid int,
status varchar(100),
loginname varchar(2000),
hostname varchar(2000),
blkby varchar(100),
dbname varchar(200),
cmd varchar(max),
cputime int,
diskio int,
lastbatch varchar(100),
pgmname varchar(500),
parentspid int,
request_id int
)

insert into #sp_who2
EXEC sp_who2

select loginname, hostname, dbname  from #sp_who2
where dbname = @mySIMSdb


drop table #sp_who2

[vikpaw]

How are the accounts disabled? What if there is a rogue machine out there with SIMS open..?

Try going on the local console and then disabling tcp/ip access.

Also try a script in sql management studio to see who is logged in...

 

use master;

select distinct sysprocesses.loginame,
/* sysprocesses.spid, */
sysprocesses.hostname,
sysdatabases.name,
/* sysprocesses.nt_username, */
/* sysprocesses.login_time, */
sysprocesses.status,
sysprocesses.memusage,
sysprocesses.dbid
from sysprocesses


join sysdatabases on sysprocesses.dbid = sysdatabases.dbid


where sysdatabases.name = 'sims' OR sysdatabases.name = 'fms'


order by sysdatabases.name, sysprocesses.loginame

 

Gah! he beat me to it. Maybe you can check my code is good @jinnantonnixx - though i probably got it from you anyway

Edited July 26, 2011 by vikpaw
add

[creese]

I think the clue is in the FSM error. We don't support FSM so can't really check.

 

Are you using the shortcut on the start menu?

[jinnantonnixx]

The other option is to run the detach manually from SQL manager.

 

Be sure to copy the db users to the transfer table first. PM is you want the SQL code.

[jinnantonnixx]

Does anyone know if SIMS are now flagging users as 'logged in' in the database?

If this is new, it could explain the problem.

[vikpaw]

Try running SQLApplication setup, from memory FMS has it's own dbattach, I think you may be using that one.

True.

I have 1.24.24.0 too from Spring.

 

The sims one and the fms one are the same though. Not sure it would make a difference. There was an explanation somewhere previously from Phil Neal about why there were two, but i didn't really get it. It came from a developer!

 

It's a pain that they both have the same name.

 

The SIMS one lives in the db binn folder :

Program Files\Microsoft SQL Server\MSSQL10_50.SIMS2008\MSSQL\Binn

 

FMS as above but in the FMSSQL subfolder. Mine actually for FMS is 1.24.22.0 - So you can't be using that, plus you don't have FMS..

 

I usually track them down and change the shortcut names so i can ID them. Also you can add parameters to save you typing in server / db name every time. Helps if you use it alot, mostly on test server.

[vikpaw]

I know from bitter experience, that if a workstation has SIMS open it will be making a connection even if no-one is there, because of the stupid homepage graphs!

Not sure about the 'logged in' flag, but that script i use does show both the user and the workstation, so a remote shutdown command does the trick. Or kill tcp/ip connections which is more reliable.

[matt40k]

sp_who2 will give you a list

 

@vikpaw Basically, they both currently are build from the same tree - ie the source code is the same - however it's build for SIMS and another for FMS. So why should you care? Because they could change it in the next release so they are database specific.

[Cache]

We have (admittedly a long time ago now) had a phantom user before, a Server restart fixed it. Might be worth a try?

[creese]

We have (admittedly a long time ago now) had a phantom user before, a Server restart fixed it. Might be worth a try?

 

Yes it happens, that's why I suggested it. For the time it takes, it's always worth a shot.

[matt40k]

Had fun with one of those pesky third party apps that link into SIMS, as it was running as a service and was using a staff login, didn't help the service was on another machine.

[fiza]

Or kill tcp/ip connections which is more reliable.

 

disabling tcp/ip and named pipes in SQL management studio did the trick .Thanks for the help everyone.

[vikpaw]

disabling tcp/ip and named pipes in SQL management studio did the trick .Thanks for the help everyone.

Awesome, would be good if you can run one of those scripts though, at some point and pinpoint what task/user/service is doing this. Useful to know what's on the network

[RobT]

I have encoutered this issue before and have found the problem was caused by groupcall. It seems that sometimes groupcall can leave a connection open to the database. To resolve this I now stop the groupcall service and then restart it after I have finished.

[Hecate]

If you run sql management studio look in the activity manager to see if any users show as active.

[matt40k]

...only if you have SQL Standard :'(