By R Teachout on 6/24/2010 2:58 PM
NOTE: THIS THIS SHOULD ONLY BE USED AS A LAST RESORT TO FREE SPACE IN AN EMERGENCY (such as when you have a server drive with 1Mb free, and have to free space to prevent the databases from causing errors) USE AT YOUR OWN RISK exec sp_MSforEachDb 'print "[?]"; USE [?];ALTER DATABASE [?] SET RECOVERY SIMPLE WITH NO_WAIT;'
exec sp_MSforEachDb 'print "[?]"; USE [?]; DBCC SHRINKFILE ("?_log" , 1);'
exec sp_MSforEachDb 'print "[?]"; USE [?];ALTER DATABASE [?] SET RECOVERY FULL WITH NO_WAIT;'
|
By R Teachout on 2/26/2010 5:42 PM
This was a booger, as the install kept bombing, even thought it said I needed latest SP. This was the FIX: http://support.microsoft.com/kb/969985 Took me a few to figure that out, and find this KB article ! Install piece of cake after that.
|
By R Teachout on 2/26/2010 5:25 PM
Archived from http://sqlserverbuilds.blogspot.com/ What version of SQL Server do I have?
This unofficial build chart lists all of the known KB articles, hotfixes and other builds of MS SQL Server 2008, 2005, 2000 and 7.0 that have been released.
Useful articles:
321185 How to identify your SQL Server version and edition913089 How to obtain the latest service pack for SQL Server 2005Microsoft SQL Server HomeMicrosoft SQL Server Developer CenterMicrosoft TechNet: Microsoft SQL Server TechCenterMicrosoft Knowledge BaseSqlservr.exe versions... Read More » |
By R Teachout on 2/25/2010 1:08 PM
These errors for me were occurring when trying to change the “log on as” user for SQL Server via SQL Server Configuration Manager
 WMI Provider Error [call to WMI Provider returned error code 0x8007481d]
To work around this problem, add the user account to the SQLServerMSSQLUser$ComputerName$InstanceName group. To do this, follow these steps:
Click Start, point to Administrative Tools, and then click Active Directory Users and Computers. In the Active Directory Users and Computers snap-in, click Users. (or in the local groups in computer manager) ... Read More » |
By R Teachout on 2/9/2010 2:24 PM
READ COMPLETELY BEFORE YOU DO ANYTHING
Here was the error I received when running a maintenance plan
Request to run job XYZ (>) refused because the job has been suspended.
I checked in the ~/MSSQL/Logs/SQLAGENT.OUT And I get: Subsystem 'SSIS' could not be loaded (reason: Access is denied) AHA, now to troubleshoot that
I opened up procmon, and started a watch excluding Operations containing Reg, and only including path containing my MSSQL folders…Set autoscroll, and restarted SQL Agent… I found: ~ MSSQL\MSSQL10.MSSQLSERVER\MSSQL\Binn\SQLDTSSS.DLL QueryOpen SUCCESS Odd, that meant it’s not an NT permission error (to me) But I still got “Subsystem 'SSIS' could not be loaded (reason: Access is denied)” in the SQLAGENT.LOG folder humm… Reading more in the procmon output.. I find a “FILE LOCKED WITH ONLY READERS” on MSSQL\MSSQL10.MSSQLSERVER\MSSQL\Binn\SQLDTSSS.DLL Maybe this is it??? Google finds nothing (Great) and to humor myself, neither does... Read More » |
By R Teachout on 1/16/2010 2:44 AM
I was having problems purging my data using exec sp_delete_backuphistory @oldest_date='1/1/2010' in my msdb database, so I instead ran the following code to purge the data successfully Read More » |
By R Teachout on 12/19/2009 2:48 PM
Just run on source, then run the output on destination. Voila! Read More » |
By R Teachout on 7/6/2009 4:09 PM
Is This Possible? NO, but you CAN ACCOMPLISH THE GOAL YOU WANT ANYWAY...Sorta, but it works !!!! Getting queries to use multiple clustered indexes. Read More » |
By R Teachout on 7/3/2008 1:50 AM
Just a quick way to find the number of rows in all of your tables. Read More » |
By R Teachout on 7/3/2008 1:43 AM
Recommended steps to migrate a database from SQL 2000 to SQL 2005 Read More » |
By R Teachout on 7/3/2008 1:30 AM
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,GETDATE())),DATEADD(m,1,GETDATE()))) Read More » |
By R Teachout on 7/3/2008 1:23 AM
Ever wonder how to put a mssql table into RAM after it has been read? Read More » |
By R Teachout on 7/2/2008 3:04 AM
For years I have used a script that I had to manually add the ADO user to, and then run it on a database, take the results, and then execute them. I finally got fed up with this, so I wrote a script I could use to automatically just run after I added a Stored Procedure, Function or User to a database, and have it just fix everything.
SLICK! Read More » |
By R Teachout on 6/27/2008 10:06 PM
How to Find Orphaned Users on a database
use database; exec sp_change_users_login 'Report'Read More » |
By R Teachout on 6/27/2008 9:59 PM
Ever need to immediately disconnect users on a database, such as when running a Database Restore? Here's a quick trick. Read More » |
By R Teachout on 6/27/2008 7:14 PM
Need to transfer your users from SQL2000 to SQL2005? Here's howRead More » |
By R Teachout on 6/27/2008 5:56 PM
Useful with a combination of some other scripts I have in blog entries. I found this online, so I can't take credit for this, but I swear I at least had started the same type of script before I googled..Read More » |
By R Teachout on 6/27/2008 4:54 PM
Useful when transferring all databases from one SQL server to another.Read More » |
By R Teachout on 6/27/2008 4:46 PM
Useful when transferring all databases from one SQL server to another.Read More » |
By R Teachout on 6/24/2008 9:04 PM
Have you ever faced the problem of assigning more than 8000 characters to varchar data type or more than 4000 characters to nvarchar data type?Read More » |
By R Teachout on 6/24/2008 8:01 PM
This is actually a simple method to do it, but it is extremely effective.Read More » |
By R Teachout on 6/24/2008 7:47 PM
So, no, you do not know the 'sa' password, but…you do know that the 'sa' password will only be needed for an upgrade or something, not for ongoing connectivity, so you proffer a solution, "I will change the 'sa' password for you for long enough to complete the upgrade, and then I will need to change it back again. This is because I do not know what processes may be trying to use 'sa'".Read More » |
By R Teachout on 6/24/2008 7:46 PM
Ever have a need to disable your triggers to do some maintenance or update work?
Here's a way!Read More » |
By R Teachout on 4/14/2008 1:11 PM
MSSQL Scripts to do this.. (Set Identity Insert)Read More » |
By R Teachout on 4/3/2008 3:52 PM
Just run this query, which will build a list of commands you can put into a batch file, and execute!Read More » |