Database With Fun (While 1 = 1)

If you get the error message for Tempdb Transaction log is full. Msg 9002, Level 17, State 4, Procedure sp_helpdb, Line 19 The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases Solution: 1 -- First check Tempdb Tran log file size using  dbcc sqlperf(logspace) -- tempdb 999.9922 99.99628 0 USE MASTER GO ALTER DATABASE TEMPDB MODIFY FILE (NAME='templog', SIZE=1500MB) Solution:2   ALTER DATABASE Tempdb  ADD LOG FILE  ( NAME = tempdblog2, FILENAME = 'E:\MSSQL.1\MSSQL\DATA\tempdblog2.ldf', SIZE = 10MB, MAXSIZE = 100MB, FILEGROWTH = 10MB)

This script is for SQL Server 2008 Enterprise Edition. Run the script in SSMS with "results to text". It will generate the compression statements for all tables and indexes for the database it which is run in. It creates the compression scripts in ascending order based on table/index size so smaller tables/indexes are compressed first creating additional space for larger tables/indexes to be compressed. --Creates the ALTER TABLE Statements SET NOCOUNT ON SELECT 'ALTER TABLE ' + '[' + s.[name] + ']'+'.' + '[' + o.[name] + ']' + ' REBUILD WITH (DATA_COMPRESSION=PAGE);' FROM sys.objects AS o WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id] INNER JOIN sys.schemas AS s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id] INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK) ON i.[object_id] = ps.[object_id] AND ps.[index_id] = i.[index_id] WHERE o.[type] = 'U' ORDER BY ps.[rese...

The last script will basically loop through the entire server/database your specify and create the indexes for you, sending you an email for each failed index that fails to get created. The first script creates a table to store the indexes. The second script creates the SQL Agent Job.  You can eitherpaste the main segment of code directly into the job step where it says "INSERT CODE FROM ABOVE INTO THIS JOB STEP", or create a stored-procedure, and use that instead. Deploy the job/procedure to any server you wish to keep back up your index definitions, setting an appropriate schedule for it to run. Use the final portion of code to loop through the table created in step 1, to automagically create the indexes on the target server. /* Create table to hold indexes */ CREATE TABLE [dbo].[MasterIndexes]( [ServerName] [varchar](75) NOT NULL, [DBName] [varchar](75) NOT NULL, [IndexTable] [varchar](75) NOT NULL, [Type] [varchar](3) NOT NULL, [IndexName] [varchar](500)...