Solved

sp_helpfile & size

Posted on 2011-02-25
6
559 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 40

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 40

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

685 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