scan for startup procs Option – when to use

Scan for Startup Procs Option is a SQL Server database option. Why use it ? From SQL Server BOL “Use the scan for startup procs option to scan for automatic execution of stored procedures at Microsoft SQL Server startup time.” Why use it? Some examples of applying Scan for Startup Procs Option 1) Create a stored procedure mark it for start up. The stored procedure creates temporary objects in the TEMPDB . These temporary objects are available in the TEMPDB before any requests are made 2) I’ve seen DBAs trigger a stored procedure on the SQL Service service startup that... Read more →

Find who made a database name change with Object:Altered

Question: How can I find out who made a SQL Server database name change? A SQL Agent job failed this morning – and on closer inspection I discovered the database name was changed. Answer: A quick way to discover details on a database name change is to inspect the SQL Server default trace. The database name change is recorded in the SQL Server default trace as event Object:Altered. Follow this link for a query to grab the name change. SQL Server - Who made DDL table changes on the database. If the database name change is a persistent problem, create... Read more →

Who dropped the table in SQL Server

SQL Server has a default trace feature – a lightweight background trace capturing Object creation, object deletion, error events, auditing events and full text events. For more information read SQL Server – default trace FAQ If a user has deleted a database object , such as a table – as long as the trace log is still available – it is easy to write a query and return the relevant information. The query returns useful information such as Application Name,LoginName,StartTime,ObjectName,Databasename. Enough information to begin a proper analysis of what events led to the object deleted. I’ve used this query many... Read more →

How to Monitor SQL Transaction Log AutoGrowth for performance issues

During long running sql transactions autogrowth can cause performance issues. Use this script to assist in identifying slow down Read more →

SQL Server – Troubleshoot memory pressure with Default Trace and Server Memory Change event class

Troubleshooting external memory pressure for SQL Server can be done using Perfmon or the sys.dm_os_memory_clerks DMV. Another method for external memory troubleshooting , is the Server Memory Change event class , reported as part of the Default Trace. SQL Server BOL defines the Server Memory Change event class as “ occurs when Microsoft SQL Server memory usage has increased or decreased by either 1 megabyte (MB) or 5 percent of the maximum server memory, whichever is greater.” The Server Memory Change event class reports on the variances in the delta changes. Using this information the DBA can identify if large... Read more →