March 5, 2009 at 9:59 am
Okay, I have an odd issue. We are running SQL2000 for a particular DB and we had some issues. We the DB file was moved to a SAN then back to attached storage. Ever since then, no file size or time stamp is changing which is very um scary. However, if I do a backup of the DB the file size is larger. (A full one time backup) Plus the users are abe to add/modify data within the DB itself. I have double triple checked that the DB container is pointed to the correct drive and files and they are pointed to the file in question. Oh, the Log file is size and date stamp are updating correctly, it is just the MDF that is having the problem.
Has anyone seen this, or know why it may happen? The server is a win2K box.
Thanks so much!
Justin
Okay I ran
exec sp_spaceused on my db and here is what I get
database_name database size unallocated space
my_db 22375.19MB -202016.20MB (thats a negative)
reserved data index_size unused
228344784KB 9223648KB 11456408KB 207664728KB
How can the unallocated space be negative? Could this attribute to the file size not modifying? (still not sure about the date stamp)
March 5, 2009 at 11:02 am
does is change on stop/restart of SQL?
strange, I'd be concerned that you are using the wrong files. Have you tried to rename a file? See if SQL is using it?
March 5, 2009 at 11:09 am
I am using the manager to confirm the file location. I have been unable to restart it since it was place back to the attached storage (same path as always & its in production, yikes)
what query can I run to confirm file locations?
March 5, 2009 at 11:36 am
select * from sys.database_files
in master should help.
You can go to sysinternals as well and get the filetracker and see if the file is being held open by the SQL process.
March 5, 2009 at 12:26 pm
Hey,
Mind running CHECKDB and putting the output? The number being negative something is not correct about freepage information in the database. And in one case I actually had issue where my file information in sysfiles for the database did not reflect the right location but the system worked properly still; but that was only with system db.
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 5, 2009 at 12:44 pm
The negative space is likely a result of inaccuracies in your catalog views. Re-run sp_spaceused with the @updateusage parameter set to true; that will run DBCC UPDATEUSAGE which should correct whatever's causing the negative number.
EXEC sp_spaceused @updateusage = N'TRUE';
Disclaimer: I don't believe that DBCC UPDATEUSAGE locks (from what I can see in BOL and from prior experience), but it can take time to finish so be patient.
March 5, 2009 at 1:24 pm
March 5, 2009 at 2:06 pm
Thanks a lot everyone.
I will look into these suggestions off hours.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply