Fraggle-805517 (5/1/2012)

---

General question. ...

If you have 6GB RAM then how would setting max memory to 15000MB help you restrict the SQL Server from growing the buffer pool to the point where it affected...

"MSDB database MDF is 13.5 GB" suggests it may need some attention. backup history, job history and database mail logs are common sources of msdb bloat.

With 6GB of RAM in your machine 1500MB would be very conservative. 1500MB may make sense for you though, it depends on what else you have running on your server...

OK, I see now.

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'ActiveTravelNetwork'

Backup history is deleted when a database is dropped. Originally I thought we were talking job history...for backup history it is...

Krasavita (5/1/2012)

---

PhysicalMemoryGBVasGBBufferPoolCommittedMemoryGB BufferPoolTargetMemoryGB

5.99 8192.00 2.21 ...

pamozer (5/1/2012)

---

and assign the values from the DataSet to another variable, per the Variable Mapping Page". Then you can use the second variable I mentioned that receives the values...

You will not achieve a "deep understanding" without putting in some time learning on your own. Feel free to use Google.

Here are some good articles to get you started:

PAE and...

Hmmm, Google must have been broken again mohan.bndr. Using an exact phrase from your original post I see many results that can help you understand SQL Server Error Logs:

error logs...

Can you please provide the version, edition and bitness of your OS and SQL Server instance?

And the results of these queries:

-- memory overview

SELECT CAST(physical_memory_in_bytes / (1024.0 * 1024.0 *...

What you may want to do is chunk it out until you catch up. How many days worth of job history do you need to delete?

sp_purge_jobhistory accepts a parameter to...

PPS I would also make NUM_ALLOWED an INT to match the data type that COUNT() returns.

And one other perfromance bump might be to capture the ORIGINAL_LOGIN() in a variable, instead...

PS I would recommend making LOGIN_ID a SYSNAME and making it the unique clustered index of dbo.LimitedLogins.

You would need to make sure logon_trigger_login can SELECT from the table:

USE GK50LIVE;

GO

CREATE USER [logon_trigger_login] FROM LOGIN [logon_trigger_login] WITH DEFAULT_SCHEMA = [dbo];

GO

GRANT SELECT ON dbo.LimitedLogins TO logon_trigger_login;

GO

Then you could modify...

Are you sure you were encountering deadlocks? and not just significant blocking?

Deleting large swaths of job history can wreak havoc on your server if you have a lot of jobs...