In this blog I would like to write on a topic that I see many developers (and few DBA’s too) doing without trying to understand or think about the complete picture of what is affected. The task of making changes to an existing database object such as tables, stored procedures, views etc.
Many times, database objects will have to be modified (altered) at a later time, for a table or view it would be adding new columns or changing the data types of the existing columns. For stored procedures it would be change in logic that needs to be incorporated. There are multiple ways to achieve this, one way is to drop the existing one and create the newer one (most probably for a view or stored procedure), the other way is to alter the existing one. What a user needs to keep in mind is, that when you drop a database object and create the new one, the underlying security permissions get whacked and those users / logins that have been assigned additional permissions to these objects will not have the permissions to carry out their task, they should be reassigned with those permissions on the newly created database.
Hence I feel, it is better to alter the existing one instead of dropping and creating.
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.