[sims] Transaction Log File

[caffrey]

Right, database is now set to SIMPLE, shrank the logfiles

MDF is 884.94MB

LDF is 1.09MB

 

I think it's worked Thanks again for the help

 

Maybe something should be stickied so more people are aware of this ?

[vikpaw]

The worry is that people rush in, read the last few posts then wreck their system cos they're not really sure what they are doing or what has been set-up by others. You were walked through by an expert and that man needs some severe thanks. {doths cap in direction of @jinnantonnixx}

 

A carefully worded sticky with contribution from jinn and matt about checking the backup mode would be useful, then leave the solution finding open.

[caffrey]

Totally agree, it's what makes Edugeek the way it is

 

The running of the scripts can be expanded on how they are run for people who don't know. e.g. right click on a database and run query - cut and paste then press F5

[clareq]

I was advised by Capita back in January to check the recovery mode, and at that point I changed it to simple. Today, after complaints of SIMS slowing down I checked it again, and it was in Full mode. I've changed it back, but both Capita and I have no idea how or why it reverted - at no time have I restored the database. Event logs show that the recovery mode has changed 20 times since January - sometimes twice in 10 seconds.

[jinnantonnixx]

There is an excellent free book available as a PDF which covers everything about SQL backups. It's a very comprehensive book, and well worth a read if you have the time. Even if you don't make use of the information, you'll understand your SQL system a whole lot more.

 

Hats off to Red Gate, they make nice products and give away lots of free stuff.

 

Redgate book store - SQL Server Backup and Restore

[matt40k]

Nice offices too, they do free community events too and sponsors a lot of the free Microsoft SQL events as well, worth going to if you can get to any.

[jinnantonnixx]

I was advised by Capita back in January to check the recovery mode, and at that point I changed it to simple. Today, after complaints of SIMS slowing down I checked it again, and it was in Full mode. I've changed it back, but both Capita and I have no idea how or why it reverted - at no time have I restored the database. Event logs show that the recovery mode has changed 20 times since January - sometimes twice in 10 seconds.

 

Any idea what happened at the times of these changes?

[clareq]

No idea - on at least one occasion the school was closed. There is no consistency in the time the change occurred. Now I know it is happening I'll check it when notified of a SIMS issue and hopefully we can narrow it down.

[linkazoid]

A big thank you for your helpful posts @jinnantonnixx

 

We noticed that our log files had grown to 71Gb. Having followed your posts, it seems that our SQL was also set to FULL and no limit was set. We are also a school that has come from E1 so had a new installation about a year ago.

 

Ours has shrunk from the 71Gb to 20Mb with it limited to 2Gb.

 

Thanks you for sharing your wonderful knowledge with the rest of the community!

[jinnantonnixx]

Here's a way to get warnings when the recovery model changes. Using "SQL Policy Management" policies it's possible to check the recovery model of the SIMS database(s) and raise an alert if the databases are not in compliance.

 

*** Again, the usual caveats - if you have doubts about any of the procedure, do not proceed.

 

 

First, take a look at the principles behind SQL policy management, described in this excellent website.

Identify SQL Server Databases Not in Full Recovery Model

 

That covers setting up the polices, but we need to do more to create the alerts that will tell us when a problem crops up.

 

Read this to understand the principles of alerts.

Configuring Alerts for SQL Server Policy Based Management

 

A tip of the hat to that excellent website for such clear and informative articles. Those two links should be enough to set up a compliance policy and an alert, but just in case, here's my recipe for creating a policy and an alert to tell me when the recovery model gets flipped to the wrong settings.

Apologies in advance if it's incomplete or erroneous, but hopefully it's of some use.

 

In SQL Server Management...

 

Expand the SQL server

 

Expand: Management>Policy Management>Policies

 

Right-click Policies > New Policy

 

Name: Describe the policy (e.g. Check Recovery Model)

 

Check condition: Select New Condition

 

Name: Name the condition (e.g. Check Recovery Model)

 

Select the correct Facet (in this case: Database Maintenance)

 

Add a new expression (e.g. @RecoveryModel = Simple) (or Full, if you use the full model)

 

Against targets (choose New condition)

 

Name: Name the condition (e.g. Select the SIMS database)

 

Facet: Choose 'Database'

 

Add a new expression: (e.g. @Name='SIMS', or whatever your db is called. Don't forget the quotes around the name.)

 

OK to save the policy

 

Test the policy with [The New Policy] > Right Click > Evaluate

 

If your database is set to the correct model, you should see a 'green' result, otherwise you'll see an error.

 

Change the policy's 'evaluation mode' to 'On schedule'

 

Create a new schedule. Once an hour should be OK.

 

Right-click the policy and choose 'Enable'. Check that the little red mark is not showing against the policy icon.

 

Now set up the Alert system, so we know about a fault when it occurs.

 

SQL Server Agent>Alerts>New Alert

Name the new alert 'Database Recovery Model Policy Violation'

Enter 34052 in the Error Number field. This error number is a system number that is logged when a policy violation occurs.

 

Under the Response page, choose 'Notify Operators' and choose the appopriate notification method (e.g. Email)

 

Under the Options page, check the tickbox for "include error text in email"/net send or whatever

In the additional notifications, type something informative about the recovery model being wrong; this text will be sent in the alert.

 

 

If you don't have an email operator, create a new 'Database Mail' account (Management > Database Mail) and a new operator to use this email account (SQL Server Agent > Operators)

Full instructions here.

http://www.mssqltips.com/sqlservertip/1100/setting-up-database-mail-for-sql-2005/

 

----

 

Well, everything’s in place, but to test that this is going to work, change the recovery mode (not near a backup period!) and see if the alert does its job.

 

-----

 

 

Post back if this goes well, or not, or if the instructions need amendments. It was a bit rushed, so probably needs refinements.

Edited April 17, 2015 by jinnantonnixx

[clareq]

@Jinnatonnixx -thank you! My first job for Monday morning.

[jinnantonnixx]

I'm going to use the policy management to set up alerts for log file sizes. As any policy violation generates the same error number (34052), I'm going to have to create a separate alert with a filter for some key words. It shouldn't be a problem, though.

[vikpaw]

I haven't looked at the policies yet, but maybe you know, can you use it to check free space on a drive, e.g. the backup location? Or do they have to be SQL / database conditions?

Would be useful to know when backup space is running low.

[vikpaw]

I haven't looked at the policies yet, but maybe you know, can you use it to check free space on a drive, e.g. the backup location? Or do they have to be SQL / database conditions?

Would be useful to know when backup space is running low.

No matter - found this, that is a great site, and can whack it in as a maintenance plan with a conditional failure alert, similar to the one i have for my backups. )

Determine Free Disk Space in SQL Server with TSQL Code

[jinnantonnixx]

Hi Vik,

 

There are a lot of example policies on the system already. Import them with Management>Policy Management>Policies>Right-click

 

Browse to c:\program files(x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033 (you version and country code might be different)

 

Import whatever you think looks interesting (or import them all, you can always delete them later).

 

I don't remember seeing a disk-space-low policy.

 

 

Edit:

------

 

To see what information is available to you when you create your conditions, expand the Management>Policy Management>Facets and examine the fields available inside a facet that looks interesting.

Edited April 20, 2015 by jinnantonnixx

[jinnantonnixx]

I've added a policy to check the log file size in use. I'm less concerned with the physical file size, but I want to know when the space in use by the log file goes over a threshold.

I used the 'Log File' facet and the '@UsedSpace' field for the condition. With the policy scheduled to run every hour, this looks quite useful.

[Cache]

Well, the last 2 times my database recovery model has changed, Solus3 has been deploying an update.

 

1st time it was deploying Solus 3.9, 2nd time (tonight) it was pushing out patch 19098....

 

That's the only link I've got for it changing, but yet it pushed out the SIMS Spring Upgrade before I put the patch on and it didn't change it and it was the 3rd attempt to push out 3.9 that it changed the recovery model.

[jinnantonnixx]

See if this is helpful. It will look through the last log file (which is defined by sys.traces) and look for tell-tale signals of the recovery model changing. It will only work with recent events as the log files are cycled, but try it anyway.

 

SQL Server usually has a lightweight default trace running all the time. It's a kind of 'black box', recording major events. Several log files are kept, and these are cycled in turn.

 

 

First, run this:

exec Sp_readerrorlog 0, 1, "recovery", "option"

 

This will show when the recovery model changed, and the SPID which changed it. Note the SPID and the time of the event that is of interest to you.

 

Very well, but all we have is the SPID, not the user or process. Not much help so far.

 

Next step is to get some useful data from the default trace table.

 

Edit: Because of the stupid way that 'MENTION' quotes are used, even in code blocks(!), I have had to insert a space after every '@' character. Remove the space after the '@' character if you want to try this code.

declare @ logfile varchar(max)

set @ logfile=(select path from sys.traces) -- get the current log file path

print @ logfile

select ('spid'+cast(tt.spid as varchar)), tt.ntusername, tt.DatabaseName, tt.ApplicationName, tt.starttime, tt.sessionloginname from fn_trace_gettable(@ logfile,null) tt
where tt.EventClass=164 and tt.EventSubClass=1
and tt.DatabaseName <> 'tempdb'

 

This lists the SPID and other data of the processes that altered database objects (this includes its recovery model).

 

Take the SPID and the time from the first result, match the SPID and the time in the second result, and voila, we have fairly good proof of who or what is changing the recovery model.

 

@Cache if you suspect SOLUS is monkeying with your recovery model, this might help.

Edited April 22, 2015 by vikpaw

[Cache]

Well, for the time it changed last night the application recorded next to it is .Net SqlClient Data Provider using the login SA - probably doesn't say a lot.

[jinnantonnixx]

Can you add 'tt.hostname' to the last query? This will show the machine from which the command was run.

 

..... tt.sessionloginname, tt.HostName from......

[Cache]

Hostname comes back as our SIMS server name.

 

Had a quick nosey around, under current processes in Activity Monitor, the 3 processes currently accessing the Solus3 database are.... .Net SqlClient Data Provider, but they are running under my administrator login rather than SA

Edited April 23, 2015 by Cache

[vikpaw]

I'd be scared someone has set up some kind of random script / "maintenance task" to run on the DB.

Can you check if there are any maintenance plans?

[Cache]

Nope no maintenance plans set and no scheduled tasks on the server other then those I can identify (B2B processes, CommandReporter, Discover Transfer, Antivirus scan)

[jinnantonnixx]

Hostname comes back as our SIMS server name.

 

Had a quick nosey around, under current processes in Activity Monitor, the 3 processes currently accessing the Solus3 database are.... .Net SqlClient Data Provider, but they are running under my administrator login rather than SA

 

 

Hmmmm. Capita could have passed the application name in the connection string so that the particular application can be identified. But it seems that they use 'STA' for SIMS and the default '.Net SqlClient Data Provider' for everything else.

 

At least you know it's a task running on the server, and if you keep an eye on what happens when, the timing might help you zero in to the problem.

 

A MacGyver solution could be to set up different Windows logins/users for different apps. These logins/users can be traced more easily.

Edited April 24, 2015 by jinnantonnixx