My Fried Tech! - Sql

Moving System Databases in sql 2008

papabear — Fri, 25 Jun 2010 14:52:37 GMT

I stumbled across this - http://deepakrangarajan.blogspot.com/2008/11/moving-system-databases-in-sql-server.html.

Sql 2008 'truncate_only' is not a recognized BACKUP option.

papabear — Thu, 24 Jun 2010 13:38:55 GMT

If you have ever had to truncate a log in Sql 2005, the 'with truncate_only' is fairly familiar. Unfortunately for you, if you have it in your scripts, it has been removed in Sql 2008.

You can use the Sql Management Studio to shrink the log files, I can post later about how to do this, but for some databases it will appear not to work (even if it throws no error). The log files stay the same size.

What may be the issue is that the Database is using a Full vs a Simple backup recovery method. You can find out more about the difference between the two here -> http://msdn.microsoft.com/en-us/library/ms187048.aspx. What we are concerned about is the note show below:

Note
Under the simple recovery model, the transaction log is automatically truncated to remove any inactive virtual log files. Truncation usually occurs after each checkpoint but can be delayed under some conditions. For more information, see Transaction Log Truncation.

What this means in the short run is that by switching your backup recovery method to Simple, you will instantly be able to shrink your log file.

Ex: Alter Database %your Db Name Here% Set Recovery Simple

I would be remiss if I did not mention this note from the page on the Simple recovery method:

Important
The simple recovery model is inappropriate for production systems for which loss of recent changes is unacceptable. In these cases, we recommend that you use the full recovery model. For more information, see Backup Under the Full Recovery Model.

One could always switch it to Simple, truncate, and switch back to Full, but it would probably be better to keep it at Full and establish a more functional means to keep those pesky log files small...

Sql 2008 and 'Agent XPs' component is turned off as part of the security configuration of this server when creating a Maintenance Plan

papabear — Tue, 09 Feb 2010 19:55:42 GMT

If you try to create a Maintenance Plan on Sql 2008 you may get the following error:
'Agent XPs' component is turned off as part of the security configuration of this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure....

The reason is simple - the Sql Server Agent Service, which handles Maintenance plans among other things, is not started automatically. The service is set to Manual start. The solution is simple - just set it to Automatic (so it starts in case of a reboot) and start it up!

You can, of course, do this through windows services, but you can also do it through the Sql tools - specifically the "Sql Server Configuration Manager".

Step 1 - open up the Sql Server Configuration Manager and click on 'Sql Server Services in the left pane. You will see that the SQL Server Agetn is Stopped and has its 'Start Mode' set to Manual.

Step 2 - double click on the Sql Server Agent in the right tab and its properties will appear. Click on the 'Start' button to begin it running.

Step 3 - now click on the 'Service' tab and change the Start Mode to Automatic. This will ensure that if your server restarts this service will start as well (and hence you will get your Maintenance plans.

Now, give yourself a raise and take the rest of the day off....

Sql 2008 Disk Allocation Unit Size

papabear — Tue, 10 Nov 2009 17:22:50 GMT

Installing a new sql 2008 box and thought I would throw out the reminder to partition the drives that sql uses to 64k (not the default 4k) due to Sql's 'extents', which come in packages of eight 8k boxes...

Cannot open database requested in login 'SharedServicesDatabase'. Login fails. Login failed for user MYDOMAIN\MyServer$

papabear — Tue, 02 Jun 2009 17:45:15 GMT

You may get an error like:

Cannot open database requested in login 'SharedServicesDatabase'. Login fails.
Login failed for user ' MYDOMAIN\MyServer$'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'SharedServicesDatabase'. Login fails.
Login failed for user ' MYDOMAIN\MyServer$'.

This is triggered by the odd fact (which I can not find documented anywhere) that the server that is housing the Sharepoint site must have its computer account listed as a user within the Shared Services database for the search to function.

Sharepoint Backup Permissions or SqlException: Cannot open backup device '\\myserver\backups\spbr0000\00000024.bak'. Device error or device off-line

papabear — Mon, 18 May 2009 16:14:45 GMT

For those of you who are using Sharepoint's 'built-in' backup feature, you probably already know that one of the biggest problems with it is that you can not automate it directly. In order to do that you need to used AT ->

AT 01:00 /every:1,2,3,4,5,6,7 cmd /c "stsadm.exe -o backup -directory file://myserver/mybackupshare -backupmethod full"

This is not a showstopper. It works. However unless you have the correct perms it will fail. Or, to be more specific, the Sql Service Account needs to be able to read/write to that share. Again, this is the account that the Sql Service runs under. Hopefully a domain account.

Also, if you are going to run it from the Sharepoint Central Administration Website, the account you are logged in with needs those perms as well.