January 25, 2009 at 1:55 am
I am new to Sql Server. Dear friends i need your support.
I have two environments in Sql Server 2005. one is test and second is production.
The size of production is 60GB and the size of test is 70GB.
Production environment has 11 files with one .mdf and remaining with .ndf extensions.
Test environment has 4 files with one .mdf and remaining with.ndf extensions.
The problem is test environment is bigger in size than Production and its eating up lots of space on Server.
please.............help me
January 25, 2009 at 2:01 am
msalmaan (1/25/2009)
please.............help me
What do you want help with? I didn't notice a question.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2009 at 2:29 am
My dear the question is how come the test environment will have the database size bigger than production environment.
I want to decrease the size of test database and definitely it doesn't have that much data as of the its size. Unable to predict how can test is bigger than production.
Hope anyone of You guys have some similar type of situations ever in the past.
I would be grateful to you if you can help me in this issue.
January 25, 2009 at 2:39 am
msalmaan (1/25/2009)
My dear the question is how come the test environment will have the database size bigger than production environment.
Who knows. Maybe someone created a really large table and then dropped it. Maybe someone grew the files. Could be a number of causes.
I want to decrease the size of test database and definitely it doesn't have that much data as of the its size.
How big is the mdf of the two, how big's the log file?
How much space is used in the mdf (use sp_spaceused), how much space is used in the log? (use DBCC SQLPERF (LogSpace))
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2009 at 3:58 am
The size of production mdf file is 26.7GB and the size of Test mdf file is 60.6GB.
The size of log file of Production database is 1.1GB but used is only 1.6%.
The size of log file of Test database is 400MB but used is only 7%.
SP_SPACEUSED command displays that 30GB of data is unused in Test database.
Can you tell me what can i do to unreserve this space from test database.
Thanks a lot for the help
January 25, 2009 at 10:20 am
You should spend some time learning about SQL Server and it's structure. Look in Books Online for logical and physical architectures.
There should always be free space in the database as you add and delete data. This is not like a file in Word or Excel where you use only what you have data for.
The data sizes can grow in test v production, depending on activity and work. You can always delete some data from test.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 25, 2009 at 10:37 am
post the results of sp_spaceused please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2009 at 8:51 pm
The data in the last message which was posted by me was not exactly what i have seen.
The data which is posted in my last reply was before running DBCC UPDATEUSAGE.
but after running DBCC UPDATEUSAGE, the firures are completely changed---it is below
Database_name
JDE_CRP 72270.50 MB3756.09 MB
69749152 KB31524424 KB38047848 KB176880 KB
January 26, 2009 at 12:54 am
So the database size is 72GB, the unallocated space is 3GB
Then the reserved size is 69 GB, the data is 31 GB, the indexes are 38 GB and the unused is 170 MB?
Doesn't look like a problem here. The only odd thing is that the index size is bigger than the data size, which it shouldn't be. Has someone been playing with indexes on that test server?
There's very little free space in the file, so, unless you can find some object that shouldn't be in the test server, there's nothing you can really do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2009 at 1:25 am
Thank You Very Much Mr. Gail Shaw
I really got the source of concern now about this database.
I m checking my database but Can You guess something regading this database like what may be the cause of this increased index size.
And also the below is result of sp_spaceused of my production database
JDE_PRODUCTION63137.63 MB288.23 MB
63231384 KB31177592 KB31495520 KB558272 KB
If u can predict something from this result.
January 26, 2009 at 6:50 am
You have two things going on. First, you have data in the database. The more data, the bigger the files. To make the testing system smaller, remove unused data.
Second, as Gail pointed out, it's pretty unusual to see indexes that are a lot bigger than the database. The cause for this is quite simple, you've created a lot of indexes. You probably have too many. You're going to need to evaluate them to see if they're needed or not.
By the way, structurally, except for objects under development, your production & test machines should be identical. If they're not, or only different where you're currently testing new code, you need to make them so.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply