Team
I wrote following code
Create PROCEDURE asp_nykl_Full_Update_605ProcStat
--@.sku_barcode varchar(12)
AS
--declare @.Err1 int
--begin transaction
UPDATE pix_tran
SET proc_stat_code = 90
FROM ITEM_MASTER
WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
--AND sku_brcd=@.sku_barcode
AND TRAN_TYPE = '605'
AND proc_stat_code = 10
I have been advised that I must put
1. begin and end transaction
2. Must have SELECT ... (UPDLOCK) before update statement
3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
Is it always advisable to do so?
ThanksD Goyal (goyald@.gmail.com) writes:
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
As long as you only have a single update statement, that's a bit
of overkill - as long as you can be dead sure that the code is running
with implicit_transactions off. This setting is indeed off by default,
but if the procedure is invoked remotely, this is not so. So BEGIN/END
would make it a little safer.
> 2. Must have SELECT ... (UPDLOCK) before update statement
I don't really see the point with this here.
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is indeed recommendable, as without setting SQL Server
produces a rowcount about affected rows which clients more often does
not care about than they do. In fact, our load tool automatically inserts
a SET NOCOUNT ON in all our stored procedures.
SET LOCK_TIMEOUT I can't really comment on, as this is more tied to
business rules. It prevents the procedure from being locked forever,
but then again what should you do if you time out?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||D Goyal wrote:
> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
You can, but it's not always necessary. SQL Server will run that single
statement in a transaction for you if you leave off the begin
tran/commit. Autocommit mode is the default, but if you have standards
in place, you can start a transaction and check @.@.ERROR after each DML
statement and subsequently commit or rollback. It will save you some
headaches should you add a second DML statement to the procedure. In
autocommit mode (without a begin tran) if the first succeeds and the
second statement fails, the first statement still commits.
> 2. Must have SELECT ... (UPDLOCK) before update statement
It's not needed. But if I look at the next item for LOCK_TIMEOUT, I
think I see why it might have been proposed. If you set a lock timeout
to say 5 seconds and try and select the rows with an escalated lock
(would need to be in a transaction), and other processes have locks on
the required pages, the SELECT will abort. But you can do the same
without the SELECT and just leave it to the UPDATE to time out if there
is lock contention.
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is a highly advisable addition to every stored procedure
(first line). I don't generally use a lock timeout unless I'm running
something that I need to make sure doesn't sit there forever in the case
of someone hold extended locks on the required pages.
> Is it always advisable to do so?
> Thanks
David Gugick
Quest Software
www.imceda.com
www.quest.com|||(1) Single statment like this does not require explicit transactions.
(2) If you are planning for any updates after a read and want to insure that
the data did not change between reads, then you need to add UPDLOCK hint in
your SELECT. If not, I do not see any need.
(3) SET NOCOUNT ON does not have any performance impact. No matter how you
set this option, the @.@.ROWCOUNT value will be affected. It simply does not
send the count as part of the result to the client.
(4) Unless you know how much time you want to wait for a blocked resource, I
would not recommend to change LOCK_TIMEOUT. Remember that this setting change
is for your connection.
"D Goyal" wrote:
> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
> 2. Must have SELECT ... (UPDLOCK) before update statement
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
> Is it always advisable to do so?
> Thanks
>|||satheeshks wrote:
> (3) SET NOCOUNT ON does not have any performance impact. No matter
> how you set this option, the @.@.ROWCOUNT value will be affected. It
> simply does not send the count as part of the result to the client.
I have disagree with # 3.
Using SET NOCOUNT ON can cause a improvement in some queries (batches)
as well as prevent some ADO issues caused by the rowcount information
being returned to the client.
On a test I just performed that inserts 1000 rows into a table in a
loop, the CPU and Reads were the same, but the Duration dropped from an
average of 550ms to 450ms (a 19% improvement in speed).
This test was on local SQL Server box using Query Analyzer. Results
might vary when running on a network or when ignoring the row count
results (which are displayed on screen in QA).
But I would urge the OP to set NOCOUNT ON at the very top of every
stored procedure and also as the first command after connecting should
any embedded SQL be executed from the app.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Friday, March 30, 2012
Monday, March 19, 2012
Newbie needs help with DBCC Shrinkfile
Help SQLServer newbie..
Hello. I have a 32gb transaction log that I just backedup with truncate option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is used. When I run
use projectserverdev
go
dbcc shrinkfile (ProjectServer_log, 600)
I get the following output.
142409647263409647256
Message posted via http://www.sqlmonster.com
Can you take a look into Database properties and see what is a initial size
of Transaction Log?
If it is 4000, then this out put is normal as you cannot shrink under
initial size.
Danijel
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.c om...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com
|||Hi,
If that doesnt work, you may need to "wrap" the transaction log file. Run
this script on the database in question, then runn your DBCC command again:
(make sure to verify the database parameters on lines 6 and 8)
SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE ProjectServer -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'ProjectServer_Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@.MaxMinutes = 10, -- Limit on time allowed to wrap log.
@.NewSize = 10 -- in MB
-- Setup / initialize
DECLARE @.OriginalSize int
SELECT @.OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @.LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @.Counter INT,
@.StartTime DATETIME,
@.TruncLog VARCHAR(255)
SELECT @.StartTime = GETDATE(),
@.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
EXEC (@.TruncLog)
-- Wrap the log if necessary.
WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
not expired
AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name =
@.LogicalFileName) -- the log has not shrunk
AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @.Counter = 0
WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @.Counter = @.Counter + 1
END -- update
EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.c om...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com
Hello. I have a 32gb transaction log that I just backedup with truncate option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is used. When I run
use projectserverdev
go
dbcc shrinkfile (ProjectServer_log, 600)
I get the following output.
142409647263409647256
Message posted via http://www.sqlmonster.com
Can you take a look into Database properties and see what is a initial size
of Transaction Log?
If it is 4000, then this out put is normal as you cannot shrink under
initial size.
Danijel
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.c om...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com
|||Hi,
If that doesnt work, you may need to "wrap" the transaction log file. Run
this script on the database in question, then runn your DBCC command again:
(make sure to verify the database parameters on lines 6 and 8)
SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE ProjectServer -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'ProjectServer_Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@.MaxMinutes = 10, -- Limit on time allowed to wrap log.
@.NewSize = 10 -- in MB
-- Setup / initialize
DECLARE @.OriginalSize int
SELECT @.OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @.LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @.Counter INT,
@.StartTime DATETIME,
@.TruncLog VARCHAR(255)
SELECT @.StartTime = GETDATE(),
@.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
EXEC (@.TruncLog)
-- Wrap the log if necessary.
WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
not expired
AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name =
@.LogicalFileName) -- the log has not shrunk
AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @.Counter = 0
WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @.Counter = @.Counter + 1
END -- update
EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.c om...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com
Newbie needs help with DBCC Shrinkfile
Help SQLServer newbie..
Hello. I have a 32gb transaction log that I just backedup with truncate option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is used. When I run
use projectserverdev
go
dbcc shrinkfile (ProjectServer_log, 600)
I get the following output.
14 2 4096472 63 4096472 56
--
Message posted via http://www.sqlmonster.comCan you take a look into Database properties and see what is a initial size
of Transaction Log?
If it is 4000, then this out put is normal as you cannot shrink under
initial size.
Danijel
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.com...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com|||Hi,
If that doesnt work, you may need to "wrap" the transaction log file. Run
this script on the database in question, then runn your DBCC command again:
(make sure to verify the database parameters on lines 6 and 8)
SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE ProjectServer -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'ProjectServer_Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@.MaxMinutes = 10, -- Limit on time allowed to wrap log.
@.NewSize = 10 -- in MB
-- Setup / initialize
DECLARE @.OriginalSize int
SELECT @.OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @.LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @.Counter INT,
@.StartTime DATETIME,
@.TruncLog VARCHAR(255)
SELECT @.StartTime = GETDATE(),
@.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
EXEC (@.TruncLog)
-- Wrap the log if necessary.
WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
not expired
AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name =@.LogicalFileName) -- the log has not shrunk
AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @.Counter = 0
WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @.Counter = @.Counter + 1
END -- update
EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.com...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com
Hello. I have a 32gb transaction log that I just backedup with truncate option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is used. When I run
use projectserverdev
go
dbcc shrinkfile (ProjectServer_log, 600)
I get the following output.
14 2 4096472 63 4096472 56
--
Message posted via http://www.sqlmonster.comCan you take a look into Database properties and see what is a initial size
of Transaction Log?
If it is 4000, then this out put is normal as you cannot shrink under
initial size.
Danijel
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.com...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com|||Hi,
If that doesnt work, you may need to "wrap" the transaction log file. Run
this script on the database in question, then runn your DBCC command again:
(make sure to verify the database parameters on lines 6 and 8)
SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE ProjectServer -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'ProjectServer_Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@.MaxMinutes = 10, -- Limit on time allowed to wrap log.
@.NewSize = 10 -- in MB
-- Setup / initialize
DECLARE @.OriginalSize int
SELECT @.OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @.LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @.Counter INT,
@.StartTime DATETIME,
@.TruncLog VARCHAR(255)
SELECT @.StartTime = GETDATE(),
@.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
EXEC (@.TruncLog)
-- Wrap the log if necessary.
WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
not expired
AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name =@.LogicalFileName) -- the log has not shrunk
AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @.Counter = 0
WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @.Counter = @.Counter + 1
END -- update
EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.com...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com
Newbie needs help with DBCC Shrinkfile
Help SQLServer newbie..
Hello. I have a 32gb transaction log that I just backedup with truncate opti
on. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is used. Wh
en I run
use projectserverdev
go
dbcc shrinkfile (ProjectServer_log, 600)
I get the following output.
14 2 4096472 63 4096472 56
Message posted via http://www.droptable.comCan you take a look into Database properties and see what is a initial size
of Transaction Log?
If it is 4000, then this out put is normal as you cannot shrink under
initial size.
Danijel
"Jigar Lakhani via droptable.com" <forum@.droptable.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQ
droptable.com...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.droptable.com|||Hi,
If that doesnt work, you may need to "wrap" the transaction log file. Run
this script on the database in question, then runn your DBCC command again:
(make sure to verify the database parameters on lines 6 and 8)
SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE ProjectServer -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'ProjectServer_Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@.MaxMinutes = 10, -- Limit on time allowed to wrap log.
@.NewSize = 10 -- in MB
-- Setup / initialize
DECLARE @.OriginalSize int
SELECT @.OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @.LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @.Counter INT,
@.StartTime DATETIME,
@.TruncLog VARCHAR(255)
SELECT @.StartTime = GETDATE(),
@.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
EXEC (@.TruncLog)
-- Wrap the log if necessary.
WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
not expired
AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name =
@.LogicalFileName) -- the log has not shrunk
AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @.Counter = 0
WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @.Counter = @.Counter + 1
END -- update
EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
"Jigar Lakhani via droptable.com" <forum@.droptable.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQ
droptable.com...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.droptable.com
Hello. I have a 32gb transaction log that I just backedup with truncate opti
on. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is used. Wh
en I run
use projectserverdev
go
dbcc shrinkfile (ProjectServer_log, 600)
I get the following output.
14 2 4096472 63 4096472 56
Message posted via http://www.droptable.comCan you take a look into Database properties and see what is a initial size
of Transaction Log?
If it is 4000, then this out put is normal as you cannot shrink under
initial size.
Danijel
"Jigar Lakhani via droptable.com" <forum@.droptable.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQ
droptable.com...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.droptable.com|||Hi,
If that doesnt work, you may need to "wrap" the transaction log file. Run
this script on the database in question, then runn your DBCC command again:
(make sure to verify the database parameters on lines 6 and 8)
SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE ProjectServer -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'ProjectServer_Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@.MaxMinutes = 10, -- Limit on time allowed to wrap log.
@.NewSize = 10 -- in MB
-- Setup / initialize
DECLARE @.OriginalSize int
SELECT @.OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @.LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @.Counter INT,
@.StartTime DATETIME,
@.TruncLog VARCHAR(255)
SELECT @.StartTime = GETDATE(),
@.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
EXEC (@.TruncLog)
-- Wrap the log if necessary.
WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
not expired
AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name =
@.LogicalFileName) -- the log has not shrunk
AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @.Counter = 0
WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @.Counter = @.Counter + 1
END -- update
EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
"Jigar Lakhani via droptable.com" <forum@.droptable.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQ
droptable.com...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.droptable.com
Friday, March 9, 2012
Newbie backup question
I just start learning how to use SQL Server 2000.
A question about transaction log backup:
There is a full backup on Sunday night and differential backup from Monday
to Saturday night. Transaction log backup on 9am, 12pm, 3pm, 6pm on
weekdays.
Just wonder is the transaction log at 6pm on Saturday night containing all
transactions from Monday to Friday of the same week ?Nope, the log is emptied each time you do a backup. So the 6PM log backup contains the transactions
produced on sat from 3pm to 6pm (roughly).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alan" <alanpltse@.yahoo.com.au> wrote in message news:e7T46DlhDHA.1864@.TK2MSFTNGP10.phx.gbl...
> I just start learning how to use SQL Server 2000.
> A question about transaction log backup:
> There is a full backup on Sunday night and differential backup from Monday
> to Saturday night. Transaction log backup on 9am, 12pm, 3pm, 6pm on
> weekdays.
> Just wonder is the transaction log at 6pm on Saturday night containing all
> transactions from Monday to Friday of the same week ?
>|||To restore your DB to Saturday post log backup you will need
Sunday Full
Friday Differential (Differences between last Full backup and now)
Saturday Logs(9,12,3,6)
BOL has a good example
Differential Backup and Restore
A differential backup creates a copy of all the pages in a database modified
after the last database backup. Differential logs are used primarily in
heavily used systems where a failed database must be brought back online
quickly. Differential backups are smaller than full database backups;
therefore, they have less of an effect on the system while they run.
For example, a site executes a full database backup on Sunday night. A set
of transaction log backups is made every four hours during the day, with the
backups from one day overwriting the backups from the day before. Each night
the site makes a differential backup. If one of the data disks for the
database fails at 9:12 A.M. on Thursday, the site can:
1.. Back up the current transaction log.
2.. Restore the database backup from Sunday night.
3.. Restore the differential backup from Wednesday night to roll the
database forward to that point.
4.. Restore the transaction log backups from 4:00 A.M. and 8:00 A.M. to
roll the database forward to 8:00 A.M.
5.. Restore the log backup taken after the failure. This will roll the
database forward to the time of the failure
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Alan" <alanpltse@.yahoo.com.au> wrote in message
news:e7T46DlhDHA.1864@.TK2MSFTNGP10.phx.gbl...
> I just start learning how to use SQL Server 2000.
> A question about transaction log backup:
> There is a full backup on Sunday night and differential backup from Monday
> to Saturday night. Transaction log backup on 9am, 12pm, 3pm, 6pm on
> weekdays.
> Just wonder is the transaction log at 6pm on Saturday night containing all
> transactions from Monday to Friday of the same week ?
>
A question about transaction log backup:
There is a full backup on Sunday night and differential backup from Monday
to Saturday night. Transaction log backup on 9am, 12pm, 3pm, 6pm on
weekdays.
Just wonder is the transaction log at 6pm on Saturday night containing all
transactions from Monday to Friday of the same week ?Nope, the log is emptied each time you do a backup. So the 6PM log backup contains the transactions
produced on sat from 3pm to 6pm (roughly).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alan" <alanpltse@.yahoo.com.au> wrote in message news:e7T46DlhDHA.1864@.TK2MSFTNGP10.phx.gbl...
> I just start learning how to use SQL Server 2000.
> A question about transaction log backup:
> There is a full backup on Sunday night and differential backup from Monday
> to Saturday night. Transaction log backup on 9am, 12pm, 3pm, 6pm on
> weekdays.
> Just wonder is the transaction log at 6pm on Saturday night containing all
> transactions from Monday to Friday of the same week ?
>|||To restore your DB to Saturday post log backup you will need
Sunday Full
Friday Differential (Differences between last Full backup and now)
Saturday Logs(9,12,3,6)
BOL has a good example
Differential Backup and Restore
A differential backup creates a copy of all the pages in a database modified
after the last database backup. Differential logs are used primarily in
heavily used systems where a failed database must be brought back online
quickly. Differential backups are smaller than full database backups;
therefore, they have less of an effect on the system while they run.
For example, a site executes a full database backup on Sunday night. A set
of transaction log backups is made every four hours during the day, with the
backups from one day overwriting the backups from the day before. Each night
the site makes a differential backup. If one of the data disks for the
database fails at 9:12 A.M. on Thursday, the site can:
1.. Back up the current transaction log.
2.. Restore the database backup from Sunday night.
3.. Restore the differential backup from Wednesday night to roll the
database forward to that point.
4.. Restore the transaction log backups from 4:00 A.M. and 8:00 A.M. to
roll the database forward to 8:00 A.M.
5.. Restore the log backup taken after the failure. This will roll the
database forward to the time of the failure
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Alan" <alanpltse@.yahoo.com.au> wrote in message
news:e7T46DlhDHA.1864@.TK2MSFTNGP10.phx.gbl...
> I just start learning how to use SQL Server 2000.
> A question about transaction log backup:
> There is a full backup on Sunday night and differential backup from Monday
> to Saturday night. Transaction log backup on 9am, 12pm, 3pm, 6pm on
> weekdays.
> Just wonder is the transaction log at 6pm on Saturday night containing all
> transactions from Monday to Friday of the same week ?
>
Wednesday, March 7, 2012
Newbie ? -- Does 'master' transaction log need to be backed up?
Hope someone can help answer this question for this newbie.
We are running SQL Server 2000 SP3a on Windows Server 2003, all updates current.
We have been running smoothly for some time now with two user databases. The maintenance plans work well for them. The maintenance plans for the system databases, however, have been giving me fits. Occasionally, one or more steps will fail and there is
no information in any log with which to begin an investigation. The Books Online offer no clue, either.
Here's what I need to know:
Most maintenance plans consist of 1 or more options: Optimizations, Integrity Checks, Database Backup, Transaction Log Backup. I have all 4 options set up for my user databases.
For the system databases (master, model, and msdb), the Transaction Log Backup continues to fail for the 'master' database with this error:
Backup can not be performed on this database. This sub task is ignored
The Properties Tab of the 'master' database shows that the Transaction Log file is:
C:\Program Files\Microsoft SQL Server\MSSQL\data\mastlog.ldf
Is there a problem with the database?
Any assistance is greatly appreciated.
Robert
By the way, 'master' is set to FULL recovery
Robert
|||Hi,
You canot perform a Trasaction log backup for MASTER database even though
recovery model is "FULL". Only database backup is possible. Why do you need
to backup the trasnaction log of MASTER database, because it is not advised
to have user tables / transaction tables in system databases.
Thanks
Hari
MCDBA
"Robert" <rclay@.kerrdrug.com> wrote in message
news:uEutur8TEHA.1356@.TK2MSFTNGP09.phx.gbl...
> By the way, 'master' is set to FULL recovery
> Robert
>
|||Hari,
Thanks for the information.
We don't use the system databases for user data at all! I merely need to
understand what is involved in a standard daily system maintenance process.
That being said, is it okay to just have the Optimizations, Integrity
Checks, and Database Backup options for those three databases (master, model
and msdb) and forget about the Transaction Log backup for each?
Thanks!
Robert
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ObCeux8TEHA.4048@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You canot perform a Trasaction log backup for MASTER database even though
> recovery model is "FULL". Only database backup is possible. Why do you
need
> to backup the trasnaction log of MASTER database, because it is not
advised
> to have user tables / transaction tables in system databases.
> Thanks
> Hari
> MCDBA
> "Robert" <rclay@.kerrdrug.com> wrote in message
> news:uEutur8TEHA.1356@.TK2MSFTNGP09.phx.gbl...
>
|||Hi,
Since you are not adding any trasnactions to System database, you can ignore
the transaction log backup
for system databases.
Thanks
Hari
MCDBA
"Robert" <rclay@.kerrdrug.com> wrote in message
news:#S$Kw28TEHA.3988@.TK2MSFTNGP10.phx.gbl...
> Hari,
> Thanks for the information.
> We don't use the system databases for user data at all! I merely need to
> understand what is involved in a standard daily system maintenance
process.
> That being said, is it okay to just have the Optimizations, Integrity
> Checks, and Database Backup options for those three databases (master,
model[vbcol=seagreen]
> and msdb) and forget about the Transaction Log backup for each?
> Thanks!
> Robert
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ObCeux8TEHA.4048@.TK2MSFTNGP12.phx.gbl...
though
> need
> advised
>
|||Thanks, Hari.
I will only backup the Transaction Logs for my user databases, then.
Robert
|||Be aware that the backup history is in the msdb database. Because of this, I also do log backup for msdb. For
some strange reason, MS decided to set msdb to simple recovery mode when agent starts, but I just fix that by
setting msdb to full in an auto-start agent job.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <rclay@.kerrdrug.com> wrote in message news:%23sbmuX9TEHA.3140@.tk2msftngp13.phx.gbl...
> Thanks, Hari.
> I will only backup the Transaction Logs for my user databases, then.
> Robert
>
We are running SQL Server 2000 SP3a on Windows Server 2003, all updates current.
We have been running smoothly for some time now with two user databases. The maintenance plans work well for them. The maintenance plans for the system databases, however, have been giving me fits. Occasionally, one or more steps will fail and there is
no information in any log with which to begin an investigation. The Books Online offer no clue, either.
Here's what I need to know:
Most maintenance plans consist of 1 or more options: Optimizations, Integrity Checks, Database Backup, Transaction Log Backup. I have all 4 options set up for my user databases.
For the system databases (master, model, and msdb), the Transaction Log Backup continues to fail for the 'master' database with this error:
Backup can not be performed on this database. This sub task is ignored
The Properties Tab of the 'master' database shows that the Transaction Log file is:
C:\Program Files\Microsoft SQL Server\MSSQL\data\mastlog.ldf
Is there a problem with the database?
Any assistance is greatly appreciated.
Robert
By the way, 'master' is set to FULL recovery
Robert
|||Hi,
You canot perform a Trasaction log backup for MASTER database even though
recovery model is "FULL". Only database backup is possible. Why do you need
to backup the trasnaction log of MASTER database, because it is not advised
to have user tables / transaction tables in system databases.
Thanks
Hari
MCDBA
"Robert" <rclay@.kerrdrug.com> wrote in message
news:uEutur8TEHA.1356@.TK2MSFTNGP09.phx.gbl...
> By the way, 'master' is set to FULL recovery
> Robert
>
|||Hari,
Thanks for the information.
We don't use the system databases for user data at all! I merely need to
understand what is involved in a standard daily system maintenance process.
That being said, is it okay to just have the Optimizations, Integrity
Checks, and Database Backup options for those three databases (master, model
and msdb) and forget about the Transaction Log backup for each?
Thanks!
Robert
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ObCeux8TEHA.4048@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You canot perform a Trasaction log backup for MASTER database even though
> recovery model is "FULL". Only database backup is possible. Why do you
need
> to backup the trasnaction log of MASTER database, because it is not
advised
> to have user tables / transaction tables in system databases.
> Thanks
> Hari
> MCDBA
> "Robert" <rclay@.kerrdrug.com> wrote in message
> news:uEutur8TEHA.1356@.TK2MSFTNGP09.phx.gbl...
>
|||Hi,
Since you are not adding any trasnactions to System database, you can ignore
the transaction log backup
for system databases.
Thanks
Hari
MCDBA
"Robert" <rclay@.kerrdrug.com> wrote in message
news:#S$Kw28TEHA.3988@.TK2MSFTNGP10.phx.gbl...
> Hari,
> Thanks for the information.
> We don't use the system databases for user data at all! I merely need to
> understand what is involved in a standard daily system maintenance
process.
> That being said, is it okay to just have the Optimizations, Integrity
> Checks, and Database Backup options for those three databases (master,
model[vbcol=seagreen]
> and msdb) and forget about the Transaction Log backup for each?
> Thanks!
> Robert
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ObCeux8TEHA.4048@.TK2MSFTNGP12.phx.gbl...
though
> need
> advised
>
|||Thanks, Hari.
I will only backup the Transaction Logs for my user databases, then.
Robert
|||Be aware that the backup history is in the msdb database. Because of this, I also do log backup for msdb. For
some strange reason, MS decided to set msdb to simple recovery mode when agent starts, but I just fix that by
setting msdb to full in an auto-start agent job.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <rclay@.kerrdrug.com> wrote in message news:%23sbmuX9TEHA.3140@.tk2msftngp13.phx.gbl...
> Thanks, Hari.
> I will only backup the Transaction Logs for my user databases, then.
> Robert
>
Newbie ? -- Does 'master' transaction log need to be backed up?
Hope someone can help answer this question for this newbie
We are running SQL Server 2000 SP3a on Windows Server 2003, all updates current
We have been running smoothly for some time now with two user databases. The maintenance plans work well for them. The maintenance plans for the system databases, however, have been giving me fits. Occasionally, one or more steps will fail and there is no information in any log with which to begin an investigation. The Books Online offer no clue, either
Here's what I need to know
Most maintenance plans consist of 1 or more options: Optimizations, Integrity Checks, Database Backup, Transaction Log Backup. I have all 4 options set up for my user databases
For the system databases (master, model, and msdb), the Transaction Log Backup continues to fail for the 'master' database with this error:
Backup can not be performed on this database. This sub task is ignore
The Properties Tab of the 'master' database shows that the Transaction Log file is
C:\Program Files\Microsoft SQL Server\MSSQL\data\mastlog.ld
Is there a problem with the database
Any assistance is greatly appreciated
RobertBy the way, 'master' is set to FULL recovery
Robert|||Hi,
You canot perform a Trasaction log backup for MASTER database even though
recovery model is "FULL". Only database backup is possible. Why do you need
to backup the trasnaction log of MASTER database, because it is not advised
to have user tables / transaction tables in system databases.
Thanks
Hari
MCDBA
"Robert" <rclay@.kerrdrug.com> wrote in message
news:uEutur8TEHA.1356@.TK2MSFTNGP09.phx.gbl...
> By the way, 'master' is set to FULL recovery
> Robert
>|||Hari,
Thanks for the information.
We don't use the system databases for user data at all! I merely need to
understand what is involved in a standard daily system maintenance process.
That being said, is it okay to just have the Optimizations, Integrity
Checks, and Database Backup options for those three databases (master, model
and msdb) and forget about the Transaction Log backup for each?
Thanks!
Robert
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ObCeux8TEHA.4048@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You canot perform a Trasaction log backup for MASTER database even though
> recovery model is "FULL". Only database backup is possible. Why do you
need
> to backup the trasnaction log of MASTER database, because it is not
advised
> to have user tables / transaction tables in system databases.
> Thanks
> Hari
> MCDBA
> "Robert" <rclay@.kerrdrug.com> wrote in message
> news:uEutur8TEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > By the way, 'master' is set to FULL recovery
> >
> > Robert
> >
> >
>|||Hi,
Since you are not adding any trasnactions to System database, you can ignore
the transaction log backup
for system databases.
Thanks
Hari
MCDBA
"Robert" <rclay@.kerrdrug.com> wrote in message
news:#S$Kw28TEHA.3988@.TK2MSFTNGP10.phx.gbl...
> Hari,
> Thanks for the information.
> We don't use the system databases for user data at all! I merely need to
> understand what is involved in a standard daily system maintenance
process.
> That being said, is it okay to just have the Optimizations, Integrity
> Checks, and Database Backup options for those three databases (master,
model
> and msdb) and forget about the Transaction Log backup for each?
> Thanks!
> Robert
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ObCeux8TEHA.4048@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > You canot perform a Trasaction log backup for MASTER database even
though
> > recovery model is "FULL". Only database backup is possible. Why do you
> need
> > to backup the trasnaction log of MASTER database, because it is not
> advised
> > to have user tables / transaction tables in system databases.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Robert" <rclay@.kerrdrug.com> wrote in message
> > news:uEutur8TEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > > By the way, 'master' is set to FULL recovery
> > >
> > > Robert
> > >
> > >
> >
> >
>|||Thanks, Hari.
I will only backup the Transaction Logs for my user databases, then.
Robert|||Be aware that the backup history is in the msdb database. Because of this, I also do log backup for msdb. For
some strange reason, MS decided to set msdb to simple recovery mode when agent starts, but I just fix that by
setting msdb to full in an auto-start agent job.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <rclay@.kerrdrug.com> wrote in message news:%23sbmuX9TEHA.3140@.tk2msftngp13.phx.gbl...
> Thanks, Hari.
> I will only backup the Transaction Logs for my user databases, then.
> Robert
>
We are running SQL Server 2000 SP3a on Windows Server 2003, all updates current
We have been running smoothly for some time now with two user databases. The maintenance plans work well for them. The maintenance plans for the system databases, however, have been giving me fits. Occasionally, one or more steps will fail and there is no information in any log with which to begin an investigation. The Books Online offer no clue, either
Here's what I need to know
Most maintenance plans consist of 1 or more options: Optimizations, Integrity Checks, Database Backup, Transaction Log Backup. I have all 4 options set up for my user databases
For the system databases (master, model, and msdb), the Transaction Log Backup continues to fail for the 'master' database with this error:
Backup can not be performed on this database. This sub task is ignore
The Properties Tab of the 'master' database shows that the Transaction Log file is
C:\Program Files\Microsoft SQL Server\MSSQL\data\mastlog.ld
Is there a problem with the database
Any assistance is greatly appreciated
RobertBy the way, 'master' is set to FULL recovery
Robert|||Hi,
You canot perform a Trasaction log backup for MASTER database even though
recovery model is "FULL". Only database backup is possible. Why do you need
to backup the trasnaction log of MASTER database, because it is not advised
to have user tables / transaction tables in system databases.
Thanks
Hari
MCDBA
"Robert" <rclay@.kerrdrug.com> wrote in message
news:uEutur8TEHA.1356@.TK2MSFTNGP09.phx.gbl...
> By the way, 'master' is set to FULL recovery
> Robert
>|||Hari,
Thanks for the information.
We don't use the system databases for user data at all! I merely need to
understand what is involved in a standard daily system maintenance process.
That being said, is it okay to just have the Optimizations, Integrity
Checks, and Database Backup options for those three databases (master, model
and msdb) and forget about the Transaction Log backup for each?
Thanks!
Robert
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ObCeux8TEHA.4048@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You canot perform a Trasaction log backup for MASTER database even though
> recovery model is "FULL". Only database backup is possible. Why do you
need
> to backup the trasnaction log of MASTER database, because it is not
advised
> to have user tables / transaction tables in system databases.
> Thanks
> Hari
> MCDBA
> "Robert" <rclay@.kerrdrug.com> wrote in message
> news:uEutur8TEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > By the way, 'master' is set to FULL recovery
> >
> > Robert
> >
> >
>|||Hi,
Since you are not adding any trasnactions to System database, you can ignore
the transaction log backup
for system databases.
Thanks
Hari
MCDBA
"Robert" <rclay@.kerrdrug.com> wrote in message
news:#S$Kw28TEHA.3988@.TK2MSFTNGP10.phx.gbl...
> Hari,
> Thanks for the information.
> We don't use the system databases for user data at all! I merely need to
> understand what is involved in a standard daily system maintenance
process.
> That being said, is it okay to just have the Optimizations, Integrity
> Checks, and Database Backup options for those three databases (master,
model
> and msdb) and forget about the Transaction Log backup for each?
> Thanks!
> Robert
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ObCeux8TEHA.4048@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > You canot perform a Trasaction log backup for MASTER database even
though
> > recovery model is "FULL". Only database backup is possible. Why do you
> need
> > to backup the trasnaction log of MASTER database, because it is not
> advised
> > to have user tables / transaction tables in system databases.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Robert" <rclay@.kerrdrug.com> wrote in message
> > news:uEutur8TEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > > By the way, 'master' is set to FULL recovery
> > >
> > > Robert
> > >
> > >
> >
> >
>|||Thanks, Hari.
I will only backup the Transaction Logs for my user databases, then.
Robert|||Be aware that the backup history is in the msdb database. Because of this, I also do log backup for msdb. For
some strange reason, MS decided to set msdb to simple recovery mode when agent starts, but I just fix that by
setting msdb to full in an auto-start agent job.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <rclay@.kerrdrug.com> wrote in message news:%23sbmuX9TEHA.3140@.tk2msftngp13.phx.gbl...
> Thanks, Hari.
> I will only backup the Transaction Logs for my user databases, then.
> Robert
>
Newbie ? -- Does 'master' transaction log need to be backed up?
Hope someone can help answer this question for this newbie.
We are running SQL Server 2000 SP3a on Windows Server 2003, all updates curr
ent.
We have been running smoothly for some time now with two user databases. Th
e maintenance plans work well for them. The maintenance plans for the syste
m databases, however, have been giving me fits. Occasionally, one or more s
teps will fail and there is
no information in any log with which to begin an investigation. The Books O
nline offer no clue, either.
Here's what I need to know:
Most maintenance plans consist of 1 or more options: Optimizations, Integri
ty Checks, Database Backup, Transaction Log Backup. I have all 4 options se
t up for my user databases.
For the system databases (master, model, and msdb), the Transaction Log Back
up continues to fail for the 'master' database with this error:
Backup can not be performed on this database. This sub task is ignored
The Properties Tab of the 'master' database shows that the Transaction Log f
ile is:
C:\Program Files\Microsoft SQL Server\MSSQL\data\mastlog.ldf
Is there a problem with the database?
Any assistance is greatly appreciated.
RobertBy the way, 'master' is set to FULL recovery
Robert|||Hi,
You canot perform a Trasaction log backup for MASTER database even though
recovery model is "FULL". Only database backup is possible. Why do you need
to backup the trasnaction log of MASTER database, because it is not advised
to have user tables / transaction tables in system databases.
Thanks
Hari
MCDBA
"Robert" <rclay@.kerrdrug.com> wrote in message
news:uEutur8TEHA.1356@.TK2MSFTNGP09.phx.gbl...
> By the way, 'master' is set to FULL recovery
> Robert
>|||Hari,
Thanks for the information.
We don't use the system databases for user data at all! I merely need to
understand what is involved in a standard daily system maintenance process.
That being said, is it okay to just have the Optimizations, Integrity
Checks, and Database Backup options for those three databases (master, model
and msdb) and forget about the Transaction Log backup for each?
Thanks!
Robert
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ObCeux8TEHA.4048@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You canot perform a Trasaction log backup for MASTER database even though
> recovery model is "FULL". Only database backup is possible. Why do you
need
> to backup the trasnaction log of MASTER database, because it is not
advised
> to have user tables / transaction tables in system databases.
> Thanks
> Hari
> MCDBA
> "Robert" <rclay@.kerrdrug.com> wrote in message
> news:uEutur8TEHA.1356@.TK2MSFTNGP09.phx.gbl...
>|||Hi,
Since you are not adding any trasnactions to System database, you can ignore
the transaction log backup
for system databases.
Thanks
Hari
MCDBA
"Robert" <rclay@.kerrdrug.com> wrote in message
news:#S$Kw28TEHA.3988@.TK2MSFTNGP10.phx.gbl...
> Hari,
> Thanks for the information.
> We don't use the system databases for user data at all! I merely need to
> understand what is involved in a standard daily system maintenance
process.
> That being said, is it okay to just have the Optimizations, Integrity
> Checks, and Database Backup options for those three databases (master,
model
> and msdb) and forget about the Transaction Log backup for each?
> Thanks!
> Robert
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ObCeux8TEHA.4048@.TK2MSFTNGP12.phx.gbl...
though[vbcol=seagreen]
> need
> advised
>|||Thanks, Hari.
I will only backup the Transaction Logs for my user databases, then.
Robert|||Be aware that the backup history is in the msdb database. Because of this, I
also do log backup for msdb. For
some strange reason, MS decided to set msdb to simple recovery mode when age
nt starts, but I just fix that by
setting msdb to full in an auto-start agent job.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <rclay@.kerrdrug.com> wrote in message news:%23sbmuX9TEHA.3140@.tk2msftngp13.phx.gbl.
.
> Thanks, Hari.
> I will only backup the Transaction Logs for my user databases, then.
> Robert
>
We are running SQL Server 2000 SP3a on Windows Server 2003, all updates curr
ent.
We have been running smoothly for some time now with two user databases. Th
e maintenance plans work well for them. The maintenance plans for the syste
m databases, however, have been giving me fits. Occasionally, one or more s
teps will fail and there is
no information in any log with which to begin an investigation. The Books O
nline offer no clue, either.
Here's what I need to know:
Most maintenance plans consist of 1 or more options: Optimizations, Integri
ty Checks, Database Backup, Transaction Log Backup. I have all 4 options se
t up for my user databases.
For the system databases (master, model, and msdb), the Transaction Log Back
up continues to fail for the 'master' database with this error:
Backup can not be performed on this database. This sub task is ignored
The Properties Tab of the 'master' database shows that the Transaction Log f
ile is:
C:\Program Files\Microsoft SQL Server\MSSQL\data\mastlog.ldf
Is there a problem with the database?
Any assistance is greatly appreciated.
RobertBy the way, 'master' is set to FULL recovery
Robert|||Hi,
You canot perform a Trasaction log backup for MASTER database even though
recovery model is "FULL". Only database backup is possible. Why do you need
to backup the trasnaction log of MASTER database, because it is not advised
to have user tables / transaction tables in system databases.
Thanks
Hari
MCDBA
"Robert" <rclay@.kerrdrug.com> wrote in message
news:uEutur8TEHA.1356@.TK2MSFTNGP09.phx.gbl...
> By the way, 'master' is set to FULL recovery
> Robert
>|||Hari,
Thanks for the information.
We don't use the system databases for user data at all! I merely need to
understand what is involved in a standard daily system maintenance process.
That being said, is it okay to just have the Optimizations, Integrity
Checks, and Database Backup options for those three databases (master, model
and msdb) and forget about the Transaction Log backup for each?
Thanks!
Robert
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ObCeux8TEHA.4048@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You canot perform a Trasaction log backup for MASTER database even though
> recovery model is "FULL". Only database backup is possible. Why do you
need
> to backup the trasnaction log of MASTER database, because it is not
advised
> to have user tables / transaction tables in system databases.
> Thanks
> Hari
> MCDBA
> "Robert" <rclay@.kerrdrug.com> wrote in message
> news:uEutur8TEHA.1356@.TK2MSFTNGP09.phx.gbl...
>|||Hi,
Since you are not adding any trasnactions to System database, you can ignore
the transaction log backup
for system databases.
Thanks
Hari
MCDBA
"Robert" <rclay@.kerrdrug.com> wrote in message
news:#S$Kw28TEHA.3988@.TK2MSFTNGP10.phx.gbl...
> Hari,
> Thanks for the information.
> We don't use the system databases for user data at all! I merely need to
> understand what is involved in a standard daily system maintenance
process.
> That being said, is it okay to just have the Optimizations, Integrity
> Checks, and Database Backup options for those three databases (master,
model
> and msdb) and forget about the Transaction Log backup for each?
> Thanks!
> Robert
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ObCeux8TEHA.4048@.TK2MSFTNGP12.phx.gbl...
though[vbcol=seagreen]
> need
> advised
>|||Thanks, Hari.
I will only backup the Transaction Logs for my user databases, then.
Robert|||Be aware that the backup history is in the msdb database. Because of this, I
also do log backup for msdb. For
some strange reason, MS decided to set msdb to simple recovery mode when age
nt starts, but I just fix that by
setting msdb to full in an auto-start agent job.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <rclay@.kerrdrug.com> wrote in message news:%23sbmuX9TEHA.3140@.tk2msftngp13.phx.gbl.
.
> Thanks, Hari.
> I will only backup the Transaction Logs for my user databases, then.
> Robert
>
Newbie : Hard sql query?
Hey,
I have a table that records all my bank transactions. So in it there
is one entry per transaction. This entry has the date the transaction
occured.
All I want to do is perform a query that returns how many transactions
I had for each month.
Ie.
jan : 24 transactions
feb : 19 transactions
etc
is this possible? I know I can do "counts" ... but it seems hard
because I need to split the results return by the month it occurs in.
Any help you can give would be greatly appreciated!!
Thanx
Ryan RittenNot the most efficient way, but...
SELECT YEAR(TransactionDate), MONTH(TransactionDate), COUNT(*)
FROM TransactionTable
-- if you want to limit to specific year:
-- WHERE TransactionDate >= '20050101' AND TransactionDate < '20060101'
GROUP BY YEAR(TransactionDate), MONTH(TransactionDate)
ORDER BY 1,2
"Sparticus" <sparticusREMOVE@.thesparticusarena.com> wrote in message
news:1129914248.191858.104870@.f14g2000cwb.googlegroups.com...
> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>|||> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
>
select Year(Date) As Year ,month(Date) Month, count(*) as TransCount from
YourTable
group by Year(Date),month(Date)
Francesco Anti|||Sparticus,
This should give you what you want. You'll need to WHERE for a specify year
or year range. If you want month names and/or you want the months with 0 or
as columns instead of rows...let me know.
SELECT DATEPART(MM,TDATE)AS 'MONTH', COUNT(*) 'TRANSACTION COUNT'
FROM #TRANSACTIONS
GROUP BY DATEPART(MM,TDATE)
HTH
Jerry
"Sparticus" <sparticusREMOVE@.thesparticusarena.com> wrote in message
news:1129914248.191858.104870@.f14g2000cwb.googlegroups.com...
> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>|||wow...thanx everyone. Learn somethign new everyday :)|||group your data by datepart(month,datefield),datepart(year,
datefield)
http://sqlservercode.blogspot.com/
"Sparticus" wrote:
> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>|||CREATE TABLE #MyTransaction
(
id int NOT NULL IDENTITY (1, 1),
[date] datetime NULL,
amount money NULL
) ON [PRIMARY]
insert into #MyTransaction
([date], amount)
values
('1-1-05', 100)
insert into #MyTransaction
([date], amount)
values
('1-10-05', 100)
insert into #MyTransaction
([date], amount)
values
('1-20-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-1-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-5-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-7-05', 100)
insert into #MyTransaction
([date], amount)
values
('3-8-05', 10)
select datepart(yy,[date]) as Year, datename(mm,[date]) as month, count(id)
as Count
from #MyTransaction
group by datepart(yy,[date]), datename(mm,[date])
drop table #MyTransaction
"Sparticus" <sparticusREMOVE@.thesparticusarena.com> wrote in message
news:1129914248.191858.104870@.f14g2000cwb.googlegroups.com...
> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>
I have a table that records all my bank transactions. So in it there
is one entry per transaction. This entry has the date the transaction
occured.
All I want to do is perform a query that returns how many transactions
I had for each month.
Ie.
jan : 24 transactions
feb : 19 transactions
etc
is this possible? I know I can do "counts" ... but it seems hard
because I need to split the results return by the month it occurs in.
Any help you can give would be greatly appreciated!!
Thanx
Ryan RittenNot the most efficient way, but...
SELECT YEAR(TransactionDate), MONTH(TransactionDate), COUNT(*)
FROM TransactionTable
-- if you want to limit to specific year:
-- WHERE TransactionDate >= '20050101' AND TransactionDate < '20060101'
GROUP BY YEAR(TransactionDate), MONTH(TransactionDate)
ORDER BY 1,2
"Sparticus" <sparticusREMOVE@.thesparticusarena.com> wrote in message
news:1129914248.191858.104870@.f14g2000cwb.googlegroups.com...
> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>|||> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
>
select Year(Date) As Year ,month(Date) Month, count(*) as TransCount from
YourTable
group by Year(Date),month(Date)
Francesco Anti|||Sparticus,
This should give you what you want. You'll need to WHERE for a specify year
or year range. If you want month names and/or you want the months with 0 or
as columns instead of rows...let me know.
SELECT DATEPART(MM,TDATE)AS 'MONTH', COUNT(*) 'TRANSACTION COUNT'
FROM #TRANSACTIONS
GROUP BY DATEPART(MM,TDATE)
HTH
Jerry
"Sparticus" <sparticusREMOVE@.thesparticusarena.com> wrote in message
news:1129914248.191858.104870@.f14g2000cwb.googlegroups.com...
> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>|||wow...thanx everyone. Learn somethign new everyday :)|||group your data by datepart(month,datefield),datepart(year,
datefield)
http://sqlservercode.blogspot.com/
"Sparticus" wrote:
> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>|||CREATE TABLE #MyTransaction
(
id int NOT NULL IDENTITY (1, 1),
[date] datetime NULL,
amount money NULL
) ON [PRIMARY]
insert into #MyTransaction
([date], amount)
values
('1-1-05', 100)
insert into #MyTransaction
([date], amount)
values
('1-10-05', 100)
insert into #MyTransaction
([date], amount)
values
('1-20-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-1-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-5-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-7-05', 100)
insert into #MyTransaction
([date], amount)
values
('3-8-05', 10)
select datepart(yy,[date]) as Year, datename(mm,[date]) as month, count(id)
as Count
from #MyTransaction
group by datepart(yy,[date]), datename(mm,[date])
drop table #MyTransaction
"Sparticus" <sparticusREMOVE@.thesparticusarena.com> wrote in message
news:1129914248.191858.104870@.f14g2000cwb.googlegroups.com...
> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>
Saturday, February 25, 2012
Newbie - sql server 2000 transaction log size limit
Has anybody encountered a physical size limit for a sql server 2000 transaction log running on win2k?
Transaction log reached ~6Gb before rolling back the delete stating transaction log was full. There was 42Gb free on the server and the log was set to unlimited growth.Originally posted by axis
Has anybody encountered a physical size limit for a sql server 2000 transaction log running on win2k?
Transaction log reached ~6Gb before rolling back the delete stating transaction log was full. There was 42Gb free on the server and the log was set to unlimited growth.
When the size is set to unlimited I think the limit is as big the win2k can handle?
I had once a log file of 15 Gb big|||usually helps if you make sure the path to the transaction log actualy is where you think it is. Created an extra log on the drive that did have 42gb and all is well.
thanks|||usually helps if you make sure the path to the transaction log actualy is where you think it is. Created an extra log on the drive that did have 42gb and all is well.
thanks
:D Thats true !!! sp_helpdb dbname always helps ...
Transaction log reached ~6Gb before rolling back the delete stating transaction log was full. There was 42Gb free on the server and the log was set to unlimited growth.Originally posted by axis
Has anybody encountered a physical size limit for a sql server 2000 transaction log running on win2k?
Transaction log reached ~6Gb before rolling back the delete stating transaction log was full. There was 42Gb free on the server and the log was set to unlimited growth.
When the size is set to unlimited I think the limit is as big the win2k can handle?
I had once a log file of 15 Gb big|||usually helps if you make sure the path to the transaction log actualy is where you think it is. Created an extra log on the drive that did have 42gb and all is well.
thanks|||usually helps if you make sure the path to the transaction log actualy is where you think it is. Created an extra log on the drive that did have 42gb and all is well.
thanks
:D Thats true !!! sp_helpdb dbname always helps ...
Subscribe to:
Posts (Atom)