February 1, 2013 at 2:27 am
Hi All,
I am told by one DBA that the Database which is used in company is carry forward..
means the onc DB which was created in 2008 was used for other process..
Where in when any DB is required then the developer use to restore the old DB's & then used to delete the tables & other things for new process.. & this got continued to almost 4 yrs years.. now there are 200 Databases.
I asked what exactly the developer use to do in past.
He said..
in 2008 there was ABC named DB... he required new DB so he restored the copy of ABC & then he deleted the tables, views, SP & changed the DB name as XYZ & then for some other they process they restored the copy of XYZ & deleted tables, sp etc & renamed with some other name.
My confusion here is.. although the data is less still the filesize is maximum :w00t:
how do i rectify all of them? please suggest..
What is the impact & cause of the above scenario which is going on since 2008..
Will this imapct perfromcane or Size on disk or what???
Expert comments are awaited..
************************************
Every Dog has a Tail !!!!! :-D
February 1, 2013 at 2:43 am
As the object is deleted, it doesn't release the space back to the operating system, as grabbing space and growing the database is a very costly and expensive operation.
Your options to release the space from the file is 1 of 2 ways. DBCC SHRINKFILE or create a blank database then transfer the objects.
If you go for shrinkfile, remember to rebuild all your indexes on all objects and add some free space back to the file otherwise your performance is going to stink.
Is it bad practise, I would say so personally, others may disagree or give their 2cents worth, if you have the objects in a DB already and you want a copy of them, just create a blank DB and transfer what you need, thats what I would do.
February 1, 2013 at 3:17 am
increased size could be due to large/unmanaged log too
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 1, 2013 at 3:36 am
anthony.green (2/1/2013)
Your options to release the space from the file is 1 of 2 ways. DBCC SHRINKFILE or create a blank database then transfer the objects.If you go for shrinkfile, remember to rebuild all your indexes on all objects and add some free space back to the file otherwise your performance is going to stink.
.
hi Anthony thanks.. DBCC Shrinkfile & rebuild is ok.. i can perfrom the activity.. but how do i add free space back to the file?? please expalin
************************************
Every Dog has a Tail !!!!! :-D
February 1, 2013 at 3:38 am
ALTER DATABASE .... MODIFY FILE (NAME = '.....', SIZE = 45GB) --Put the name of the logical file from sp_helpfile in the command
February 1, 2013 at 3:42 am
anthony.green (2/1/2013)
ALTER DATABASE .... MODIFY FILE (NAME = '.....', SIZE = 45GB) --Put the name of the logical file from sp_helpfile in the command
Na Didnt understand the above query.. is it the standard query (manditory one) to be executed for all databases???
Didnt understand the context of it 🙁
************************************
Every Dog has a Tail !!!!! :-D
February 1, 2013 at 3:45 am
You need to add enough free space in all the files you shrink.
So if your DB has 10 files, you run it 10 times for each file in the DB.
You have 10 databases, with 10 files, you run it 100 times changing the parameters
ALTER DATABASE runal_jagtap MODIFY FILE (NAME = 'runal_jagtap_data', SIZE = 45GB)
--Set the logical file runal_jagtap_data to 45GB in the database runal_jagtap
Google "ALTER DATABASE MODIFY FILE"
February 1, 2013 at 3:51 am
Thanks Anthony..
U see i have a DB and maximum it grows upto 25 GB. thats the max volume.. sounds very less.. but i have DB's upto 25 GB only
Now runal_jagtap DB is having mdf file - 25 GB & ldf file 1GB
Below are the operations needs to be performed
1) DBCC SHRINKFILE.
2) Rebuild Index
3) ALTER DATABASE runal_jagtap MODIFY FILE (NAME = 'runal_jagtap_data', SIZE = 45GB)?
is this way correct .. coz i didnot get the last point.. is it necessary
************************************
Every Dog has a Tail !!!!! :-D
February 1, 2013 at 3:54 am
Well if they are at 25GB and they cant grow past 25GB then why bother shrinking? Do you need the space back to the OS for some reason? What is the reasoning behing limiting the DB's to maximum 25GB?
February 1, 2013 at 4:03 am
anthony.green (2/1/2013)
Well if they are at 25GB and they cant grow past 25GB then why bother shrinking? Do you need the space back to the OS for some reason? What is the reasoning behing limiting the DB's to maximum 25GB?
Ya space is concerneed... i am not limiting the size to 25GB.. its just till now it reached upto 25GB max.. later may be if new process come in or heavy activity is done.. size will surely grow in 100's...
If i have 500 GB size hardisk & if i alter the size to 50GB.. what is the benifit?
what say about only shrinking it & then rebuilding it???
************************************
Every Dog has a Tail !!!!! :-D
February 1, 2013 at 4:08 am
Shrinking then rebuilding is ok it will add space to the MDF as the rebuild will need space to do the sorts etc.
But you should really add enough space to cover growth for at least a month or 2 or even better a year or 2, that is if you know how much your files grow in a month. Only way to know that would be to baseline your server.
As I said in an earlier post, growing the file and adding space is a very costly operation and puts a stop on all activity while the file grows as it effectivly issues the ALTER DATABASE command meaning the DB is exclusivly locked to the growth operation.
February 1, 2013 at 4:20 am
thanks Anthony, me still confused regarding the last Alter statment.. will study more on this and will come soon to ask you more on this post:-)
************************************
Every Dog has a Tail !!!!! :-D
February 1, 2013 at 4:21 am
What is confusing you about it?
February 1, 2013 at 4:40 am
Like..
I will shrink the File & rebuild via wizard..
But for alter statement
(ALTER DATABASE runal_jagtap MODIFY FILE (NAME = 'runal_jagtap_data', SIZE = 50GB))
i have to execute this for all db's one after one after rebuild is done right..
I mean even if the file grows more than 50GB... and i am limiting it to 50GB.. then what?
what should i remeber at this time.. i mean Do's n Dont's before & after executing the alter statement?
************************************
Every Dog has a Tail !!!!! :-D
February 1, 2013 at 4:48 am
You can do it by going into the properties and changing the filesize of the files to something more sensible than leaving it a a stupidly small size that will cause you issues when it comes to grow the files again.
The command needs to be done for every file in every database that you shrink to ensure that you dont run into the expensive operations of growing the files.
You should be proactivly growing your files manually, not waiting for SQL to auto grow the files, auto grow is there as a redundancy really for the times that things go wrong or for some reason this month we inserted 100GB of data instead of 75GB of data.
If you limit the file to 50GB it cant grow past that point, so if it want to go to 51GB it will fail.
You should be capturing how often files grow, how much they grow by, just ensure that you have enough free space in the files to accomdate the growth for a period.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply