Monthly archive

SQL Server – Get Size of Database Files

ben himself's picture

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]
, buf.physical_name
FROM msdb..backupfile buf
INNER JOIN msdb..backupset bus
ON buf.backup_set_id = bus.backup_set_id
ORDER BY bus.database_name

Tags: 

TSQL – Get Number Of Cores & MaxDOP In Single Query

ben himself's picture

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''; 
RECONFIGURE;
GO
        ' AS [Configure Script]
FROM [sys].[dm_os_sys_info]

Tags: 

Estimating the Size of an Index

ben himself's picture

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.

Tags: 

Finding Duplicate Records In Database

ben himself's picture
SELECT COL_TO_CHECK, COUNT(COL_TO_CHECK)
FROM TABLE_NAME
GROUP BY COL_TO_CHECK
HAVING COUNT(COL_TO_CHECK) > 1
If there is more than 1 column with that value it will show up like:
COL_TO_CHECK               COUNT(COL_TO_CHECK)
-------------------------------------------------------------
Value                             3
Value2                            2
Value99                           4

Tags: 

SQL Server – Find Stored Procedures User Can Access

ben himself's picture

Returns a report that has information about user permissions for an object, or statement permissions, in the current database.

To find stored procedures that “username” can access:


exec sp_helprotect @username = 'username'

To see all objects in database and who has access:


exec sp_helprotect

For complete capabilities see http://msdn.microsoft.com/en-us/library/ms190310.aspx

Tags: 

SQL Server Profiler New Templates Failing

ben himself's picture

Source of info: http://www.mattbutton.com/2011/06/01/sql-profiler-templates-missing/

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.

Tags: 

TSQL – Loop Through Result Set WITHOUT Cursors

ben himself's picture

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:

Tags: 

Pages