I am presenting to the Birmingham, AL and Dallas, TX SQL Server User Groups this week and wanted to post some collateral to accompany my presentation.  The topic was centered around how to use SQL Server Reporting Services as a DBA’s tool.  I have blogged on the topic before and you can view an abstract and link to download slides and examples below.  I’d love to hear your feedback!!

http://sqlserverpedia.com/blog/im-a-newbie/sql-server-reporting-services-a-dbas-tool/

SQL Server Reporting Services (SSRS) will attempt to determine whether the data in the ChunkData table is still needed all by itself. If you review the error log, you should see messages where the SSRS engine is attempting to purge the data by itself using the following query:

DELETE ChunkData FROM ChunkData INNER JOIN #tempSnapshot ON
ChunkData.SnapshotDataID = #tempSnapshot.SnapshotDataID

I would venture a guess that the following error is being thrown:

ERROR: Sql Error in
CleanOrphanedSnapshots: System.Data.SqlClient.SqlException: Timeout
expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.

If that is the case, you should monitor that instance to see whether there are blocking locks or other performance bottlenecks that are stopping the purge and tune accordingly.

Traditionally, SQL Server Reporting Services has been thought of as strictly a Business Intelligence tool that helps organizations make business decisions based on the data they have stored.  Over the past several years I have been preaching that there is a great deal of utility that Reporting Services can provide above and beyond what is strictly thought of as Business Intelligence. 

As a DBA, one of the most tedious and repetitive tasks that you must do on a daily, sometimes hourly, basis is to deliver reports to colleague’s and/or managers.  A manager might as for something simple such as, “Show me how much disk space is available on each drive on server X?” or “Show me the status of all of our scheduled jobs on server Y?”  What might appear a trivial tasks involves several steps, many I’m sure you’ve gone through already today:

1. Build a query that returns the necessary data.
2. Run the query in Management Studio and validate the result set.
3. Copy and paste the result set into Microsoft Word or Excel
4. Organize and format the data so that it looks presentable
5. Compose an e-mail to the requestor with the file you created as the attachment.

What this presentation goes into is an approach for automating these tasks using Reporting Services.  If you build these reports into SSRS they can first of all be created with a single mouse click, and even better they can be automatically delivered on any given schedule.

 If you’re still interested in learning more about this topic, I have posted the presentation materials (including the slide deck, sample RDL files, and sample SQL Scripts) to Quest’s SQL Server Community Site (http://sqlserver.quest.com/thread.jspa?threadID=6781) where anyone can download them and ask any follow up questions.

Happy Reporting!!!

You should definitely read about Report Snapshots in MSDN to ensure you have all the necessary background information on report snapshots before you try to dive too deep. Once you’ve gone through that, you can find instructions on accessing report snapshots and snapshot histories here:

• Finding and Viewing Reports in Report Manager
  • To select a report, search for a report by name or description, or browse report server folders to find the report you want. In Reporting Services, published reports and folders are represented as a hierarchy similar to those of file systems and Web sites. To find the reports that you want to run, you can browse through the folder hierarchy. Not all reports that are visible in the folder hierarchy are accessible. Some reports may prompt you for your user name and password to determine whether you can access the data source for the report.
• How to: View and Configure Report History (Management Studio)
  • Start Management Studio and connect to the report server.
  • In Object Explorer, navigate to a report. Reports are located under Home, or in a folder under Home.
  • Expand the report folder.
  • Expand the History folder to view report history.
  • To view an individual snapshot, right-click the snapshot you want to view, and then click View Snapshot. A Web browser opens and displays the snapshot.

Hope that helps.

Q: I developed a clinical progam in SQl server and vb which I’m using it for the last 5 years.At times there is some delay in executing the search commands esp the name and address text boxes.I overcome this problem by backing up the database or transferring it to another computer.Would like to know a permanent remedy for this problem

Kevin Kline says: The problem is that you are not updating index statistics regularly. (You do have indexes on the tables in your database, right?) Index statistics become stale over time and are ove no value when stale. There are two ways to refresh index statistics. The very slow and resource intensive way is to drop and recreate the indexes. The fast and less resource intensive way is to run the UPDATE STATISTICS command against each table in the database.

For my production databases, I create a job that runs every night. The job simply executes UPDATE STATISTICS against all of the user databases in the database. I also create a weekly job that runs during the weekends, when few if any users are on the system, that drops and recreates the clustered index of each table to restore the table fill factor to my preferred value of 75%. I recommend that you do the same.

Q:  How can I find the description of an error in reporting services?

Kevin Kline says:   This is a little unclear since I don’t have your error number and error message for me to give you more help.

In the meanwhile, here’s a general troubleshooting process to follow:

1. Write down the error number. If you don’t have the error number, write down as much of the error as possible.
2. Press Ctrl-PrtSc to save a screenshot of the error msg to the clipboard. Save it in Paint for later reference.
3. Go to http://support.microsoft.com
4. Click the hyperlink “Switch to Advanced Search” in the upper right corner of the frame. Then:
A. In the “Search Product” box, enter SQL Server, SQL Server 2000, or SQL Server 2005.
B. In the “For” box, enter the error number and/or the error message.
C. You should be able to leave the rest of the choices as-is.
D. Make sure that Include boxes are checked: How-to Articles, Downloads, Troubleshooting, Guided Help, MSDN content, TechNet Content.
5. Click Search.

Now, look at the links that are returned and choose the one(s) that work best for your problem.

Hope this helps

Technorati Tags:
SQL Server, reporting services, MSDN, TechNet
Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |