manjunathcbhat's blog

T-SQL to Get Default Audit Level of SQL server Instance

I have come across situations where you need to go and look for Default Audit Level for Installed Instances or servers. You can do this by getting info in server instances. Now what if you have around 100 server and instance. For this I could find a easy way and that's a T-SQL function to get the default Audit Level for Instance.

Function:

Get Free Disk Space for SQL server 2000

As a DBA you always need to monitor various metrics of a Database server. Disk space is one such metric you gotta watch out for. Monitoring Disk space for SQL server 2000 is bit difficult through script. Just happened to write a Script for SQL server 2000 which gives you free disk space in GB for all existing drives on database server.

Impersonating Sql User

As a SQL server DBA many times you may get to hear from users that he is not able to access the database, SQL server or any of the objects in SQL server. This may sometimes that the user is passing a wrong credentials or may be is trying to connect to SQL server through some applications but due to some ODBC / OLEDB connectivity problem the application is not able to connect even though user is passing correct credentials. Sometimes the issue may be genuine too that he is actually not having the access.

Cannot use the PAGE granularity hint on the table

This seems to be an very interesting error which one may receive while running DBCC ShrinkDatabase command.

The full error is as follows

Cannot use the PAGE granularity hint on the table "tablename" because locking at the specified granularity is inhibited.

The error occurs when you have one or more non-clustered indexes created on table without a clustered index on table. One should always create a clustered index on table, without which it does not make more sense.

Dropping A Role in SQL server using T-SQL

It happens so that when you drop/delete a Database Role in SQL Server Database, the Role members who were assigned to this role wont get de-associated from this Role. No doubt the role will be dropped/deleted but the member will be having this Role still existing in their permission list. So I came up with the below T-SQL which will first de-associate all the role member's from the role to be dropped and then it will go ahead and drop the role. For Testing Purpose, I have taken Role "db_execute" as the role which needs to be dropped/deleted.

Pages