Spring Cleaning Your Dev Box


After some time your dev box ends up looking like a well-used tool shed complete with unused tools and cobwebs in the corners.

While it’s quick and easy to get rid of half the icons on your desktop and delete droves of temporary working files – I always end up returning to a few well used scripts to get my much needed disk space back as well as a cleaner and faster box.

Often the trigger is exhausted disk space.

These are the scripts I always end up using.

Delete old site collections

If you are making site definitions (or web templates) you’ll likely end up with tons of small test site collections over time. I often use scripts to create them named after the current time.

To clean that all up I use the PS script (run in the SharePoint Administrative Console):

    get-spsite http://* -limit all |? { $_.Url -Match "http://.*/" -and $_.LastContentModifiedDate -gt [DateTime]::Today.AddMonths(-2)} | remove-spsite

In human terms: “Delete every site collection that is not a root site collection and that have not been modified within the last two months”. Phew.

Do NOT run this in production.

Set Simple SQL Recovery Mode

Next I ensure that all my local databases run in simple recovery mode, i.e. avoid huge transactions logs that need to truncated once in a while.

I nicked this script somewhere in google (likely here) (updated Jun 27: Fixed – my angle brackets had been eaten):

USE MASTER
declare
	@isql varchar(2000),
	@dbname varchar(64)
	
	declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
	open c1
	fetch next from c1 into @dbname
	While @@fetch_status <> -1
		begin
		select @isql = 'ALTER DATABASE [@dbname] SET AUTO_CLOSE OFF'
		select @isql = replace(@isql,'@dbname',@dbname)
		print @isql
		exec(@isql)
		select @isql = 'ALTER DATABASE [@dbname] SET RECOVERY SIMPLE'
		select @isql = replace(@isql,'@dbname',@dbname)
		print @isql
		exec(@isql)
		select @isql='USE [@dbname] checkpoint'
		select @isql = replace(@isql,'@dbname',@dbname)
		print @isql
		exec(@isql)
		
		fetch next from c1 into @dbname
		end
	close c1
	deallocate c1

Run it within the SQL Server Management Studio.

Note that it is likely that the script will report a few minor errors if some DBs are detached/offline. Never mind.

Shrink DBs

Finally save some much needed space by shrinking the DB files. After site collection deletions and recovery mode changes there are likely a lot of space to be freed within the DB files.

This script will try to shrink all the DB files (I think I got it from here):

DROP TABLE #CommandQueue

CREATE TABLE #CommandQueue
(
    ID INT IDENTITY ( 1, 1 )
    , SqlStatement VARCHAR(1000)
)

INSERT INTO    #CommandQueue
(
    SqlStatement
)
SELECT
    'USE [' + A.name + '] DBCC SHRINKFILE (N''' + B.name + ''' , 1)'
FROM
    sys.databases A
    INNER JOIN sys.master_files B
    ON A.database_id = B.database_id
WHERE
    A.name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )

DECLARE @id INT

select * from #CommandQueue

SELECT @id = MIN(ID)
FROM #CommandQueue

WHILE @id IS NOT NULL
BEGIN
    DECLARE @sqlStatement VARCHAR(1000)
    
    SELECT
        @sqlStatement = SqlStatement
    FROM
        #CommandQueue
    WHERE
        ID = @id

    PRINT 'Executing ''' + @sqlStatement + '''...'

    EXEC (@sqlStatement)

    DELETE FROM #CommandQueue
    WHERE ID = @id

    SELECT @id = MIN(ID)
    FROM #CommandQueue
END

Again expect some errors, inspect and accept them 😉

Expand the Disk?

If the three steps above didn’t free enough space for you, the solution often is to just expand the VHDs on your virtual machines.

It’s a fairly easy process in both VMWare and HyperV it only requires you to turn off the VM, remove any snapshots, and expand the disk using the wizard for it. This will only expand the VHD; your partitions will not grow, so you need to do that next.

You can use the disk management tool for it – however I find it cumbersome. Especially if it is the system disk you’re expanding it is awkward. I prefer to use GPartED, which is a very nice linux partition editor. It is a downloadable iso that is a breeze to boot into and expand the partition whether it is the system or not.

One note: In a typical Linux way you are asked all sorts of questions at boot; just hit return at every one of them. Who cares about the keyboard layout for a GUI program with big buttons anyway?

It looks something like this:

(Note: Before using this program make sure that you close down the VM nicely from the guest)

Easy.

Advertisements

Fixing Event Error 7888: “User not found”


We’ve been having an annoying recurring event log error every minute for a while on some of our farms. Recently we digged deep into the issue and finally fixed it J

This applies to SharePoint 2007 and SQL Server 2005. It’s likely fixed in other versions.

The error message is “Windows NT user or group ‘domain\user‘ not found” returned from the SQL server when a SSP synchronization timer jobs tries to sync service account access to the databases. It executes “sp_grantlogin ‘domain\user'” even though the account is already granted login in the databases (content and config).

After a lot of digging and code disassembling it turns out that the database stored procedure is case sensitive on the user name and SharePoint provides the user name in whatever casing you originally supplied to SharePoint (when creating app pools). I’m quite surprised by this and not sure if it’s always the case. We are using a SQL Server 2005 with proper case insensitive collation, but still…

Interestingly the domain name is not case sensitive.

The Implications

This error seems to be only an annoyance in the event log and nothing more as the user the job tries to add to the database is actually already there. Nothing is really broken by this.

The Fix

Fortunately it can easily be fixed by an administrator.

Procedure:

  1. Go to Central Administration / Operations / Service Accounts (/_admin/FarmCredentialManagement.aspx)
  2. Choose “Web Application Pool” and “Windows SharePoint Services Web Application”

  3. Go through every application pool and verify that the user name on the page is using the correct casing
    1. What is the correct casing? Lookup the user in your AD and use the value for sAMAccountName
  4. Click Ok

That’s it!

For good measure I restarted the timer services on every SharePoint server in the farm.

The Symptoms

You get the following lines in the ULS log:

12/08/2010 07:15:31.75     OWSTIMER.EXE (0x01D0)                       0x1FB8    Office Server                     Office Server General             6pqn    High        Granting user 'domain\user' login access to server 'cbdks173\mosstest,1433'.     
12/08/2010 07:15:31.77     OWSTIMER.EXE (0x01D0)                       0x1FB8                                      484                               880i    High        System.Data.SqlClient.SqlException: Windows NT user or group 'domain\user' not found. Check the name again.     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)     at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)     at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)     at Syste...     
12/08/2010 07:15:31.77*    OWSTIMER.EXE (0x01D0)                       0x1FB8                                      484                               880i    High        ...m.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)     at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)     at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()     at Microsoft.Office.Server.Data.SqlSession.ExecuteNonQuery(SqlCommand command)     
12/08/2010 07:15:31.77     OWSTIMER.EXE (0x01D0)                       0x1FB8                                      484                               880j    High        SqlError: 'Windows NT user or group 'domain\user' not found. Check the name again.'    Source: '.Net SqlClient Data Provider' Number: 15401 State: 1 Class: 11 Procedure: 'sp_grantlogin' LineNumber: 49 Server: 'cbdks173\mosstest,1433'     
12/08/2010 07:15:31.77     OWSTIMER.EXE (0x01D0)                       0x1FB8                                      484                               880k    High           at Microsoft.Office.Server.Data.SqlServerManager.GrantLogin(String user)     at Microsoft.Office.Server.Administration.SharedResourceProvider.SynchronizeConfigurationDatabaseAccess(SharedComponentSecurity security)     at Microsoft.Office.Server.Administration.SharedResourceProvider.SynchronizeAccessControl(SharedComponentSecurity sharedApplicationSecurity)     at Microsoft.Office.Server.Administration.SharedResourceProvider.Microsoft.Office.Server.Administration.ISharedComponent.Synchronize()     at Microsoft.Office.Server.Administration.SharedResourceProviderJob.Execute(Guid targetInstanceId)     at Microsoft.SharePoint.Administration.SPTimerJobInvoke.Invoke(TimerJobExecuteData& data, Int32& result)       
12/08/2010 07:15:31.77     OWSTIMER.EXE (0x01D0)                       0x1FB8                                      484                               880l    High        ConnectionString: 'Data Source=cbdks173\mosstest,1433;Initial Catalog=master;Integrated Security=True;Enlist=False;Pooling=False'    ConnectionState: Open ConnectionTimeout: 15     
12/08/2010 07:15:31.77     OWSTIMER.EXE (0x01D0)                       0x1FB8                                      484                               880m    High        SqlCommand: 'sp_grantlogin'     CommandType: StoredProcedure CommandTimeout: 0     Parameter: '@loginame' Type: NVarChar Size: 128 Direction: Input Value: 'domain\user'
   

Combined with this application event log error every minute:

Log Name:      Application
Source:        Office SharePoint Server
Date:          08-12-2010 08:32:31
Event ID:      7888
Task Category: (1516)
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      <computer>
Description:
The description for Event ID 7888 from source Office SharePoint Server cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.
If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event: 

Windows NT user or group 'domain\user' not found. Check the name again.
System.Data.SqlClient.SqlException: Windows NT user or group 'domain\user' not found. Check the name again.
 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
 at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
 at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
 at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
 at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
 at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
 at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
 at Microsoft.Office.Server.Data.SqlSession.ExecuteNonQuery(SqlCommand command)
 at Microsoft.Office.Server.Data.SqlServerManager.GrantLogin(String user)
 at Microsoft.Office.Server.Administration.SharedResourceProvider.SynchronizeConfigurationDatabaseAccess(SharedComponentSecurity security)
 at Microsoft.Office.Server.Administration.SharedResourceProvider.SynchronizeAccessControl(SharedComponentSecurity sharedApplicationSecurity)
 at Microsoft.Office.Server.Administration.SharedResourceProvider.Microsoft.Office.Server.Administration.ISharedComponent.Synchronize()

Convert “virtual” content types to “physical”


What do you do, if you in a fit of madness/desperation/stupidity created the content types used throughout your site, through the web interface and you now want to do the “right” thing and place them in xml files packaged as a feature?

Well this is description on how to convert the existing “virtual” content type to that xml file, while maintaining the integrity of your existing site and content. Warning: I’m modifying the SharePoint content database directly – use at your own risk!

The basic idea:

  1. Create a content type xml file and package it in a feature (don’t deploy it yet) as you would if you started in a blank environment
  2. “Steal or copy” the content id for “virtual” content type from the database and use it in your xml files. In other words the existing content id that is used throughout your existing SharePoint database in the inheritance hierarchy, will remain unchanged
  3. Modify the database so that SharePoint sees your content type as being feature based instead of “database based”
  4. Deploy your new content type feature. You can now update that content type as if you had started it out xml based to begin with

It seems fairly straightforward doesn’t it? It actually is.

Howto

Information on creating xml based content type can be found here (and on many other sources), it’s really not that hard. Your deployment will be much easier after this.

Right about now would be a good time to do a backup of your content database 😉

Step 1: Steal the Content Type ID

Your content type will need a very specific ID that the SharePoint created for you when you created your new content type in the first place (either through the web frontend or API). It looks like “0x0101……” and will probably be a rather long string. You need to grab this id from the content database:

  1. Connect to the content database in question, probably named wss_content_XXXX (if you didn’t choose a database name the XXXX will be a guid)
  2. Execute the following query to find the right content typeselect ResourceDir, ContentTypeId, Definition

    from dbo.ContentTypes

    where ResourceDir like ‘%Article Page%’

    Obviously substitute your own content type name, note that the web interface might have appended some trailing numbers to the name, so you’ll have to do a “like” selection

  3. Copy the ContentTypeID and insert it into the xml file. You might also want to verifiy that the definition corresponds to your that in your xml file (or just copy it over)

Step 2: Connect the Content Type to the XML File

Now you need to go into the database and modify the ContentType table to make SharePoint see it as a feature based content type as opposed to those solely in the database.

  1. Connect to the content database again (you might just have kept the window open)
  2. Execute begin tran once, just to give you an undo option
  3. Execute the following SQL statementUpdate dbo.ContentTypes

    Set Size = 16, Definition = null, IsFromFeature = true

    where ContentTypeId = 0x010100C5…..

    It should only modify one row

  4. If the name “ResourceDir” has been mangled by the web interface, you might want to take the opportunity to fix that too now
  5. If you are satisfied with the update execute commit tran, otherwise rollback tran, do not forget this as you are locking the table for the duration (btw: Isn’t that a neat trick?)

Caveats

I will not take any responsibility if you lose your databases, however I would like to know if you find flaws with the procedure 😉

If you have many environments this technique only works if they have the same content type id for the same type across the farms. They will have if you did a backup/restore or content deployment from one to the other. They won’t if you created them through the web on both servers. Then you either choose which one is the master of the content or you are out of luck.

Note that if you update/change the content type xml files at a later time, the changes will only apply to the site scoped content type, not the actual list content types that the system created for every list where the type is enabled. This is very bad news, but not to worry I’ll post the fix for that in a few days (give me a bit of time).

If you modify the content type through the web interface after deployment it will once again be disconnected from the xml source, and you’ll have to complete Step 2 (only) to reconnect it.

Find the needle in the SQL database


[AKA: SQL Grep]

[Updated Nov 11 2008]

[Updated March 10 2009]

A few years ago I was debugging an annoying SharePoint 2003 issue with some wrong usernames after renaming them. It turned out that I had to dig deep into the SQL database tables to figure out how everything worked (nobody cares about the actual result anymore).

To do that I had to write a piece of SQL scripting that could search the database like: “search for a string in any column in any record in any table in the current database”. The script was actually quite useful (and hard to write) so I’ve kept it for a few years.

The other day I had another strange problem with the new SharePoint server and had to search the database for a specific guid. I dusted off my old script and it turned out to be very useful again. I updated it slightly and figure that it might be useful for you too.

So here it is.

There are really two parameters that you need to provide. “@searchText” is the search string and “@datatype” is the datatype to search (i.e. what types of columns that should be searched).

“@datatypes” values (select system_type_id, name from sys.types order by name):

Datatype value

SQL DataType

null

All types of strings*

-1

All types of numbers*

127

Bigint

173

Binary

104

Bit

175

Char

61

Datetime

106

Decimal

62

Float

34

Image

56

Int

60

Money

239

Nchar

99

Ntext

108

Numeric

231

Nvarchar

59

Real

58

smalldatetime

52

Smallint

122

Smallmoney

98

sql_variant

231

Sysname

35

Text

189

Timestamp

48

Tinyint

36

uniqueidentifier

165

Varbinary

167

Varchar

241

Xml

* I added special support for the first two options

Remarks:

  • The script is not actually a function or a stored procedure because I usually use it by copying the SQL into a SQL query window and execute. I don’t want to create a new function/stored procedure in the databases that I’m peeking into.
  • You can only search datatypes that are convertible to a “nvarchar” column (and only the first 4096 characters are searched), i.e. pretty much everything barring “image” and “timestamp” (see table on http://msdn2.microsoft.com/en-us/library/ms187928.aspx)
  • Every value that is searched is converted to a “nvarchar” column by using default options, i.e. dates and thousand separators will follow your database settings
  • The reason I convert everything is that if you search for a number you probably want to search all number like columns, not just “short” or “long” etc., likewise you’ll want to search every type of text column, not just the one of “char”, “varchar”, “nvarchar”, “text” etc.
  • Is it slow? Absolutely. I wanted to find the string I was searching for, not a query to be part of any production systems
  • Add “%” to search for wildcards (i.e. before and after your string)
  • No it does not search every database only the current one. It’s easy/possible to add a search through all databases, but I didn’t really need it
  • Output is a list of found values with table name, column name and the actual value found (might be interesting if you used wildcards)

Finally! The actual script (copy/paste + modify params):

declare @searchText varchar(255)
set @searchText = '%mystring%'

declare @datatype int
set @datatype = null

/*Set type of coloumn to search

null All types of strings*
-1 All types of numbers*
127 Bigint
173 binary
104 Bit
175 Char
61 datetime
106 decimal
62 Float
34 image
56 Int
60 money
239 nchar
99 ntext
108 numeric
231 nvarchar
59 Real
58 smalldatetime
52 smallint
122 smallmoney
98 sql_variant
231 sysname
35 text
189 timestamp
48 tinyint
36 uniqueidentifier
165 varbinary
167 varchar
241 xml
*/

declare @innerSQL varchar(4000)

DECLARE tables CURSOR READ_ONLY
FOR (
	select so.name as TableName, sc.name as ColName, ss.name as sch
	from syscolumns sc left join sysobjects so
	on sc.id = so.id
	left join sys.all_objects ao
	on so.id = ao.object_id
	left join sys.schemas ss
	on ao.schema_id = ss.schema_id
	where OBJECTPROPERTY(so.id, N'IsUserTable') = 1
		and (sc.xtype = @datatype
		or (@datatype is null and collation is not null)
		or (@datatype = -1 and sc.xprec>0))) -- to include text only

create table ##found ( tablename varchar(255), colname varchar(255), val nvarchar(4000) )

DECLARE @tablename varchar(255)
declare @colname varchar(255)
declare @sch varchar(255)

OPEN tables

FETCH NEXT FROM tables INTO @tablename, @colname, @sch

WHILE (not @@fetch_status = -1)
BEGIN
	IF (not @@fetch_status = -2)
	BEGIN
		--print 'table: ' + @sch + '.' + @tablename + ', column: '+ @colname
		set @innerSql = 'insert into ##found (tablename, colname, val) select '''
		+ @sch + '.' + @tablename + ''', ''' + @colname + ''', Convert(nvarchar(4000),['
		+ @colname + ']) from ' + @sch + '.[' +@tablename + '] where Convert(nvarchar(4000),['
		+ @colname + ']) like ''' + @searchText + ''' '

		exec( @innerSql )

	END
	FETCH NEXT FROM tables INTO @tablename, @colname, @sch
END

CLOSE tables

DEALLOCATE tables

--Output result
select *
from ##found
drop table ##found

I hope some of you will find it useful, if not at least I have a place to find it the next time I need it 😉

Enjoy.

Download

[Added March 10 2009]

For your convenience I’ve added download links

Sql Grep (2005 version)

Sql Grep (2000 version) – Thanks Sootie!