massive sim.ldf file - anyone else had there's suddenly grow?

I do, and i got it from you!
It doesn't have the AS to make the cols friendly and i've got a commented out .login_time and .memusage for extra fun on really boring days.
The best addition i can make to that for anyone is to add, 'OR sysdatabases.name like 'fms%' after the first where clause. works a treat and shows me users on both systems. I've adjusted it from a like to an ' = "sims" ' as that's all my db is called. :)

Quote:

---

Originally Posted by jinnantonnixx

Hey Vik, I'm sure you have this script (or some variant), but this tells you who (or what) is active on the system.

It's not the same as who's left their workstations on because the SIMS application creates an SQL connection on demand, then deletes that connection so it can never be 100% accurate, but it's something.

It works on multi-hosted servers, too, provided your database names start with 'sims'. Change as needed.

You can schedule it to make a 'naughty list' of who to blame when your upgrades go wrong. :D

Code:

---

use master;
select        sysprocesses.loginame as [Logged-in user],        sysprocesses.hostname as [Machine name],        sysdatabases.name as [Database name] from sysprocesses
join sysdatabases on sysprocesses.dbid = sysdatabases.dbid where sysdatabases.name like 'sims%' order by sysdatabases.name

---

---

Snap! Also have something similar here we use. It however only returns users for one database:

Code:

---

declare @mySIMSdb as nvarchar(100)
 
set @mySIMSdb = 'sims'



-- 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

---

Ah, that looks familiar!
http://www.edugeek.net/forums/mis-sy...tml#post711495

I prefer the simpler version as you're dependent on copying the structure of the sp_who2 and this does change! I think it's different in SQL 2005 or was it 2000; either way it broke some of my scripts.

We really should put together a tool set of stuff; there's some good stuff floating around, but everything is scattered around.

And it works a treat when we're doing site specific work!

I knew that script was off here somewhere but buggered if I could remember who made it and what thread it came from! Delayed thanks to you for sharing! (Won't let me +rep you until I "spread the love!") :)

Good idea about a central resource. Like you say, people seem to have different tools/scripts/processes etc shared all over the place. A lot of it I'm sure would prove useful to everyone!

Back to the topic of log files, truncation, size, etc, here's a good article on how it works.

http://msdn.microsoft.com/en-us/library/ms190925.aspx

and the finder details of truncation...
Transaction Log Truncation

Quote:

---

Originally Posted by jinnantonnixx

...

We really should put together a tool set of stuff; there's some good stuff floating around, but everything is scattered around.

---

Quote:

---

Originally Posted by Rawns

...

Good idea about a central resource. Like you say, people seem to have different tools/scripts/processes etc shared all over the place. A lot of it I'm sure would prove useful to everyone!

---

How about the wiki? There is already some foundation laid, i think Matt put some stuff in too. It probably needs updating, the problem is finding the time..