March 10, 2010 at 11:54 pm
This is on 32 bit SQL 2005 std sp3.
March 11, 2010 at 1:16 am
Define 'fails'.
What exactly did you do and what exactly was the result?
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
March 11, 2010 at 1:57 am
HDD real state is somewhere in futire and I can see extra 20 GB allocated to an MDF which is dearly needed by others. On the MDF SHRINKFILE doesnothng & completes in 10 sec or so after that I dont see a change in file size, while using SHRINKDATABASE actual recovery of unused space from the MDF was possible.
March 11, 2010 at 2:08 am
Did you try to run the dbcc shrinkfile without the truncateonly option?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2010 at 2:22 am
Truncate_only will only reduce the size of the DB is there's free space at the end of the file. Shrinkdatabase will move pages to the beginning of the file and then release space. Hence, if there's free space in the file but not at the end of the file shrinkfile with truncate_only will do very little.
Do note that you've badly fragmented indexes by shrinking, and those indexes should be rebuilt. I would strongly suggest getting more hard drive space rather than focusing on 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
March 11, 2010 at 2:47 am
Thanks a lot I got it now.
Any handy script that you can refer to check sequence of pages which are free & used?
March 11, 2010 at 4:52 am
yusufanis (3/11/2010)
Any handy script that you can refer to check sequence of pages which are free & used?
Why you require this ?
but if you want to see how indexes are using pages and how pages are fragmented that you can achieve from select * from sys.dm_db_index_physical_stats(db_id('YourDbName'),NULL, NULL, NULL, NULL);
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 11, 2010 at 8:45 am
If you want a list of what pages are allocated and what aren't, there's no documented way. You could use DBCC Page (undocumented) on all of the GAM and SGAM pages and crack them manually, you could use DBCC IND on each and every index in the DB and record which pages are allocated.
Not sure why that's of value other than investigating the internals of a database...
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
March 11, 2010 at 6:09 pm
Yep just looking at the inside however was a little lazy & have someone scan the ?GAM for me. working on this script myself.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply