October 28, 2015 at 3:27 pm
Does there a way or script to find the rows, space used by the specific tables based on every year.
Thanks
October 28, 2015 at 5:04 pm
Admingod (10/28/2015)
Does there a way or script to find the rows, space used by the specific tables based on every year.Thanks
No, you have to monitor/audit it yourself. Normally I use sys.partitions, sys.allocation_units, sys.tables etc. to periodically gather the information.
😎
October 28, 2015 at 5:20 pm
Same here. It allows you to track growth over time, but I only track databases. I don't go down to the table level, although it does sound like an interesting idea. That's a whole new level of graphing. 😉
October 28, 2015 at 5:21 pm
At the database level, I'll say "kind of" but only if you know what your database backup sizes have been or that info is still in the MSDB database.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2015 at 5:27 pm
I am populating all the table information to main table on daily basis based on date time stamp. However, how to create a script to query against the table. Any ideas?
Thanks
October 28, 2015 at 5:30 pm
I have all the information gathered in the tables, howerver i want to show the row count and size growth based on the each year for specific tables.
Thanks
October 28, 2015 at 5:34 pm
So you're already gathering the data about the space used by each table? It sounds like you've got the hard part done already. Post the DDL for your table and some consumable sample data so we have something to work with. Also, what do you expect to see for your output?
October 28, 2015 at 5:37 pm
Admingod (10/28/2015)
I am populating all the table information to main table on daily basis based on date time stamp. However, how to create a script to query against the table. Any ideas?Thanks
Here is one I sometimes use, the MONITORING_DB database being the central collection point
😎
CREATE PROCEDURE [dbo].[LOG_MY_TABLES]
AS
SET NOCOUNT ON;
DECLARE @SQL_STR NVARCHAR(MAX) = N'
USE {{@DBNAME}}
INSERT INTO MONITORING_DB.dbo.TBL_TABLE_LOG
(
[SERVER_NAME]
,[DB_NAME]
,[SCHEMA_NAME]
,[TABLE_NAME]
,[INDEX_NAME]
,[ROWS]
,[TOTAL_PAGES]
,[USED_PAGES]
,[DATA_PAGES]
,[TOTAL_MB]
,[USED_MB]
,[DATA_MB]
)
SELECT
@@SERVERNAME AS [SERVER_NAME]
,DB_NAME(DB_ID()) AS [DB_NAME]
,OBJECT_SCHEMA_NAME(SPART.object_id) AS [SCHEMA_NAME]
,STAB.name AS [TABLE_NAME]
,SIDX.name AS [INDEX_NAME]
,SPART.rows AS [ROWS]
,SUM(SALU.total_pages ) AS [TOTAL_PAGES]
,SUM(SALU.used_pages ) AS [USED_PAGES]
,SUM(SALU.data_pages ) AS [DATA_PAGES]
,(8 * SUM(SALU.total_pages )) / 1024.0 AS [TOTAL_MB]
,(8 * SUM(SALU.used_pages )) / 1024.0 AS [USED_MB]
,(8 * SUM(SALU.data_pages )) / 1024.0 AS [DATA_MB]
FROM sys.tables STAB
INNER JOIN sys.indexes SIDX
ON STAB.object_id = SIDX.object_id
INNER JOIN sys.partitions SPART
ON SIDX.object_id = SPART.object_id
AND SIDX.index_id = SPART.index_id
INNER JOIN sys.allocation_units SALU
ON SPART.partition_id = SALU.container_id
WHERE SIDX.index_id IN (0,1)
AND STAB.object_id > 255
GROUP BY STAB.name
,SIDX.name
,SPART.object_id
,SPART.rows
;
';
DECLARE @EXEC_STR NVARCHAR(MAX) = N'';
SELECT @EXEC_STR =
(
SELECT
REPLACE(@SQL_STR,N'{{@DBNAME}}', QUOTENAME(SDB.name))
FROM master.sys.databases SDB
WHERE SDB.name NOT IN (N'master',N'tempdb',N'model',N'msdb')
AND SDB.state_desc = N'ONLINE'
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)');
EXEC (@EXEC_STR);
Edit: Added QUOTENAME to prevent malicious database naming sql injection;-).
October 29, 2015 at 7:02 am
Eirikur Eiriksson (10/28/2015)
Admingod (10/28/2015)
I am populating all the table information to main table on daily basis based on date time stamp. However, how to create a script to query against the table. Any ideas?Thanks
Here is one I sometimes use, the MONITORING_DB database being the central collection point
😎
CREATE PROCEDURE [dbo].[LOG_MY_TABLES]AS
SET NOCOUNT ON;
DECLARE @SQL_STR NVARCHAR(MAX) = N'
USE {{@DBNAME}}
INSERT INTO MONITORING_DB.dbo.TBL_TABLE_LOG
(
[SERVER_NAME]
,[DB_NAME]
,[SCHEMA_NAME]
,[TABLE_NAME]
,[INDEX_NAME]
,[ROWS]
,[TOTAL_PAGES]
,[USED_PAGES]
,[DATA_PAGES]
,[TOTAL_MB]
,[USED_MB]
,[DATA_MB]
)
SELECT
@@SERVERNAME AS [SERVER_NAME]
,DB_NAME(DB_ID()) AS [DB_NAME]
,OBJECT_SCHEMA_NAME(SPART.object_id) AS [SCHEMA_NAME]
,STAB.name AS [TABLE_NAME]
,SIDX.name AS [INDEX_NAME]
,SPART.rows AS [ROWS]
,SUM(SALU.total_pages ) AS [TOTAL_PAGES]
,SUM(SALU.used_pages ) AS [USED_PAGES]
,SUM(SALU.data_pages ) AS [DATA_PAGES]
,(8 * SUM(SALU.total_pages )) / 1024.0 AS [TOTAL_MB]
,(8 * SUM(SALU.used_pages )) / 1024.0 AS [USED_MB]
,(8 * SUM(SALU.data_pages )) / 1024.0 AS [DATA_MB]
FROM sys.tables STAB
INNER JOIN sys.indexes SIDX
ON STAB.object_id = SIDX.object_id
INNER JOIN sys.partitions SPART
ON SIDX.object_id = SPART.object_id
AND SIDX.index_id = SPART.index_id
INNER JOIN sys.allocation_units SALU
ON SPART.partition_id = SALU.container_id
WHERE SIDX.index_id IN (0,1)
AND STAB.object_id > 255
GROUP BY STAB.name
,SIDX.name
,SPART.object_id
,SPART.rows
;
';
DECLARE @EXEC_STR NVARCHAR(MAX) = N'';
SELECT @EXEC_STR =
(
SELECT
REPLACE(@SQL_STR,N'{{@DBNAME}}', SDB.name)
FROM master.sys.databases SDB
WHERE SDB.name NOT IN (N'master',N'tempdb',N'model',N'msdb')
AND SDB.state_desc = N'ONLINE'
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)');
EXEC (@EXEC_STR);
That's pretty slick, Eirikur. I appreciate how you use string replacement using the rows in sys.databases to generate the final SQL to fire. I've noticed that there aren't many of us who use that approach, even though it's so very efficient. Thanks for sharing it.
Back to the OP. Can you post the DDL of your data collection table, some consumable sample data and what you'd like to see in the output?
October 29, 2015 at 7:46 am
Ed Wagner (10/29/2015)
That's pretty slick, Eirikur. I appreciate how you use string replacement using the rows in sys.databases to generate the final SQL to fire. I've noticed that there aren't many of us who use that approach, even though it's so very efficient. Thanks for sharing it.
I don't believe I knew you back when I did lightning rounds at SPID for this method. Joe Flemming walked up to me and gave me a bear hug because I had just made something he was having severe problems doing a whole lot easier using such token replacement methods.
It does come with a warning though. You absolute must delouse any type of user input because it's still a concatenation of sorts. For example, the use of QUOTENAME (or similar if you need more than 128 characters) is still important, in such cases as well as other delousing techniques.
For the DBA, though... I agree.... It's freakin' awesome and makes life sooooooo much easier.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 29, 2015 at 8:24 am
Jeff Moden (10/29/2015)
Ed Wagner (10/29/2015)
That's pretty slick, Eirikur. I appreciate how you use string replacement using the rows in sys.databases to generate the final SQL to fire. I've noticed that there aren't many of us who use that approach, even though it's so very efficient. Thanks for sharing it.
I don't believe I knew you back when I did lightning rounds at SPID for this method. Joe Flemming walked up to me and gave me a bear hug because I had just made something he was having severe problems doing a whole lot easier using such token replacement methods.
It does come with a warning though. You absolute must delouse any type of user input because it's still a concatenation of sorts. For example, the use of QUOTENAME (or similar if you need more than 128 characters) is still important, in such cases as well as other delousing techniques.
For the DBA, though... I agree.... It's freakin' awesome and makes life sooooooo much easier.
I think you're right. I can just imaging Joe bear-hugging you for something cool like this. I wish I would have seen the surprised look on your face when he did it. 😛
For this case, I'm going to be the only one who has permission to run it and there won't be any input to clean, but your point is well-taken. It's absolutely string concatenation, but it'll be clean as built. Just out of habit, when I created it, the QUOTENAME function is added around the name in the final SELECT that builds the full list.
It's not only much simpler, but it's also faster to build the strings. As an added bonus, we get to avoid a loop. 😀 I honestly don't know why some people rail against it, but it is what it is.
October 29, 2015 at 8:27 am
Maybe a bit redundant now.
Only a single database is targeted.
Ben
-- ben brugman
-- 20151029
--
CREATE TABLE ##TableSizes (name sysname, rows varchar(16),
reserved varchar(16), data varchar(16), index_size varchar(16),unused varchar(16))
INSERT ##TableSizes
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
update ##TableSizes set
Reserved = replace(reserved,'kb',''),
index_size = replace(index_size,'kb',''),
data = replace(data,'kb',''),
unused = replace(unused,'kb','')
CREATE TABLE ##TableSizes2 (name sysname, rows bigint,
reserved bigint, data bigint, index_size bigint,unused bigint)
insert into ##TableSizes2 select * from ##TableSizes
SELECT * FROM ##TableSizes2
ORDER BY reserved desc
drop table ##TableSizes
drop table ##TableSizes2
October 29, 2015 at 1:42 pm
Ed Wagner (10/29/2015)
Jeff Moden (10/29/2015)
Ed Wagner (10/29/2015)
That's pretty slick, Eirikur. I appreciate how you use string replacement using the rows in sys.databases to generate the final SQL to fire. I've noticed that there aren't many of us who use that approach, even though it's so very efficient. Thanks for sharing it.
I don't believe I knew you back when I did lightning rounds at SPID for this method. Joe Flemming walked up to me and gave me a bear hug because I had just made something he was having severe problems doing a whole lot easier using such token replacement methods.
It does come with a warning though. You absolute must delouse any type of user input because it's still a concatenation of sorts. For example, the use of QUOTENAME (or similar if you need more than 128 characters) is still important, in such cases as well as other delousing techniques.
For the DBA, though... I agree.... It's freakin' awesome and makes life sooooooo much easier.
I think you're right. I can just imaging Joe bear-hugging you for something cool like this. I wish I would have seen the surprised look on your face when he did it. 😛
For this case, I'm going to be the only one who has permission to run it and there won't be any input to clean, but your point is well-taken. It's absolutely string concatenation, but it'll be clean as built. Just out of habit, when I created it, the QUOTENAME function is added around the name in the final SELECT that builds the full list.
It's not only much simpler, but it's also faster to build the strings. As an added bonus, we get to avoid a loop. 😀 I honestly don't know why some people rail against it, but it is what it is.
Jeff is absolutely right, quotename must be there, consider this
😎
USE master;
GO
SET NOCOUNT ON;
CREATE DATABASE [MALICIOUSNAME];
CREATE DATABASE [MALICIOUSNAME; DROP DATABASE XXX];
and then the stored procedure's output
USE MALICIOUSNAME; DROP DATABASE XXX
INSERT INTO MONITORING_DB.dbo.TBL_TABLE_LOG
(
[SERVER_NAME]
,[DB_NAME]
,[SCHEMA_NAME]
,[TABLE_NAME]
,[INDEX_NAME]
,[ROWS]
,[TOTAL_PAGES]
,[USED_PAGES]
,[DATA_PAGES]
,[TOTAL_MB]
,[USED_MB]
,[DATA_MB]
)
SELECT
@@SERVERNAME AS [SERVER_NAME]
,DB_NAME(DB_ID()) AS [DB_NAME]
,OBJECT_SCHEMA_NAME(SPART.object_id) AS [SCHEMA_NAME]
,STAB.name AS [TABLE_NAME]
,SIDX.name AS [INDEX_NAME]
,SPART.rows AS [ROWS]
,SUM(SALU.total_pages ) AS [TOTAL_PAGES]
,SUM(SALU.used_pages ) AS [USED_PAGES]
,SUM(SALU.data_pages ) AS [DATA_PAGES]
,(8 * SUM(SALU.total_pages )) / 1024.0 AS [TOTAL_MB]
,(8 * SUM(SALU.used_pages )) / 1024.0 AS [USED_MB]
,(8 * SUM(SALU.data_pages )) / 1024.0 AS [DATA_MB]
FROM sys.tables STAB
INNER JOIN sys.indexes SIDX
ON STAB.object_id = SIDX.object_id
INNER JOIN sys.partitions SPART
ON SIDX.object_id = SPART.object_id
AND SIDX.index_id = SPART.index_id
INNER JOIN sys.allocation_units SALU
ON SPART.partition_id = SALU.container_id
WHERE SIDX.index_id IN (0,1)
AND STAB.object_id > 255
GROUP BY STAB.name
,SIDX.name
,SPART.object_id
,SPART.rows
;
Runs without an error!
October 29, 2015 at 2:00 pm
Ed Wagner (10/29/2015)
That's pretty slick, Eirikur. I appreciate how you use string replacement using the rows in sys.databases to generate the final SQL to fire. I've noticed that there aren't many of us who use that approach, even though it's so very efficient. Thanks for sharing it.
You are welcome Ed;-)
I use this method quite extensively as it lessens the maintenance, think of systems with hundreds or thousands of client specific databases, often tens of databases per client.
😎
October 29, 2015 at 2:12 pm
I put the QUOTENAME around the database name in the query that concatenates the final SQL.
SELECT @EXEC_STR =
(
SELECT REPLACE(@SQL_STR,N'{{@DBNAME}}', QUOTENAME(SDB.name))
FROM master.sys.databases SDB
WHERE SDB.name NOT IN (N'master',N'tempdb',N'model',N'msdb')
AND SDB.state_desc = N'ONLINE'
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)');
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply