How to find initial size of a DB | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to find initial size of a DB

Hi , Suppose i have a database file (.MDF ) have size 6GB ,having auto growth set .Anyways to find how much was its initial size ? rajiv
Initial size is 1Gb. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
When you use CREATE DATABASE statement it will be created using MODEL database sizes, which is by default 1MB or depending upon your specification. BOL clarifies
SIZE Specifies the size of the file defined in the <filespec>. When a SIZE parameter is not supplied in the <filespec> for a primary file, SQL Server uses the size of the primary file in the model database. When a SIZE parameter is not specified in the <filespec> for a secondary or log file, SQL Server makes the file 1 MB.
 Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Hoop, misstake writing. Is 1MB.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
ok. Suppose i have created a database with size 2 GB , and it grown to 6GB .So is there anyway to find that it was created as 2GB database.?
-Rajiv
As far I know, no. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
No I cant think of a way either.
Why do you need this? Are you trying to get an idea of how the database has grown over time ?
There will not be such information stored, look at Model database size and while creating if you haven’t mentioned the size this db size will be equal to model. As Chappy referred why do you need this? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
If you’re looking for the historical growth and don’t have anything in place to trap it already, you can use the backup_size column from table backupset in msdb and calculate your growth rates from there.
I had found errors in my error log like ‘TempDB full’ ,So i just enabled autogrowth for my TempDB.next day i found my G: drive full and tempDB size was 15GB . So i thought i will shrink the file to a size that was set before.But i donno how much it was before.So just shrunk the file to 4096MB .
Looks like (perhaps) excessive use of temporary tables. Set a maximum size for TempDB’s data and log files.
True and you must take control on TEMPDB and let system not drive by itself.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Also, may be, some maintenance jobs could grow tempdb. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
So is it good to set max size? if i set max size somtime i am getting error "TempDB" full .If i enable autogrowth its goin up to 15GB and drive full. Does it means i have to configure TempDB with a HUGE size more than 15GB .Anyways to tackled this pbm?
For temporary tables (# and ##), they are created in the tempdb database, and by thus on the disk. But as soon as the table goes out of scope or is droppped, the table is removed from the file. In SQL 2000, you can declare variables of type table, which can be the same. It depends on the size of data… http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=8652
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
ok. Suppose i have created a database with size 2 GB , and it grown to 6GB .So is there anyway to find that it was created as 2GB database.?
-Rajiv
———————————————————————————————–
By VIshnu You can execute this query to fetch the sql server page details and in the property name called ‘Min Size’, you could find the initial size of the DB when it got created first will be there. (here 2GB)


DBCC TRACEON(3604)
GO DBCC PAGE(‘Your_DBName’, 1, 0,3) WITH tableresults
]]>