October 14, 2009 at 1:10 pm
Hi,
We have Upgraded SQL Server 2000 Standard editon x86 with SP4 to SQL Server 2005 Enterprise edition x64 with SP3 (in a New server) using side-by-side upgrade method.
I using the below query to run daily to monitor the Database growth.
sp_msforeachdb 'USE [?]
select Name,
(convert(float,size)) * (8192.0/1048576) File_Size,
(convert(float,fileproperty(name,''SpaceUsed''))) * (8192.0/1048576) MB_Used,
((convert(float,size)) * (8192.0/1048576) - (convert(float,fileproperty(name,''SpaceUsed''))) * (8192.0/1048576)) MB_Free
from sysfiles
order by
fileproperty(name,''IsLogFile'')'
Before Upgrade:
The database files size details:
Name File_Size MB_Used MB_Free
-------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------- ------------
PROD_DATA 18050.0 17559.25 490.75
PROD_LOG 05.9375 33.59375 7372.34375
After Upgrade:
The database files size details:
Name File_Size MB_Used MB_Free
-----------------------------------------------------------------------------------
PROD_DATA 18050.0 8311.0 9739.0
PROD_LOG 8146.5625 117.53125 8029.03125
I did not understand why MB_Used is decreased 8 GB all of the sudden. After that its showing the same file size daily. I mean the below:
Name File_Size MB_Used MB_Free
-----------------------------------------------------------------------------------
PROD_DATA 18050.0 8311.0 9739.0
PROD_LOG 8146.5625 117.53125 8029.03125
What should be the reason for such a huge decrease in MB_Used? Does Upgrade remove any unwanted space (Disk fragmentation or something like that)?
Plz advice
Thanks
October 14, 2009 at 1:50 pm
Since this was side by side, are those the current file stats for the 2000 instance?
Did you by chance perform a Reindex operation on the 2000 Instance while the file stat query was being executed?
Three things that I could think of for this issue:
1. An error in the query results for the 2000 instance (maybe a SQL 2K bug).
2. An indexing operation was being performed at the execution time of the query provided.
3. Not all data and/or schema was transferred over to the new instance.
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
October 14, 2009 at 7:12 pm
Run the same statements after running dbcc updateusage with count_rows on the 2000 database and compare them.
MJ
October 14, 2009 at 10:35 pm
1. An error in the query results for the 2000 instance (maybe a SQL 2K bug).
May be, I'm not sure.But How can we prove that its a bug?
2. An indexing operation was being performed at the execution time of the query provided.
No, I have included this query in SQL Agent job & its runs daily(But not when an index operation runs) and stores the values in a text file and appends to it daily. I had the below values for months in SQL 2000
Name File_Size MB_Used MB_Free
------------------------------------------------------------------------------------
PROD_DATA 18050.0 17559.25 490.75
PROD_LOG 05.9375 33.59375 7372.34375
3. Not all data and/or schema was transferred over to the new instance.
No, because I have used Backup & Restore method to Upgrade the database. So everything is restored and we got NO complaints from application folks so far(which confirms everything is fine). Its already 2 months completed after the upgrade. Everything is fine. But I'm surprised that why "Space used" is decreased suddenly?(still the .mdf file size is same which it was used to be in SQL 2000)
Run the same statements after running dbcc updateusage with count_rows on the 2000 database and compare them
The SQL 2000 server is no longer available.Its already decommissioned.
May be from the front end, they have removed unwanted data...I need to investigate
thanks for your help
October 14, 2009 at 10:56 pm
My 2 cents... I could be entirely wrong :ermm:
Reindex organizes the pages but does not release the space on the files back to OS. So your file size will not decrease.
But when you restore the database on to SQL 2008 the files use only the space that is actually required.
"Keep Trying"
October 14, 2009 at 11:15 pm
Reindex organizes the pages but does not release the space on the files back to OS. So your file size will not decrease.
But when you restore the database on to SQL 2008 the files use only the space that is actually required.
But I have Upgraded to SQL Server 2005 NOT 2008...Is the above true for SQL Server 2005 too..
thanks
October 15, 2009 at 2:43 am
Sorry it was a typo i meant 2005
"Keep Trying"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply