ALTER DATABASE to set new minimum translog filesize not working 1

[Hablaras]

Hello,

I have a database with a transaction log file of 500MB. I want to increase it to 10000MB, but this must also become the new "minimum logsize".

According to BOL the ALTER DATABASE statement with the SIZE=10000MB property should do the trick... BUT after an autoshrink or forced shrink ( DBCC SHRINKDATABASE ) of the database the size is shrunk to below 10000MB. I do not want this to happen. ( It seems like a bug or incorrect description in BOL )

Creating a new transaction log is not possible because it is the primary transaction log. This file cannot be removed. Creating a new database with the same name, detaching the original database and retaching it with the newly created transaction log ( thus trying to fool sql server ) does also not work.

Any ideas anyone?

 

[mrdenny]

Disable the auto shrink.

Shrinking a database file is a bad thing to do. It wastes disk time, and cpu time, and ends up making the SQL Server slower over time.

The reason that this happens is that as the file shrinks and grows the file gets fragments on the disk. When this happens it takes SQL Longer to find the correct disk cluster becuase they are no longer in order on the disk, and the disk has to spin all over the place to find the correct cluster that it's looking for.

If you are constently shrinking your file because the sql server keeps growing the file stop shrinking it. SQL obvsoully needs the file at the size it is growing it to. If you shrink it, and the SQL Server grows it again, then it actually needs the space, and you should stop fighting it.

SQL is actually very good about managing how much space it actually needs.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Hablaras]

thanks mrdenny,

some questions i still have:

1) does your argument also hold for shrinking the transaction log? the shrinkdatabase statement shrinks both, but the transactions in the transaction log (LDF) are commited to the database file and the file is "emptied" (perhaps some open transaction remain). So no fragmentation there, i would say.

2) I have always assumed that the shrink procedure also "defragments" the database file (MDF), in effect laying it out in a sort-of optimum order on disk. In other words a physical file defragmentation. You're saying this is not the case?

 

[mrdenny]

1. the transaction log isn't emptied until the log is backed up (unless the database is in simple recovery mode).

2. shrinking the file won't defrag the files.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Hablaras]

1. it is in simple recovery mode, so transactions are written to the database (backed up).

2. ok

thanks for all your help sofar,

gr.,

Hablaras

 

[Catadmin]

The only way to "defrag" your DB files is to actually do an optimization of your index and data pages. You can do this through the Tools -> Database Maintenance Planner tool or you can do this through QA. What Denny said about the shrinking of files is true. However, don't forget that re-indexing your DB, and running the DBCC commands, is a good thing for maintaining your DB. If you don't occasionally do this and your DB keeps growing, you're going to run into the same problem as you would if you constantly shrank the database. Queries taking too long, slow DB, etc.

The schedule you use for preventive maintenance depends on how fast your DB grows. Some people do it once a month, some do it less often (or not at all) and some do it more often.

Just remember, an apple a day doesn't keep the SQL Server running perfectly. It fact, it rather gums up the works. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Hablaras]

Catadmin,

I am running the maintenance plan for my database once a week on Sunday already. I have it set to optimize data pages and indexes, amongst others.

I am finding that some (huge) insert statements are running slowly after the weekend, so my guess was it is because I also shrink the transaction log to a far to small size. Since SQL Server needs to store all data affected in the transaction log until the transaction is complete it needs to expand the transaction log quite bit. So, if I set it to grow 1000 MB (or x%), it may need to perform this action say 10 times, because this one insert operation needs 10GB in the transaction log.

At least... that was my guess and that was why I wanted the minimum transaction log size set larger.

But perhaps the problem lies with the maintenance plan optimizing data and index pages and not doing a good job there for my insert statement.

And yes, i know, looking at the design (of the insert statement) is also an option