February 11, 2012 at 12:23 pm
Hi Friends,
What is the best method if my datafile .mdf is reach to 100% size?
a) create a new .ndf file or
b) to shrink database or
c) any other solution
Thanks in Advance.
________________________________________
M.I.
[font="Times New Roman"]
February 11, 2012 at 12:44 pm
Like most questions ... the answer is "It Depends"
Because of that I strongly suggest you read:
http://sqlcat.com/sqlcat/b/top10lists/archive/2007/11/21/storage-top-10-best-practices.aspx
Before making your decision.
-----Found after above was posted -----------------
I see in another post of yours at
http://www.sqlservercentral.com/Forums/Topic1250789-146-1.aspx#bm1250793
My database infromation;
Database size = 6GB
Data Free space = 16%
Log Free Space = 94%
Auto growth = Restricted
Now my question to you is why have is your Auto growth Restricted ?
So the recommendation that I originally posted
February 11, 2012 at 1:28 pm
Yet again I see another question which obviously effects the size of your database at:
http://www.sqlservercentral.com/Forums/Topic1250789-146-1.aspx
Now how about try this T-SQL and we can see if any of those indexes are actually being used
/* origingal author: http://www.sql-server-performance.com/2009/obtaining-index-usage-information/
amd slightly modified by this poster */
SELECT o.name Object_Name,i.name Index_name,i.Type_Desc,user_seeks,user_scans,user_lookups
FROM sys.objects AS o
JOIN sys.indexes AS i
ON o.object_id = i.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s
ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE o.type = 'u'
AND i.type IN (1, 2)
-- AND (s.index_id IS NULL)
-- Indexes that have been updated by not used
OR (s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 );
Now removing unused indexes will of course free up space in your database, so my original statement of "It depends" still holds true.
February 11, 2012 at 3:08 pm
Typically grow the data file (unless you've reached the 16TB limit) and ensure you have enough disk space.
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
February 12, 2012 at 11:26 pm
bitbucket-25253 (2/11/2012)
Yet again I see another question which obviously effects the size of your database at:http://www.sqlservercentral.com/Forums/Topic1250789-146-1.aspx
Now how about try this T-SQL and we can see if any of those indexes are actually being used
/* origingal author: http://www.sql-server-performance.com/2009/obtaining-index-usage-information/amd slightly modified by this poster */
SELECT o.name Object_Name,i.name Index_name,i.Type_Desc,user_seeks,user_scans,user_lookups
FROM sys.objects AS o
JOIN sys.indexes AS i
ON o.object_id = i.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s
ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE o.type = 'u'
AND i.type IN (1, 2)
-- AND (s.index_id IS NULL)
-- Indexes that have been updated by not used
OR (s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 );
Now removing unused indexes will of course free up space in your database, so my original statement of "It depends" still holds true.
Hi Bitbucket,
This is the output,
Type_Descuser_seeksuser_scansuser_lookups
CLUSTEREDNULLNULLNULL
CLUSTERED191432180
CLUSTERED01154200
CLUSTERED000
CLUSTERED000
CLUSTERED0155360
CLUSTERED000
CLUSTEREDNULLNULLNULL
CLUSTERED19298193860
CLUSTERED164938149360
________________________________________
M.I.
[font="Times New Roman"]
February 13, 2012 at 6:21 am
Hmmm not as many unused indexes as I thought might be there. Now with the names of the table using those unused indexes you could run:
The undocumented procedure sp_MSindexspace 'dbo.Order Details' --note dbo.Order Details is the table name.
Using the Nortwind DB and the table Order Details the results:
Index IDIndex NameSize (KB)Comments
1PK_Order_Details144Size excludes actual data.
2OrderID 16 (None)
3OrdersOrder_Details16 (None)
4ProductID16 (None)
5ProductsOrder_Details16 (None)
6_WA_Sys_00000004_1367E6060(None)
7_WA_Sys_00000005_1367E6060(None)
8_WA_Sys_00000003_1367E6060(None)
And the determine the amount of space to be regained by dropping the unused indexes.
But I have the feeling that that would not be as useful, and that you will be better served by following Gail Shaws recommendation.
Edited after reading Gail Shaws following post.
Anyone reading this post scroll down and read Gail Shows post
February 13, 2012 at 6:50 am
bitbucket-25253 (2/13/2012)
Hmmm not as many unused indexes as I thought might be there.
Bear in mind those are clustered indexes, so they don't have the same 'unused' rule as nonclustered indexes and they are not wasted space, they are the tables themselves. Dropping a clustered index won't save space and it will probably have other effects
The query to find them was also rather odd...
o.type = 'u'
AND i.type IN (1, 2)
OR (s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 );
So you're looking for any index that is *either* type 1 or 2 (which is clustered and nonclustered) OR has no seeks, scans and lookups. That'll get every clustered and nonclustered index in the system.
In fact, from the OP's response, it appears there are non nonclustered indexes at all in his database and only 10 clustered indexes.
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply