Truncating all logs within a SQL server

While working on a development server farm, I had issues with lax backups and growing log files. This has also been an issue on stand-alone development VPC images.

In these cases, I do not need to back up the databases as they are constantly being rebuilt using “Smoke & Build” and continuous integration. What is not in source control is not worth keeping.

I created a batch script that executes in the SQL agent on intervals to truncate all database logs.

/*

Truncate ALL log files on a server

Written by Tobias Lekman, 28 April 2009.

*/

DECLARE @Database VARCHAR(MAX)

/* Get all DBs expect exclusions */

DECLARE log_cursor CURSOR LOCAL FOR SELECT name from master..sysdatabases WHERE name NOT IN

(

'master', 'model', 'msdb', 'tempdb'

) ORDER BY name

/* Open and loop */

OPEN log_cursor

FETCH NEXT FROM log_cursor INTO @Database

/* Continue until done */

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @Query NVARCHAR(MAX)

/* Check initial size */

SET @Query = 'SELECT * FROM [' + @Database + '].dbo.sysfiles'

PRINT @Query

EXEC(@Query)

/* Truncate log */

SET @Query = 'BACKUP LOG [' + @Database + '] WITH TRUNCATE_ONLY'

PRINT @Query

EXEC(@Query)

SET @Query = 'ALTER DATABASE [' + @Database + '] SET RECOVERY FULL'

PRINT @Query

EXEC (@Query)

/* Shrink log file */

DECLARE @LogFile NVARCHAR(MAX), @ParmDefinition NVARCHAR(500)

SET @Query = 'USE [' + @Database + '] SELECT @LogFile = name FROM [' + @Database + '].dbo.sysfiles WHERE filename LIKE ''%.ldf'''

SET @ParmDefinition = '@LogFile VARCHAR(MAX) OUTPUT'

PRINT @Query

EXECUTE sp_executesql @Query, @ParmDefinition, @LogFile = @LogFile OUTPUT 

SET @Query = 'USE [' + @Database + '] DBCC SHRINKFILE ([' + @LogFile + '], 0)'

PRINT @Query

EXEC(@Query)

/* Truncate log */

SET @Query = 'BACKUP LOG [' + @Database + '] WITH TRUNCATE_ONLY'

PRINT @Query

EXEC(@Query)

SET @Query = 'USE [' + @Database + '] DBCC SHRINKFILE ([' + @LogFile + '], 0)'

PRINT @Query

EXEC(@Query)

/* Check new size */

SET @Query = 'SELECT * FROM [' + @Database + '].dbo.sysfiles'

PRINT @Query

EXEC(@Query)

/* Get next DB */

FETCH NEXT FROM log_cursor INTO @Database

END

CLOSE log_cursor

DEALLOCATE log_cursor

Multiple language support without variations

My site uses variations to publish Arabic and English content. This works well for all pages except for the login page, which sits in the root site at “/pages/login.aspx” and uses forms authentication.

I have implemented a custom language switch which allows the culture to be switched dynamically. This is done by using a custom code-behind page for the layout.

First, I override the OnPreInit to set the culture manually:

   1: /// <summary>

   2: /// Override to set UI culture according to query string or client settings.

   3: /// </summary>

   4: /// <param name="e">An <see cref="T:System.EventArgs"/> that contains the event data.

   5: /// </param>

   6: protected override void OnPreInit(EventArgs e)

   7: {

   8:     Thread.CurrentThread.CurrentCulture = 

   9:         Thread.CurrentThread.CurrentUICulture = CurrentCulture;

  10: }

I then get culture by query string or by client settings as:

   1: /// <summary>

   2: /// Gets or sets the current culture by query string or client settings.

   3: /// </summary>

   4: /// <returns>The current <see cref="CultureInfo"/>.</returns>

   5: protected virtual CultureInfo CurrentCulture

   6: {

   7:     get

   8:     {

   9:         if (currentCulture == null)

  10:         {

  11:             currentCulture = !string.IsNullOrEmpty(Page.Request.QueryString["lang"])

  12:                                  ? new CultureInfo(Page.Request.QueryString["lang"])

  13:                                  : new CultureInfo(Page.Request.UserLanguages[0]);

  14:         }

  15:         return currentCulture;

  16:     }

  17:     set { currentCulture = value; }

  18: }

Finally, I populate my dropdown list of choices as:

   1: /// <summary>

   2: /// Gets the variations of the system.

   3: /// </summary>

   4: /// <value>The variations.</value>

   5: private static SPListItem[] Variations

   6: {

   7:     get

   8:     {

   9:         if (variations == null)

  10:         {

  11:             using (SPSite site = new SPSite(SPContext.Current.Site.ID))

  12:             {

  13:                 using (SPWeb web = site.OpenWeb(SPContext.Current.Site.RootWeb.ID))

  14:                 {

  15:                     SPList list = web.Lists[new Guid(web.AllProperties["_VarLabelsListId"].ToString())];

  16:                     List<SPListItem> items = new List<SPListItem>(list.ItemCount);

  17:                     for (int step = 0; step < list.ItemCount; step++)

  18:                     {

  19:                         items.Add(list.Items[step]);

  20:                     }

  21:                     variations = items.ToArray();

  22:                 }

  23:             }

  24:         }

  25:         return variations;

  26:     }

  27: }

  28:  

  29: /// <summary>

  30: /// Override to populate the variation selection drop down list.

  31: /// </summary>

  32: /// <param name="e">

  33: /// The <see cref="T:System.EventArgs"/> object that contains the event data. 

  34: /// </param>

  35: protected override void OnLoad(EventArgs e)

  36: {

  37:     base.OnLoad(e);

  38:     SPSecurity.RunWithElevatedPrivileges(() => BindVariations(ddlVariations));

  39:     ddlVariations.Attributes.Add("onchange", "window.location.href='?lang='+this.options[this.selectedIndex].value");

  40: }

  41:  

  42: /// <summary>

  43: /// Binds the variations from the language variations to a list control.

  44: /// </summary>

  45: /// <param name="list">The list.</param>

  46: private void BindVariations(ListControl list)

  47: {

  48:     bool selected = false;

  49:     foreach (SPListItem variation in Variations)

  50:     {

  51:         ListItem item = new ListItem(variation["ows_Description"].ToString(),

  52:                                      variation["ows_Language"].ToString().ToLowerInvariant());

  53:         if (string.Compare(variation["ows_Language"].ToString(),

  54:                            CurrentCulture.ToString(),

  55:                            true) == 0 &&

  56:             !selected)

  57:         {

  58:             item.Selected = true;

  59:             selected = true;

  60:         }

  61:         list.Items.Add(item);

  62:     }

  63:     if (!selected)

  64:     {

  65:         //Exact culture not found. Use language, not culture as "en-US" == "en-GB"

  66:         string language = CurrentCulture.TwoLetterISOLanguageName;

  67:         foreach (ListItem item in list.Items)

  68:         {

  69:             if (item.Value.StartsWith(language + "-"))

  70:             {

  71:                 item.Selected = true;

  72:                 break;

  73:             }

  74:         }

  75:     }

  76: }

In the end, I will move the OnPreInit code into the master page or a HTTP module and encapsulate the variations list into a control, but you get the point from the code above. The benefit of using this in a HTTP module would be translation of system pages without usage of variations, but that’s another story.

Deploying Language Variations using Features

As I perform daily smoke & build using continuous integration, I required the rollout of variation labels to be automatic.

There are a few solutions out there, but the best solution out there, in my mind, is created by Waldek Mastykarz, which uses reflections to create the variation labels.

I have tweaked the code to make it feature configurable as I needed to specify the variations in the ONET.XML site definition as follows:

   1: <!-- Variation creation --> 

   2: <Feature ID="FE0615C8-582F-4a04-BECF-56BBD1E646A8"> 

   3:     <Properties xmlns="http://schemas.microsoft.com/sharepoint/"> 

   4:         <Property Key="VariationLabels" Value="ar,ar-SA,1025,العربية;en,en-US,1033,English;"/> 

   5:         <Property Key="Template" Value="MYCUSTOMTEMPLATE#1" /> 

   6:     </Properties> 

   7: </Feature>

 

The labels are delimited as “URL,CULTURE,LCID,TITLE;”

This is done within the feature receiver as:

   1: <?xml version="1.0" encoding="utf-8" ?> 

   2: <Feature 

   3:     Id="00000000-0000-0000-0000-000000000000" 

   4:     Title="Variation Creation" 

   5:     Description="" 

   6:     Scope="Web" 

   7:     Hidden="True" 

   8:     xmlns="http://schemas.microsoft.com/sharepoint/" 

   9:     ReceiverAssembly="MyAssembly, Version=1.0.0.0, Culture=neutral, PublicKeyToken=0000000000000000" 

  10:     ReceiverClass="MyAssembly.FeatureReceivers.VariationCreationFeatureReceiver"> 

  11:     <Properties> 

  12:         <Property Key="VariationLabels" Value="" /> 

  13:         <Property Key="Template" Value="" /> 

  14:     </Properties> 

  15:     <ElementManifests /> 

  16: </Feature> 

 

I add a call to create variations within the  method of the feature receiver as:

   1: public override void FeatureActivated(SPFeatureReceiverProperties properties) 

   2: { 

   3:     CreateVariations(GetVariationLabels(properties), 

   4:     properties.Feature.Properties["Template"].Value); 

   5: }

 

The GetVariationLabels  method gets all unique variations including labels from the system:

   1: /// <summary> 

   2: /// Gets the variation labels declared in the feature properties. 

   3: /// </summary> 

   4: /// <param name="properties">The feature properties.</param> 

   5: /// <returns>A strongly typed collection of variations.</returns> 

   6: private static ICollection<Variation> GetVariationLabels 

   7:     (SPFeatureReceiverProperties properties) 

   8: { 

   9:     List<Variation> variations = new List<Variation>(); 

  10:     foreach ( 

  11:         string label in 

  12:             properties.Feature.Properties["VariationLabels"].Value.Split( 

  13:                 new[] {';'}, 

  14:                 StringSplitOptions.RemoveEmptyEntries)) 

  15:     { 

  16:         string[] parts = label.Split(new[] {','}); 

  17:         bool source = variations.Count == 0; 

  18:         variations.Add(new Variation 

  19:             { 

  20:                 DisplayName = parts[0], 

  21:                 Label = parts[0], 

  22:                 Language = parts[1], 

  23:                 Locale = int.Parse(parts[2], CultureInfo.InvariantCulture), 

  24:                 Description = parts[3], 

  25:                 IsSource = source 

  26:             }); 

  27:     } 

  28:     return variations; 

  29: }

The CreateVariations method calls to create a variation entry per variation, sets the variation settings and creates the site hierarchies:

   1: /// <summary> 

   2: /// Creates the variation labels and the variation label hierarchy for a site collection. 

   3: /// </summary> 

   4: /// <param name="variations">A collection of variations to create.</param> 

   5: /// <param name="template">The ID of the template used to create new variation sites.</param> 

   6: private void CreateVariations(ICollection<Variation> variations, string template) 

   7: { 

   8:     if (variations.Count == 0) 

   9:     { 

  10:     return; 

  11:     } 

  12:     using (SPSite site = new SPSite(Site.ID)) 

  13:         { 

  14:         using (SPWeb web = site.OpenWeb(Web.ID)) 

  15:         { 

  16:             SPList list = web.Lists[new Guid( 

  17:             web.AllProperties["_VarLabelsListId"].ToString())]; 

  18:             foreach (Variation variation in variations) 

  19:             { 

  20:                 CreateVariation(list, variation, test); 

  21:             } 

  22:             UpdateVariationSettings(web, template); 

  23:             timeout = 5; 

  24:             CreateSiteHierarchies(list, timeout); 

  25:         } 

  26:     } 

  27: }

  28:  

  29: /// <summary> 

  30: /// Provides an instance of the Variation class. 

  31: /// </summary> 

  32: public class Variation 

  33: { 

  34:     #region Properties 

  35:  

  36:     /// <summary> 

  37:     /// Gets or sets the label. 

  38:     /// </summary> 

  39:     /// <value>The label.</value> 

  40:     public string Label { get; set; } 

  41:  

  42:     /// <summary> 

  43:     /// Gets or sets the display name. 

  44:     /// </summary> 

  45:     /// <value>The display name.</value> 

  46:     public string DisplayName { get; set; } 

  47:  

  48:     /// <summary> 

  49:     /// Gets or sets the language, i.e. the ISO specification for the language pack to use. 

  50:     /// For example, the culture en-GB, or 2057, is not a valid language pack but a valid 

  51:     /// culture and should therefore use en-US. 

  52:     /// </summary> 

  53:     /// <value>The language.</value> 

  54:     public string Language { get; set; } 

  55:  

  56:     /// <summary> 

  57:     /// Gets or sets the locale. 

  58:     /// </summary> 

  59:     /// <value>The locale.</value> 

  60:     public int Locale { get; set; } 

  61:  

  62:     /// <summary> 

  63:     /// Gets or sets a value indicating whether this instance is source. 

  64:     /// </summary> 

  65:     /// <value><c>true</c> if this instance is source; otherwise, <c>false</c>.</value> 

  66:     public bool IsSource { get; set; } 

  67:  

  68:     /// <summary> 

  69:     /// Gets or sets the description. 

  70:     /// </summary> 

  71:     /// <value>The description.</value> 

  72:     public string Description { get; set; } 

  73:  

  74:     #endregion 

  75: }

The method CreateVariation adds the variation to the system:

   1: /// <summary> 

   2: /// Creates a variation. 

   3: /// </summary> 

   4: /// <param name="list">The variations list.</param> 

   5: /// <param name="variation">The variation to create.</param> 

   6: private static void CreateVariation(SPList list, Variation variation) 

   7: { 

   8:     SPListItem item = list.Items.Add(); 

   9:     item["Hierarchy Is Created"] = false; 

  10:     item["Title"] = variation.Label; 

  11:     item["Description"] = variation.Description; 

  12:     item["Flag Control Display Name"] = variation.DisplayName; 

  13:     item["Language"] = variation.Language; 

  14:     item["Locale"] = variation.Locale; 

  15:     item["Hierarchy Creation Mode"] = "Publishing Sites and All Pages"; 

  16:     item["Is Source"] = variation.IsSource; 

  17:     item.Update(); 

  18: } 

The UpdateVariationSettings method sets overall variation settings. Note: I am considering adding these as feature properties as well, but have put that on hold. Feel free to modify to do so :)

   1: /// <summary> 

   2: /// Udpates the variation settings. 

   3: /// </summary> 

   4: /// <param name="web">The <see cref="SPWeb"/> containing the variations.</param> 

   5: /// <param name="template">The ID of the template used to create new variation sites.</param> 

   6: private static void UpdateVariationSettings(SPWeb web, string template) 

   7: { 

   8:     SPList variationSettingsList = web.Lists[ 

   9:                                        new Guid(web.AllProperties["_VarRelationshipsListId"].ToString())]; 

  10:     SPListItem variationroot = variationSettingsList.Items[0]; 

  11:     variationroot["ObjectID"] = string.Format( 

  12:                                     CultureInfo.InvariantCulture, 

  13:                                     "{0}/, /", web.Site.Url); 

  14:     variationroot.Update(); 

  15:     Hashtable properties = variationSettingsList.RootFolder.Properties; 

  16:     properties["SourceVarRootWebTemplatePropertyName"] = template; 

  17:     properties["EnableAutoSpawnPropertyName"] = true; 

  18:     properties["AutoSpawnStopAfterDeletePropertyName"] = true; 

  19:     properties["UpdateWebPartsPropertyName"] = true; 

  20:     properties["CopyResourcesPropertyName"] = true; 

  21:     properties["SendNotificationEmailPropertyName"] = true; 

  22:     variationSettingsList.RootFolder.Update(); 

  23: } 

Finally, the hierarchies are compiled using the method CreateSiteHierarchies. This is done using reflections, as no public interfaces exists for performing this task. Also, if the operation fails, as would happen if the site template has an error, then I need to use a timeout to fail the job.

   1: /// <summary> 

   2: /// Creates the site hierarchies. 

   3: /// </summary> 

   4: /// <param name="list">The variation labels list.</param> 

   5: /// <param name="timeOutMinutes"> 

   6: /// The minutes to wait before timeout, while the hierarchies are being created. 

   7: /// </param> 

   8: private static void CreateSiteHierarchies(SPList list, double timeOutMinutes) 

   9: { 

  10:     Assembly assembly = Assembly.GetAssembly(typeof (PublishingSite)); 

  11:     if (assembly == null) 

  12:     { 

  13:         throw new Exception("The Publishing API could not be loaded"); 

  14:     } 

  15:     Type type = assembly.GetType("Microsoft.SharePoint.Publishing.Internal.WebControls.CreateVariationHierarchiesLro", 

  16:                     false, 

  17:                     false); 

  18:     if (type == null) 

  19:     { 

  20:         throw new Exception("The variations API could not be loaded."); 

  21:     } 

  22:     //Waiting for hierarchy to be created. 

  23:     SPWeb web = list.ParentWeb; 

  24:     object lro = Activator.CreateInstance(type, 

  25:                     BindingFlags.Instance | BindingFlags.NonPublic, 

  26:                     null, 

  27:                     new object[] {web.Site.Url}, 

  28:                     CultureInfo.CurrentCulture); 

  29:     PropertyInfo redirectWhenFinished = type.GetProperty("RedirectWhenFinished"); 

  30:     redirectWhenFinished.SetValue(lro, 

  31:                                   false, 

  32:                                   null); 

  33:     type.InvokeMember("Start", 

  34:                         BindingFlags.InvokeMethod, 

  35:                         null, 

  36:                         lro, 

  37:                         new object[] {web}, 

  38:                         CultureInfo.InvariantCulture); 

  39:                         //Wait while hierachies are created. 

  40:     bool isNormalFinish = false; 

  41:     DateTime endTime = DateTime.Now.AddMinutes(timeOutMinutes); 

  42:     while (DateTime.Now < endTime) 

  43:     { 

  44:         int hierarchyIsCreatedCounter = 0; 

  45:         SPListItemCollection items = list.Items; 

  46:         foreach (SPListItem item in items) 

  47:         { 

  48:             bool hierarchyIsCreated; 

  49:             if (!bool.TryParse(item["Hierarchy Is Created"].ToString(), out hierarchyIsCreated)) 

  50:             { 

  51:                 hierarchyIsCreated = false; 

  52:             } 

  53:             if (hierarchyIsCreated) 

  54:             { 

  55:                 hierarchyIsCreatedCounter++; 

  56:             } 

  57:          } 

  58:         if (hierarchyIsCreatedCounter == items.Count) 

  59:         { 

  60:             isNormalFinish = true; 

  61:             break; 

  62:         } 

  63:         Thread.Sleep(1000); //Wait for a second to release the thread. 

  64:     } 

  65:     if (!isNormalFinish) 

  66:     { 

  67:         //Write trace here (using custom TraceProvider in my actual version) 

  68:     } 

  69: } 

Apologies for the lengthy post – I could not attach the original code as parts were under IP, hence some bits are removed.