Adding tempdb files

Best Practices are -

1. Number of Physical tempdb files = number of processors
2. All tempdb files should have same initial size and unrestricted growth.

More best practices -

http://msdn.microsoft.com/en-us/library/ms175527.aspx

Syntax to add -

ALTER DATABASE tempdb
ADD FILE
(
NAME = tempdev5,
FILENAME = 'F:\SQLDatabases\tempdb\tempdev5.ndf',
SIZE = 14000MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100MB
),
(
NAME = tempdev6,
FILENAME = 'F:\SQLDatabases\tempdb\tempdev6.ndf',
SIZE = 14000MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100MB
),
(
NAME = tempdev7,
FILENAME = 'F:\SQLDatabases\tempdb\tempdev7.ndf',
SIZE = 14000MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100MB
)

GO

Getting Performance Metrics

To get the information on File level fragmentation - Use following commands
Following command will analyze the file level fragmentation -

Defrag -a

Same defrag command can be used to defragment files.

To find missing statistics run below query - SELECT O.name AS [table name], I.rowmodctr AS [Rows Modified] FROM SYSOBJECTS o JOIN SYSINDEXES I ON O.id = I.id WHERE I.rowmodctr > 0 and O.xtype = 'U' ORDER BY I.rowmodctr DESC

Following the query to get report for Index defrag scan percentage from 10 to 99% for various indexes.

use
GO
Select [Object_id] AS ObjectId,
index_id AS IndexId,
avg_fragmentation_in_percent AS FragPercent,
partition_number AS PartitionNumber,
index_type_desc AS IndexType,
alloc_unit_type_desc AS AllocUnitType,
0 AS Online
FROM sys.dm_db_index_physical_stats (db_id(''), NULL, NULL, NULL, 'LIMITED')
WHERE
avg_fragmentation_in_percent > 10.0 AND
index_id > 0 and page_count > 32
ORDER BY ObjectId

Useful SQL Links

http://sqlskills.com/blogs/kimberly/


http://www.microsoft.com/events/default.mspx

Restore Job Fails in SQL Server 2005 with error 3159

Error MesaThe tail of the log for the database "%ls" has not been backed up. Use BACKUP LOG WITH NORECOVERY to back up the log if it contains work that you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

Here the scenario was , we were robo copying the database from other server to local drive and restoring the existing database
Step leading for failure:

EXEC master.dbo.xp_restore_database @database='ABCD', @filename = 'E:\MSSQL\Bak\ABCD.sls'

Step replaced to trouble shoot it
EXEC master.dbo.xp_restore_database @database='ABCD', @filename = 'E:\MSSQL\Bak\ABCD.sls'
@with='MOVE "ABCD_DATA" TO "H:\MSSQL\Data\ABCD.mdf"', @with='MOVE "Configuration_LOG" TO "O:\MSSQL\Data\ABCD.ldf"',@with = 'REPLACE'

Explaination: In most cases, under the full or bulk-logged recovery models, SQL Server 2005 requires that you back up the tail of the log to capture the log records that have not yet been backed up. A log backup taken of the tail of the log just before a restore operation is called a tail-log backup.
When you are recovering a database to the point of a failure, the tail-log backup is the last backup of interest in the recovery plan. If you cannot back up the tail of the log, you can recover a database only to the end of the last backup that was created before the failure.
SQL Server 2005 usually requires that you take a tail-log backup before you start to restore a database. The tail-log backup prevents work loss and keeps the log chain intact. However, not all restore scenarios require a tail-log backup. You do not have to have a tail-log backup if the recovery point is included in an earlier log backup, or if you are moving or replacing (overwriting) the database. Also, if the log files are damaged and a tail-log backup cannot be created, you must restore the database without using a tail-log backup. Any transactions committed after the latest log backup are lost