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

[Rawns]

Always good to read what some of you guys write about SIMS. Happened across this thread by accident and looked at SIMS dbs. I have Options set to Full Recovery model! They were only in here at end of summer getting me on new version of SQL, so going to change that. BUT. as ldf is 1.8GB and 3 times size of data file, just not sure which order to do things? Obviously Backup first (done every night). Rec model first , to reduce file size, or Shrink?

 

Then I'll tell you about my SOLUS 3 / firewall troubles :-(

 

Changing the recovery model of the database won;t actually do anything to tour log file size. It will only stop it from growing further. I've also found that if you truncate the database without changing the recovery model to simple, it barely makes any difference to the size of the log file either. Therefore, I would backup your database first of all, change the recovery model to simple mode and then truncate the database either using SQL management studio or using the script I posted above.

Edited December 13, 2011 by Rawns

[CAM]

Our server filled it's hard disk up and the LA pointed to a file (didn't say which) growing huge as people weren't logging off overnight and not allowing SIMS to do it's cleaning up. Could that be the cause?

[CadlaM]

Changing the recovery model of the database won;t actually do anything to tour log file size. It will only stop it from growing further. I've also found that if you truncate the database without changing the recovery model to simple, it barely makes any difference to the size of the log file either. Therefore, I would backup your database first of all, change the recovery model to simple mode and then truncate the database either using SQL management studio or using the script I posted above.

 

Thanks Rawns, and CAM too. Carried out the above and ldf is now down to "tiny". That's a technical term. File size reduced after another backup by SIMS system manager after the truncate. Don't get the problem of users not logging of, we use Ranger to log EVERYONE off. Hee Hee.

 

Now, about Solus 3 and firewalls.....

[vikpaw]

Our server filled it's hard disk up and the LA pointed to a file (didn't say which) growing huge as people weren't logging off overnight and not allowing SIMS to do it's cleaning up. Could that be the cause?

Yep it's usually the ldf. do as above and check the file, and maybe change recovery model, or just regularly backup and shrinkfile. I don't think SIMS does any 'cleaning up'. I have users staying logged in all the time, though am in Simple mode. The only issue with users logged in, is that they keep a connection open and the bloomin' homepage keeps making calls to the server. I guess in theory, this could always be sending transactions even during the backup period. EDIT: But they are only reports that are running, it's not like anyone is editing data. Or are they? I have had to set a rule that all users must be logged out by midnight, so if i need to, i can kick them off their computers and do any maintenance i want.

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

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

Edited December 14, 2011 by jinnantonnixx

[vikpaw]

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.

[Rawns]

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.

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:

 

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

[jinnantonnixx]

Ah, that looks familiar!

http://www.edugeek.net/forums/mis-systems/80105-dbattach-issue.html#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.

Edited December 14, 2011 by jinnantonnixx

[Rawns]

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!

[jinnantonnixx]

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

Edited December 14, 2011 by jinnantonnixx

[vikpaw]

...

 

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

 

...

 

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