August 25, 2011 at 8:58 am
OK, I want a script I can run monthly to output three things for any database I want. I want to be able to run it for several Db if I need to. I am assuming I need a text file to read in the database name(s) I want to look at on my SQL server, so I will name it "customers.txt" and place the database name(s) in it. \\wvsdata01\dsdata001\Mycustomer name is the format.
I'll need to create a FileSystemObject and a timestamp keep track of the databases I'm reading from and the time and date I read them from.
The three things I want to track:
Storage in GB over the last month. It seems the best way to do this is to read the backups and subtract the 1st of the month from the end of the month
Size on disk as reported by the Opeating System in GB
select left(round(size*8.0/1024/1024,2),4) + ' GB' from sys.database_files
This query returns the proper data, but I don't really want it rounded. It returns 0.10--and I'd like 0.123
Lastly, Total file size as report in the itemdatapage table in GB.
SELECT 1.00 * (SUM(CAST(hsi.itemdatapage.filesize AS BIGINT))) / POWER(1024, 3) AS FileSize
FROM hsi.itemdata, hsi.itemdatapage
WHERE hsi.itemdata.itemnum = hsi.itemdatapage.itemnum AND (hsi.itemdata.status = 0) AND (hsi.itemdatapage.physicalpagenum = 0)
Ths query does a god job of returning that..
I would like to output them to a txt file and keep it as a report.
Format:
DBName
7/1/2011 -- 7/31/2011 Storage in GB
0.333008
Size on Disk as reported by the Operating System
1.54
Total file size as reported in the itemdatapage table in GB
1.51 x$9.28 per GB, minimum 10 GB $92.80
August 25, 2011 at 2:04 pm
well you really have two questions rolled into a single post so for the first question about rounding, you are using implicit conversion which will strip trailing zeros.
Try this instead:
select cast(cast(round(size*8.0/1024/1024, 2) as numeric(9,3)) as varchar(20)) + ' GB', name, * from sys.database_files
Now for the second piece. It seems you have a process that requires some level of manual intervention anyway so maybe something like this would work.
create table #CheckDB
(
DBName varchar(100)
)
insert #CheckDB (DBName) values ('DataBaseOne'), ('DateBaseTwo')
select 'USE ' + DBName + ' ' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 'select cast(cast(round(size*8.0/1024/1024, 2) as numeric(9,3)) as varchar(20)) + '' GB'', name, * from sys.database_files'
from #CheckDB
drop table #CheckDB
You would have to set your output to text so the lines are correct. but you could run this, copy and paste the output and it would generate your script. Then if you want to save it to file just use the Results to File output option. This seems a lot easier than all the work of maintaining a file and jumping through all the hoops to do this in a single step.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply