September 25, 2009 at 12:23 am
1. I need to shrink my database file. what is the maximum size of database file(i.e mdf).my mdf file size is 978 mb.so can i shrink my database file?.Plz give me the answers.
2. Actually my production server is in working 24x7.But when i was open the my production server the tempdb mdf file size is extended to 4gb. Is this harmful to my productionserver? if any problem occurs please give me solution to reduce the tempdb mdf file size.
Regards,
P. KiranKumar
September 25, 2009 at 12:59 am
kiranmca24 (9/25/2009)
1. I need to shrink my database file. what is the maximum size of database file(i.e mdf).my mdf file size is 978 mb.so can i shrink my database file?.Plz give me the answers.2. Actually my production server is in working 24x7.But when i was open the my production server the tempdb mdf file size is extended to 4gb. Is this harmful to my productionserver? if any problem occurs please give me solution to reduce the tempdb mdf file size.
Regards,
P. KiranKumar
To answer your second question, tempdb will grow depending on activity, it shouldnt be an issue, unless you run into the situation where you run out of diskspace on that drive.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 25, 2009 at 1:31 am
1. I need to shrink my database file. what is the maximum size of database file(i.e mdf).my mdf file size is 978 mb.so can i shrink my database file?.Plz give me the answers.
Answer:978 MB is a small figure.You can easily shrink it if you want.Hardly it would take 10-15 min to shrink.
Use the below SQL statement
Use databasename
sp_helpfile----to get the file details of the database
note down the used and free space by this .mdf file
dbcc shrinkfile(logicalname,(used space size+some figure 50,100))
Open a new query window:
Use databasename
select cmd,* from master..sysprocesses where db_name(dbid)='databasename'------to check if your DBCC query is being blocked by any other transaction.
2. Actually my production server is in working 24x7.But when i was open the my production server the tempdb mdf file size is extended to 4gb. Is this harmful to my productionserver? if any problem occurs please give me solution to reduce the tempdb mdf file size.
Answer: No it won't harm.
September 26, 2009 at 12:26 pm
Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply