Rozdíly

Zde můžete vidět rozdíly mezi vybranou verzí a aktuální verzí dané stránky.

Odkaz na výstup diff

Obě strany předchozí revize Předchozí verze
Následující verze
Předchozí verze
windows:mssql [18.08.2014 10:29]
vm
windows:mssql [26.01.2015 10:32]
vm
Řádek 18: Řádek 18:
   GROUP BY    GROUP BY 
     dbid, loginame     dbid, loginame
 +    ​
 +===== Reduce Initial File Size of SQL Server Transaction Log File =====
 +So here is the quick dirty way to change the initial size of the transaction log file:
 +
 +  - Create a full backup of the database in question
 +  - 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
 +  - right click on the database to go "​Tasks"​ -> "​Shrink"​ -> "​Files"​
 +  - From the "File Type" menu, select "​Log"​ and press OK
 +  - go back to database properties -> options and change the recovery model back to "​Full"​ (do not press OK)
 +  - click on the "​Files"​ section of the properties window and change the initial size and growth rate of the log file. then press OK
 +  - 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]])
 +
 +===== Make LDF file smaller =====
 +It depends on current recovery model:
 +==== Simple 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). ​
 +
 +==== Full Recovery model ====
 +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]]).
windows/mssql.txt · Poslední úprava: 26.01.2015 10:32 autor: vm