Some quite useful MSSQL notes here.
Simple Recovery Model vs Full Recover Model
Simple Mode
No log backups. Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.
Full Mode
Full Recovery Mode on a database means that we intend to be able to recover to a point in time in the event of a failure. This means we plan on using a combination of Full Backups and Transaction Log Backups (and possibly differentials).
SQL Server understands our intent, and it will not truncate (free up space within the file.. notice the file stays the same, I didn’t say shrink, I said truncate. Truncate frees space within a file, shrink removes that “free” space to make the physical file smaller) the log file(s) of our database (the .LDF files). Instead, they will continue to grow until such time as you take a transaction log backup.
It’s the best explanation I found.
Why Should One Shrink Logs Before Creating a Backup
As Microsoft (and life practice) say, it might take a long time to restore a database in Microsoft SQL Server 2008/R2 or in Microsoft SQL Server 2012 and to build the Virtual Log File (VLF) list if there are many VLFs in the database.
SQL Server cannot move log records from the end of the log file toward the beginning of the log file. This means that SQL Server can only cut down the file size if the file is empty at the end of the file. The end-most log record sets the limit of how much the transaction log can be shrunk. A transaction log file is shrunk in units of Virtual Log Files (VLF).
Get the Size of T-Logs for All Databases
To see the size of the transaction logs as well as how much space is being used:
DBCC SQLPERF(logspace)
Get Physical Paths of Databases
USE master SELECT name, physical_name, size FROM sys.master_files
Get Databases Log File Name and Log Size
USE database_name;
EXEC sp_helpfile
Shrink Logs
USE database_name; ALTER DATABASE database_name SET RECOVERY SIMPLE; DBCC SHRINKFILE (database_log_name, 1); ALTER DATABASE database_name SET RECOVERY FULL;
Rebuild Table Indexes
Rebuild process drops the existing index and recreates the index.
USE database_name; ALTER INDEX ALL ON dbo.table_name REBUILD
Reorganise Table Indexes
Reorganise process physically reorganises the leaf nodes of the index.
USE database_name; ALTER INDEX ALL ON dbo.table_name REORGANIZE
General Recommendation
Index should be rebuilt when index fragmentation is great than 40%. Index should be reorganised when index fragmentation is between 10% to 40%.
Index rebuilding process uses more CPU and it locks the database resources, unless SQL server supports ONLINE option. ONLINE option will keep index available during the rebuilding.
Check SQL Server a Specified Database Index Fragmentation Percentage
USE database_name;
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent DESC
SQL query was taken from: http://gallery.technet.microsoft.com/scriptcenter/Check-SQL-Server-a-a5758043