Joseph Michael Pesch
VP Programming

SQL Script to Shrink Database Transaction Log Files

by 26. October 2012 15:53

This script will operate on all databases that are ONLINE.  Will set recovery mode to SIMPLE temporarily shrink the log file then set recovery mode back to FULL.  In the case where the database recovery mode is already SIMPLE it will simply shrink the log file and leave the recovery mode set to SIMPLE.

declare @db varchar(255)
, @rcm varchar(25)
, @log varchar(255)
, @sql varchar(max) 

create table #tmp (db varchar(255), rcm varchar(25), lg varchar(255))
declare cur cursor for
select name, recovery_model_desc
/* convert(varchar(25), databasepropertyex(names, 'RECOVERY')) */
from sys.databases
where name not in('master', 'tempdb', 'model', 'msdb', 'distribution')
and state_desc = 'ONLINE' /* Skip anything OFFLINE */
open cur
while 1=1 begin
  fetch next from cur into @db, @rcm
  if @@FETCH_STATUS <> 0 break
  set @sql = 'use [' + @db + '] insert into #tmp'
      + ' select ''' + @db + ''', ''' + @rcm + ''''
      + ', name from sys.sysfiles where fileid = 2'
  exec(@sql) 
end
deallocate cur

declare cur cursor for
select * from #tmp
open cur
while 1=1 begin
  fetch next from cur into @db, @rcm, @log
  if @@FETCH_STATUS <> 0 break
  if @rcm = 'FULL' /* When FULL we will set simple then back to FULL */
    select @sql = 'use [' + @db + ']'
      + ' alter database [' + @db + '] set recovery simple with no_wait'
      + ' dbcc shrinkfile (N''' + @log + ''', 1) '
      + ' alter database [' + @db + '] set recovery full with no_wait'
   else /* When already SIMPLE just shrink and leave it alone */
    select @sql = 'use [' + @db + ']'
      + ' dbcc shrinkfile (N''' + @log + ''', 1) '
  exec(@sql)
end

drop table #tmp

Tags:

SQL Server

SQL Script to Shrink Database Transaction Log Files

by 26. October 2012 15:40

This script will operate on all databases that are ONLINE.  Will set recovery mode to SIMPLE temporarily shrink the log file then set recovery mode back to FULL.  In the case where the database recovery mode is already SIMPLE it will simply shrink the log file and leave the recovery mode set to SIMPLE.

declare @db varchar(255)
, @rcm varchar(25)
, @log varchar(255)
, @sql varchar(max) 

create table #tmp (db varchar(255), rcm varchar(25), lg varchar(255))
declare cur cursor for
select name, recovery_model_desc
/* convert(varchar(25), databasepropertyex(names, 'RECOVERY')) */
from sys.databases
where name not in('master', 'tempdb', 'model', 'msdb', 'distribution')
and state_desc = 'ONLINE' /* Skip anything OFFLINE */
open cur
while 1=1 begin
  fetch next from cur into @db, @rcm
  if @@FETCH_STATUS <> 0 break
  set @sql = 'use [' + @db + '] insert into #tmp'
      + ' select ''' + @db + ''', ''' + @rcm + ''''
      + ', name from sys.sysfiles where fileid = 2'
  exec(@sql) 
end
deallocate cur

declare cur cursor for
select * from #tmp
open cur
while 1=1 begin
  fetch next from cur into @db, @rcm, @log
  if @@FETCH_STATUS <> 0 break
  if @rcm = 'FULL' /* When FULL we will set simple then back to FULL */
    select @sql = 'use [' + @db + ']'
      + ' alter database [' + @db + '] set recovery simple with no_wait'
      + ' dbcc shrinkfile (N''' + @log + ''', 1) '
      + ' alter database [' + @db + '] set recovery full with no_wait'
   else /* When already SIMPLE just shrink and leave it alone */
    select @sql = 'use [' + @db + ']'
      + ' dbcc shrinkfile (N''' + @log + ''', 1) '
  exec(@sql)
end

drop table #tmp


Tags:

SQL Server

Excel Services in SharePoint 2010 Error: This workbook is larger than the maximum workbook size allowed to be opened in the browser

by 24. October 2012 08:10

By default the maximum file size that can be opened in the browser (by Excel services) is 10MB.  This is managed through the Central Administration > Application Management > Manage Service Applications > Excel Services > Trusted File Locations.  Below is an excerpt from Microsofts documentation on this topic located here: http://technet.microsoft.com/en-us/library/ff191194.aspx  When you attempt to open a file larger than this setting allows you will receive this error message: "This workbook is larger than the maximum workbook size allowed to be opened in the browser"

In the Workbook Properties section, you determine the resource capacity for the server when Excel Services opens workbooks.

Performance and resource availability can be compromised if users open extensive workbooks. If you do not manage the approved size that workbooks running in open Excel Calculation Services sessions can have, you risk having users exceed your resource capacity and overloading the server.

  1. In the Maximum Workbook Size box, type a value in megabytes (MB) for the maximum size of workbooks that Excel Calculation Services can open. The default size is 10 megabytes. 

Tags:

Sharepoint

SQL Server Query to Gather Individual Table Space Used

by 22. October 2012 16:29

 

select

  tablename = o.name

, totalpages = sum(a.total_pages)

, usedpages = sum(a.used_pages)

, pages = sum(case when a.type <> 1 then a.used_pages

when p.index_id < 2 then a.data_pages else 0 end)

, SUM(a.used_pages)*8096 AS 'Size(B)'

, rows = sum(case when (p.index_id < 2) and (a.type = 1) then p.rows else 0 end)

from sys.objects o

join sys.partitions p on p.object_id = o.object_id

join sys.allocation_units a on p.partition_id = a.container_id

where o.type = 'U'

group by o.name

order by usedpages desc

 

Tags:

SQL Server

Windows Command ForFiles to Search Folders/Sub-Folders and Output File Details

by 22. October 2012 12:23

Below is a sample command to search a folder (/p) and all subfolders (/s) for files mathing *.zip (/m)
and run echo command (/c) to print the filename, size, date and time to a text file

forfiles /p c:\foldertosearch /s /m *.zip /c "cmd /c echo @file;@fsize;@fdate @ftime" > output.txt

See Microsoft site for full usage of forfiles:
http://technet.microsoft.com/en-us/library/cc753551(v=ws.10).aspx

Tags:

Windows

ASP.Net C# Export GridView Control Data to Excel File

by 22. October 2012 08:44

This is a simple method to export data directly from an ASP.Net GridView control into an Excel file (CSV formatted).

protected void ExportGridViewDataButton_Click(object sender, EventArgs e)
{
  StringBuilder sb = new StringBuilder();
  foreach (TableCell cel in this.GridViewControl.HeaderRow.Cells)
  {
    sb.Append("\"").Append(cel.Text.Replace(" ", "")).Append("\",");
  }
  sb.Append(((Char)10).ToString());
  foreach (GridViewRow row in this.GridViewControl.Rows)
  {
    foreach (TableCell cel in row.Cells)
    {
      sb.Append("\"").Append(cel.Text.Replace(" ", "")).Append("\",");
    }
    sb.Append(((Char)10).ToString());
  }
  Response.Clear();
  Response.AddHeader("Content-Disposition"
    , "attachment;filename=GridViewExport.csv");
  Response.ContentType = "text/csv";
  Response.Write(sb.ToString());
  Response.Flush();
  Response.End();
}

Tags:

C# | Excel

Combine Binary Files Using C#

by 19. October 2012 09:07
using (var fs = File.OpenWrite("combined.bin"))
{
  var buffer = File.ReadAllBytes("file1.bin");
  fs.Write(buffer, 0, buffer.Length);
  buffer = File.ReadAllBytes("file2.bin");
  fs.Write(buffer, 0, buffer.Length);
  fs.Flush();
}

Tags:

C#

Sys Undefined Exception in ASP.Net Web Form Menu Control Generated JS with URL Routing

by 16. October 2012 08:07

Although the exception only seems to occur when using URL routing it is not consistent (i.e. doesn't always happen).

The page generates this line at the end of the Form tag:

new Sys.WebForms.Menu({ element: 'Navigation Menu', disappearAfter: 500, orientation: 'horizontal', tabIndex: 0, disabled: false });

Throws JS runtime exception:

'Sys' is undefined

Solution:

void Application_Start(object sender, EventArgs e)
{
  RouteTable.Routes.Ignore("{resource}.axd");
}

Or:

  
<system.web>
  <pages controlRenderingCompatibilityVersion="3.5"/>
</system.web>

Tags:

ASP.Net | C# | JavaScript