February 8, 2008 at 3:48 pm
OK, I'm confused here.
I want to set the minimum log file sizes on several databases so they don't have to keep growing once they are shrunk.
What I know:
I can use ALTER DATABASE statement to set a file size.
I can use DBCC ShrinkFile to shrink a file to a specific size.
I can set up jobs to do this automatically.
All this is good. However, if another DBA right-clicks on one of these databases and chooses to "Shrink Database", the files get shrunk down to their original minimum sizes.
Example: We have a very large freight management database (6 gigs) that undergoes lots of early morning processing each day. The log file is typically around 2.5 to 3 Gigs. I want to set it's minimum size to 2.5 gigs and allow for 512 meg incremental increases. I don't want the log file to ever be smaller than 2.5 Gigs no matter what method is used to shrink it (Enterprise Manager, DBCC, etc.).
Is this possible? I've been searching for hours and everywhere spells out the same thing, using ALTER DATABASE to set the initial size. However, a plain vanilla shrink will bring it back down to 1 meg.
This is even more important for the tempdb, which we want to set to a very large size (5 gigs) and have it be 5 gigs when SQL Server restarts and creates a new one. There is a ton of processing going on with this and a dozen other databases with transactions, etc. Allowing these files to start so small is causing a lot of unnecessary overhead on the server during processing while it keeps growing these files until they reach their optimum sizes.
(This is a SQL 2000 installation on Windows 2003 Server)
Thanks in advance
Jerry Boutot, MCAD MCP, MTA
Jerry Boutot Official
February 8, 2008 at 4:10 pm
Regarding your tempdb sizing you can use the alter database command to size it and then you need to restart sql services.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 8, 2008 at 4:23 pm
Check if this link helps to solve your problem.
http://www.sqlservercentral.com/Forums/Topic372527-146-1.aspx
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply