Hi All
I have tables with Text / nText columns. On an average I add 12-15K rows/day in each of these tables.
Is there any easy way to compute the growth of SIZE of these tables, on a daily basis.
I used datalength (Text Col Name), but not sure if that is the right way.
Any ideas.I plot the growth of databases in Excel. To do this I use the backup
information stored in msdb. If it's just a single table you want to monitor
this won't be of any help.
select backup_start_date,backup_size from msdb..backupset where
database_name = 'pubs'
--
HTH
Ryan Waight, MCDBA, MCSE
"Prasanna Prabhu" <pprabhu@.pbs.solutionsiq.com> wrote in message
news:A98763F8-F73C-4EDF-918A-2B5D1D33F2E1@.microsoft.com...
> Hi All
> I have tables with Text / nText columns. On an average I add 12-15K
rows/day in each of these tables.
> Is there any easy way to compute the growth of SIZE of these tables, on a
daily basis.
> I used datalength (Text Col Name), but not sure if that is the right way.
> Any ideas.
>|||How about using sysindexes to get table/index size each night? Just be aware that the info might be
out-of-date (DBCC UPDATEUSAGE).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Prasanna Prabhu" <pprabhu@.pbs.solutionsiq.com> wrote in message
news:A98763F8-F73C-4EDF-918A-2B5D1D33F2E1@.microsoft.com...
> Hi All
> I have tables with Text / nText columns. On an average I add 12-15K rows/day in each of these
tables.
> Is there any easy way to compute the growth of SIZE of these tables, on a daily basis.
> I used datalength (Text Col Name), but not sure if that is the right way.
> Any ideas.
>|||Hi All,
Thanks for your help. Because there is something wrong on Prasanna side. He
asked me post his response here. Here is the reply from the Prasanna:
--
Hi Tibor
I could only get the "COUNT of rows" information from sysindexes.
How will get the information about the SPACE usage of these newly added
rows.
I want to know the rate at which the DB is growing evey 3-4 hours.
----
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Prasanna,
Thanks for your response. I understand that you want to monitor the growth
size of the tables in your database on a daily basis. If I have
misunderstood, please feel free to let me know.
As I understand, there is no easy way to meet your requirements with SQL
Server. However, we can produce a stored procedure selecting data from the
sysindexes table and storing the result records in the specified tables.
Then we perform the stored procedure every day via scheduled job. In that
case, we can monitor the growth size of the tables via reviewing the
specified tables storing the useful records.
Sysindexes contains one row for each index and table in the database and
stored in each database.
For additional information regarding the sysindexes tables, please refer to
the following articles on SQL Server Books Online.
Topic: "sysindexes"
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Sorry for the late reply:
> Hi Tibor
> I could only get the "COUNT of rows" information from sysindexes.
> How will get the information about the SPACE usage of these newly added
> rows.
Check out the pages, dpages and reserved columns. Sysindexes is documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:ibfolwcmDHA.2464@.cpmsftngxa06.phx.gbl...
> Hi All,
> Thanks for your help. Because there is something wrong on Prasanna side. He
> asked me post his response here. Here is the reply from the Prasanna:
> --
> Hi Tibor
> I could only get the "COUNT of rows" information from sysindexes.
> How will get the information about the SPACE usage of these newly added
> rows.
> I want to know the rate at which the DB is growing evey 3-4 hours.
> ----
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>
>
Showing posts with label usage. Show all posts
Showing posts with label usage. Show all posts
Thursday, March 29, 2012
Friday, February 17, 2012
Data from two tables
I am developing a system that tracks phonecall usage. I have an sql server db that includes two tables which include (amongst others) the fields below them:
phone_emp_mast (contains user info.)
-------
Account_Code
First_Name
Last_Name
Department
detail (contains info on individual calls)
---
Account_Code
Duration
I want to write a query that returns the account_code, first_name, last_name, no. of calls made, avg. length of call, max. length of call and total minutes of calls for each employee in a given department
I tried the following query:
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall, SUM(d.DURATION) AS TotalMins
FROM DETAIL d CROSS JOIN
PHONE_EMP_MAST p
WHERE (d.ACCOUNT_CODE = d.ACCOUNT_CODE) AND (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE;
and it keeps returning the error message:
Column p.last_name is invalid in the select list because it is not contained in either an aggregate function or the group by clause
Can anyone figure out a better way of doing this? please?The GROUP BY clause has to specify every column that was not aggregated in the SELECT clause - irritatingly!
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall, SUM(d.DURATION) AS TotalMins
FROM DETAIL d CROSS JOIN
PHONE_EMP_MAST p
WHERE (d.ACCOUNT_CODE = d.ACCOUNT_CODE) AND (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME;|||Thanks for that, I have used the following select statement and it is working perfectly. However I now have a new problem...
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall,
SUM(d.DURATION) AS TotalMins
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME
ORDER BY p.LAST_NAME;
I now need to get a count of the number of fields in the detail table where the duration is less than a minute... can anyone suggest a way of doing this. I tried putting in the bits between the stars
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall,
SUM(d.DURATION) AS TotalMins,
***(SELECT COUNT(d.DURATION)
WHERE duration < 1) AS NoOfCallsLessThan1***
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'operations admin')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, ***d.DURATION, p.ACCOUNT_CODE***
ORDER BY p.LAST_NAME
but when I do this it returns all the calls individually since instead of only returning 1 row for each account code... anyone have any suggestions or is there anything you don't understand about my explanation?
Thanks in advance!|||You need to add a CASE (or DECODE if using Oracle pre-9i) expression in your first query:
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall,
SUM(d.DURATION) AS TotalMins,
SUM( CASE WHEN d.duration < 1 THEN 1 ELSE 0 END ) shortcalls
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME
ORDER BY p.LAST_NAME;
The DECODE version would be:
SUM( DECODE( SIGN( d.duration-1), -1, 1, 0 ) ) shortcalls|||I'm actually using SQL server... do you know if SQL server supports this function?|||Originally posted by kieranodwyer
I'm actually using SQL server... do you know if SQL server supports this function?
Not DECODE - that's Oracle's own, but CASE is ANSI standard so I imagine that at least the latest version of SQL Server supports it.|||Thanks alot Tony... Thats working perfectly!
phone_emp_mast (contains user info.)
-------
Account_Code
First_Name
Last_Name
Department
detail (contains info on individual calls)
---
Account_Code
Duration
I want to write a query that returns the account_code, first_name, last_name, no. of calls made, avg. length of call, max. length of call and total minutes of calls for each employee in a given department
I tried the following query:
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall, SUM(d.DURATION) AS TotalMins
FROM DETAIL d CROSS JOIN
PHONE_EMP_MAST p
WHERE (d.ACCOUNT_CODE = d.ACCOUNT_CODE) AND (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE;
and it keeps returning the error message:
Column p.last_name is invalid in the select list because it is not contained in either an aggregate function or the group by clause
Can anyone figure out a better way of doing this? please?The GROUP BY clause has to specify every column that was not aggregated in the SELECT clause - irritatingly!
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall, SUM(d.DURATION) AS TotalMins
FROM DETAIL d CROSS JOIN
PHONE_EMP_MAST p
WHERE (d.ACCOUNT_CODE = d.ACCOUNT_CODE) AND (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME;|||Thanks for that, I have used the following select statement and it is working perfectly. However I now have a new problem...
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall,
SUM(d.DURATION) AS TotalMins
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME
ORDER BY p.LAST_NAME;
I now need to get a count of the number of fields in the detail table where the duration is less than a minute... can anyone suggest a way of doing this. I tried putting in the bits between the stars
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall,
SUM(d.DURATION) AS TotalMins,
***(SELECT COUNT(d.DURATION)
WHERE duration < 1) AS NoOfCallsLessThan1***
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'operations admin')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, ***d.DURATION, p.ACCOUNT_CODE***
ORDER BY p.LAST_NAME
but when I do this it returns all the calls individually since instead of only returning 1 row for each account code... anyone have any suggestions or is there anything you don't understand about my explanation?
Thanks in advance!|||You need to add a CASE (or DECODE if using Oracle pre-9i) expression in your first query:
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall,
SUM(d.DURATION) AS TotalMins,
SUM( CASE WHEN d.duration < 1 THEN 1 ELSE 0 END ) shortcalls
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME
ORDER BY p.LAST_NAME;
The DECODE version would be:
SUM( DECODE( SIGN( d.duration-1), -1, 1, 0 ) ) shortcalls|||I'm actually using SQL server... do you know if SQL server supports this function?|||Originally posted by kieranodwyer
I'm actually using SQL server... do you know if SQL server supports this function?
Not DECODE - that's Oracle's own, but CASE is ANSI standard so I imagine that at least the latest version of SQL Server supports it.|||Thanks alot Tony... Thats working perfectly!
Subscribe to:
Posts (Atom)