Le Kevin dot com

It backs up with an expiration of 30 days, but a year for anything on the first of the month.
Emails if it fails… (assume that you have sql email profile already setup)

CREATE PROCEDURE spBackupAllDatabases
AS
BEGIN
  SET NOCOUNT ON;
  declare @db varchar(50)
  declare @d varchar(100)
  declare @n varchar(100)
  declare @rd int
  set @rd=case when datepart(day,getdate())=1 then 365 else 30 end
  declare db cursor for select name from sys.databases
   where name not in ('master','tempdb','model','msdb')
  open db
  begin try
    fetch next from db into @db
    while @@FETCH_STATUS=0
      begin
        set @d='c:\\backup sql server\'+@db+'.bak'
        set @n=@db+'-Full Database Backup'
        backup database @db to disk=@d
         with retaindays=@rd, noformat, noinit, name=@n,
         skip, norewind, nounload, stats=10
        fetch next from db into @db
      end
  end try
  begin catch
    --sending emails
    exec msdb.dbo.sp_send_dbmail
     @profile_name='myprofile', @recipients='me@domain.com',
     @subject='SQL Server Backup failed on SQL Server',
     @body='The SQL Server Backup Operation failed on the SQL Server.',
     @body_format='HTML'
  end catch
  close db
  deallocate db
end
GO

When you try to install Microsoft SQL Server 2000 on a computer that has certificates,installation can fail and you may receive the following error message:

[Microsoft][ODBC SQL Server Driver][Shared Memory]SSL Security error
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (SECDoClientHandshake()).
SQL Server configuration failed.
-or-
When you add certificates to a server that already has SQL Server installed, local connections fail.

CAUSE:
Certificates that exist on the server.
Certificates install for various reasons. The most common are:

* Certificates you use to sign e-mail digitally.

* Certificates you use on a wireless network for security reasons.

SOLUTION:

1. Hard RESTART the SQL server
2. or try http://support.microsoft.com/kb/309398

In SQL Server 2005, a shrink operation (DBCC SHRINKFILE) tries to shrink the specified transaction log file to the requested size immediately. To shrink the transaction log file manually under the full recovery model, first back up the transaction log file. Then, use the DBCC SHRINKFILE statement to shrink the transaction log file.

Typically, shrinking the transaction log file in SQL Server 2005 is faster than shrinking the transaction log file in SQL Server 2000. The reason is that the SQL Server 2005 log manager creates or reuses inactive virtual log files by following the physical disk storage order. Therefore, the inactive part of the transaction log file is usually at the end of the file.

For example, the transaction log file may have 100 virtual log files, and only 2 virtual log files are used. SQL Server 2000 may store the first used virtual log file at the start of the transaction log file and the second used virtual log file in the middle of the transaction log file. To shrink the transaction log file to only 2 virtual log files, SQL Server fills the remaining part of the second virtual log file by using dummy log entries. SQL Server moves the start of the logical log to the next available virtual log file that is specified by the log manager. The log manager may create a virtual log file in the middle of the transaction log file just ahead of the last active virtual log file. In that case, you have to use multiple log backup operations and multiple shrink operations to successfully shrink the transaction log file to 2 virtual log files. In the worst case of this example, you may have to use 50 log backup operations and 50 shrink operations to successfully shrink the transaction log file to 2 virtual log files.

However, in SQL Server 2005, you can perform one DBCC SHRINKFILE statement to shrink the transaction log file immediately to 2 virtual log files. You can do this because the SQL Server 2005 log manager creates 2 virtual log files by following the physical disk storage order. Both of these virtual log files are at the start of the transaction log file.

alter database mydatabase set recovery simple;
dbcc shrinkfile (mydatabase_log,1);
alter database mydatabase set recovery full;


Run on all databases

begin
  declare @dbname varchar(50)
  declare @logname varchar(50)
  declare @sql varchar(2000)
  declare cDB cursor for select name from sys.databases
  open cDB
  fetch next from cDB into @dbname
  while @@fetch_status=0
    begin
      exec ('use ['+@dbname+']')
      exec ('sp_MSForEachTable @command1="dbcc dbreindex (''?'')"')
      begin try
        exec ('alter database ['+@dbname+'] set recovery simple')
        declare cLog cursor for select name from sys.database_files where type_desc='LOG'
        open cLog
        fetch next from cLog into @logname
        while @@fetch_status=0
          begin
            begin try
              dbcc shrinkfile (@logname,0)
            end try begin catch end catch
            fetch next from cLog into @logname
          end
        close cLog
        deallocate cLog
        dbcc shrinkdatabase (@dbname, TRUNCATEONLY)
        exec ('alter database ['+@dbname+'] set recovery full')
      end try begin catch end catch
      fetch next from cDB into @dbname
    end
  close cDB
  deallocate cDB
end  

via: http://support.microsoft.com/kb/907511

When you perform any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. When changes are made to the data that affect the index, index fragmentation can occur and the information in the index can get scattered in the database. Fragmented data can cause SQL Server to perform unnecessary data reads, so a queries performance against a heavy fragmented table can be very poor. If you want to determine the level of fragmentation, you can use the DBCC SHOWCONTIG statement. The DBCC SHOWCONTIG statement displays fragmentation information for the data and indexes of the specified table or view.

sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

via: http://www.mssqlcity.com/Articles/Adm/index_fragmentation.htm