SQL Server Performance Forum – Threads Archive
Changing the log initial size
Folks: Is there a way to decrease the initial size of a database/log file? I’ve noticed you can increase it, but if you decrease it, after you confirm the change and go checking again, you will see nothing happened. Is there a way? Am I missing anything?!If you want to change the initial size of the db/log files, you have to change the model database…
When ever you create a db is is the copy of the model… Once the db/log files created, if you want to shrink you have to use DBCC SHRINKFILE command…
MohammedU.
Moderator
SQL-Server-Performance.com
Thanx Mohammed… The problem is: the initial sizes in model are 3mb for data files and 1mb for log files. Still, some databases kept growing, and their initial size defaults are now changed to some weird values (the last size data/log had reached, probably), and all I can do is to increase them – whenever I try to reduce their size (in the initial size option), it doesnt work. Since their default is big, to shrink them just get them back to those (big) sizes. I’ve tried to shrink them, after changing their recovery model to simple, and run a full backup. In vain. Any clue?
Why do do you want to reduce the size? See discussion in the next thread:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6894&SearchTerms=log
mmarovic, Thanx, but this thread goes much further than what I need or have in mind. It’s more about data archiving (and recovery) strategy than simply reducing file sizes. It shows how guys here are read good, though. Nice thread!! My question is far simpler: how do I reduce a log/data initial size to a lesser value? I’ve tried some of the commands stated in this thread (alter database modify file) and got sth like "MODIFY FILE failed. Specified size is less than current size.". I was wondering if there is sth less radical than "create database … for attach_rebuild_log", which recreates the log file according to the model db size.
The point of the thread is: Do not reduce db log size. You said:
quote:
The problem is: the initial sizes in model are 3mb for data files and 1mb for log files. Still, some databases kept growing, and their initial size defaults are now changed to some weird values (the last size data/log had reached, probably), and all I can do is to increase them – whenever I try to reduce their size (in the initial size option), it doesnt work.
Databases kept growing because they need more space. Reducing the space you gain nothing. Anyway if you think you have to shrink the db log, next thread may help you:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8018&whichpage=1
It is about sql server 2000, but I guess it should work on 2005 too.
The problem is: the initial sizes in model are 3mb for data files and 1mb for log files. Still, some databases kept growing, and their initial size defaults are now changed to some weird values (the last size data/log had reached, probably), and all I can do is to increase them – whenever I try to reduce their size (in the initial size option), it doesnt work.
One of the reasons it is unable to SHRINK is due to the open transactions and not being empty, so you have to be clear about other jobs and processess that are running contributing this type of information. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Thanx mmarovic! This really helped. Using a slight variation of what is used in that thread solved the problem: DBCC SHRINKFILE (‘bd_log’, 1 , TRUNCATEONLY) — 1 or any other size in mb here. The point is: the databases were already there, when I’ve come to the company (small size, just beginning). They were created with recovery model full (default, right), but it’s a non-critical DB in the development environment (read data mess), so the simple recovery model is more than enough, with weekly full backups and daily differential ones. Since this DB was in full mode and has never been backed up, you can figure out how big the log file is. I’m trying to fix this, and get the log back to its original size. Thanx you and Satya!
Have you checked DBCC OPENTRAN for more information on open transctions, during the SHRINK operations.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
When the recovery model is full… I don’t think daily differential backup is any good for you at all, because simple recovery model will not support poin in time recovery… As Satya mentioned check the if there are any open transactions… Read the following articles which help you to understand and shrink log file… How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005
http://support.microsoft.com/kb/907511 How to stop the transaction log of a SQL Server database from growing unexpectedly
http://support.microsoft.com/kb/873235 http://www.codeproject.com/useritems/truncate_log_SQL_server.asp MohammedU.
Moderator
SQL-Server-Performance.com
Yes Satya, it returned "No active open transactions." – before shrinking, dont worry <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />. <br /><br />The weird behaviour was SQL Server to have changed the initial size of both log/data to the biggest size it has achieved. The person who created the database (at the time) hasnt changed either model db, or the database defaults. Still, log/data files initial sizes were set to the last actual data/log file sizes.
Thanx Mohammed, I am gonna check them out!
You need not worry about sizes for Model & other system databases, Model is used at first time when the database is created with default settings and anyhow you have to set the values once it is created. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
I forgot to mention…
If you don’t specify the size of the db in your create statement db will be created as the same size as MODEL
where as if you specify the size higher than model db size then db size will be over written with your specified size…
MohammedU.
Moderator
SQL-Server-Performance.com
Satya,
I came across your article on sql-server-performance.com. I am a entry level DBA ,and the log file size of one of the critical production databases is currently growing at an alarming rate and is about 20GB. Don’t know what is happening. I tried the following (from the article) alter database db
set recover simple
go
dbcc shrinkfile (db_log, 100)
go
checkpoint
go
dbcc shrinkfile (db_log, 100)
alter database db
set recovery fullgo
When I am executing this, I am getting the message as: Cannot shrink log file 2 (ris_log1) because all logical log files are in use.
DBCC execution completed.Please contact your system admin. Also, I ran dbcc opentran(db) and found no errors. what am I doing wrong. still the log size is the same.
no change.. Please help. Thanks,
Meena.
Run the DBCC OPENTRAN command to see if there is any open transactions…
If there are any replication transactions pending you can’t shink the log file…if the db is part of replication… Why you want to be restrictive with shrink of database files
http://www.karaszi.com/SQLServer/info_dont_shrink.asp?
MohammedU.
Moderator
SQL-Server-Performance.com
Not only replication if there are any queries that are still running with maximum batch size such as updating more than 100,000+ rows at a time or deleting any such records will have open transaction, this will not allow the SHRINK operation to interact and until unless the statements are COMMITTED the log will be in same size. So its always better to perform a SHRINK during less traffic hours, but before that follow as suggested in the article & also the replies in thsi post. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>