Full recovery mode and large log

Hi,
I have several databases using full recovery mode.
Once an hour I backup the log by
USE master
GO
BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
, NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
and once a day I backup the full db by
USE Prozess_Daten
GO
DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
GO
USE master
GO
BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
NOFORMAT
Nevertheless the log file increases up to several GB (as large as the
datafile!). Only a tiny part of that space is used.
DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
does not help. I must switch to simple recovery mode first, then DBCC
SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
recovery mode.
I don't understand why, cause most of the Logfile is unused and could be
truncated(?)
Should I change the way I perform my backups? Maybe:
DBCC CHECKDB ...
BACKUP DATABASE ...
ALTER DATABASE ... (simple)
DBCC SHRINKFILE ... (log)
ALTER DATABASE ... (full)
What's best practice?
Thanks for your help!
Regards, InspektorDerrick
Most probably you are doing work in between two log backup occasions, and the work you do requires
this amount of log space. My guess is index rebuilds. If you do require this amount of log space, do
not shrink the file regularly, as this comes with a penalty, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"InspektorDerrick" <inspektor.derrick@.kstp.at> wrote in message
news:2F653064-7990-40B1-82C9-0BF53CE40D53@.microsoft.com...
> Hi,
> I have several databases using full recovery mode.
> Once an hour I backup the log by
> USE master
> GO
> BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
> , NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
> and once a day I backup the full db by
> USE Prozess_Daten
> GO
> DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
> GO
> USE master
> GO
> BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
> NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
> NOFORMAT
> Nevertheless the log file increases up to several GB (as large as the
> datafile!). Only a tiny part of that space is used.
> DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
> does not help. I must switch to simple recovery mode first, then DBCC
> SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
> recovery mode.
> I don't understand why, cause most of the Logfile is unused and could be
> truncated(?)
> Should I change the way I perform my backups? Maybe:
> DBCC CHECKDB ...
> BACKUP DATABASE ...
> ALTER DATABASE ... (simple)
> DBCC SHRINKFILE ... (log)
> ALTER DATABASE ... (full)
> What's best practice?
> Thanks for your help!
> --
> Regards, InspektorDerrick