DB-REC-MODELS

1) What are system databases?

When we install Microsoft SQL Server, five system databases are automatically created for each SQL Server instance. These system databases allow the database engine and administrative applications to properly manage the system:

• master
• model
• msdb
• tempdb
• Resource (SQL Server 2005 and higher only)

2) What is master database?

The Master database is the heart and soul of SQL Server. It basically records all the system level information. Every instance of SQL Server will have an independent Master database; as it captures instance level configuration information. The information which is captured in the Master database includes SQL Server instance level configurations, linked server configurations, SQL Server Logins, Service Broker Endpoints, System Level Stored Procedures, and System level Functions etc. The system and user databases related information such as name and location for user and system database are captured in Master database.

If master database is corrupted or if it is not available then the SQL Server Service will not start. In SQL Server 2005 and later versions the system objects are stored in Resource Database rather than in Master Database. The Master database is created using Simple Recovery Model.

3) What is model database?

The Model database is basically used as a template when creating databases in SQL Server. Basically SQL Server takes a copy of Model database whenever a user tries to create a new database in SQL Server. This also means that if a user creates any tables, stored procedures, user defined data types or user defined functions within a Model database; then those objects will be available in every newly created database on that particular instance of SQL Server.

If the Model database is damaged or corrupted then SQL Server Service will not start up as it will not be able to create the tempdb database.

4) What is msdb database?

SQL Server Agent uses MSDB database to store information related to the configuration of SQL Server Agent Jobs, Job schedules, Alerts, Operators etc. MSDB also stores information related to configuration of Service Broker, Log Shipping, database backups and restore information, Maintenance Plan Configuration, Configuration of Database Mail, Policy Bases Information of SQL Server 2008 etc.

If the MSDB database is corrupted or damaged then scheduling information used by SQL Server Agent will be lost. This will result in the failure of all scheduled activities.

5) What is tempdb database?

The tempdb database is considered a global resource that all connections and all users can access. The tempdb database holds user-created temporary database objects, such as temporary tables and temporary stored procedures. This database is also used heavily by the database engine to serialize intermediate results and to hold other temporary data and objects that are generated during processing. Tempdb is always recreated from when the SQL Server service is first started. Because tempdb is so heavily used by the system, many system-wide performance optimizations are necessarily focused on making tempdb as efficient as possible.

6) What is resource database?

The Resource database is a read only, hidden system database that contains all the SQL Server system objects such as sys.objects which are physically available only in the Resource database, even though they logically appear in the SYS schema of every database. The Resource Database does not contain any user data or any user metadata. By design, the Resource database is not visible under SQL Server Management Studio’s Object Explorer | Databases | System Databases Node.

The DBA shouldn’t rename or move the Resource Database file. If the files are renamed or moved from their respective locations then SQL Server will not start. The other important thing to be considered is not to put the Resource Database files in a compressed or encrypted NTFS file system folders as it will hinder the performance and will also possibly prevent upgrades.

7) What is the location of system databases?

We can specify the location of system database under Data Root Directory option.

8) What is the location of resource database?

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.

9) What are the typical objects that are created in the TempDB database?

There are three different types of objects stored in tempdb.

• Internal Objects:

Intermediate runs for sort.

1. Intermediate results for hash join and hash aggregates.
2. XML variables or other large object (LOB) data type variables. (text, image, ntext, varchar(max), varbinary(max))
3. Queries that need a spool to store intermediate results.
4. Keyset cursors to store the keys.
5. Static cursors to store a query result.
6. Service Broker to store messages in transit.
7. INSTEAD OF triggers to store data for internal processing.
8. DBCC CHECK internally uses a query that may need to spool intermediate results.
9. Query notification and event notification use Service Broker.

• Version Store:

Snapshot Isolation / Read Committed Snapshot Islotaion

1. Triggers (After Triggers). Instead of triggers doesn’t generate versions.
2. MARS (Multiple Active Result Sets)
3. Index Rebuilds

• User Objects:

User defined tables and indexes

1. Local and global temporary tables, bulk insert and BCP intermediate results
2. Index rebuilds with “SORT IN TEMPDB” option.

10) What is the database that has the backup and restores system tables?  What are the backup and restore system tables?  What do each of the tables do?

The MSDB database is the database with the backup and restores system tables. Here are the backup and restore system tables and their purpose:

backupfile – contains one row for each data file or log file backed up

backupmediafamily – contains one row for each media family

backupmediaset – contains one row for each backup media set

backupset – contains one row for each backup set

restorefile – contains one row for each restored file

restorefilegroup – contains one row for each restored filegroup

restorehistory – contains one row for each restore operation

11) How to check the version of Resource database?

SELECT SERVERPROPERTY('ResourceVersion');

GO

12) How to determine when the Resourcedatabase was last updated?

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

GO

13) What are the system databases created when we configure Reporting services?

ReportServer and ReportServerTempdb

14) How to move model database?

a. Determine the logical file names of the msdb and model database and their current physical location on the disk.

USE master

Go

SELECT DB_NAME(database_id)  AS "Database Name", name AS "Logical File Name",

physical_name AS "Physical File Location",

state_desc AS "State"

FROM sys.master_files WHERE database_id IN (DB_ID(N'model'))

Go

b. For each file to be moved, run the following statement by providing the logical file name and the new physical location of where the file will be moved to.

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

c. Stop the instance of SQL Server.

d. Move the physical files to the new location.

e. Start the SQL Server instance.

f. Verify the location using the script used in step a.

15) How to move msdb database?

a. Determine the logical file names of the msdb and model database and their current physical location on the disk.

USE master

Go

SELECT DB_NAME(database_id)  AS "Database Name", name AS "Logical File Name",

physical_name AS "Physical File Location",

state_desc AS "State"

FROM sys.master_files WHERE database_id IN (DB_ID(N'msdb'))

Go

b. For each file to be moved, run the following statement by providing the logical file name and the new physical location of where the file will be moved to.

ALTER DATABASE msdb MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

c. Stop the instance of SQL Server.

d. Move the physical files to the new location.

e. Start the SQL Server instance.

f. Verify the location using the script used in step a.

g. Verify that Service Broker is enabled for the msdb database by running the following query.

USE master

Go

SELECT  is_broker_enabled FROM    sys.databases

WHERE   database_id = DB_ID(N'msdb');

Go

If is_broker_enabled is not 1 then enable the service broker.

ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Go

16) How to move tempdb database?

• a. Determine the logical file names of the msdb and model database and their current physical location on the disk.

USE master

Go

SELECT DB_NAME(database_id)  AS "Database Name", name AS "Logical File Name",

physical_name AS "Physical File Location",

state_desc AS "State"

FROM sys.master_files WHERE database_id IN (DB_ID(N'msdb'))

Go

b. For each file to be moved, run the following statement by providing the logical file name and the new physical location of where the file will be moved to.

ALTER DATABASE tempdb MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

c. Restart the instance of SQL Server.

d. Verify the location using the script used in step a.

e. delete the old Tempdb database files from the old location.

 17) How to move master database?

1. Open SQL Server Configuration Manager.
2. In the SQL Server Services node, right-click the instance of SQL Server for example,   and choose Properties.
3. Open Startup Parameters dialog.

4. Edit the Startup Parameters values to point to the planned location for the master database data and log files,     and click OK. Moving the error log file is optional.

• In theSQL Server (instance_name) Properties dialog box, click the Startup Parameters
• The parameter value for the data file must follow the-d parameter and the value for the log file must follow the -l  The following example shows the parameter values for the default location of the master data and log files.

-dC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf -eC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\ERRORLOG -lC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

• If the planned relocation for the master data and log file isF:\MSSQL\Data, the parameter values would be changed as follows:

-dF:\MSSQL\Data\master.mdf -eC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\ERRORLOG -lF:\MSSQL\Data\mastlog.ldf

5. Stop the instance of SQL Server.

C:\> net stop SQLSERVERAGENT[SQLPROD1] C:\> net stop MSSQLSERVER[SQLPROD1

6. Move the master.mdf and mastlog.ldf files to the new location.

C:\> move “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf” F:\MSSQL\Data\ 1 file(s) moved.[SQLPROD1] C:\> move “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf” F:\MSSQL\Data\ 1 file(s) moved.

7. Restart the instance of SQL Server.

C:\> net start MSSQLSERVER[SQLPROD1] C:\> net start SQLSERVERAGENT[SQLPROD1]

8. Verify the file change for the master database.

18) Is it important to take the system database backups?

Yes, It is very important to take system database backups except tempdb.

19) Is it possible to take the tempdb database backup?

No, it’s not possible to take tempdb backup and it’s not required.

20) How to restore model database?

model Database can be restores same as any other user defined database from its backup.

RESTORE DATABASE [model] FROM  DISK = N'E:\model.bak'

WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 10

GO

21) How to restore msdb database?

• Stop SQL Server agent service
• Run the below command to restore the msdb database.

RESTORE DATABASE [msdb] FROM  DISK = N'E:\msdb.bak' 

WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10 

GO

• Start SQL Server agent service

22) How to restore master database?

• Start the SQL Server Instance in single user mode using –m parameter in the SQL Server configuration manager for SQL Service.
• Issue the below command from command prompt to restore the master database.

23) Is it possible to rename any of system databases?

No, it’s not possible to rename any system database.

24) What is the default owner of master, model, tempdb and msdb database? Is it possible to change the owner of system databases?

Default owner of system databases is sa, We can’t change the default owner of master, model, tempdb and distributor databases.

25) What are the recommended Data files settings for tempdb?

• Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents tempdb from expanding too frequently, which can affect performance. The tempdb database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions.
• Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs.
• Make each data file the same size; this allows for optimal proportional-fill performance.
• Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.
• Put the tempdb database on disks that differ from those that are used by user databases.

26) What is recovery model in SQL Server?

SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available.

27) How many types of Recovery models we have in SQL Server?

Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time. 

28) Difference between Full, Bulk Logged and Simple recovery model?

Recovery model	Description	Work loss exposure	Recover to point in time?
Simple	No log backups. (Only Full and Differential backups)Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.	Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone.	Can recover only to the end of a backup.
Full	Requires log backups. No work is lost due to a lost or damaged data file. Can recover to an arbitrary point in time (for example, prior to application or user error).	Normally none.If the tail of the log is damaged, changes since the most recent log backup must be redone..	Can recover to a specific point in time, assuming that your backups are complete up to that point in time.
Bulk logged	Requires log backups. An adjunct of the full recovery model that permits high-performance bulk copy operations. Reduces log space usage by using minimal logging for most bulk operations. For more information, see	If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone. Otherwise, no work is lost.	Can recover to the end of any backup. Point-in-time

29) How can you setup a default recovery model for all newly created databases as simple?

We can setup the recovery model of model database as simple to make sure that newly created database’s recovery is automatically configured as simple.

30) How to change the recovery model of a database?

USE [master] 

GO 

ALTER DATABASE [AdventureWorks] SET RECOVERY SIMPLE 

GO

Note: It is always advisable to take a full backup of the database immediatelly after changing the database recovery model.

31) Recovery model of system databases?

System database	Recovery model	Comments
master	Simple	For backwards compatibility with earlier versions of Microsoft SQL Server, the recovery model of master can be set to FULL or BULK_LOGGED. However, BACKUP LOG is not supported for master. Therefore, even if the recovery model of master is changed to full or bulk-logged, the database continues to operate as if it were using the simple recovery model.
model	User configurable	Newly created user databases use the same recovery model as the model database. If you want your new databases to use the simple recovery model, change the recovery model of model to SIMPLE. Best practice:  We recommend that you create only full database backups of model, as required. Because model is small and rarely changes, backing up the log is unnecessary.
msdb	Simple (default)	If you want to use the backup and restore history information in msdb when you recover user databases, we recommend that you use the full recovery model for msdb. Additionally, consider placing the msdb transaction log on fault tolerant storage.
Resource	—	The recovery model is irrelevant. SQL Server backup cannot back up the Resource database. Note You can perform a file-based or a disk-based backup on the Resource database by treating Mssqlsystemresource.mdf as if it were a binary (.exe) file. But you cannot use SQL Server restore on these backups.
tempdb	Simple	The simple recovery model is required; therefore, tempdb log space is always automatically reclaimed. You cannot backup tempdb.

1. What are the basic functions for master, msdb, model, tempdb databases?

The Master database contains catalog and data for all databases of the SQL Server instance and it holds the engine together. Because SQL Server cannot start if the master database is not working.

The msdb database contains data of database backups, SQL Agent, DTS packages, SQL Server jobs, and log shipping.

The tempdb contains temporary objects like global and local temporary tables and stored procedures.

The model is a template database which is used for creating a new user database. 

2 Which database will affect if we install service pack in SQL Server?

A Resource Database

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server. 

3 As a database is recovering, after which phase will the database be available /online?

A (SQL Server 2000): After the Undo phase.

A (SQL Server 2005): In all editions but Enterprise, after the Undo phase (if running FULL recovery model). With Enterprise edition, after the Redo phase. Fast recovery is possible because transactions that were uncommitted when a crash occurred reacquire whatever locks they held before the crash. While these transactions are being rolled back, their locks protect them from interference by users. 

4 How to Recover Suspected Database without data loss?

EXEC sp_resetstatus [Your_Database];

ALTER DATABASE [Your_Database] SET EMERGENCY

DBCC checkdb ([Your_Database])

ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Note: Instead of above query DBCC CheckDB ([Your_Database],

REPAIR_ALLOW_DATA_LOSS)

Use below parameters.

ALTER DATABASE [Your_Database] SET MULTI_USER

REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD

Specifies that DBCC CHECKDB repair the found errors. The specified database must be in single-user mode to use one of the following repair options.

REPAIR_ALLOW_DATA_LOSS

Tries to repair all reported errors. These repairs can cause some data loss.

REPAIR_FAST

Maintains syntax for backward compatibility only. No repair actions are performed.

REPAIR_REBUILD

Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index. 

5.  TempDB growing fastly? What will be the reason?

Check what is causing the TempDB to grow by running the following queries

Select * from sys.dm_db_file_space_usage

Select * from sys.dm_db_session_file_usage

Select * from sys.dm_db_task_space_usage

In my experience, the most likely cause for something like this is an out-of-control Cross Join query (though there are lots of other possibilities).

There are a couple of possible reasons for TempDB filling up. The most obvious is of course a process that fills up a large temp table.

One way to investigate this, would be to do:

SELECT * FROM TempDB. Sysobjects (NOLOCK) WHERE xtype = 'U'

This tells you, if there are any temp tables at all right now. You might be able to get an indication of sizes by looking in sysindexes:

SELECT object name (id), reserved FROM TempDB. Sysindexes (NOLOCK) WHERE in did in (0, 1)

reserved gives you the size in 8192 KB pages. But the numbers may lag behind and not be accurate.

There are other sources than temp tables: sort operations, cursors, worktables for queries. These are more difficult to track down.

You could shrink TempDB, and trace for auto-grow events, and see if you can connect the events to a certain user or host. 

6.  Possible reasons for TempDB Full Issue

SQL Server makes extensive use of TempDB when:

·         Tracking versions for row-versioning concurrency.

·         Performing bulk load operations on tables with triggers enabled.

·         Running DBCC CHECKDB.

·         Rebuilding an index with SORT_IN_TEMPDB option.

·         Variables of LOB data types.

·         Storing intermediate query results, for example, during joins, aggregates, or sorts.

·         Service broker dialog information.

·         Caching temporary objects and tables.

·         Storing inserted and deleted tables in triggers.

·         Running sp_xml_preparedocument.

·         If you’re SQL Server Agent Job doing any of this then this will the cause for TempDB growth. I suggest you to read my article ie. Recovering from out-of-disk space conditions for TempDB. This will hopefully help you to resolve this issue.

·         Another potential problem that you need to tackle as a DBA is running out of disk space on the hard disk that contains tempdb.This is because SQL Server makes extensive use of tempdb when:

Viewing tempdb space usage

The following dynamic management views (DMVs) can be used to report information about tempdb space:

-sys.dm_db_session_space_usage – Retrieves the number of pages allocated and deallocated by each session for the database.

-sys.dm_db_task_space_usage – Retrieves the page allocation and deallocation activity by task for the database.

-sys.dm_db_file_space_usage – Retrieves information about disk space used by any database. 

7 Why is TempDB full, and how can I prevent this from happening? 

Note that the majority of this article describes symptoms and workarounds for any database that is larger than you think it should be; it is not applicable only to tempdb.  

SQL Server allocates a database called tempdb, primarily for worktable / #temp table usage. Sometimes, you will have one of the following symptoms: 

§ An error message in the event log: Source: MSSQLSERVER Event ID: 17052 Description: The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space 

§ An error message in Query Analyzer:   

Server: Msg 8624, Level 16, State 1 Internal SQL Server error or Server: Msg 1101, Level 17, State 10, Line 1 Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.

§ Or you will notice that the files are much bigger than they should be -- by using EXEC sp_spaceused, looking at the taskpad view in Enterprise Manager, seeing the MDF/LDF files themselves within Windows Explorer, or being alerted by monitoring software like SiteScope or Quest Spotlight. 

Causes Usually, tempdb fills up when you are low on disk space, or when you have set an unreasonably low maximum size for database growth. 

Many people think that tempdb is only used for #temp tables. When in fact, you can easily fill up tempdb without ever creating a single temp table. Some other scenarios that can cause tempdb to fill up: 

§  any sorting that requires more memory than has been allocated to SQL Server will be forced to do its work in tempdb; 

§  if the sorting requires more space than you have allocated to tempdb, one of the above errors will occur;  

§  DBCC CheckDB('any database') will perform its work in tempdb -- on larger databases, this can consume quite a bit of space;  

§  DBCC DBREINDEX or similar DBCC commands with 'Sort in tempdb' option set will also potentially fill up tempdb;  

§  large resultsets involving unions, order by / group by, cartesian joins, outer joins, cursors, temp tables, table variables, and hashing can often require help from tempdb;   

§  any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb;  

§  use of an ODBC DSN with the option 'create temporary stored procedures' set can leave objects there for the life of the connection. 

Other points of analysis The following will tell you how tempdb's space is allocated:  

USE tempdb GO EXEC sp_spaceused

The following should give you some clues as to which table(s) consume most of the space in the data file(s) -- this will help you narrow down any transactions that are either taking a long time or repeatedly being left in limbo:  

USE tempdb GO SELECT name     FROM tempdb..sysobjects SELECT OBJECT_NAME(id), rowcnt     FROM tempdb..sysindexes     WHERE OBJECT_NAME(id) LIKE '#%'     ORDER BY rowcnt DESC

The higher rowcount values will likely indicate the biggest temporary tables that are consuming space. And while it won't tell you everything, since tempdb is used for internal I/O and other processes such as sorting, it may help you narrow down the stored procedure(s) that are causing the growth (you can query INFORMATION_SCHEMA.ROUTINES for ROUTINE_DEFINITION LIKE '%#table_name%' from above).  In addition to this, you can use Profiler to watch for events like database file auto grow and log file auto grow. If this is happening often, then you know that the space you've allocated to tempdb is not sufficient. You can also watch performance monitor's counter for PhysicalDisk: CurrentDiskQueueLength on the drive where tempdb exists. If this number is consistently greater than 2, then there is likely a bottleneck in disk I/O.   

Short-term fix Restarting SQL Server will re-create tempdb from scratch, and it will return to its usually allocated size. In and of itself, this solution is only effective in the very short term; assumedly, the application and/or T-SQL code which caused tempdb to grow once, will likely cause it to grow again.  

To shrink tempdb, you can consider using DBCC ShrinkDatabase, DBCC ShrinkFile (for the data or the log file), or ALTER DATABASE. See KB #256650, KB #272318 and KB #307487 for more information. If you can't shrink the log, it might be due to an uncommitted transaction. See if you have any long-running transactions with the following command:  

DBCC OPENTRAN -- or DBCC OPENTRAN('tempdb') 

Check the oldest transaction (if it returns any), and see who the SPID is (there will be a line starting with 'SPID (Server Process ID) : <number>'). Use that <number> in the following: 

DBCC INPUTBUFFER(<number>) 

This will tell you at least a portion of the last SQL command executed by this SPID, and will help you determine if you want to end this process with:  

KILL <number>  

Long-term prevention Here are some suggestions for maintaining a healthy tempdb: 

§  Make sure that tempdb is set to autogrow -- do *NOT* set a maximum size for tempdb. If the current drive is too full to allow autogrow events, then buy a bigger drive, or add files to tempdb on another device (using ALTER DATABASE) and allow those files to autogrow. You will need at least one data file and at least one log file in order to avoid this problem from halting your system in the future.  

§  For optimal performance, make sure that its initial size is adequate to handle a typical workload (autogrow events can cause performance to suffer as it allocates new extents).

§  If possible, put tempdb on its own physical disk, array or disk subsystem (see KB #224071 for more information).  

§  To prevent tempdb log file growth, make sure tempdb is in simple recovery mode (this allows the log to be truncated automatically). To check if this is the case:  

-- SQL Server 7.0, should show 'trunc. log on chkpt.' -- or 'recovery=SIMPLE' as part of status column: EXEC sp_helpdb 'tempdb' -- SQL Server 2000, should yield 'SIMPLE': SELECT DATABASEPROPERTYEX('tempdb', 'recovery')

§  If the database is not set to simple recovery, you can force it so as follows:   

ALTER DATABASE tempdb SET RECOVERY SIMPLE

§  Use SQLOLEDB, not ODBC / DSN for database access (for VB / ASP, see Article #2126 for sample connection strings).  

§  Try to make sure you have covering indexes for all large table that are used in queries that can't use a clustered index / index seek.  

§  Batch larger heavily-logged operations (especially deletes) that *might* overflow into tempdb into reasonable 'chunks' of rows, especially when joins are involved.  

§  Pore over your code for potential uncommitted transactions and other elements from the list at the top of the page.  

§  In general, try to make your code as efficient as possible... avoid cursors, nested loops, and #temp tables if possible. See Article #2424 for some other general ideas on efficiency. 

§  Check out the WebCast in KB #834846 for some ideas from Microsoft about administering and maintaining TempDB. 

31. Datafile is growing fastly. What will be reasons? How you resolve it/ 

Data may be refreshing that is wt data file growing fastly 

-Shrink the file

-That is only when Datafile size below 80%

-Otherwise add one more file different location

32.  Can we add file on model?
The master and model databases cannot have files added to them. ALTER DATABASE was aborted.