February 28, 2012 at 3:08 am
Expertrs,
Client DB is in full recovery.
There's procedure which takes parameters.
Once of the parameter is of type nvarchar(max)
This procedures is called many times by passing a string of 50KB.
So if it's executed 10 times this way, the log will be atleast 5 MB
And in the log there will be entry for "execute proc procname(@var1 = 50KB string)", am I right ?
Thanks in advance,
Smith.
February 28, 2012 at 3:55 am
Not exactly. You do something with that parameter, right?
You persist it to one or more tables and that's what gets logged in the transaction log.
That's an interesting question anyway.
If you did nothing with that parameter, nothing would have to be logged IMHO, but the response depends on some SQL Server internals beyond my knowledge. There's only one way to know for sure: test it! That's what I'm doing and I will get back with the results as soon as I have some findings.
-- Gianluca Sartori
February 28, 2012 at 4:18 am
Here's a quick test that confirms my initial idea.
-- YOU HAVE A TEST DATABASE, DON'T YOU?
USE TEST
GO
-- SETUP
CREATE TABLE testDestination (
longString nvarchar(max)
)
GO
-- PROCEDURE THAT DOES SOMETHING WITH THE PARAMETER
CREATE PROCEDURE logTest1 @longString nvarchar(max)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO testDestination VALUES(@longString)
END
GO
-- PROCEDURE THAT DOES NOTHING WITH THE PARAMETER
CREATE PROCEDURE logTest2 @longString nvarchar(max)
AS
BEGIN
PRINT ''
END
GO
PRINT '********************************************'
PRINT ' INITIAL LOG USAGE '
PRINT '********************************************'
-- INITIAL LOG USAGE
SET NOCOUNT ON;
SELECT cntr_value AS [log kb used]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log File(s) Used Size (KB)'
AND instance_name = 'TEST'
GO
PRINT '********************************************'
PRINT ' TEST PROCEDURE 1 '
PRINT '********************************************'
GO
-- CALL TEST PROCEDURE 1 (SAVES THE DATA TO A TABLE)
DECLARE @s-2 nvarchar(max) = REPLICATE('A', 50000)
EXEC logTest1 @s-2
GO 10
PRINT '********************************************'
PRINT ' LOG USAGE AFTER TEST 1 '
PRINT '********************************************'
-- LOG USAGE AFTER TEST 1
SET NOCOUNT ON;
SELECT cntr_value AS [log kb used]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log File(s) Used Size (KB)'
AND instance_name = 'TEST'
GO
PRINT '********************************************'
PRINT ' TEST PROCEDURE 2 '
PRINT '********************************************'
GO
-- CALL TEST PROCEDURE 1 (SAVES THE DATA TO A TABLE)
DECLARE @s-2 nvarchar(max) = REPLICATE('A', 50000)
EXEC logTest2 @s-2
GO 10
PRINT '********************************************'
PRINT ' LOG USAGE AFTER TEST 2 '
PRINT '********************************************'
-- LOG USAGE AFTER TEST 2
SET NOCOUNT ON;
SELECT cntr_value AS [log kb used]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log File(s) Used Size (KB)'
AND instance_name = 'TEST'
This is the output I get:
********************************************
INITIAL LOG USAGE
********************************************
log kb used
--------------------
8676
********************************************
TEST PROCEDURE 1
********************************************
Beginning execution loop
Batch execution completed 10 times.
********************************************
LOG USAGE AFTER TEST 1
********************************************
log kb used
--------------------
8876
********************************************
TEST PROCEDURE 2
********************************************
Beginning execution loop
Batch execution completed 10 times.
********************************************
LOG USAGE AFTER TEST 2
********************************************
log kb used
--------------------
8876
Verdict: no data modification means no log records written.
-- Gianluca Sartori
February 28, 2012 at 4:34 am
Joy Smith San (2/28/2012)
And in the log there will be entry for "execute proc procname(@var1 = 50KB string)", am I right ?
No. It's not an audit log, it's not an activity log. The transaction log contains records of changes made to the database, that's all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 28, 2012 at 5:57 am
@Gianluca Sartori
Thank you so much !. That part is clear, Scripts are awesome.
But still I dont know what exaclt is stored in Log.
Gila, I notice that my log grows too large in less than 3-4 hours. I ran profiler and found the above mentioned procedure or similar one get executed too many times. That's why I asked.
Can you please brief me what happens to the log in the case I mentioned above. Procedures either insert or updated the data which is passed as parmaeter (50 KB). Either will happen for sure.
Thanks a lot..
San.
February 28, 2012 at 6:00 am
It's got nothing to do with procedures and nothing to do with parameters. The transaction log logs changes to database objects. So if an insert inserts 50kb of data, that's at least 50kb of log space (probably more). So don't fixate on procedures, look at what those procedures are doing, what data changes they make. That's what uses the log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 28, 2012 at 7:40 am
So if an insert inserts 50kb of data, that's at least 50kb of log space (probably more).
Think that's what happening in this case.
look at what those procedures are doing, what data changes they make. That's what uses the log.
It's a simple proceure. It checks for the data that's passed as Params in the base table.
If a record allready exists for that GUID, then it updates all the fields with the passed ones.
If there's no record for that GUID, it just inserts those passed values into fields.
So either updation or insertion happens.
Thanks.
February 28, 2012 at 8:11 am
Joy Smith San (2/28/2012)
It's a simple proceure. It checks for the data that's passed as Params in the base table.If a record allready exists for that GUID, then it updates all the fields with the passed ones.
If there's no record for that GUID, it just inserts those passed values into fields.
So either updation or insertion happens.
So the update or insert is logged, that's all that will be logged, not the execute of the procedure, not the check of the data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 28, 2012 at 8:14 am
Ok.. Thanks a lot Gila.
I inserted some sample recs in my test DB and just tried to open and see. 🙂
Can't make anything out of it. :w00t:
Thanks again.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply