Restrictions on TEMPDB

• Adding filegroups.
• Backing up or restoring the database.
• Changing collation. The default collation is the server collation.
• Changing the database owner. tempdb is owned by dbo.
• Creating a database snapshot.
• Dropping the database.
• Dropping the guest user from the database.
• Enabling change data capture.
• Participating in database mirroring.
• Removing the primary filegroup, primary data file, or log file.
• Renaming the database or primary filegroup.
• Running DBCC CHECKALLOC.
• Running DBCC CHECKCATALOG.
• Setting the database to OFFLINE.
• Setting the database or primary filegroup to READ_ONLY.

How the TempDB becomes Full?

• DBCC CHECKDB will perform its work in tempdb
• DBCC DBREINDEX or similar DBCC commands with "Sort in Tempdb" option can make the tempdb full
• Large resultsets involving unions, order by, group by, joins, temp tables etc. can also fill up tempdb
• Any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb
• An ODBC DSN with the option 'Create  temporary stored procedures' set can leave objects in tempdb.

Compatibility Levels

• 60 = SQL Server 6.0
• 65 = SQL Server 6.5
• 70 = SQL Server 7.0
• 80 = SQL Server 2000
• 90 = SQL Server 2005
• 100 = SQL Server 2008
 

Database Role Permissions

Please refer the following table for Database Level Permissions.

Role	DML Operations
db_accessadmin	Select, Update, Delete, Insert Not Working
db_backupoperator	Select, Update, Delete, Insert Not Working
db_datareader	Select Working
db_datawriter	Select Not Working     Update, Delete, Insert Working
db_ddladmin	Select, Update, Delete, Insert Not Working
db_denydatareader	Select, Update, Delete, Insert Not Working
db_denydatareader with db_datareader	Select, Update, Delete, Insert Not Working
db_denydatawriter	Select, Update, Delete, Insert Not Working
db_denydatawriter with db_datawriter	Select, Update, Delete, Insert Not Working
db_dbowner	Select, Update, Delete, Insert Working
db_dbowner with db_denydatareader	Select Not Working Update, Delete, Insert Working
db_dbowner with db_denydatawriter	Select Working  Update, Delete, Insert Not Working
db_securityadmin	Select, Update, Delete, Insert Not Working
sysadmin	Select, Update, Delete, Insert Working
sysadmin with db_denydatareader	Select, Update, Delete, Insert Working
sysadmin with db_denydatawriter	Select, Update, Delete, Insert Working

 

Login Created without Default Database

If you create an SQL Server login and if the login is not mapped to any databases, you will not be able to login into the Database Server. It will throw an error indicating Default Database is missing.

SQL Server Jobs Migration

Suppose we are migrating a job from sql server to another through scripts. We have created the scripts and we have applied it on the second server. Jobs are created. But what about the jobs which are created through maintenance plans? It fails. By default maintenance plans will not be created by running job scripts. We have to recreate those jobs in the second server.

Thanks,