Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 629
  • Last Modified:

SQL command to know how much DB left for SQL Express edition before it stopped working ?

Hi People,

Since I'm using SQL Express Edition 2005, can someone please assist me in how much disk space left do I have before it stopped working ?

From memory SQL Express can only be used up until 4 GB and then I have to delete some older data to make it working again.

Is this correct ?
0
Senior IT System Engineer
Asked:
Senior IT System Engineer
  • 2
  • 2
  • 2
  • +1
5 Solutions
 
HuaMinChenBusiness AnalystCommented:
Ensure that the machine is with the space of at least 10 GB. Yes, express version is supporting up to 4GB.
0
 
Rainer JeschorCommented:
Hi,
from
http://technet.microsoft.com/en-us/library/ms345154%28v=sql.90%29.aspx

it is clearly stated, that the 4GB database limit is the size limit of the database file - not including the logs.

You can use this script:
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files

Open in new window

to get a list of all files of all databases in MB
(from http://blog.sqlauthority.com/2010/02/08/sql-server-find-the-size-of-database-file-find-the-size-of-log-file/)

HTH
Rainer
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
Yes, the SQL script does works.

ok, in this case the size is quite confusing.

if the log size (.LDF file) is 3.8 GB and the database (.MDF file) size is 160 MB, the database will be stopped working if the .MDF file size hits 4 GB or 4096 MB ?

in that case I still have plenty of time before the DB is reaching 4 GB right ?

what happened if there is Transaction Log backup committing the big chunks of logs to the Database ?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
HuaMinChenBusiness AnalystCommented:
Please check this

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

and shrink unnecesary spaces in log file. Read
Shrink log file
0
 
Rainer JeschorCommented:
Hi,
yes - you have plenty of space left. The db will stop when the mdf hits 4096 MB.
And no, the transaction log backup will not commit anything to the DB. The log would be just used if you have to restore to a specific transaction. So everything in the log is already commited to the db.
0
 
jogosCommented:
"if the log size (.LDF file) is 3.8 GB and the database (.MDF file) size is 160 MB"
Then you probably don't have a good recovery model.

Guess your recovery model is 'Full' (see at database properties, options) and you are not taking a transactionlog backup so the transactions keep on piling up in your transactionlog.
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
surprisingly,on the largest DB file, the recovery mode is set to Simple not Full.
Auto Shrink False.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now