SharePoint 2010: SQL Server 2008R2 SA Recovery and Autoshrink - TechNet Articles - United States (English) - TechNet Wiki

This caped hero came to simplify (y)our world one database at a time. His queries will dazzle you. His keystrokes will simplify. With SQLMan there are no transactions logs.
SQLMan fears only one thing, GokMan. If you ask what’s his secret weapon………………………celery and kidney beans.
Source to them for the great image: http://danmagyar.blogspot.be/2008/02/sql-man.html

The last week I began with SQLMan a new project for a Multinational society in my country. I had to terminate the job from the former SharePoint Administrator. I received 1500 pages of documentation for read, but all went to the “Recycle Bin” :)
On Command Prompt, I wrote MSTSC and began my analysis. First thing that I remarked was that the SQL Server wasn’t in mixed mode and there wasn’t a SA user/Password. So the first question was:

“How to recover SA password ”

1. Start SQL Server Configuration Manager

1. Stop the SQL services

1. Edit the properties of the SQL Service

1. Change the startup parameters of the SQL service by adding a –m; in front of the existing parameters

1. Start the SQL services. These are now running in Single User Mode.
2. Start CMD on the SQL server
3. Start the SQLCMD command. Now you will see following screen

1. Now we create a new user. Enter following commands

1. Now this user is created. Now we grant the user SYSADMIN roles using the same SQLCMD window.

1. Stop the SQL service again
2. Change the SQL service properties back to the default settings

1. Start the SQL service again and use the new created login (recovery in my example)
2. Go via the security panel to the properties and change the password of the SA account.

1. Now write down the new SA password.

But this was not the only way to do it, here some other possibilities all chooses randomly on Google or BING.

To reset the SA password, you can make the following:

• Open the SQL Server express management studio
• Connect to SQL Server using windows authentication
• Right click the server name and choose properties
• Go to security tab. Change server authentication to “SQL Server and Windows Authentication mode”
• Click OK and restart SQL Server
• Go to SQL Server studio management express
• Expand the server and choose security and expand logins
• Right click on SA, from properties modify the password and confirm password

OR To reset the SA password, you can make the following:

• Login to the SQL Server box as the Administrator.
• Run SQL Server Enterprise Manager.
• Right-click the server name and choose ‘Edit SQL Server Registration properties’.
• Choose ‘Use Windows authentication’ and click OK button.
• Expand a server, expand a Security and click Logins.
• Double-click the sa login and specify new password on the General tab. (enable Login to)

OR From a Query

• USE [master]
• GO
• ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master],
• DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
• GOUSE [master]
• GO
• ALTER LOGIN [sa] WITH PASSWORD=N’<insert_new_password_here>’ MUST_CHANGE
• GO

OR From a command prompt

• OSQL -S <insert_servername_here> -E
• 1> EXEC sp_password NULL, ‘<insert_new_password_here>’, ‘sa’
• 2> GO

Finally I could connect with my SQL Account on my SQL Server. I was thinking that everything was OK, but another stranger error was shown.

• Problem: The transaction log for database ‘Search Database’ is full" when tried to save the updated settings on SharePoint.
• Finding: Found that the drive that containing the Crawl database log is having very little space remaining.
• Why: Because there was not a backup (SQL Agent, PS1, … ) on the server with the option Autogrowth = “ON”. Who makes the LOG file larger and larger.
• Resolution: Following steps will help you to resolve this issue

From the SQL UI

1. Do full backups of all the SharePoint databases.
2. Open the database, right click SharePoint Crawl database > Properties
3. Select Simple under Recovery model list > Click OK
4. Right click the database, Tasks > Shrink > Files
5. In File type select Log
6. Click OK

From a Query

• Run SQL Management Studio and login to your SharePoint instance.
• Click on ‘New Query’
• Type in the following commands:
• USE [database name];
  BACKUP LOG [database name] WITH TRUNCATE_ONLY;
  DBCC
  SHRINKFILE ([log file name], 1);
• Replace [database name] and [log file name] with your log file.
• This will truncate your log file to 1mb if there was a backup before

When planning the capacity for your SharePoint 2010 Farm, we don’t have to forget about the size of our Search databases. SharePoint 2010 Search leverages three distinct databases, with very separate roles as follows:

• Search Administration Database
• Crawl Database
• Property Database

Per TechNet, the recommendations are as follows:

But what when you want to Shrink all your databases? Ikarstein has the solution for us: Here is his script:
http://ikarstein.wordpress.com/2011/08/28/truncate-shrink-log-files-of-databases-in-sql-server-2008-r2/

So why using this script and not the option “Auto-Shrink” from SQL Server?

Here are the words of Paul Randal – MSFT

In my opinion, this feature causes way more problems than it solves (in fact, I can't think of a single problem it solves) and should be removed from the product. Remember I'm talking about auto-shrink, not manual shrink. Post a comment or drop me a line if you think there's a scenario where it's required.
So why?

1. The algorithms that shrink use are basically brute force. It starts at the end of the data file, picks up allocated pages and moves them as near to the front of the data file as it can, fixing up all the links as it goes. This is fine as long as there are no indexes involved - if there are, every time shrink moves a non-clustered index leaf-page, or a clustered index data-page, it's causing fragmentation. Yes, any shrink (apart from a TRUNCATEONLY) can cause fragmentation - this is not widely known, although every opportunity I get to explain this I do. I updated the Books Online for DBCC SHRINKDATABASE in SQL Server 2005 to call this out as a reason not to run shrink in general. So, although this affects all shrink operations, its still a very good reason not to run shrink automatically.
2. You can't control when it kicks in. Although it doesn't have any effect like long-term blocking, it does take up a lot of resources, both IO and CPU. It also moves a lot of data through the buffer pool and so can cause hot pages to be pushed out to disk, slowing things down further. If the server is already pushing the limits of the IO subsystem, running shrink may push it over, causing long disk queue lengths and possibly IO timeouts.
3. You can't control when it kicks in - it will start up every 30 minutes and try to shrink one of the databases that has AUTO_SHRINK turned on.
4. You're likely to get into a death-spiral of auto-grow then auto-shrink then auto-grow then auto-shrink... (in my experience, if someone is using auto-shrink, they're most likely using and relying on auto-grow too). An active database usually requires free space for normal operations - so if you take that free space away then the database just has to grow again. This is bad for several reasons:
   • Repeatedly shrinking and growing the data files will cause file-system level fragmentation, which can slow down performance
   • It wastes a huge amount of resources, basically running the shrink algorithm for no reason
   • Auto-grow itself can be bad, especially if you're using SQL Server 2000 (or don't have Instant File Initialization turned on - see this recent post from Kimberly's blog) where all allocations to the file being grown are blocked while the new portion of the file is being zero-initialized.

So - if that hasn't convinced you to make sure its turned off, I don't know what will. For databases created on SQL Server 2005, the AUTO_SHRINK option is off by default. You can use ALTER DATABASE yourdb SET AUTO_SHRINK OFF for all others, especially the MODEL database, so new databases don't inherit the setting unwittingly.