Powershell – adding multiple worksheets to Excel spreadsheet

Using : platform Powershell 2 CTP & Excel 2003

 I was troubleshooting a Powershell problem –- on a script that creates a Excel spreadsheet. All worksheets up to 4 were OK- but soon as I past 5 the following error was displayed.

Exception getting "Item": "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"

At C:\projects\testing\security\scripts\general_users.ps1:141 char:40

+     $sheet5 = $workbook.worksheets.Item <<<< (5)

Property 'name' cannot be found on this object; make sure it exists and is settable.

 

 

The  line of code throwing the error was :

$sheet5 = $workbook.worksheets.Item(5)

 

 

This problem is caused by the Default worksheets amount  set in Excel .

 Adjusting the default worksheets to the amount required will solve the problem as a quick solution.

 I will post a function where programmatically the worksheet amounts can be increased

 Ref:Jack Vamvas(http://www.sqlserver-dba.com)

 

TSM ACO5422E BackUp failure and OOM on SQL server

This message appeared on the error logs this morning:

 “Backup of MYDB failed. ACO5422E Received the following from the MS SQL server: An exception occurred while executing a Transact-SQL statement or batch. BackupVirtualDeviceSet::SetBufferParms: Request large buffers failure on backup device 'TDPSQL-00002B50-0000'. Operating system error 0x8007000e(error not found). BACKUP DATABASE is terminating abnormally”

 The MemToLeave area had filled up and caused heavy fragmentation. SQL server backup allocates a defined buffer count in a defined size . i.e BUFFERCOUNT and MAXTRANSFERSIZE. Default MAXTRANSFERSIZE is 1M. Default BUFFERCOUNT is defined by SQL server based on database volumes and backup devices. On a 32 bit system – as SQL Server is also allocating VAS for various other functions, such as OLE Automation – it is possible there aren’t x number of contigious 1M chunks of VAS.   (Not to be confused with physical memory).

 As the first allocation fails – the backup retries – with smaller MAXTRANSFERSIZE sizes – until there is no VAS left – which will cause the backup to fail.

 One well documented way to deal with this issue is to increase the MemToLeave area via the –g switch such as –g512 .  Another approach is to decrease the BUFFECRCOUNT to ? and the MAXTRANSFERSIZE to ? – this will have a negative impact on backup performance but needs to be weighed up against failed backups. The BUFFERCOUNT and MAXTRANSFERSIZE options can be defined on the BACKUP command

 

The BUUFERCOUNT option details the total number of I\O buffers in the backup

The MAXTRANSFERSIZE option details the largest unit of transfer in bytes – for the transfer between the SQL Server and the backup plaftform. Values range from 65536 bytes (64 KB) – 4194304 bytes (4 MB)

 Total buffer space used is : buffercount * maxtransfersize

Ref:Jack Vamvas (http://www.sqlserver-dba.com)

List all indexes of all tables

If you require a list of every index on every table on a database use  the query below

 

SELECT

  T.[name] AS [table_name], I.[name] AS [index_name]

FROM sys.[tables] AS T  

  INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]  

 WHERE  I.[type_desc] <> 'HEAP'

ORDER BY T.[name], I.[index_id]

 

 

A few details of the query , which will allow customising – depending on requirements.

 

The “type_desc” column -  gives a description of the index type. In SQL Server 2005 – possible types are: Heap, Clustered,NonClustered,XML

 

As a table is either a clustered table or a heap – I’m not interested in heaps – for the purpose of this query

 Ref:Jack Vamvas(http://www.sqlserver-dba.com)

Last time a stored procedure was executed

Quite often , this question appears on forums or on various jobs."Is there any way that I can tell when a stored procedure was last executed or who executed it? Is there any execution history that I can look at?"

 The following query will assist in telling you what time a stored procedure was last executed – assuming the query hasn’t been flushed from the cache.

 

SELECT object_name(m.object_id), MAX(qs.last_execution_time)

  FROM   sys.sql_modules m

  LEFT   JOIN (sys.dm_exec_query_stats qs

               CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st)

         ON m.object_id = st.objectid

        AND st.dbid = db_id()

  GROUP  BY object_name(m.object_id);

Ref:Jack Vamvas (http://www.sqlserver-dba.com)

 

SQL server OLE Automation memory pressure

Focus on the following areas:

1)OLE Automation usage that is causing VAS pressure\fragmentation

2)Excessive Linked Server Usage

3)Custom Extended Stored Procedure

4)3rd Party Backup Application (such as  Redgate SQL Backup) using to large of a Network Packet Size.

5)Your applications are connecting with a Network Packet Size exceeding 8KB and going into VAS/Multi-page allocations. 

6)Adhoc (non-parameterized) workload leading to  plan cache expansion

7)SQL CLR usage that is causing VAS Pressure\fragmentation

 

Potential approaches for the above are:

1,2,3,7)Try pushing up the VAS reservation by using the -g option.Also for linked server , plan to run out of process

4)Tune the applications to minimise VAS pressure - work with the vendor , who may have knowledge

5)Use sys.dm_exec_connections and report on network packet size greater than 4096 - Further details below

6)Consider FORCED parametisation - this will disguise the problem , consider returning to developers for development using parameterized queries

 

 

Make the relevant change - monitor the server performance for a few days and check if the issue appears again

 

 

Query to see if you have an application that is connecting with a network packet size of 8KB which requires multi-page allocations from VAS. 

If you do, change the connection string to include (Packet Size=4096)

 

select s.session_id, s.host_name, s.host_process_id, s.program_name, c.net_packet_size

from sys.dm_exec_connections c

join sys.dm_exec_sessions s on c.session_id = s.session_id

where net_packet_size >= 8000

Ref:Jack Vamvas (http://www.sqlserver-dba.com)