This script is intended for environments where space constraints require the space to be closely maintained. This is not a good script to run as part of a normal production maintenance plan, unless you adjust the variables to high enough levels that it only catches extreme cases. Shrinking a database takes a lot of time… Read More »
Today I ran a script to delete a huge portion of data in a DEV environment and I was excited to get back a few hundred GB, but I only got back a portion of the free space that the Properties window said I had. What was going on? It turns out that when shrinking… Read More »
This returns a result for each database and includes its size in MB. SELECT DATABASE_NAME DBNAME ,(COMPRESSED_BACKUP_SIZE / (1024*1024) ) [COMPRESSED BACKUP IN MB] FROM MSDB..BACKUPSET BUS WHERE BUS.BACKUP_SET_ID IN ( — GET THE NEWEST BACKUP SELECT MAX(BACKUP_SET_ID)ASPState FROM MSDB..BACKUPSET WHERE TYPE = ‘D’ — FULL BACKUP ONLY GROUP BY DATABASE_NAME ) ORDER BY DBNAME
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
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… Read More »
Finding Orphaned Users USE [db] GO EXEC sp_change_users_login ‘Report’ GO Fixing Orphaned Users USE [db] GO EXEC sp_change_users_login ‘auto_fix’, ‘USERNAME’ GO
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… Read More »
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
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
Within a Stored Procedure, you may want the results of a particular query emailed to you, but only if there is a result. In other words, I don’t want to be notified when the result set size is 0. When you put a query into the “@query” of the stored procedure, the email will be… Read More »