Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Контроль размера базы данных сервера MS SQL 2008R2 средствами VBA/ T-SQL  [new]
РоманРоман
Member

Откуда:
Сообщений: 17
WinXP, MS SQL 2008R2 Express, имеется база данных, состоящая из одной таблицы dbo.Statistic, (модель Simple, autogrowth отключен).
Задача - контролировать размер таблицы, и удалять самые старые записи, если размер больше предельного.
Решение:

Периодически вызываю макрос на VBA, где создаю коннекцию к базе и:
1. Определяю размер таблицы
автор
SELECT
SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME LIKE 'Statistic' AND
i.index_id <= 1
GROUP BY
t.NAME


2. Удаляю записи, относящиеся самому последнему дню, если размер слишком большой.

автор
"DELETE FROM dbo.statistic WHERE dateandtime BETWEEN (SELECT MIN(dateandtime) FROM dbo.statistic) AND (SELECT MIN(dateandtime) FROM dbo.statistic) + 1"



Вроде всё должно работать. Но вопросы такие, хотелось бы упростить решение:

1. Можно ли создать процедуру/функцию в SQL Server Management Studio, чтобы она периодически (раз в час например) вызывалась, и выполняла эти действия средствами T-SQL и SQL Server, обойдясь без VBA. Или вызывать её с помощью Windows scheduler. Я никогда не создавал пользовательские процедуры или функции...(
2. SQL запрос для определения размера таблицы слишком наворочен, нет? Я просто нашел готовый универсальный запрос и адаптировал его под себя.

Спасибо.
9 окт 17, 10:30    [20853629]     Ответить | Цитировать Сообщить модератору
 Re: Контроль размера базы данных сервера MS SQL 2008R2 средствами VBA/ T-SQL  [new]
iiyama
Member

Откуда:
Сообщений: 642
РоманРоман,

sqlcmd + Windows Sheduler
9 окт 17, 10:36    [20853653]     Ответить | Цитировать Сообщить модератору
 Re: Контроль размера базы данных сервера MS SQL 2008R2 средствами VBA/ T-SQL  [new]
РоманРоман
Member

Откуда:
Сообщений: 17
iiyama,

Да, я просто не знаю как переделать мой макрос на T-SQL, он такого плана:

автор
'Option Explicit
Dim strConn As String
Dim strSQL As String
Dim objConn As Object
Dim objRecSet As Object
Dim Command As Object
Dim objRecSetValue as Double

On Error Resume Next
Err.Number = 0

strConn = "Provider=SQLOLEDB;Data Source=STUDIO5K\FTVIEWX64TAGDB;Initial Catalog=Stat;Persist Security Info=True;User ID=sa;Password=#Fireball."


Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = strConn
objConn.CursorLocation = 3
objConn.Open

Set objRecSet = CreateObject("ADODB.Recordset")
Set Command = CreateObject("ADODB.Command")
Command.CommandType = 1
Set Command.ActiveConnection = objConn

strSQL = "SELECT SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB FROM sys.tables t "
strSQL = strSQL & "INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id "
strSQL = strSQL & "INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id "
strSQL = strSQL & "INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id "
strSQL = strSQL & "WHERE t.NAME LIKE 'Statistic' AND i.index_id <= 1 GROUP BY t.NAME "

Command.CommandText = strSQL
Command.CommandTimeout = 200
Set objRecSet = Command.Execute

objRecSet.MoveFirst
objRecSetValue = objRecSet.Fields(0).Value
objRecSet.Close

If objRecSetValue> 5000 then
strSQL = "delete from dbo.statistic where dateandtime BETWEEN (SELECT MIN(dateandtime) FROM dbo.statistic) AND (SELECT MIN(dateandtime) FROM dbo.statistic) + 1"
Command.CommandText = strSQL
Set objRecSet = Command.Execute
End if


objConn.Close

ErrorHandler1:
Set Command = Nothing
Set objConn = Nothing
Set objRecSet = Nothing
9 окт 17, 10:42    [20853667]     Ответить | Цитировать Сообщить модератору
 Re: Контроль размера базы данных сервера MS SQL 2008R2 средствами VBA/ T-SQL  [new]
iiyama
Member

Откуда:
Сообщений: 642
РоманРоман,
Типа того (не проверял, накидал as is )
declare @objRecSetValue int,
	@lval datetime,
	@pval datetime

set @objRecSetValue =
(
SELECT SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB FROM sys.tables t 
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id 
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id 
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id 
WHERE t.NAME LIKE 'Statistic' AND i.index_id <= 1 GROUP BY t.NAME 
)
select  @objRecSetValue 

If @objRecSetValue> 5000 
begin
select 
	@lval = (SELECT MIN(dateandtime) FROM dbo.statistic),
	@pval = DATEADD(dd,1, @lval)	

--delete from dbo.statistic where dateandtime BETWEEN @lval AND @pval

end
9 окт 17, 13:52    [20854468]     Ответить | Цитировать Сообщить модератору
 Re: Контроль размера базы данных сервера MS SQL 2008R2 средствами VBA/ T-SQL  [new]
o-o
Guest
он же объем в Мб считает,
почему тогда сие(SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB) имеет кодовое название @objRecSetValue?
как операция "ы", чтобы никто не догадался?
9 окт 17, 14:30    [20854668]     Ответить | Цитировать Сообщить модератору
 Re: Контроль размера базы данных сервера MS SQL 2008R2 средствами VBA/ T-SQL  [new]
РоманРоман
Member

Откуда:
Сообщений: 17
Спасибо, остановился на таком варианте:

автор
CREATE PROCEDURE proPurgeTableData
AS
BEGIN TRY
--variable for storing tablesize
DECLARE @tablesizeMB INT

--setting size to variable
SELECT
@tablesizeMB = SUM(a.total_pages) * 8 / 1024
FROM
sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME LIKE 'Statistic' AND i.index_id <= 1
GROUP BY
t.NAME

--if size greater than 5000 delete from table
IF (ISNULL(@tablesizeMB,0)>5000)
BEGIN
BEGIN TRAN
DELETE FROM dbo.statistic
WHERE dateandtime BETWEEN (SELECT MIN(dateandtime) FROM dbo.statistic) AND (SELECT MIN(dateandtime) FROM dbo.statistic) + 1
COMMIT TRAN
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0 ROLLBACK TRAN
END CATCH
10 окт 17, 10:39    [20856843]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить