Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-12-18
7
Medium Priority
?
612 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 11

Assisted Solution

by:HuaMinChen
HuaMinChen earned 800 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 800 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 8

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
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.

 
LVL 11

Assisted Solution

by:HuaMinChen
HuaMinChen earned 800 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 800 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 400 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 8

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

604 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