Solved

sp_helpfile & size

Posted on 2011-02-25
6
556 Views
Last Modified: 2012-05-11
the size mentioned for the index file in sp_helpfile is not accurate. Is this what it began with? and apparently, there has been growth?
0
Comment
Question by:anushahanna
  • 2
  • 2
  • 2
6 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 34982655
You could try a before that:

DBCC UPDATEUSAGE
(   { database_name | database_id | 0 }
    [ , { table_name | table_id | view_name | view_id }
    [ , { index_name | index_id } ] ]
) [ WITH [ NO_INFOMSGS ] [ , ] [ COUNT_ROWS ] ]


http://msdn.microsoft.com/en-us/library/ms188414.aspx
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34982669
good idea- can i run this on a busy system in production, or should wait till maintenance window?
0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 250 total points
ID: 34982676
It should be correct.  Per BOL sp_helpfile
Returns the physical names and attributes of files associated with the current database. Use this stored procedure to determine the names of files to attach to or detach from the server.


possibly run DBCC UPDATEUSAGE
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 17

Expert Comment

by:dbaSQL
ID: 34982690
Maintenance window is best.  See best practices in BOL:


Best Practices
Always run DBCC UPDATEUSAGE after upgrading a database from SQL Server 2000. The page and row counts are corrected and are maintained thereafter.


Do not run DBCC UPDATEUSAGE routinely for databases created in SQL Server 2005 or higher or on upgraded databases that have been corrected once by using DBCC UPDATEUSAGE. Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should not be used only unless you suspect incorrect values are being returned by sp_spaceused.


Consider running DBCC UPDATEUSAGE routinely (for example, weekly) only if the database undergoes frequent Data Definition Language (DDL) modifications, such as CREATE, ALTER, or DROP statements.

0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 34982701
I would NOT run it on live systems under load - please see below from BOL:

Do not run DBCC UPDATEUSAGE routinely for databases created in SQL Server 2005 or higher or on upgraded databases that have been corrected once by using DBCC UPDATEUSAGE. Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should not be used only unless you suspect incorrect values are being returned by sp_spaceused.

Consider running DBCC UPDATEUSAGE routinely (for example, weekly) only if the database undergoes frequent Data Definition Language (DDL) modifications, such as CREATE, ALTER, or DROP statements.

0
 
LVL 6

Author Comment

by:anushahanna
ID: 34982804
thanks very much.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question