TSQL to find database size

Earlier today I had the need to calculate both the size of my SQL database files as well as the amount of used space in the aforementioned database files. While I was at it I added a percent used column. I found lots of posts out there some horribly complicated and some way too simple and not providing enough detail.

The FILEPROPERTY (http://technet.microsoft.com/en-us/library/ms188401.aspx) was the key here and what I used to simply calculate the amount of space used within the database files.

Also make sure you replace the "<your_database_name_here>" at the beginning of the query or make sure you have the appropriate database selected in SSMS.

-- Title: SQL - Get Database Size -- Description: Displays Database Size, Size Used, and Percent Used -- Author: David Silva USE <your_database_name_here> -- Returns database size as it would be displayed in database properties SELECT ROUND(SUM(size)/128.0,2)*0.000976562 As GB_size FROM sys.sysfiles -- Returns data for database size, file usage, %used. Can be exported to Excel for Pivot rollup SELECT ROUND((size)/128.0,2)*0.000976562 As GB_Size, name, [filename], ((convert(Float,FILEPROPERTY (name, 'SpaceUsed'))) * (8192.0/1048576)) * 0.000976562 As GB_Used, (((convert(Float,FILEPROPERTY (name, 'SpaceUsed'))) * (8192.0/1048576)) * 0.000976562)/(ROUND((size)/128.0,2)*0.000976562) As '% Used' FROM sys.sysfiles

SSRS - SQL Server Reporting Services Scheduling Error

I've worked with SSRS 2000 and 2005. I have done very little in the way of report creation using SSRS. Mainly have used the Report Manager to schedule subscriptions. I also have dealt with issues on the SQL side of SSRS. My role related to SSRS has been the support of the existing infrastructure for SSRS.

Error 8198 - The job failed. Unable to determine if the owner (domain\username) of job myjob has server access (reason: Could not obtain information about Windows NT group/user 'domain\username'. [SQLSTATE 42000] (Error 8198)).

If running the SSRS Report Server service under a network account, you will be unable to schedule reports. The work around for us was to change the SQL Agent job owner to 'sa'. The issue is that once you change the owner to 'sa', you are no longer able to change the job's schedule from reporting services without changing the owner of the job back to the network account used to run the Report Server. So, I created a few shared schedules in SSRS and changed those jobs to an owner of 'sa'. I just assign one of these schedules to my reports. The only time I have to change the owner of a job is when I am changing or creating a new schedule within the Report Manager. I believe this issue will go away in SQL 2005 SSRS but haven't tested it out. If you do test it and find it resolves the issue let me know and I will update the site.

After resolving the issue above I found an article on Microsoft's website that states the cause and workaround. Click here to read it.

XML What's All That About Anyway?

I recently had to work with XML within SQL 2005. It was a learning experience for sure.

So what is XML?

- XML stands for Extensible Markup Language

- If you've ever seen HTML you'll see the similarity

- The purpose of XML as I understand it is to transport, store, and structure data.

XML seems to be getting bigger and bigger. It is everywhere you turn. Apparently it is a great way to structure data and pull/index data. My use for it was in a XML datatype column. It was an IP Address Management database and it was being used to track different devices such as printers, switches, routers, desktops, servers, etc...As such, these different devices types have different properties that needed to be tracked. The best solution was an XML datatype in SQL 2005 that defines the data for each of these devices.

Please feel free to make any corrections or post comments and I will make corrections. I am certainly not an XML expert.

Below is some sql code that I used to query a table...hopefully it will help someone out I wish I had the TSQL before I started.

Here is the csv that was exported from a test table I had setup to test.

18,<Device type="Printer" name="PJSS2234" manufacturer="Xerox" /> 19,<Device type="Printer" name="Pdfs222" manufacturer="Xerox" /> 21,<Device type="Workstation" manufacturer="Dell" Mac_Addr="0000.0000.0000" />

Please feel free to post any comments or questions...Enjoy your XML experience!!!

-- Selects all columns SELECT * FROM TEAM -- Selects my xml datatype column SELECT TeamDoc.query('/Device') FROM Team WHERE TeamDoc.exist ('/Device') = 1 -- Replaces the device where @name equals "P29sdas" and changes @name to "PJSS2234" UPDATE Team SET TeamDoc.modify(' replace value of (/Device/@name)[1] with "PJSS2234"') WHERE TeamDoc.exist ('/Device') = 1 AND TeamDoc.exist('/Device[@name="P29sdas"]')=1 -- Pulls only the @name value from the TeamDoc column using query as XML SELECT TeamDoc.query('data(/Device/@name)') As Name FROM Team WHERE TeamDoc.exist ('/Device') = 1 -- Pulls only the @name value from the TeamDoc column using value as text value SELECT TeamDoc.value('data(/Device/@name)[1]','nvarchar(255)') As Name FROM Team WHERE TeamDoc.exist ('/Device') = 1 -- Pulled all the items from the XML column Select TeamID, TeamDoc.query('data(/Device/@type)') As Type, TeamDoc.query('data(/Device/@name)') As Node_Name, TeamDoc.query('data(/Device/@manufacturer)') As Manufacturer FROM Team WHERE TeamDoc.exist ('/Device') = 1 -- Something you might have in a stored procedure...this was just a test nothing special here Declare @x xml Set @x = (SELECT TeamDoc FROM Team WHERE TeamDoc.exist ('/Device') = 1 FOR XML AUTO) SELECT @x SELECT Team.TeamDoc.value('@name','VARCHAR(25)')AS Node_Name, Team.TeamDoc.value('@type','VARCHAR(25)')AS Node_Type, Team.TeamDoc.value('@manufacturer','VARCHAR(25)')AS Manufacturer FROM @x.nodes('/Team/TeamDoc/Device') Team(TeamDoc) GO

Some links you might find useful(I borrowed some parts of the example from the first link...:

1. http://www.code-magazine.com/article.aspx?quickid=0605081&page=1
2. http://www.w3schools.com/xml/xml_whatis.asp
3. http://en.wikipedia.org/wiki/XML