Solved

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

Posted on 2013-12-18
7
581 Views
Last Modified: 2013-12-19
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
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 200 total points
ID: 39728484
Ensure that the machine is with the space of at least 10 GB. Yes, express version is supporting up to 4GB.
0
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 200 total points
ID: 39728486
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
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 39728512
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 200 total points
ID: 39728525
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
 
LVL 44

Assisted Solution

by:Rainer Jeschor
Rainer Jeschor earned 200 total points
ID: 39728528
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
 
LVL 25

Assisted Solution

by:jogos
jogos earned 100 total points
ID: 39729848
"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
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 39730589
surprisingly,on the largest DB file, the recovery mode is set to Simple not Full.
Auto Shrink False.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question