Monday, March 25, 2013

How to Alter a SQL Server Database as Single User Mode and as Multi User Mode

To alter the database state to restrict the access to the single user mode, first open the Microsoft SQL Server Management Studio, and connect to a SQL Server instance. Open the list of available databases belonging to the related instance in the Object Explorer window. Right click on the SQL Server database that you want to set its mode to single user and select Properties in the context menu.
When you click properties menu item, the following Database Properties screen will be displayed for the selected database. I used database named Works for samples used for the article.
SQL Server restrict access
Select the Options page from the list in the left side of the screen. If you scroll down the options list for State options, you will see Restrict Access database options with three options listed in the combo box.
Restrict Access modes : Multiple, Single and Restricted modes.
If you select Single mode and click the OK button, you can either alter the database access mode to single user successfully or you will fail to change the access mode because of existence of active open connections to the Works database. The Management Studio IDE will prompt you to close all other connections to the related database for a successful alter database option process.
SQL Server open connections
To change the database properties, SQL Server must close all other connections to the database. Are you sure you want to change the properties and close all other connections?
After the alter command runs successfully, the database Works will be displayed as shown in the Object Explorer window.
SQL Server database single-user mode
If an active connection exists other than the Management Studio, and you click the database Works within the SQL Server Management Studio, the following warning message will be displayed:
database is not accessible warning message
The database Works is not accessible. (ObjectExplorer)
If you right click on the database Works, the following error message will be displayed.
database is already open
Database 'Works' is already open and can only have one user at a time. (Microsoft SQL Server, Error: 924)

After a database is altered as single user mode, it can be altered again back to multi user mode by running the below sql command.
ALTER DATABASE [Works] SET MULTI_USER WITH NO_WAIT
But if this command is run from a connection that is not the only active connection to the related database, the command will fail with the following message:
Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'Works' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

In a case that you want to alter the database access mode from single user mode to multi user mode or from multi user mode to single user mode, you may have to kill all the active open connections to the database.
In such a case, you have to find all the open connections and run "Kill @spid" command to close all the connections.
For a list of open connections for a specific database you can run a similar command as below :
select spid from master..sysprocesses where dbid = db_id('Works') and spid <> @@spid
The second part of the task will be closing the open connections with the Kill command. This can be achieved by running kill commands within a cursor or creating a dynamic sql command which kills the active connections to the related database.
Let's code the closing open connections using a T-SQL cursor:
DECLARE @DatabaseName nvarchar(50)
DECLARE @SPId int

SET @DatabaseName = N'Works'

DECLARE my_cursor CURSOR FAST_FORWARD FOR
SELECT SPId FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @SPId

WHILE @@FETCH_STATUS = 0
BEGIN
KILL @SPId

FETCH NEXT FROM my_cursor INTO @SPId
END

CLOSE my_cursor
DEALLOCATE my_cursor


After all connections are closed except the currently running process (@@spid) if we are trying to alter the access mode of the database that we're in from multi user mode to single user mode, we can now run the ALTER DATABASE command for single user mode or multi user mode.
ALTER DATABASE [Works] SET MULTI_USER WITH NO_WAIT
or
ALTER DATABASE [Works] SET SINGLE_USER WITH NO_WAIT

You can also use the following sp_dboption sql commands to alter the database option for single user. If you set the "single user" database option to "true", this is same as altering the database as "SINGLE_USER" with ALTER DATABASE statement.

USE master
GO
EXEC sp_dboption 'Works', 'single user', 'false';


If you run the below sp_dboption command which sets the "single user" to "false", this is same as altering the database as "MULTI_USER" with ALTER DATABASE statement.


USE master
GO
EXEC sp_dboption 'Works', 'single user', 'true';
The main difference between sp_dboption and ALTER DATABASE commands is that, sp_dboption will not be supported with further releases of SQL SERVER after SQL SERVER 2005.

Shrink the truncated log file on SQL Server

USE
 [DatabaseName];



GO




-- Truncate the log by changing the database recovery model to SIMPLE.




ALTER
DATABASE [DatabaseName]



SET
RECOVERY SIMPLE WITH NO_WAIT;



GO




-- Shrink the truncated log file to 1 MB.




DBCC
SHRINKFILE(DatabaseName_log, 1); --file_name is the logical name of the file to be shrink



GO




-- Reset the database recovery model.




ALTER
DATABASE [DatabaseName]



SET
RECOVERY FULL WITH NO_WAIT;



GO

Thursday, March 21, 2013

All checked out documents for site collection

List of all checked out documents not just Checked Out To me
Steps ( MOSS only ) :
1. Navigate to Home > View All Site Content > Content and Structures Reports > Create a new report with following CAML Query
<Where><Geq><FieldRef Name="CheckoutUser" LookupId="TRUE"/><Value Type="int">0</Value></Geq></Where>
all checkout report

2. Navigate to Site Action > Manage Content and Structure > Change the view to All Checked Out documents ( from right side view drop down)

Friday, March 15, 2013

Increase the SharePoint 2010 Upload File Size Limit

The Problem.
For teamsites and collaborations, sometimes it can be useful to upload big file size documents – which is allowed only partially by SharePoint and IIS. They both have different default settings for the maximum upload file size of a single document or file. They are:
  • SharePoint Server 2010: 50 MB
  • IIS7: ~30 MB
So, when trying to upload a big document, this results in the following error:
image
85 MB is simply too much for the above mentioned settings. Now, the solution section will show you how to fix this.

The Solution.
The good news is: This can be custom tailored to your needs, by simply following 3 steps. Here comes the first step:
1. Increase the SharePoint Upload Limit via Central Administration
First, you must increase the SharePoint 2010 upload limit.
  • Central Administration
  • “Manage Web Applications”
  • Select desired web application row (don’t click on the title, just select)
  • “General Settings” in the ribbon
  • Under Maximum Upload Size, change the setting to the desired value (e.g. 200 MB in our example)
  • “OK”

image
Now, the web app is equipped for receiving large files, but IIS7 will still prohibit it, resulting in the same error message as above. So, let’s move on to step 2.

2. Increase the IIS7 request length
Use the following command on the machine you are running the IIS 7:
%windir%\system32\inetsrv\appcmd set config -section:requestFiltering -requestLimits.maxAllowedContentLength:valueInBytes
In our case, we’ll simply put 200x1024x1024=209715200 bytes.
You will receive a confirmation message after applying the command. Please bear in mind that you will need to run the cmd.exe in administrator mode.
image

3. Increase the IIS7 connection timeout length (optional)
One more thing to keep in mind is the connection timeout settings: When you upload large files, depending on your connection speed it can happen that the connection times out. If you want, you can increase the connection timeout to a larger value. The standard is 120 seconds. This step is optional, but can become required if you have users with low speed internet connections.
image
  • Open IIS
  • Select the Web Application
  • Click on Advanced Settings
  • Expand Connection Limits
  • Set the new value for Connection Time-out (seconds)

This is it – all done!
You users will now be able to upload larger files:
image
As usual, test any commands, configuration and settings on a test environment before applying them in production. Applying request lengths bears also some security implications to keep in mind. Use any advices and configurations at your own risk and evaluate risks before applying them.