Solved

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

Posted on 2013-12-18
7
593 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 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 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 11

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

Industry Leaders: 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!

Question has a verified solution.

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

In case you ever have to remove a faulty web part from a page , add the following to the end of the page url ?contents=1
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 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