SQL Query: Set All DBs to the Simple Recovery Model–DEV



The Attached Query

The really cool part about this query is how easy it is to modify.

The first section declares the variables, in this case there are 3.

The second section, sets the action for each of he declared variables

The last section loops through each DB using the PROC sp_MSforeachdb and runs each command,,,

Set the Recover Model to Simple, Backs up All Dbs except tempdb, and shrinks the DBs



-- DBCleanup for DEV

declare @cmd1 varchar(500)
declare @cmd2 varchar(500)
declare @cmd3 varchar(500)

-- Set Recovery Model to Simple except for tempdb
SET @cmd1 = 'if ''?'' <> ''tempdb'' ALTER DATABASE [?] SET RECOVERY SIMPLE'

-- Backup All Dbs Except tempdb to C:\temp
SET @cmd2 = 'if ''?'' <> ''tempdb'' backup database [?] to disk=''G:\MSSQL\Backup\?.bak'''

-- Shrink All Dbs Except tempdb 
SET @cmd3 = 'DBCC SHRINKDATABASE ([?], 5)'

-- Execute above commands
EXEC sp_MSforeachdb    @command1 = @cmd1,
                       @command2 = @cmd2,
                       @command3 = @cmd3


Download: SET_RecoveryModelShrinkBackup.sql



del.icio.us Tags: ,

SQL Query: Set All Dbs AutoGrowth



The following are recommendations to proactively manage the growth of data and log files:

When possible, increase all data files and log files to their expected final size, or periodically increase these at set periods, for example, every month or every six months, or before rollout of a new storage-intensive site such as during file migrations.

Enable database autogrowth as a protective measure to make sure that you do not run out of space in data and log files. Consider the following:


You must factor in the performance and operations issues associated with using autogrowth. For more information, see Considerations for the “autogrow” and “autoshrink” settings in SQL Server.

Default Settings

The default settings for a new database are to grow by 1 MB increments. Because this default setting for autogrowth results in an increases in the size of the database, do not rely on the default setting. Instead, use the guidance provided in Set SQL Server options.


Set autogrowth values to a fixed number of megabytes instead of to a percentage. The bigger the database, the bigger the growth increment should be.

NoteNote:  Use care when you set the autogrowth feature for SharePoint databases. If you set a database to autogrowth as a percentage, for example at a 10-percent (%) growth rate, a database that is 5 GB grows by 500MB every time that a data file has to be expanded. In this scenario, you could run out of disk space. or the db could be 100gb and growth would be 10gb every time the file needed space

Consider for example, a scenario where content is gradually increased, say at 100MB increments, and autogrowth is set at 10MB. Then suddenly a new document management site requires a very large amount of data storage, perhaps with initial size of 50 GB. For this large addition, growth at 500 MB increments is more appropriate than 10MB increments.

For a managed production system, consider autogrowth to be merely a contingency for unexpected growth. Do not use the autogrow option to manage your data and log growth on a day-to-day basis. Instead, set the autogrowth to allow for an approximate size in one year and then add a 20 percent margin for error. Also set an alert to notify you when the database runs low on space or approaches a maximum size.

Maintain a level of at least 25 percent available space across drives to accommodate growth and peak usage patterns. If you add drives to a RAID array or allocate more storage to manage, monitor capacity closely to avoid running out of space. Setting autogrowth to use MB verse %  along with changing the autogrowth MB size will create less fragmentation than using the defaults, this SQL Query makes it easier to modify a large number of Dbs.

-- Query to Set File AutoGrowth

    DB_NAME(mf.database_id) database_name,
    mf.name logical_name,
    CONVERT(DECIMAL(20, 2), (CONVERT(DECIMAL, size) / 128)) [file_size_MB],
    CASE mf.is_percent_growth
        WHEN 1 THEN 'Yes'
        ELSE 'No'
    END AS [is_percent_growth],
    CASE mf.is_percent_growth
        WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
        WHEN 0 THEN CONVERT(VARCHAR, mf.growth / 128) + ' MB'
    END AS [growth_in_increment_of],
    CASE mf.is_percent_growth
        WHEN 1 THEN CONVERT(DECIMAL(20, 2), (((CONVERT(DECIMAL, size) * growth) / 100) * 8) / 1024)
        WHEN 0 THEN CONVERT(DECIMAL(20, 2), (CONVERT(DECIMAL, growth) / 128))
    END AS [next_auto_growth_size_MB],
    CASE mf.max_size
        WHEN 0 THEN 'No growth is allowed'
        WHEN -1 THEN 'File will grow until the disk is full'
        ELSE CONVERT(VARCHAR, mf.max_size)

    END AS [max_size],
FROM sys.master_files mf
ORDER BY database_name


Download: Set_AutoGrowth4AllDbs.sql



SQL Query: Move TempDb Files to separate LUNs



Tempdb Multiple Files

One of the important issues when hosting multiple Content Dbs with multiple terabytes data is to ensure that you have created a TempDB with additional files of the same size and once created move teach of the files to their own Volume.

Do NOT use ISCSI for SQL Dbs

In this case we are using a NetApp SAN with Fiber Chanel HBAs as iSCSI does NOT meet our SQL performance requirements. I Microsoft provides multiple whitepapers against using iSCSI for SQL Dbs. Also, I have seen performance degradation using iSCSI once the ContentDBs reach about 50GB in total size. Using Fiber Chanel I have been able to host multiple terabytes of ContentDbs without any degradation in performance.


This server has 2 PROCS with 24 Cores, and 128GB of RAM with tempdb having 1 MDF, 2 NDF, and 1 LDF files on separate LUNS.. We used this script to move the original tempdb files to the SAN and then once provisioned to move the NDF files to separate LUNs.

NAME = Tempdb file Logical Name

FILENAME = the New path with existing filename where you want the file moved




Screenshot of OLD Locations


Screenshot NEW Locations

-- Query to Move tempDB

    physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

USE master;

MODIFY FILE (NAME = tempdev, FILENAME = 'K:\MSSQL\Data\tempdb.mdf');

MODIFY FILE (NAME = templog, FILENAME = 'L:\MSSQL\Data\templog.ldf');

    physical_name AS CurrentLocation,

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');


Note: you will need to restart the SQL Server Instance to have the move take affect


Download SET_TempDBLocation.ps1



Microsoft Training Resources

Find training opportunities and recommendations anytime on the U.S. Partner Learning blog, Learning Plan Tool website, and the Local Activities Section of the Training and Events page on the U.S. partner portal.

Training for Developers

Microsoft Forefront Security

Forefront Server Security Service Kit: The Tools You Need for a Successful Deployment
TechNet Webcast: Microsoft Forefront Unified Access Gateway – Hands on with Beta 2!
TechNet Webcast: Centralizing Application Authorization with AD FS 2.0 (Level 200)
TechNet Webcast: Forefront Threat Management Gateway 2010: Protection Features and Underlying Technologies (Level 300)
Build Your Forefront Security Learning Plan

Microsoft SQL Server 2012

SQL Server 2012: Technical Overview of New Features
SQL Server 2012: Optimized Developer Productivity
SQL Server 2012: Extend Data Virtually Anywhere
SQL Server 2012 Licensing Update for ISVs
Build Your SQL Server 2012 Learning Plan

November Partner Training Sweepstakes
The Partner Training Sweepstakes offers a chance to grow your skills and be entered to win great prizes. Take any of our featured Windows 7, Microsoft Office 2010, Windows Server 2008 R2 and Microsoft System Center training sessions this month and you will be entered to win a Dell Netbook or a $150 American Express gift card.




SQL 2005 Maintenance for SharePoint

The Maintenance Plan Wizard in SQL Server 2005 enables administrators to perform the following maintenance tasks against SharePoint databases:

Check database integrity

  •   Reduce a database
  •   Reorganize an index
  •   Clean up the history
  •   Update statistics
  •   Rebuild an index

We have tested these tasks and the effects that these tasks have on database schema and performance. The following table summarizes the results of these tests.

Task Safe to perform this task?
Check database Yes
Reduce a database Yes
Reorganize an index Yes
Clean up the history Yes
Update statistics Yes. However, this task is unnecessary because the SharePoint Timer service performs this task automatically.
Rebuild an index No. The task does not restore existing index options before the rebuild operation. However, you can use scripts that restore index options.
Note This problem was corrected in SQL Server 2005 Service Pack 2.

We used the following criteria to determine whether a task was safe to perform:

    •   Whether the task modified the database schema from its default state
    •   Whether the task decreased performance

Results may vary depending on the environment. However, if you use the Maintenance Plan Wizard to perform the tasks that are listed in the table as “safe to perform,” you are likely to experience increased performance in SQL Server 2005.

For more information about support for changes to the databases that are used by Microsoft Office server products and by Windows SharePoint Services, click the following article numbers to view the articles in the Microsoft Knowledge Base:

932744 (http://support.microsoft.com/kb/932744/ ) Information about the Maintenance Plan Wizard in SQL Server 2005 and about tasks that administrators can perform against SharePoint databases..



Ivan Sanders My LinkedIn Profile, My Blog, @iasanders, MVP

Using SQL Server 2008 R2 Mirroring

Restore Dbs to the mirror server in NORECOVERY Mode

1. Restore DB > Choose from device >



2.  Click Add >



3. Locate the db to be restore, after choosing the db, Copy the db Name before clicking OK



4. Click Ok > Click Ok

5. Paste the Db Name you copied to the database name



6.   Check the restore Box > Click on Options and choose (RESTORE WITH NORECOVERY) > Click OK



7. Repeat for each of the SharePoint Dbs

Mirror the SharePoint Databases

1. Right Click on the DB to Mirror > Go to Tasks > Mirror



2. Click on Configure Security > Click Next



3. Click No, you do not want to create a Witness > Click Next



4.  Accept the Defaults (Listener Port:5022, Endpoint Name: Mirror > Click Next



5.  Choose the Endpoint that will be the next Endpoint > Click Connect



6.  Click Connect and test your authentication > Click Finish



7. Click Finish



8. Click Close



9. Start Mirror



Ivan Sanders My LinkedIn Profile, My Blog, @iasanders, MVP