SQL Server Performance Forum – Threads Archive

Expending db and trans.log

Hi !
My db is useb up to 95% When i run sp_helpdb maxsise of this db and log is unlimited .
But i got today transaction log full
Why it happened? Also db autogrow is not checked how it can be unlimited and not autogrow?

Thats because the setting did not let database to auto-grow when the transaction log wanted to increase the size. BOL defines actions against error 9002:
The user action that is appropriate to you depends on your situation. Potentially, possible actions include:
Backing up the transaction log
Freeing disk space
Moving the log file to a disk drive with sufficient space
Adding or enlarging a log file A long-running transaction prevents truncation and reclamation of transaction log space, which normally happens either automatically (under the Simple Recovery model) or as a result of taking a log backup (under the Full Or Bulk-Logged Recovery model). HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Okey then how do i expand db if it already unlimited i thought i am going to use this: USE master
GO
ALTER DATABASE db
MODIFY FILE
(NAME = db,
SIZE = 1026000KB)
GO
db log original size=1024000KB

Yes, it is as per the BOL.
Any reason why Auto-Grow is disabled and what is the recovery model used? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Well i am not sure why it’s disabled.
I guess because db is 50 g
and they afraid it will grow too much. Db is on full recovery model

Then maintain regular Tlog backpus and if possible try to shrink the database size.
ENsure enough disk space is available where the data files are located. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

also how come this db is 95% used if the seting for maxsize is unlimited?

As a whole AUTO-GROW is disabled then no other setting would work until its enabled. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

What If I add additianal log file and aditional db file?

That is one of the workaround mentioned and should be no issues.
Ensure no disk space problem on HDD. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Okey :it worked expanding db file not log file

If you get error 9002 then add log file and if its error 1105 then adding data file would resolve the issue. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>