Software
Hi,
How to know the sql server's database size? I want to migrate some data from local server to the web server. But, before migration, I want to know the Sql Server's database size. Only if the database is less, i will migrate the data otherwise, i'll do something else.
Can anybody please provide C# code or any stored procedure name, thru which i can know the database size.
by sergey
1)Use SQL Enterprise Manager. Right-click your database and select "Properties" - then look into
"Data Files" and "Transaction Log" tabs.
2)Look for database files on your server - usually
they are like MyDBname_.mdf (data),
MyDBName_.ldf (logfile) and see what size they have
by spanr
Enterprise Manager will tell you the size just as the previous poster indicates. You can also use "sp_spaceused" in QA.
Cheers
The following text is read from http://www.databasejournal.com/features/mssql/article.php/1493621
An insufficient free space in data file(s) / transaction log file(s) event for the database can result for any of the following reasons:
The database (data and/or transaction log) files were created without the "Automatically grow file" option checked (or at some point the option has been manually unchecked by the DBA)
The database (data and/or transaction log) files were created with the "Maximum file size : Restrict filegrowth ( MB ) = some_value" option checked;
The amount of free space on the disk drive where the data file/transaction log file are stored is insufficient for further database growth
regards
mrc
by ezann
The process of backing up the transaction log will mark old transactions as inactive and allow the tran log to be shrunk. Do not confuse this with backing up the database. You must specifically backup the tran log in addition to the database. If you use the "Maintenance Plan" wizard it will help you through the process.
Also, in MSSQL 2000, you can set the Recovery Model to "Simple" which will basically truncate the log entries immediately. However, do not use this method if you need point in time recovery. If you use the "Simple" recovery model, you will be limited to restoring your last full db backup. This is generally only appropriate for development systems or systems with low transactional activity.
by spanr
I don't normally set up my dbs in this manner. I normally leave log file growth to be unlimited but ensure that my Maintenance Plan includes a full backup of the db and log files. That way SQL maintains the log file at a minimum without me having to be concerned with it. The more often you backup the log file the more often the log file is truncated therefore restricting its growth without fixing the log files size when creating the db.
But in answer to your question, I think it will depend on the recovery mode the db is using. If you are using a simple mode, then you may get away with it - SQL Servers automatic checkpoints should free older entries; but I am fairly sure that with a full recovery mode (which is the default with which most SQL Servers are installed) then for it to work OK you must make sure your Maintenance Plan is backing up the log file frequently enough for it to be truncated before it runs out of free space.
I suggest you do some research into recovery modes and checkpoints before committing to a max size on the log files.
Hope this helps.
Cheers
This discussion is closed to new replies.
Sort by Trending