This script gets the size of each database file, not including backups. Essentially, you’ll see .MDF, .NDF, and .LDF files.
SELECT DISTINCT bus.database_name
, (buf.file_size / (1024*1024)) AS [File Size MB]
FROM msdb..backupfile buf
INNER JOIN msdb..backupset bus
ON buf.backup_set_id = bus.backup_set_id
ORDER BY bus.database_name
This is a handy script to show the number of cores and MaxDOP in a single output, along with a built-in script to change the value. The built-in script shows up as a query result so you can copy/paste it.
SELECT cpu_count AS [Cores]
, (SELECT value_in_use FROM sys.configurations WHERE name = 'max degree of parallelism') AS [MaxDOP]
, '/*Next Line For SQLCMD Mode*/
:CONNECT ' + @@SERVERNAME + '
sp_configure ''max degree of parallelism'', ''VALUE'';
' AS [Configure Script]
If you’re creating a new index in SQL Server, it is good practice to make sure you have enough space on your drive to do so. You’ll not only need the space to hold it, but double the space as SQL Server builds it on temporary disk space. (https://msdn.microsoft.com/en-us/library/ms191183.aspx) So if your index is going to be 1 GB, then you will need up to 2 GB to make it. By the way, when rebuilding, you’ll need triple the space, as SQL Server also retains the old index while it creates a new one.
Upon trying to create a trace template in SQL Server Profiler, I was able to name the trace but there were no events to choose from in the “Events Selection” tab, even when the “Show All Events” box was checked. If I clicked the “Column Filters” button the entire Profiler application would close itself without displaying any error message… just… gone.
There is a time and a place for everything, cursors included. Here is one way to avoid using them.
To NOT use cursors, you must create a temp table with a "flag" column to indicate whether or not it has been processed yet. Then, rather than updating the cursor, you update the flag column. Here's an example: