First of all thanks for taking interest in this thread.
I have database in Microsoft SQL Server and its size is appx. 427 GB. It consists of data from 2006. Everyday I expire data of 2-3 days and then try to shrink database as much as possible.
But it frees only 2-3 MB after 7-8 hours process. And in that database there some files which do not shrink even I put one of them on shrinking for whole day.
And as per company policy, I am only allowed to expire data which are 15 months older.
I am facing very much problem as my daily import and publish activity some times got failed for that reason.
If anyone knows the solution please tell me the solution.The simple answer would be don't shrink it. Shrinking db files is really not a good idea especially on a regular basis.
Moving thread to proper forum.|||Then what should I do?|||Then what should I do?Don't shrink the file :)
Are there any pressing needs for why you are shrinking? Is this an active database with ongoing modifications?|||Yes, I have 2 databases. One is currently in use and occupies 202 GB and the other, older one, which is not in use but have to keep data up to 15 month older, occupies appx. 427 GB.
I have 9 drives of 100 GB and there is restriction that each drive must have 10% free space. As daily input to new database done, it grows rapidly but older one doesn't frees occupied space even after expiring data older then 15 months.
Thus, my application which does daily import and publish activity get failed.
That's why I shrink the database.
Please tell me the solution for that.|||Buy more disks. I am serious - why fit your database to your server not the other way round?
10% free space is rather low too. You can't even do a proper physical defrag AFAIK.
Check out the BoL entry for shrinking databases and files. Paul Randal (SQL Server enginge lead at MS) ensured that some of the disadvantages of this approach made it into the manual. If you try googling you will find hundreds of examples of people wnating to do what you want to do and being advised not to do it.|||Thanks for serious advice! But this is not my pc or my company's pc but it is my company's clients server pc.
And in near future they have no plan to add any drive as there is much total free space. But problem is that on some drive it has 50 GB free space and on some drives only 12 GB.
I also tried to move datafiles but some files are more than 50 GB so cant move to other drive.
So still any serious advice?|||Listen to the Flump. The Flump speaks Wisdom.
Do.
Not.
Shrink.
Database.
I wish Microsoft had not even included database shrinking as an option.
Tell.
Your.
Client.
To.
Buy.
More.
Disk.
Space.|||I *love* useless advice.
Listen to Chintan. *He* needs to shrink the database-and nobody will tell him how. Doesn't matter whether it's a good idea or not. He's been told to shrink the database. He has two options-shrink the database or leave his job. Your advice boils down to option 2: leave his job because they're telling him to do something that *you* don't think is a good idea.
I'm not arguing whether or not shrinking the database is a good idea or not, but I am arguing that ignoring Chintan's problem is a *bad* idea. For him, at least.
Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.|||I *love* useless advice.
Listen to Chintan. *He* needs to shrink the database-and nobody will tell him how. Doesn't matter whether it's a good idea or not. He's been told to shrink the database. He has two options-shrink the database or leave his job. Your advice boils down to option 2: leave his job because they're telling him to do something that *you* don't think is a good idea.
I'm not arguing whether or not shrinking the database is a good idea or not, but I am arguing that ignoring Chintan's problem is a *bad* idea. For him, at least.
Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.
It's NOT useless advice. It is sound, practical advice.
The only thing I would add to it would go something like this:
What you face is not a technical issue. It is a business issue. Business people respond better to technical problems when they are presented in business terms. This usually means presenting an issue and couching it in terms of cost versus benefit.
I would approach the problem in this fashion:
John [or my manager's name],
I am technically unable to shrink the database because of limitations in MS SQL Server. There are several ways to deal with this issue:
1. We can do nothing. Eventually the application will fail because there is insufficient storage. This will cost the business no money, but the downside is that the application will no longer function and users will not have access to the most recent data.
2. We can add disk space to the existing server. This would involve purchasing XX disks at YY cost per disk for a total of ZZZ dollars. I do/do not have experience installing these disks and so there is/is not additional cost associated with bringing in a technician to do the installation.
3. We can selectively eliminate more data than what is being purged now. Instead of C years' retention, we can instead choose B years' retention. This will/will not put us in violation of selected audit rules/business requirements. Once more data has been eliminated, it may be possible for me to shrink the database effectively.
My recommendation would be to implement Option X above.
I look forward to working through this issue with you.
Regards,
Don B. Azalea
My advice:
1. Never approach your manager with a business problem without having at least two solutions that you have thought through.
2. Always include the option to do nothing. It at least rephrases the problem in terms that a manager can comprehend.
3. Learn to speak in terms of Return on Investment. It will make you a more well-rounded (and hence valuable) person in your operation.
4. Never talk down to your boss/supervisor. They make decisions that affect your paycheck. Speak to them in terms that they can understand, but never assume that they are ignorant. Try to step out of your world on occasion and see things from their point of view. It can be very enlightening.
Good luck.
By the way, I agree with Pootle and Blindman here: shrinking the db is NOT the way to go.
Regards,
hmscott
Edited for clarity.|||Pootle Flump...nice sig!!!
:D
hmscott|||Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.Hint: It's not the advice that is useless...|||sometimes, late at night, when the wind is still and the water on the pond is a smooth black mirror, you can hear the Flump calling out, wailing a sad song of how you shouldn't shrink databases.
who will listen?|||If the advice doesn't solve the problem, then it's useless-IMO. The problem isn't whether or not to shrink the database-that's already been decided (and not at my level). If I disagree with that decision I can quit or be fired.
The problem is *how* to shrink the database. Your advice doesn't solve that, so it's useless. JMHO.|||Should have addressed hmscott's reply, too. That was a nice reply & would be useful in other situations. Doesn't work here as the decision has already been made. Stating that I'm unable to shrink the database would work if I actually were unable to shrink it-but that's not the case. Both my boss & his boss know that the database can be shrunk so that approach simply tells them that *I* can't shrink it-and that's likely to get me fired. (Don't know about where you work but being unable to do something you were hired to do is grounds for dismissal here.)|||part of my job is to provide managers, users and developers with what i consider the best practices and the best options when it comes to all matters database. if i do not do that, then I am failing them by not providing them the best solution possible.
i understand that money might not always be there for the hardware I desire or require, but it is my job to point out the need. from what i understand you can mitigate the effects of the shrink with a little index defrag or or reindex. however it seems part of the problem is the time the shrink takes and the process will be even longer if you add this step in.
so thems be the options mr. manager. buy me some disks, or i am going to bog down my server by doing some expensive shrinking and defragging. do DBAs get fired? I never recall seeing it happen. half decent ones seem to hard to find. I have certainly behaved in such a way i would have fired myself once or twice.|||so thems be the options mr. manager. buy me some disks, or i am going to bog down my server by doing some expensive shrinking and defragging. do DBAs get fired? I never recall seeing it happen. half decent ones seem to hard to find. I have certainly behaved in such a way i would have fired myself once or twice.
:D
I have never done such a thing. never
Well, maybe once...there was a time when I dropped the entire schema for our data warehouse...but that's a tale for a different day.
Regards,
hmscott|||Should have addressed hmscott's reply, too. That was a nice reply & would be useful in other situations. Doesn't work here as the decision has already been made. Stating that I'm unable to shrink the database would work if I actually were unable to shrink it-but that's not the case. Both my boss & his boss know that the database can be shrunk so that approach simply tells them that *I* can't shrink it-and that's likely to get me fired. (Don't know about where you work but being unable to do something you were hired to do is grounds for dismissal here.)
Don't know what your situation is, so I can't address it directly.
Conditions under which I can understand and recommend that a database be shrunk:
1. You've purged a "significant" amount of data from the data files (through truncate or delete or drop). You do not expect to re-add this data anytime soon.
NB: "Significant" is subjective here, it can be any value, but I would generally look for anything greater than 25%.
2. You've had an issue with a log file filling up and extending beyond your intended size limit.
Some ways of shrinking your database...
Instead of using DBCC SHRINK DATABASE, consider using DBCC SHRINKFILE. This can be less expensive in terms of I/O on the disk.
You might also consider deleting some indexes in an attempt to trade off performance for space.
You might consider moving some data to another server, making a linked server and accessing the moved data through a linked server query.
On a side note, if you think that you might lose your job on account of this one issue, then your job is already lost. I do not mean this to be facetious or rude. You must have sufficient confidence in your skills and trust in your boss to be able to admit to ignorance of a particular issue and be able to get the help you need to fix it. If that confidence isn't there or the trust isn't there, then it's only a matter of time before your boss will find someone who has the skills or in whom he can trust.
Just my .02. It's worth what you paid for it.
Regards,
hmscott|||Well, I agree with your comments about the job. Long term either I'll be gone or my boss will, but that doesn't mean I want to (actually, can afford to) leave right now.
I appreciate your advice & will look into SHRINKFILE. Somehow overlooked that while I was checking on SHRINK DATABASE.
Thrasy, essentially those options were presented to my manager-who responded that buying more disks was out. He knows the database can be shrunk but it's up to me to actually do it and all the advice I've found so far is "don't do it". Even though I agree with it, it's still useless because I can't follow it & still keep my job.|||No problem. FWIW, it appears that I will soon be moving on though not by my choice nor by my manager's choice.
Long story, but it sucks 'cause I like it here.
Regards,
hmscott|||Calvin, obviously your boss does not respect your opinion or advice.
And after reading your posts in this thread, equally obvious to me is WHY he doesn't respect your advice.
Pootle gave good advice. The fact that your boss refuses to listen to the advice of some of the best DBAs in the world, coupled with your labeling that advice as "useless", makes me think that you must be a pretty good fit for your current position of employment. I can see why you are so keen on keeping it.
Basically, if your boss refuses to listen to you that is not Pootle's problem, and it is not my problem, and it is not SQL Server's problem. Who's left?|||Guys! Guys! Guys!
Lots of reply, thanks for that especially Calvin-c, flump and hmscott.
I got confused after reading all replies.
My actual problem is:
-> How to more effectively shrink SQL database?
-> Is there any alternate way through which I can get free spaces after expiring?
As I told there is appx. 270 GB free space available but in scattered form means in one drive there is available free space is 40 GB while in other 12 GB. And sizes of datafiles are very big so I can't move them from one drive to another.
So I don't think that I have any option rather that going for shrinking database however it is not preferable.
And as there is lots of free space, so I can't demand for more disk space in any condition.
So still any serious advice?|||Do you know how much data you actually delete? You know it is more than a few MB yes? Also what commands are you executing?
Just in case I forget to mention later - you need to reindex after a shrink too as you will have fragmented your indexes and the database will run poorly for many reporting applications.|||Also - what version? Is this 2000 or a database that has been upgraded to 2005? If so please run DBCC UPDATEUSAGE|||I use SQL 2000. And everyday first I expire data oldest 2-3 days data using min (timestamp) query.
Appx. 3 GB of data per day, however it depends on the log file for that day but on the average it shows reduced size of that database up to 6 to 9 GB.
In expiring, I dont face any problem but after then when I shrink database. There are total 36 tables and 24 tables get shrunken successfully but 12 tables do not shrink and those tables use much of the memory. And at the end it frees hardly 3-4 MB.
If you know how to shrink those 12 tables then most probably my problem will be solved.|||What code are you running to get the used and available space for your database? And what code do you run to shrink it?|||I recently had to shrink a 50 gig database after major clean up (previous company was ...). It went from 50 to 1 when I was done, but I had to re-run the shrink probably 100 times over a weekend before it was done. Also, I was able to do it before going live with the db.
I also would switch back and forth between shrinkfile and shrinkdatabase. Sometimes it would free up nothing, and then every 10 times or so it would give me back a couple gigs.|||I use expiredata command from DOS to expire database for specified date. However this command is only available through application I use.
And after then I shrink the tables one by one from SQL Server Enterprise Manager -> shrink database.
I have also tried shrinkdatabase and shrinktable but they dont work.|||This is what I use but I only need to deal with 2 GB database since I have my clients running MSDE Rel A. I don't know how long it will take on a 250 GB database.
HTH
backup log MyDataBase with truncate_only
dbcc loginfo
go
dbcc shrinkfile (MyDataBase_log);
dbcc loginfo
go
dbcc shrinkfile (MyDataBase_DATA);
dbcc loginfo
go|||I use expiredata command from DOS to expire database for specified date. However this command is only available through application I use.
Presumably that just deletes data from tables?
And after then I shrink the tables one by one from SQL Server Enterprise Manager -> shrink database.
I have also tried shrinkdatabase and shrinktable but they dont work.
Well - you don't shrink tables you shrink files\ databases.
Did you run updateusage?
What do you get if you run this:
USE my_db
go
CREATE TABLE #helpfile
(
name sysname
, fileid smallint
, filename nvarchar(256)
, filegroup sysname NULL
, size nvarchar(18)
, maxsize nvarchar(18)
, growth nvarchar(18)
, usage varchar(9)
)
INSERT INTO #helpfile
EXEC dbo.sp_helpfile
SELECT *
, file_size_mb - space_used_mb AS spare_space
FROM (
SELECT name
, usage
, CAST(SUBSTRING(size, 1, CHARINDEX(' ', size) - 1) AS INT) / 1024 AS file_size_mb
, CAST(fileproperty(name,'SpaceUsed') * (8/ 1024.0) AS INT) AS space_used_mb
FROM #helpfile
) AS der_t
DROP TABLE #helpfile|||It shows all datafiles and spare space. The total space is 24813 MB. And there are 4 files which have spare space more than 5000 MB. So if I manage with there files, my problem will automatically be solved.
But main problem is that as I earlier said; when I shrink these files it hardly frees 1-2 MB after whole day process.|||Can you confirm you ran updateusage before running that script?|||What is updatemanage? I don't know what it is? Will you please tell me what it is and how will it be useful to me?|||Sorry, I mean updateusage. Will you please tell me what it is?|||Check the BoL entry for details. The short answer is that usage stats are not necessarily accurate in SS 2K - databases. Fixed for SS2005. updateusage updates the stats to ensure you are looking at accurate info.
USE my_db
GO
DBCC UPDATEUSAGE (0)Once complete then rerun the code I supplied. We can then be assured the results are correct.|||Check the BoL entry for details. The short answer is that usage stats are not necessarily accurate in SS 2K - databases. Fixed for SS2005. updateusage updates the stats to ensure you are looking at accurate info.
USE my_db
GO
DBCC UPDATEUSAGE (0)Once complete then rerun the code I supplied. We can then be assured the results are correct.|||I executed both codes but no change in result. It shows the same as before.|||I executed both codes but no change in result. It shows the same as before.Good stuff.
I forgot about sysfiles (that's what comes of using 2005 for 6 months)...
How do you get on with the below? Substitute file_name with the name of the first file (not database) you want to shrink.
USE my_db
GO
SELECT name,size
FROM sysfiles
GO
DBCC shrinkfile (file_name, 0)
GO
SELECT name,size
FROM sysfiles
GO
Some other questions - is this database we are shrinking no longer used for writing data to? Do you run dbreindex or indexdefrag? Do you back up this database?|||I find this threaad amusing
What do you mean you "expire" data?
Do mean you delete data?
Then if that's what you are doing, then why do you keep around the other database that is "no longer in use"
And I haven't read anywhere in this thread that says why shrinking the database is not a good idea.
The reason is, that space is allocated, and when you delete the data, the space becomes available, and you don't incur the overhead of shrinking, AND growth.
And how come nobody asked about the transaction log size?|||I *love* useless advice.
Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.
This is priceless
Would you like fries with that advice?|||And I haven't read anywhere in this thread that says why shrinking the database is not a good idea.You are kidding right? The first two pages pretty well talk about nothing else.|||You are kidding right? The first two pages pretty well talk about nothing else.
Where?
I just reread it. Nowhere does anyone say that it's just gonna grow again anyway, so why incurr the overhead?
Want tto post a quote where it discusses that?|||if you can't shrink the data, and...
you can't add new disk space, and...
you cannot persuade people of another course of action, then...
basically you are stuffed with a capital F
then Id examine the data that you are archiving... does the full 427Mb db need to be where it is, does it really need to be so close to production (ie could it be archived off in a better mechanism). how often do they access data that is 15 months old., can it be filleted to remove much of the redundancy, or just to keep the data that is actually required
id want to seriously look at table design.. are there columns that are poorly defined in terms of size, are there redundant columns/tables that can go
are there tables or rows that can be dropped within the archive.
but that is not a short term fix, and right now I guess short term fix is what is required. The most immediate short term fix is bigger disk.|||This is a common myth, that you can shrink databases without removing data. It is NOT a zip file, it does not compress.
No amount of sitting on the suitcase will allow it to buckle any easier.
What DBCC SHRINKFILE actually does, is reassign excess space allocated at the end of the file and moves it to the transaction log and doesn't really reduce the size at all until the database is backed up and the transaction logs dumped and then not really at all if you have your database set to allocate 10% growth over your current database size.
You can site this article from Microsoft explaining what "shrinking" the database actually does: DBCC SHRINKFILE (http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx)
I am going to assume that you ARE backing up the database and dumping the transaction logs when you do so.
Bits of data are not compressible. You can reorganize it, and label it in a way that allows it to be more streamlined and smaller, but those are programming/coding changes to the database itself. You can do performance tuning on your database, and implement some best practices if possible, but you still can not physically reduce the size of a bit of data.
If the requirement is that you MUST keep 15 months of data, then you must have enough space to store 15 months of data. If your company has grown in any way since the inception of the database, then your database will also grow accordingly.
That's what we've been trying to explain. Shrink is a misnomer. Think of it as a full glass of water in a plastic cup. You can put your hand over the top and suck all the air out of the little space above the water. But it does not reduce the water, and as soon as your hand (the 10% growfile function) comes off the top of the glass, you still have a container that is the same size and still has the same amount of water.|||Stop that, Mo.
Damn.
You're supposed to say "Refer to the BOL for details of the SHRINKFILE process". ;)
And you forget that this thread is being posted to by primarily men. Your analogy should refer to a glass of beer to be most effective.
Other than that, good description!|||Only rednecks serve beer in plastic cups! =P|||What? Glasswear at a kegger? That's just askin' for cracked skulls later on in the evening.
Besides, we like to drink our beer from a mason jar.|||fyi, here's a script courtesy of MVJ over on sqlteam that might be of use to you:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355|||Want tto post a quote where it discusses that?Sure:
http://www.dbforums.com/showpost.php?p=6283790&postcount=8489 ;)
We pointed out several reasons why he should avoid doing this if at all possible however we didn't make your point because of this little nugget:
Yes, I have 2 databases. One is currently in use and occupies 202 GB and the other, older one, which is not in use but have to keep data up to 15 month older, occupies appx. 427 GB.|||Liebling - I've read the BoL 2005 entry (which has more detail than B0L 2000 on this) and Inside SQL Server 2005 and that is not my understanding of shrinkfile.|||I haven't read the 2005 entry, but my assumption was that if they were not willing to spring for hard drive space (really cheap) then they were probably also still on 2000. In fact, I think he stated he was on SQL 2000 (http://www.dbforums.com/showpost.php?p=6282845&postcount=25). You can't refer to SQL 2005 BOL's because they aren't the same beast. Similar, but a lot of recoding and changes went into 2005.
In my experience, and according to every SQL 2000 reference to SHRINKFILE I've read thus far, that is exactly what it is doing.|||Ok - reread the 2000 entry. It is, as far as I can tell, the same as the 2005 entry except the 2005 entry is more thorough.
What DBCC SHRINKFILE actually does, is reassign excess space allocated at the end of the file and moves it to the transaction log and doesn't really reduce the size at all until the database is backed up and the transaction logs dumped and then not really at all if you have your database set to allocate 10% growth over your current database size. I don't get that at all - my understanding and what it says in BoL is that pages are moved from the end of the file and allocated to the beginning of the file (depending on various stuff, including the amount of free space indicated when running the dbcc command, size of model and min size of the db). There is no mention of moving stuff from one file to another except when emptyfile is specified.
Ultimately the mechanism behind shrinkfile is (as I see it) something like indexdefrag. It simply moves pages within the file around, just for a different purpose and using a different algorithm. Nothing within pages changes so the total used space remains constant.|||Right, it moves the pages that are not complete around a bit. Most of those pages are actually placeholders created by the grow function from my understanding. But yes, much like defrag it still doesn't really change the size of the container just the places the data is stored. It never shrinks the data.
It doesn't actually say that it moves anything to the transaction log file, but run it and see how much your TL grows. It's writing a refrence to where it was to where it is going in my understanding.|||Right, it moves the pages that are not complete around a bit. It moves any pages that fall in the part of the file to be "emptied" around irrespective of their contents. It just sees them as 8kb blocks. (TMK of course).
It doesn't actually say that it moves anything to the transaction log file, but run it and see how much your TL grows. It's writing a refrence to where it was to where it is going in my understanding.Way ahead of you :) Wrote the below this lunch break.
SET NOCOUNT ON
GO
USE master
go
CREATE DATABASE pf_test
GO
USE pf_test
GO
ALTER DATABASE pf_test SET RECOVERY SIMPLE
GO
CREATE TABLE dbo.size_info
(
order_col TINYINT IDENTITY(1, 1)
, stage VARCHAR(20)
, name NVARCHAR(15)
, size_mb DECIMAL(8, 5)
, free_space_mb DECIMAL(8, 5)
)
GO
INSERT INTO dbo.size_info
SELECT 'Initial', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
CREATE TABLE dbo.dummy_table
(
number INT
, dummy_data VARCHAR(7990)
)
GO
INSERT INTO dbo.dummy_table
SELECT number, REPLICATE('a', number)
FROM admin.dbo.numbers
WHERE number BETWEEN 1 AND 7990
GO
INSERT INTO dbo.size_info
SELECT 'Full', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DELETE
FROM dbo.dummy_table
GO
INSERT INTO dbo.size_info
SELECT 'Post Delete', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DBCC SHRINKFILE (pf_test, 0) WITH NO_INFOMSGS
GO
INSERT INTO dbo.size_info
SELECT 'Data Shrunk', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DBCC SHRINKFILE (pf_test_log, 0) WITH NO_INFOMSGS
GO
INSERT INTO dbo.size_info
SELECT 'Log Shrunk', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
SELECT *
FROM dbo.size_info
ORDER BY order_col
GO
USE master
go
DROP DATABASE pf_test
GO
----
--take 2
USE master
go
CREATE DATABASE pf_test
GO
USE pf_test
GO
ALTER DATABASE pf_test SET RECOVERY FULL
GO
CREATE TABLE dbo.size_info
(
order_col TINYINT IDENTITY(1, 1)
, stage VARCHAR(20)
, name NVARCHAR(15)
, size_mb DECIMAL(8, 5)
, free_space_mb DECIMAL(8, 5)
)
GO
INSERT INTO dbo.size_info
SELECT 'Initial', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
CREATE TABLE dbo.dummy_table
(
number INT
, dummy_data VARCHAR(7990)
)
GO
INSERT INTO dbo.dummy_table
SELECT number, REPLICATE('a', number)
FROM admin.dbo.numbers
WHERE number BETWEEN 1 AND 7990
GO
INSERT INTO dbo.size_info
SELECT 'Full', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DELETE
FROM dbo.dummy_table
GO
INSERT INTO dbo.size_info
SELECT 'Post Delete', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DBCC SHRINKFILE (pf_test, 0) WITH NO_INFOMSGS
GO
INSERT INTO dbo.size_info
SELECT 'Data Shrunk', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DBCC SHRINKFILE (pf_test_log, 0) WITH NO_INFOMSGS
GO
INSERT INTO dbo.size_info
SELECT 'Log Shrunk', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
SELECT *
FROM dbo.size_info
ORDER BY order_col
GO
USE master
go
DROP DATABASE pf_test
GO Produces (depends on your model settings of course):
order_col stage name size_mb free_space_mb
--- ------- ----- ------------ ------------
1 Initial pf_test 2.18736 0.81245
2 Initial pf_test_log 0.54684 0.27342
3 Full pf_test 42.18480 0.74995
4 Full pf_test_log 42.93475 7.91356
5 Post Delete pf_test 42.18480 27.62323
6 Post Delete pf_test_log 111.74285 42.98162
7 Data Shrunk pf_test 2.18736 0.81245
8 Data Shrunk pf_test_log 111.74285 101.15759
9 Log Shrunk pf_test 2.18736 0.81245
10 Log Shrunk pf_test_log 0.54684 0.31248
order_col stage name size_mb free_space_mb
--- ------- ----- ------------ ------------
1 Initial pf_test 2.18736 0.81245
2 Initial pf_test_log 0.54684 0.28123
3 Full pf_test 42.18480 0.74995
4 Full pf_test_log 42.93475 7.91356
5 Post Delete pf_test 42.18480 27.62323
6 Post Delete pf_test_log 111.74285 42.98944
7 Data Shrunk pf_test 2.18736 0.81245
8 Data Shrunk pf_test_log 111.74285 101.16540
9 Log Shrunk pf_test 2.18736 0.81245
10 Log Shrunk pf_test_log 0.54684 0.31248|||Once again I would be grateful if someone could enlighten me on copying & pasting from SSMS to this flipping page whilst retaining the formatting. Ideally not mocking me this time.|||Once again I would be grateful if someone could enlighten me on copying & pasting from SSMS to this flipping page whilst retaining the formatting. Ideally not mocking me this time.
I'm betting it's a browser issue. I use firefox and I've never been able to preserve the colorization of code either. I assume that's what you are referring to? probably it only works with IE.|||I am an IEer. Works fine on SQLTeam not here. And it was the same at my old job and now this one, both QA and SSMS. Poor Poots :o|||Good stuff.
I forgot about sysfiles (that's what comes of using 2005 for 6 months)...
How do you get on with the below? Substitute file_name with the name of the first file (not database) you want to shrink.
USE my_db
GO
SELECT name,size
FROM sysfiles
GO
DBCC shrinkfile (file_name, 0)
GO
SELECT name,size
FROM sysfiles
GO
Some other questions - is this database we are shrinking no longer used for writing data to? Do you run dbreindex or indexdefrag? Do you back up this database?
I use above code for 2 days and for around 20 hrs. but it hardly frees 4-5 MBs. So it is also not working properly.
And this database is not used for writing only used for report viewing. As having less memory, we dont hv backup of this database.
No, I dont run dbreindex or indexdefrag as it is for faster retrival of data but we dont face any problem of retrival.|||What about back ups?|||What about back ups?
As I told in post no.58 that we dont hv backup as having less free space.|||As I told in post no.58 that we dont hv backup as having less free space.
Well, that has got to be as good a reason not to buy more disk space....
If you go to the Access Forum, I'm sure they'll have an answer for you|||Access forum? But I am using SQL. then how can they know my problem?|||Access forum? But I am using SQL. then how can they know my problem?Don't even try to make sense out of Brett's coment... It makes sense in a larger context (based on many different threads), but its just outright bizzare in the context of just this one thread.
Brett's having one of those mornings... Or maybe having three or four of those mornings, all in one day!
-PatP|||1. You don't have any backups, and it sounds like a VLDB...how quaint
2. You don't try the code in the link from Kristen...how clever
3. You ignore all the advice from everyone here in the SQL Server forum
I just thought maybe all of the sophisticated programmers in the Access forum might have the answer you are looking for
Nomad|||you'd have a lot more free space if you just dropped the database.
you could just tell your boss that it got irreversibly corrupted, and since you don't have a backup, that should be fine, right?
I mean, they probably expect to lose the data entirely one day through some mishap, since you don't have backups.
read: maybe you should think about creating a backup.|||A brief synopsis for those just now joining our story:
1st Skydiver: How can I shrink my parachute?
Instructor: Don't shrink your parachute.
1st Skydiver: I don't have enough room in my pack for it.
Instructor: Get a bigger pack.
2nd Skydiver: Try cutting a big wedge out of one side.
Instructor: Don't shrink your parachute. It is as big as it needs to be.
1st Skydiver: My pack has plenty of space. I can still fit a sandwich in it for lunch.
2nd Skydiver: ...or you could just cut a large hole in the center. That would probably work too.
Instructor: Better make sure your reserve parachute is working before you do that.
1st Skydiver: I don't have a reserve chute. I told you, I don't have enough space in my pack.
Instructor: Geronimo!|||Dude: They have company policies about data retention but you don't have a backup? That has GOT to take the cake.|||For SQL Server files which won't shrink, try changing the backup method to Full, then shrink, then change it to simple, then shrink. It works!! I guarantee it. I had the same problem with SQL db's not shrinking and discovered this hiccup by Microsoft.|||For SQL Server files which won't shrink, try changing the backup method to Full, then shrink, then change it to simple, then shrink. It works!! I guarantee it. I had the same problem with SQL db's not shrinking and discovered this hiccup by Microsoft.
I dont know what is full backup. BTW, i told that we dont take backup of that older database. If u had the same problem, how u solved it? And I am not allowed to take backup of that database.|||Read posts 61 (http://www.dbforums.com/showpost.php?p=6284483&postcount=61) and 66 (http://www.dbforums.com/showpost.php?p=6284560&postcount=66) :)|||Wow, this was fun to catch up on after a week on the beach, thanks guys! It's a relief to know that the good threads have staying power.
After n posts saying "I can't do anything you guys suggest, but I still need you to tell me how to fix my issue", if the reader/contributor can resist the urge to throw up his/her hands in frustration, and if he/she can distance him/herself emotionally, a thread becomes almost a source of glee.
This is especially true when a parachuting analogy can be successfully applied.|||Where d'ya go? Stop by the corral and fill us insql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment