Microsoft SQL Server

• Odeslání e-mailů z procedury

1. Pomocí SP_Who zjistit Session ID daného spojení
2. Pomocí KILL <session id> spojení ukončit

SELECT 
  DB_NAME(dbid) as DBName, 
  COUNT(dbid) as NumberOfConnections,
  loginame as LoginName
FROM
  sys.sysprocesses
WHERE 
  dbid > 0
GROUP BY 
  dbid, loginame
  

So here is the quick dirty way to change the initial size of the transaction log file:

1. Create a full backup of the database in question
2. go to the database properties (right click on the DB → properties), under the „options“ section, change the database recovery model to „Simple“ then press OK
3. right click on the database to go „Tasks“ → „Shrink“ → „Files“
4. From the „File Type“ menu, select „Log“ and press OK
5. go back to database properties → options and change the recovery model back to „Full“ (do not press OK)
6. click on the „Files“ section of the properties window and change the initial size and growth rate of the log file. then press OK
7. jump for joy, your log file is now a reasonable size!

(http://www.serveroperations.net/microsoft/sql-server/reduce-initial-file-size-of-sql-server-transaction-log-file)

It depends on current recovery model:

Just shrink the file! If the file doesn't shrink to the size you want, then try again, and again. If it still doesn't shrink to the size you want, then read this (the „Shrinking of transaction log file“ section).

If you are in full recovery model, and this is where you want to be, then you have to make a decision:

Is it OK to break the transaction log backup chain?

By this we mean the chain of log backups you may have. If you don't have any prior log backups (perhaps not even a prior database backup), then it is OK to break the chain of log backups. If you do have earlier log backups and want to be able to restore from them up to any point in time then it isn't OK to break the chain of log backups.

• OK to break the chain of log backups. This is easiest:
  • Set recovery model to simple.
  • Do the shrink according to the steps above (for a database in simple recovery model).
  • Set recovery model to full.
  • Perform a full database backup, so your scheduled log backups have somewhere to begin from.
• Not OK to break the log backup chain. Then you backup the log (which can produce a huge log backup file, and consequently take some time), and then shrink the file. If the file doesn't shrink to the size you want, then backup log and shrink again, and again. If it still doesn't shrink to the size you want, then read this (the „Shrinking of transaction log file“ section).

Commands to do the actual shrink: Simple recovery model

USE dbname
CHECKPOINT
--First param below is fileno for log file, often 2. Check with SELECT * FROM sys.database_files
--Second is desired size, in MB.
DBCC SHRINKFILE(2, 500)
DBCC SQLPERF(LOGSPACE) --Optional
DBCC LOGINFO --Optional 
Now repeate above commands as many times as needed!

Full or bulk_logged recovery model

USE dbname
BACKUP LOG dbname TO DISK = 'C:\x\dbname.trn'
--First param below is fileno for log file, often 2. Check with sys.database_files
--Second is desired size, in MB.
DBCC SHRINKFILE(2, 500)
DBCC SQLPERF(LOGSPACE) --Optional
DBCC LOGINFO --Optional 

Now repeate above commands as many times as needed! What you end up doing is empty the log (CHECKPOINT or BACKUP LOG) and DBCC SHRINKFILE several times, so that SQL Server can move the head of the log to beginning of the file and also so the end of the file becomes unused. Investigate the layout of the log file using DBCC LOGINFO in between.

(http://www.karaszi.com/SQLServer/info_large_transaction_log_file.asp).