Using DBCC UPDATEUSAGE to correct inaccuracies

Recently a friend of mine asked to help, their SQL Server was not showing correct database free space information even when they have added new data file. As they add new data into their database the previously existing database data files are not getting filled with data, rather the newer data files are getting filled. 

As soon as I heard the issue from him, I knew it was the due to the inaccuracies in the catalog views maintained by the SQL Server. I suggested him to run DBCC UPDATEUSAGE command on his database to correct this inaccuracies. 

In order to show you an example, I simulated the same scenario on my personal server, I added few tables and filled plenty of data into those tables. Later I purged bulk of the data in those tables to simulate the inaccuracies. Below is an example DBCC UPDATEUSAGE command that I ran on one of the tables in my database.  

Note that I used the database name ‘LearnSQLWithBru‘ and the table ‘transaction_history‘ in the above example. 

The DBCC command can be run on a database to correct inaccuracies of all objects in that database or you can also correct the inaccuracies of a single table by including that table name. If you would like to run the command for a particular database, then you can run the following command..

DBCC UPDATEUSAGE ('DATABASENAME')

You can also run the below command to correct the inaccuracies for the current database.

DBCC UPDATEUSAGE (0)

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

SSRS – Setting Page Properties for a Report

In the blog we shall learn how to change Report Properties in SQL Server Reporting Services.

When you are in Business Intelligence Development Studio (in SQL 2005 or 2008, 2008 R2), with the report in design view, in the Menu options go to Reports and choose Report Properties. (as shown below)

The Report Properties window is displayed where you can choose in which measuring units (Inches Vs Centimeters) would you prefer. Apart from that you can choose the orientation of the report whether Portrait (default) or Landscape mode.

You can also choose a various number of the paper sizes from the drop down list (as shown below). Finally you can choose how much margins you want for the report.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

Using sp_delete_backuphistory to delete backup history

You might be aware that system database msdb in SQL Server contains tables related to backup and restore operations of that Instance. Every time a database backup is performed, details regarding that backup is stored in multiple tables in msdb database. This information is useful when you want to restore a database to a point in time using the SQL Server Management Studio. But after certain period, the old data about the backups is not that useful anymore.  

It is common to have few dozens of databases on a SQL Server instance and when they are regularly backed up as part of being able to recover to a most recent point in time. Assuming the transaction log backups are scheduled at every 10 or 15 mins for most part of every day, the number of rows generated due to those backups would be slowly but surely adding up rows in those backup tables in msdb. In order to let database admins contain the size of msdb database, SQL Server provides a System Stored procedure called sp_delete_backuphistory to purge backup history from the underlying backup and restore tables in msdb.

The stored procedure accepts a date parameter @oldest_date. The stored procedure takes the value passed into that parameter and deletes the data in the backup and restore tables up to that date.  Let us assume you have never purged the backup information and the msdb database contains the backup information from the beginning of the SQL instance which was setup a couple of years back, the chances are the msdb database would be already huge in size (depends on how many db’s reside on the instance and how frequent the databases are backed up). In that situation, in order to delete all the backup history before 1/7/2011, you will have to pass the date value 1/7/2011 to the stored procedure as follows.

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '1/7/2011'

It would be good to purge the backup history on a regular basis such as weekly or daily using a SQL Agent job.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post

— Bru Medishetty