+ Post New Thread
Page 3 of 3 FirstFirst 123
Results 31 to 36 of 36
MIS Systems Thread, massive sim.ldf file - anyone else had there's suddenly grow? in Technical; I do, and i got it from you! It doesn't have the AS to make the cols friendly and i've ...
  1. #31

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    6,011
    Thank Post
    683
    Thanked 1,398 Times in 1,158 Posts
    Rep Power
    353
    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.

  2. #32
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 490 Times in 375 Posts
    Rep Power
    0
    Quote Originally Posted by jinnantonnixx View Post
    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.
    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

  3. #33

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,991
    Thank Post
    114
    Thanked 507 Times in 342 Posts
    Blog Entries
    2
    Rep Power
    286
    Ah, that looks familiar!
    DBAttach Issue

    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.
    Last edited by jinnantonnixx; 14th December 2011 at 09:41 AM.

  4. Thanks to jinnantonnixx from:

    Rawns (14th December 2011)

  5. #34
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 490 Times in 375 Posts
    Rep Power
    0
    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!

  6. #35

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,991
    Thank Post
    114
    Thanked 507 Times in 342 Posts
    Blog Entries
    2
    Rep Power
    286
    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
    Last edited by jinnantonnixx; 14th December 2011 at 10:24 AM.

  7. #36

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    6,011
    Thank Post
    683
    Thanked 1,398 Times in 1,158 Posts
    Rep Power
    353
    Quote Originally Posted by jinnantonnixx View Post
    ...

    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 View Post
    ...

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

SHARE:
+ Post New Thread
Page 3 of 3 FirstFirst 123

Similar Threads

  1. Has Anyone Else Had This Email From Crucial.
    By daz666 in forum General Chat
    Replies: 7
    Last Post: 8th April 2011, 06:35 PM
  2. Anyone else had (or about to have) an awful lunch?
    By Dos_Box in forum General Chat
    Replies: 25
    Last Post: 8th October 2010, 08:35 PM
  3. Replies: 5
    Last Post: 28th June 2010, 08:41 AM
  4. Replies: 3
    Last Post: 9th September 2008, 06:23 PM
  5. [SIMS] sims ldf file nearly 17 gig
    By edie209 in forum MIS Systems
    Replies: 11
    Last Post: 22nd November 2007, 10:30 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •