Solved

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

Posted on 2013-12-18
7
565 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
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…
Viewers will learn how the fundamental information of how to create a table.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now