SSRS Reports hang after upgrade to SQL Server 2008 R2
We thought we were having performance issues with reports when we upgraded from SQL 2008 to SQL 2008 R2 but we realized it wasn’t any long running queries, it was the rendering of the report. In SSRS 2005, we played with properties on the report to allow our users to scroll through reports without paging through reports. The setting was the interactive size. In order to allow scroll through, the setting was set to 8.5in, 0in… In R2, we changed this setting back to the default 8.5in,11in…
Error 64 Internal error: Invalid enumeration value. Please call customer support! is not a valid value for this element.
I’ve been doing a ton of work on our Data Warehouse lately and I’ve been getting this error every once in a while…
There are two solutions to the problem above; delete the file, or delete the database.
If deleting the file doesn’t work for you, then open SQL Management Studio and log into Analysis Services… Backup the affected database, then delete the database.
Go back to your Analysis Services project and reprocess your cubes. The database will be re-created and the errors should be gone.
Target did not respond in time for a SCSI request. The CDB is given in the dump data.
On Sunday nights, we run our maintenance plan to rebuild and reorganize indexes and clean up some work tables. This process takes a few hours, but we started getting errors in the event log such as these…
“Target did not respond in time for a SCSI request. The CDB is given in the dump data.”
“Initiator sent a task management command to reset the target. The target name is given in the dump data.”
We tried setting the timeout below to 60, then 90, but the errors didn’t go away until we set it to 120.
From a Microsoft support engineer post:
Event ID 9 is logged when the target did not complete a SCSI command within the timeout period specified by SCSI layer.
The dump data will contain the SCSI Opcode corresponding to the SCSI command.
Please try the following resolution to solve this issue:
1. On the Windows system, click Start, click Run, type regedit and press Enter.
2. In HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Disk, edit or add the TimeOutValue entry to set it to 60 seconds
in decimal format (0000003c in hexadecimal).
- To edit the value for the TimeOutValue entry if it already exists, double-click the entry, enter the new value, and click OK.
- To add the TimeOutValue entry if it does not yet exist, right-click anywhere in the list of values, and then
click New > DWORD value. Name the new value TimeOutValue, and then double-click it to edit the setting.
Note:
If you are not running iSCSI Initiator v1.05a, you can also check the following:
Go to the registry value MaxRequestHoldTime at the following registry key:
HKLM\SYSTEM\CurrentControlSet\Control\Class\{4D36E97B-E325-11CE-BFC1-08002BE10318}
Increase the value to 90. If that does not work, try 120.
Excel Subtotal disabled (grayed out)
I don’t use sub totaling in Excel… but someone here asked me to figure out the issue of his sub total feature being “grayed out” in Excel 2007 or 2010. I opened the file in Excel 2003 and sub totaling worked. The file he was working with is a file I created with a Microsoft Query enabled to refresh on open in the background. He runs that file and copies the data out and into another file so he can edit it. I did some research and found that the queried data is still in some kind of list or range. On the ‘Design’ tab, you’ll find a ‘Convert to Range’ feature. Click that and you should be ok.
Powershell – Get multiple server drive information
I don’t remember where I got some of this code, but it comes in handy… Create a text file called Servers.txt in your C:\MyScripts folder, then run the powershell script below.
$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.WorkSheets.Item(1)
$Sheet.Cells.Item(1,1) = “Computer”
$Sheet.Cells.Item(1,2) = “Drive Letter”
$Sheet.Cells.Item(1,3) = “Description”
$Sheet.Cells.Item(1,4) = “FileSystem”
$Sheet.Cells.Item(1,5) = “Size in GB”
$Sheet.Cells.Item(1,6) = “Free Space in GB”
$WorkBook = $Sheet.UsedRange
$WorkBook.Interior.ColorIndex = 8
$WorkBook.Font.ColorIndex = 11
$WorkBook.Font.Bold = $True
$intRow = 2
#loop through the servers.txt
foreach ($service in get-content "C:\MyScripts\Servers.txt")
{
$colItems = Get-wmiObject -class “Win32_LogicalDisk” -namespace “root\CIMV2”`
-computername $service
foreach ($objItem in $colItems) {
$Sheet.Cells.Item($intRow,1) = $objItem.SystemName
$Sheet.Cells.Item($intRow,2) = $objItem.DeviceID
$Sheet.Cells.Item($intRow,3) = $objItem.Description
$Sheet.Cells.Item($intRow,4) = $objItem.FileSystem
$Sheet.Cells.Item($intRow,5) = $objItem.Size / 1GB
$Sheet.Cells.Item($intRow,6) = $objItem.FreeSpace / 1GB
$intRow = $intRow + 1
}
}
$WorkBook.EntireColumn.AutoFit()
Clear
The transaction log for database is full. To find out why space in the log cannot be reused see the log_reuse_wait_desc column in sys.databases
We replicate our production database every morning for reporting and testing purposes. Every once in a while, replication eats away at our transaction log file. I haven’t figured out the reason, and I haven’t done much research either, but I fix it with the code below.
--Step 1 - Check if database is in 'Replication' state
SELECT name, log_reuse_wait_desc
FROM sys.databases
WHERE name = 'database name here'
--Step 2 - Disable Replication
exec msdb..sp_update_job @job_name = 'name of job here' , @enabled = 0
--Step 3 - Stop Replication
exec sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
--Step 4 - Set database to Simple Recovery mode
ALTER DATABASE [database name here]
SET RECOVERY SIMPLE
--Step 5 - Shrink log file to 1 GB
DBCC SHRINKFILE('log file name here', 1024)
--Step 6 - Set database back to Full Recovery mode
ALTER DATABASE [database name here]
SET RECOVERY FULL
Targit BI – Drill down over multiple dimension tables
Targit BI is limited in drilling through data where the data is stored over multiple dimension tables.
For example, look at my star schema, beautiful isn’t it?
Targit BI star schema
Let’s say you get a request from the President of the company, that he wants to see a list of customer sales, with the ability to drill into the products that we sold to those customers. Targit BI is the BI tool that you inherited when you took the job, so until you come up with some comparison data on why Targit BI isn’t the best tool for your company, then you have to suck it up and try to get this request done.
Since we all read, loved, and keep a copy of “The Data Warehouse Toolkit” on our desks at all times, we need to keep this post from getting to Mr. Kimball, not Detective John Kimble from Kindergarten Cop on all those sound boards you use to play when you were a kid.
Wait, you haven’t read “The Data Warehouse Toolkit”?
Here it is…. The Data Warehouse Toolkit
What, you never heard of Detective John Kimble in Kindergarten Cop?
Here he is… Detective John Kimble
Ok, so this is what we’re trying to accomplish; a drill down effect over multiple dimensions… DimCustomer and DimProduct.
Ok, Detective John Kimble, I mean Dr. Ralph Kimball Follower Data Warehouse Star Schema inventors (Real Men of Genius Theme playing in background), forgive me. In order to get his effect in Targit BI, you’ll have to create a dimension table that contains all of the distinct customers and products.
I know what you’re going to say…
“This kills performance.”
“Now I have to load a dimension table with N million records.”
“There has to be another way!”
“This goes against everything Detective John Kimble taught us!”
I get it… There is no other way to do this in Targit BI with a Star Schema, and you definitely spent too much time on the John Kimble sound board I gave you.
There is an easier way to report all of this without Targit BI, but have a good time trying to convince top managers who seem to think that Excel Pivot Tables are a “step backwards” in BI reporting. This exact functionality can be done by creating a connection to your cube through Excel 2007/2010, and getting that “drill down across multiple dimension feature”, without building a huge table to make it work in Targit BI.
Now, why did we buy Targit BI? Less Clicks?
I had a problem when I tried running Synchronize Database in AX when I had replication turned on for SQL Server. The database wouldn’t synchronize and it didn’t give me a very descriptive error. I went to the AOS server and checked the Windows logs and found that AX had a problem with certain stored procedures being replicated. I didn’t need those stored procedures so I excluded them from replication.
Full Transaction log
Overnight, a log file grew 70GB and the database grew 60GB. The only transactions that were occurring were conversions to add data to a database other than the one that was growing. Looking further, I had replication running on the suspect database so I did some more research. I found this article, http://support.microsoft.com/kb/317375, which says; “The transaction log size of the publisher database can expand if you are using replication.”
So I ran this query to find out what was the problem on the database in question.
select name, log_reuse_wait_desc from sys.databases
I found the db which showed the description was set to Replication.
I had to run the command below that tells the server that the transactions have been replicated.
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
I used the stored procedure below to tell me who was connected to the databases. I stopped the Microsoft Dynamics AX AOS services so no other transactions went in to the database.
exec sp_who2
Then I set the database to Simple Recovery Mode and used dbcc shrinkfile to shrink the log file to a 1024MB.
