Sql express edition says limit is 10GB but my database has grown more than 50GB and things are working fine and no issues reported.
how to understand this 10 GB limit over growing 50+ GB please advise.
Thanks in Advance
Sql express edition says limit is 10GB but my database has grown more thn 50GB
How did you obtain the value of 50 GB?
Check the results displayed by EXEC sp_spaceused.
Hi VidhyadharTS-6837,
How are things going? Were the answers helpful?
Please feel free to let us know if you have any other question. If the post in the thread is helpful, you could kindly accept it as answer.
Best Regards,
Amelia
Hi Vidhyadhar,
take a look at the size of your data file and the log file
The data file has the 10 GB Limit.
If your DB is that much larger, then I suppose that you're running the database in Full Recovery mode without taking log backups.
So you need to take care of a proper backup which includes Full database backup as well as T-Log backups.
Maybe you take a look at the maintenance solution Ola Hallengren provides. There are examples how to set it up for SQL Srv Express as well.
Regards
Dirk
Hey Dirk
Thanks for quick reply!
I also read somewhere that the recovery model should be simple rather a full recovery? is that true ?
am not good at db or I just have too minimum knowledge at DBs
Hi Vidhyadhar,
the thing is: is just a backup of the whole database ok for you, eg. just once or twice a day?
Then you may go for simple recovery mode and just take database backups.
If you have the need to do so called point in time restores, then your database should stay in full recovery mode and you have to take log backups regularly.
Then you are to recover your database (if needed) to a specific point in time like 14:38:33
Info about Full Recovery mode
https://www.mssqltips.com/sqlservertutorial/3/sql-server-full-recovery-model/#:~:text=The%20%22Full%22%20recovery%20model%20tells,the%20transaction%20log%20is%20truncated.
When we say 10GB, it is for the MDF file, which is actually data stored file.
There is a LOG file called .LDF file, in that its a log file and it can grow until the HDD supports and another, the LDF file growth is restricted to certain limit.
So here the drive space is set to 299 GB . and if I dont change anything in any of options, can i expect db to grow till 290 GB atleast ?
How can I truncate the Log file ?
If your database is not mission critical, then you can try
USE [yourdatabase]
Checkpoint;
USE [master]
GO
ALTER DATABASE [yourdatabase] SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE [yourdatabase]
GO
DBCC SHRINKFILE (N'dba_local_log' , 1000)
GO
This will change the recovery model and shrinks your Tlog file to 1000MB
This is just a simple example though
Hi VidhyadharTS-6837,
In addition, after shrinking the log file, you can have alter database modify file command to set max file size of log file so that the log file will not fill up the drive.
For example:
ALTER DATABASE databasename
MODIFY FILE
(NAME = logname,
MAXSIZE = 100GB);
And please also remember to monitor the log file size every once in a while. Please refer to Manage the size of the transaction log file for more details.
Best Regards,
Amelia
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
24 people are following this question.
