Josh Wieder

Yeeesh posts have been MSSQL-heavy. I've just been working on this stuff for quite a bit lately - by no means is this my favorite database. Anyway, you're not here for that. You're here because you need to fix something that's broken and Google says I have it. Here you go: Find Database Table Size Your database is growing and you need to know why. First step would be to find out which table is responsible for the growth - but how? Right click the database in question and run one of the following queries: Displays the size of every table - exec sp_Msforeachtable 'exec sp_spaceused ''?''' Displays a specific table - exec sp_spaceused TableName Disable a Maintenance Plan Let's say you want to temporarily disable a maintenance plan. You are trying to troubleshoot it, but you don't want to delete the entire thing and re-create it. You may have noticed SQL Management Studio's GUI has not straight forward way of doing this. First,...

***This guide is valid for MSSQL 2000, MSSQL 2005, MSSQL 2008 and MSSQL 2012.  Is your database stuck in single user mode? Irritating isn't it? Use the following command to restore it to normal service, where DBNAME is the name of the database: exec sp_dboption 'DBNAME', 'single user', 'FALSE' go

The following in formation should be valid for MSSQL 2005 and 2008. Sysobject creation is best handled using the GUI available at $WINDOWS\Microsoft.NET\Framework\$version_number\aspnet_regsql.exe. Under some circumstances, this may not be possible or preferable. In those circumstances, the following queries can assist you. To view all installed sysobjects for the database, run the following query, replacing DBNAME with the name of the relevant database: use DBNAME go select * from sysobjects where xtype ='P' and name like 'aspnet_%' Sysobject creation scripts are specific to the object. The script below will create aspnet_SchemaVersions: CREATE PROCEDURE [dbo].aspnet_CheckSchemaVersion    @Feature                   nvarchar(128),    @CompatibleSchemaVersion   nvarchar(128) AS BEGIN IF (EXISTS( SELECT  *                FROM dbo.aspnet_SchemaVersion...

When restoring an MSSQL database, it is common to receive an error similar to this one:  " The database could not be exclusively locked to perform the operation ." Msg 5030, Level 16, State 12, Line 1   This error can occur in MSSQL  2005, 2008 or 2012. There are a number of workarounds for this. Some will tell you to disable the website's application pool in IIS, but this doesnt help if someone remotely is connected using SQL Management Studio. You can go into Performance Monitor and manually kill each process, but often the processes jump up faster than you can disable them by hand.  Where that is the case, perform the following procedure. Assuming your restore job task window is properly configured and just open, keep it open. You can right click on the database server and select New Query without closing the restore job task window, which wastes time when you must recreate it. Then, enter the following script into the query menu (be...

You've done everything right with your MSSQL database restore. All of your versions match. All connections to the database have been killed. Your BAK file is valid, you have adequate permissions to both it and the proposed MDF and LDF file paths. And yet, when you attempt to restore using MSSQL 2005 or 2008, the task fails with the following: The file or filegroup X cannot be selected for this operation. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3219) Often the most infuriating problems are the one's with the easiest solution. To fix this, simply perform "Restore Database" instead of "Restore Files".