Yesterday I started looking at the tables in my SharePoint 2010's Content databases for the first time and I noticed something a little unnerving: There were files that were checked out and the web front end was not displaying them in any way.

More specifically I ran this query against one of my CDBs:

USE WSS_Content_IT
    dbo.AllDocs AS AD FULL JOIN 
    dbo.UserInfo AS UI ON 
        UI.tp_ID = AD.CheckoutUserId 
    UI.tp_Login IS NOT NULL AND
    AD.DirName IS NOT NULL

...and it returned just over a thousand rows. About a dozen of them were files I expected to be checked out, but the rest all belonged in the same folder, one I remember populating weeks ago. When I used the web interface to find the offending folder it appeared to not exist. Even when I was logged in with the account listed under tp_Login the folder simply wasn't there to be browsed. These files are acting specifically like they've never had a version checked-in and the "Manage files which have no checked in version" link under Document Library Settings doesn't display these files either.

So I ran the same query against my other CDBs and discovered that this has happened to all of them. Clearly this is a problem.

So since I can't check these documents in by using the web interface, and they don't show up in SharePoint Designer either, how can I use a SQL query to force a check-in of these files?

3 Answers 3



Even reading from the content databases is not considered good practice, you may cause locks which will cause SharePoint to fail.

The moment you change anything in a SharePoint database (except the usage ones) your SharePoint installation becomes unsupported.

Loop through the pages/files using PowerShell and check them in, but don't use SQL.

Read: Support for changes to the databases that are used by Office server products and by Windows SharePoint Services and yes it applies to 2010.

An example of checking in a file using Powershell could be:

$web = Get-SPWeb http://sharepoint
$file = $web.GetFile("Shared Documents/MyDoc.docx")
$file.CheckIn("Checked in using PowerShell", [Microsoft.SharePoint.SPCheckinType]::MajorCheckIn)
  • PowerShell sounds promising. I didn't realize you could use PS to force item check-ins. Do you have any recommendations for good resources about the best way to do that?
    – newuser
    Commented Apr 10, 2012 at 18:34
  • I'm having some trouble finding good resources for PowerShell online. Ideally I'd like to programatically identify files that are checked-out to a specific user and only check-in those files, and I'd like my function to check an entire document library in one go, if possible. Can you give me any pointers to helpful resources for PS methods?
    – newuser
    Commented Apr 11, 2012 at 12:40
  • 1
    Here is an example of finding all checked out files Commented Apr 11, 2012 at 13:08
  • 1
    Has AD.DeleteTransactionId any value for these? If so then they're deleted Commented Apr 12, 2012 at 18:55
  • 1
    0x isn't deleted, but those that have something after has been deleted and are now in one of the recycle bins Commented Apr 12, 2012 at 19:04

EDIT: Didn't see Per's edit in time!

I am going to post this as an answer, but you really should select Per Jackobson's as the accepted answer to the question. Here is how you can check in documents in library using PoSh. You can also used the PowerShell shell window to examine these files a little more closely. Test the code as I am posting from memory...

$web = Get-SPWeb "http://domain.com"
$list = $web.Lists["Library Name"]

foreach($item in $list.Items)
    if($item.File.CheckedOutType -ne "None")
        $item.File.CheckInComments = "Automated checkin."

If it is only one folder/library on one site. Open the offending library, Go to library settings click manage files with no checked in version. Take ownership and check them in.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.