TSQL To Start A SQL Agent Job
Most of us are familiar with the quick and easy right clicking on an agent job an select “Start job at Step…”, but what if… Read More »TSQL To Start A SQL Agent Job
EXEC
Largest tables on an instance
Here is a quick script that I find myself using frequently to find the largest tables on an entire SQL Server instance.
Find all tables with Change Tracking on your SQL Server
Change Tracking is a great feature on SQL Server, but it can have some performance implications. It is however one of those features that doesn’t… Read More »Find all tables with Change Tracking on your SQL Server
TempDB – Do This and Don’t Do That
Today I had the opportunity to present on TempDB to the Spokane SQL Server users group (PASS Chapter). The session was titled TempDB – Do This… Read More »TempDB – Do This and Don’t Do That
Temporary Stored Procedures #sproc
CREATE PROCEDURE #MyProcedureName
Yes, you can create temporary stored procedures by prefixing the name of the sproc with a #. The temporary stored procedure is sort of a “leave no trace” stored procedure that is only good for your session. If you use double ## you will get a global temporary stored procedure.
For instance:
Visualizing VLF’s – Updated
A while back a wrote a blog post with a query to Visualize the VLF’s in your database. Today I have an update to that script.
Here is an updated script that has adds another column called “TextStatus” to the output to give you a better idea of what the different statuses mean. You now get 3 statuses shown, “In Use”, “Available”, and “Available Never Used”. The If you have lots of VLFs that are “Available Never Used” that may be an indication that your log file may be larger than you need. If you don’t have any that are “Available Never Used” the log may be smaller than you need.
Visualizing Log File VLF Sizing
One of my favorite queries this week is the following query that creates a text based bar chart to quickly help visualize the VLF files on any database log file. In the last month I have given the “TempDB Do This and Don’t Do That” presentation twice, once at the Bellingham SQL Server users group, and another time at SQL Saturday Redmond. One of the questions that came up after the users group meeting was around an easy way to visualize the Virtual Log Files. Its one thing to just get a count, but to see the sizing of each VLF is helpful to understand how VLFs work.
If you are not familiar with VLFs, these are Virtual Log Files, or the chunks that make up your entire log file. When your log file grows, VLFs are added to help manage the file space. For the log growth, between 4 and 16 VLFs are added for each growth of the log file (except on SQL Server 2014 and newer, which modifies the sizing algorithm a bit).
TempDB – Do This and Don’t Do That
Today I am presenting my “TempDB – Do This and Don’t Do That” session at SQL Saturday Redmond. Here is the download of the presentation… Read More »TempDB – Do This and Don’t Do That
SQL Server MAX DEGREE OF PARALLELISM
The check for MAX DEGREE OF PARALLELISM has be added to the Database Health Monitor – Quick Scan Report. The max degree of parallelism setting… Read More »SQL Server MAX DEGREE OF PARALLELISM
SQL Server – Dedicated Admin Connection (remote DAC)
In SQL Server there is a special connection that can be used to connect to the database, the remote Dedicated Admin Connection or (DAC). This… Read More »SQL Server – Dedicated Admin Connection (remote DAC)