Monday, April 13, 2009

Find database given physical file name

I was looking for ways to clean up my server disk space. One of them by shrinking sql server log file. I found a log file that was quite big. Unfortunately, there was no database name that has similar name with the physical file. I would not check physical name of each database one by one by issuing sp_helpdb command as my server hold tens databases. I would rather run script to find exactly what database hold this file.
Here is what I get:
SELECT
DB_NAME(database_id) database_name,
physical_name
FROM master.sys.master_files
where physical_name ='physicalfilename'

Tuesday, April 7, 2009

SQL Server Reporting Service database version error

I was learning SQL Server 2005 reporting service, when I got this error during database setup:
Couldn't generate the upgrade script. There is no upgrade script available for this database version.
It seems that the database server that host the report server database is not the same with the reporting services installation.
I checked my database is SQL Server 2005 SP2, but I don't know my Reporting Service version.
Then I searched through Microsoft MSDN and found this page which tells me various ways to check reporting service version.
By checking the log file as suggested, I found that my Reporting Services is SQL Server 2005 SP1.
So now I am going to upgrade my reporting service.