Somthings got a hold on .... Your Log

There are times when shrinking a log on a database is needed.  This could be if your transaction backups have failed for a period of time.  And have now grown.

However there are times when they dont shrink.  Here is a nice script to see why they dont:-

DECLARE @DatabaseName VARCHAR(50);

 SET @DatabaseName = 'YourDataBASE'

 

 SELECT name, recovery_model_desc, log_reuse_wait_desc

   FROM sys.databases

   WHERE name = @DatabaseName

 

To Shrink, Or not to Shrink, that is the question

Whether 'tis nobler in the log to suffer
The growth and space of outrageous fortune - bla bla

You see many a post debating this issue.  On the one hand you have the newer DBA's that will shrink the data file at every opportunity to try to regain space on the disk.  Only for it to grow again the next day.

Or others that will shrink the data file and then do a reindex, and make it grow again.

Its my opinion that for every day usage, the data and log file should be left alone.  To ensure that databases can be restored in a disaster, you should be taking regular backups, this would included fulls, diffs, and transnational.

 These transnational backups should keep the database log from growing too high, by allowing the space to be reused.

However ..... There are times when the data file does need shrinking.  When?

Lets say you have a very large database that get unmanageable, you might want to split that up into smaller yearly (for example) databases.  You would then need top reclaim that space at the end.

So how should this be done.  Below is a helpful script that will shrink the file stage by stage, to avoid putting a disk performance issue on the database.

USE <DB Name>

GO

DECLARE  @vLogicalFilename VARCHAR(250) 

DECLARE  @vTargetSizeinMB INT 

DECLARE  @vCurrentSizeinMB INT 

DECLARE  @vDecrementinMB INT 

DECLARE  @vSQL VARCHAR(250) 

SET @vLogicalFileName = 'DataFileName' 

/* Please make sure that the Target size is achievable  

   otherwise the script will go in an infinite loop    */ 

SET @vTargetSizeinMB = 21000 

SET @vDecrementinMB = 200 /* Don't set this value to more than 2000 */ 

IF EXISTS (SELECT * 

           FROM   sysfiles 

           WHERE  name = @vLogicalFileName) 

  BEGIN 

    SELECT @vCurrentSizeinMB = size / 128 

    FROM   sysfiles 

    WHERE  name = @vLogicalFileName 

     

    WHILE (@vTargetSizeinMB <= @vCurrentSizeinMB - @vDecrementinMB) 

      BEGIN 

        SELECT @vSQL = 'DBCC SHRINKFILE(' + @vLogicalFileName + ',' + Cast((@vCurrentSizeinMB - @vDecrementinMB) AS VARCHAR(10)) + ')' 

         

        SELECT @vSQL 

         

        EXEC( @vSQL) 

         

        SELECT @vCurrentSizeinMB = size / 128 

        FROM   sysfiles 

        WHERE  name = @vLogicalFileName 

      END 

  END 

Failing over all database mirrors to Secondy server

You box is dying fast!  You need to get your databases to DR.  No one wants to sit and right click each.  So here is the quick way:-

DECLARE @DBName sysname
Declare @SQL nvarchar(1000)
Set NoCount On;
/*Get first database where it is online and part of a mirror as principle role and is synced*/
SET @DBName = (select min (name)
     from sys.databases
     where [state] = 0
     and database_id in (select distinct(m.database_id)
          from sys.database_mirroring m
          where m.mirroring_role = 1
           And mirroring_state = 4)
     )
while @DBName is not null
BEGIN
 Print @DBName

  /*Alter database to fail over to partner*/
   Set @SQL = 'Alter Database ' + quotename(@DBName) + ' Set Partner Failover;'
  Print @SQL
   Exec sp_executesql @SQL;
     
   /*Get next database that is online and part of a mirror as partner role that is synced*/
   set @DBName = (select min( name)
     from sys.databases
     where [state] = 0
     and database_id in (select distinct(m.database_id)
          from sys.database_mirroring m
          where m.mirroring_role = 1
           And mirroring_state = 4)
     and name > @DBName
     )
END

When you absolutely positively got to kill every mother ****er in the room ACCEPT NO SUBSTITUTES!!!!

Ever been in the situation where you need to restore a database, delete, take off line or detach, but "exclusive access cannot be obtained as the database is in use"?

Then here is the script for you.  Use with caution, it will end any open transactions, and is not recommended for any thing but a database that's now junk, ie to be deleted or restored over.


declare @SQLText varchar(8000) ,@spid int
select @spid = min(spid) from master..sysprocesses where dbid = db_id('<Databasename>')
while @spid is not null
begin
 select @SQLText = 'kill '+convert(varchar(5),@spid)
 select @SQLText
 execute (@SQLText)
 -- inc counter
 select @spid = min(spid) from master..sysprocesses where dbid = db_id('<databasename>') and spid > @spid
end


Remember when restoring a database over the top, its always best to not drop said database first. As this will cause the restore to have to recreate file's ect. And will make it take longer.


http://www.youtube.com/watch?v=yxQNYPYFq1c