The Service Broker in database "<Database Name>" cannot be enabled because there is already an enabled Service Broker with the same ID.

You receive the below error when you try to enable service broker on a database.

Msg 9772, Level 16, State 1, Line 1 The Service Broker in database "<Database Name>" cannot be enabled because there is already an enabled Service Broker with the same ID. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

This usually happens

1. When the service broker is enabled on a database and it is overwritten and then you try to enable the service broker on it again
2. When the service broker is enabled on a database and it is renamed and another database with the same name is created and then you try to enable the service broker on it again.

To fix this error, run the below command which will generate the new ID for the service broker.

ALTER DATABASE <Database Name> SET NEW_BROKER

Different ways to make a table read-only

There may be many cases where a tables needs to be made as read-only in order to maintain the existing data. This can be achieved using many ways and out of which the common methods are

• Deny Permission
• Put the table on read only file group
• Create trigger instead of insert, update, delete
• Make the database read-only
• Create a Column Store Index

When was a Database Deleted and by whom

Here is a T-SQL query which gives information on who and when was a database deleted.

SELECT name AS EventName, DatabaseName, StartTime, LoginName
    FROM sys.traces T CROSS Apply 
 fn_trace_gettable(CASE WHEN CHARINDEX('_', [path]) <> 0
                           THEN SUBSTRING(PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'
                           ELSE [path]
                         END, max_files) TT
    JOIN sys.trace_events TE ON TT.EventClass = TE.trace_event_id
WHERE name like '%Deleted%' 
 AND ObjectName IS NULL 
 AND EventSubClass = 1
ORDER BY StartTime DESC 

Size of a Backup is more than the Database

Recently one of my Junior DBA came up with a question to me relating to the size of the Backups.
The question was, How will the size of the backup be more than the database size?

We all know that the size of the backup will be less than or equal to the size of the database and no chance of increasing.
So what could be wrong in this case?

Find list of Reserved Keywords used as column names or table names

It is always good not to use any reserve keywords as any of the user objects.
If you suspect that your database has reserved keywords as column names or table names then the below query will help you find out.

Use <DatabaseName> 
GO 
DECLARE @ReservedKeyWords Table (KeyWords VARCHAR(100))
INSERT INTO @ReservedKeyWords
SELECT 'ADD' UNION
SELECT 'EXTERNAL' UNION
SELECT 'PROCEDURE' UNION
SELECT 'ALL' UNION
SELECT 'FETCH' UNION
SELECT 'PUBLIC' UNION
SELECT 'ALTER' UNION
SELECT 'FILE' UNION
SELECT 'RAISERROR' UNION
SELECT 'AND' UNION
SELECT 'FILLFACTOR' UNION
SELECT 'READ' UNION
SELECT 'ANY' UNION
SELECT 'FOR' UNION
SELECT 'READTEXT' UNION
SELECT 'AS' UNION
SELECT 'FOREIGN' UNION
SELECT 'RECONFIGURE' UNION
SELECT 'ASC' UNION
SELECT 'FREETEXT' UNION
SELECT 'REFERENCES' UNION
SELECT 'AUTHORIZATION' UNION
SELECT 'FREETEXTTABLE' UNION
SELECT 'REPLICATION' UNION
SELECT 'BACKUP' UNION
SELECT 'FROM' UNION
SELECT 'RESTORE' UNION
SELECT 'BEGIN' UNION
SELECT 'FULL' UNION
SELECT 'RESTRICT' UNION
SELECT 'BETWEEN' UNION
SELECT 'FUNCTION' UNION
SELECT 'RETURN' UNION
SELECT 'BREAK' UNION
SELECT 'GOTO' UNION
SELECT 'REVERT' UNION
SELECT 'BROWSE' UNION
SELECT 'GRANT' UNION
SELECT 'REVOKE' UNION
SELECT 'BULK' UNION
SELECT 'GROUP' UNION
SELECT 'RIGHT' UNION
SELECT 'BY' UNION
SELECT 'HAVING' UNION
SELECT 'ROLLBACK' UNION
SELECT 'CASCADE' UNION
SELECT 'HOLDLOCK' UNION
SELECT 'ROWCOUNT' UNION
SELECT 'CASE' UNION
SELECT 'IDENTITY' UNION
SELECT 'ROWGUIDCOL' UNION
SELECT 'CHECK' UNION
SELECT 'IDENTITY_INSERT' UNION
SELECT 'RULE' UNION
SELECT 'CHECKPOINT' UNION
SELECT 'IDENTITYCOL' UNION
SELECT 'SAVE' UNION
SELECT 'CLOSE' UNION
SELECT 'IF' UNION
SELECT 'SCHEMA' UNION
SELECT 'CLUSTERED' UNION
SELECT 'IN' UNION
SELECT 'SECURITYAUDIT' UNION
SELECT 'COALESCE' UNION
SELECT 'INDEX' UNION
SELECT 'SELECT' UNION
SELECT 'COLLATE' UNION
SELECT 'INNER' UNION
SELECT 'SEMANTICKEYPHRASETABLE' UNION
SELECT 'COLUMN' UNION
SELECT 'INSERT' UNION
SELECT 'SEMANTICSIMILARITYDETAILSTABLE' UNION
SELECT 'COMMIT' UNION
SELECT 'INTERSECT' UNION
SELECT 'SEMANTICSIMILARITYTABLE' UNION
SELECT 'COMPUTE' UNION
SELECT 'INTO' UNION
SELECT 'SESSION_USER' UNION
SELECT 'CONSTRAINT' UNION
SELECT 'IS' UNION
SELECT 'SET' UNION
SELECT 'CONTAINS' UNION
SELECT 'JOIN' UNION
SELECT 'SETUSER' UNION
SELECT 'CONTAINSTABLE' UNION
SELECT 'KEY' UNION
SELECT 'SHUTDOWN' UNION
SELECT 'CONTINUE' UNION
SELECT 'KILL' UNION
SELECT 'SOME' UNION
SELECT 'CONVERT' UNION
SELECT 'LEFT' UNION
SELECT 'STATISTICS' UNION
SELECT 'CREATE' UNION
SELECT 'LIKE' UNION
SELECT 'SYSTEM_USER' UNION
SELECT 'CROSS' UNION
SELECT 'LINENO' UNION
SELECT 'TABLE' UNION
SELECT 'CURRENT' UNION
SELECT 'LOAD' UNION
SELECT 'TABLESAMPLE' UNION
SELECT 'CURRENT_DATE' UNION
SELECT 'MERGE' UNION
SELECT 'TEXTSIZE' UNION
SELECT 'CURRENT_TIME' UNION
SELECT 'NATIONAL' UNION
SELECT 'THEN' UNION
SELECT 'CURRENT_TIMESTAMP' UNION
SELECT 'NOCHECK' UNION
SELECT 'TO' UNION
SELECT 'CURRENT_USER' UNION
SELECT 'NONCLUSTERED' UNION
SELECT 'TOP' UNION
SELECT 'CURSOR' UNION
SELECT 'NOT' UNION
SELECT 'TRAN' UNION
SELECT 'DATABASE' UNION
SELECT 'NULL' UNION
SELECT 'TRANSACTION' UNION
SELECT 'DBCC' UNION
SELECT 'NULLIF' UNION
SELECT 'TRIGGER' UNION
SELECT 'DEALLOCATE' UNION
SELECT 'OF' UNION
SELECT 'TRUNCATE' UNION
SELECT 'DECLARE' UNION
SELECT 'OFF' UNION
SELECT 'TRY_CONVERT' UNION
SELECT 'DEFAULT' UNION
SELECT 'OFFSETS' UNION
SELECT 'TSEQUAL' UNION
SELECT 'DELETE' UNION
SELECT 'ON' UNION
SELECT 'UNION' UNION
SELECT 'DENY' UNION
SELECT 'OPEN' UNION
SELECT 'UNIQUE' UNION
SELECT 'DESC' UNION
SELECT 'OPENDATASOURCE' UNION
SELECT 'UNPIVOT' UNION
SELECT 'DISK' UNION
SELECT 'OPENQUERY' UNION
SELECT 'UPDATE' UNION
SELECT 'DISTINCT' UNION
SELECT 'OPENROWSET' UNION
SELECT 'UPDATETEXT' UNION
SELECT 'DISTRIBUTED' UNION
SELECT 'OPENXML' UNION
SELECT 'USE' UNION
SELECT 'DOUBLE' UNION
SELECT 'OPTION' UNION
SELECT 'USER' UNION
SELECT 'DROP' UNION
SELECT 'OR' UNION
SELECT 'VALUES' UNION
SELECT 'DUMP' UNION
SELECT 'ORDER' UNION
SELECT 'VARYING' UNION
SELECT 'ELSE' UNION
SELECT 'OUTER' UNION
SELECT 'VIEW' UNION
SELECT 'END' UNION
SELECT 'OVER' UNION
SELECT 'WAITFOR' UNION
SELECT 'ERRLVL' UNION
SELECT 'PERCENT' UNION
SELECT 'WHEN' UNION
SELECT 'ESCAPE' UNION
SELECT 'PIVOT' UNION
SELECT 'WHERE' UNION
SELECT 'EXCEPT' UNION
SELECT 'PLAN' UNION
SELECT 'WHILE' UNION
SELECT 'EXEC' UNION
SELECT 'PRECISION' UNION
SELECT 'WITH' UNION
SELECT 'EXECUTE' UNION
SELECT 'PRIMARY' UNION
SELECT 'WITHIN GROUP' UNION
SELECT 'EXISTS' UNION
SELECT 'PRINT' UNION
SELECT 'WRITETEXT' UNION
SELECT 'EXIT' UNION
SELECT 'PROC'

SELECT OBJECT_NAME(object_id) AS TableName
         ,name AS ColumnName
         ,column_id AS ColumnID 
FROM sys.columns WHERE object_id in (SELECT object_id FROM sys.objects WHERE type_desc='USER_TABLE')
AND name IN (select KeyWords from @ReservedKeyWords) 

SELECT name as TableName from sys.objects  WHERE type_desc='USER_TABLE' 
AND name IN (select KeyWords from @ReservedKeyWords) 

Mockup data - 70% off this week for SansSQL Readers

What is MockupData

MockupData is a a software designed for Windows that generates a large quantity of realistic data for testing and demonstration purposes. Names will look like names, addresses will look like addresses, and phone numbers will look like phone numbers.

Why MockupData?

First off, large quantities of realistic data make test runs more realistic than just a handful of lines filled with random gibberish. Functional testing catches more bugs, saving you and your company both time and money. Plus filling your databases with a large volume of data allows for stress and performance testing to take place.

Secondly, real looking data makes more sense to anyone inspecting the application. Testing teams and company representatives will love seeing endless lines of real data, rather than a couple lines of random keystrokes. It simply makes everyone’s job easier.

MockupData will always be able to populate your databases when you have no previous information available (such as for a system in development) or privacy restrictions prevent you from using current information.

Finally, if you ever find yourself presenting your software to potential clients, you won’t want to pass up this software. How many clients have passed on your product simply because it had a “raw”, “unfinished” look to it due to fake data?

MockupData will help you make the best impression when selling your software to businesses. They want to see a finished product. They want to see what the software will look like with thousands of their clients inputted into the system. You simply can’t show them realistic results with fake data.

Click here to read more about MockupData
To get the Offer code write to sandeshsegu@sanssql.com. This exclusive Offer is valid till December 8th, 2013.