One common question I hear whenever something begins to go wrong is, “Has anything been changed recently?” Finding recent changes can be a good place to search, depending on the issue, so here is a script to do just that. /* – Relatively light weight – runs in a couple seconds. – Does not modify… Read More »
Although moving database files around is simple, it is a still big deal. When you do this, you are moving the entire database contents to another location. If you have the space, a backup beforehand wouldn’t be a bad idea. This is the script that I use to make this happen. /* –PROCESS FOR MOVING… Read More »
If you need to compare two users in SQL Server, here is a handy script. The output is simply a script to make the “UserToChange” sync up with the “ModelUser”. So this script generates a script.
The T-SQL method to find the active node that a SQL service is running on: SELECT SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as ActiveNode
For those of us not lucky enough to live in Arizona (where daylight savings is ignored and clocks stay the same all year-round), we need to consider what time our SQL jobs are run. What happens with jobs that are scheduled for 2:30am when we spring the clocks ahead? They are skipped! What happens to… Read More »
Validate SQL Code Without Running It In a nutshell: SET NOEXEC ON; — The script now will throw all DML errors without executing the code being run.
I almost found a bug… but it’s a feature after all. If you are scripting out a Partition Scheme in SSMS, you might notice that your script defaults to the PRIMARY filegroup. For the reason why, go here: http://connect.microsoft.com/SQLServer/feedback/details/641893/management-studio-incorrect-scripting-of-parition-scheme. To fix it, go into SSMS Tools, Options, SQL Server Object Explorer, Scripting, and under the… Read More »
AdventureWorks sample databases seem to be so hard to find when you want them. Here’s a link. http://AdventureWorksDW Databases – 2012, 2008R2 and 2008
/* Author: Ben Farnsworth Date: 1/7/14 Get info on Data files */ IF OBJECT_ID(‘tempdb..#space’) IS NOT NULL DROP TABLE #space create table #space( name varchar(100) ,filename varchar(200) ,filesizeMB decimal(12,2) ,spaceusedMB decimal(12,2) ,freespaceMB decimal(12,2) ) exec sp_msforeachdb @command1 = ‘ use [?] INSERT INTO #space select name , filename , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB , convert(decimal(12,2),round(fileproperty(a.name,”SpaceUsed”)/128.000,2)) as… Read More »
An issue arose where a database was giving the error: Could not allocate space for object ‘object-name’ in database ‘dbname’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. The… Read More »